srvctl command job aid - I

In this article, it records some command prompts under 'srvctl' to manipulate the instances/databases under the GRID infrastructure environment. The example provided is based on a Oracle 11g Virtual Database environment. In this machine, we install the Oracle binary of 11.2.0.3, and CRS binary of 11.2.0.3.
(Note: The Grid Infrastrcture will be discussed in more detail in the lessons of Grid Infracsture of Oracle 11g.)

In this case, we are going to use srvctl to shutdown and restart the instance. In the VDB environment we have, we have only one instance for this database. So, it's pretty similar as the normal standalone database. But we treat it as the cluster database environment, so we utilize the srvctl to conduct the task instead of sqlplus.

Ex: Shutdown the database instance and reopen it to the archivelog mode.
1. Check the dbconsole is up or not. if it's up, then shutdown the dbconsole first.

> emctl status dbconsoleOracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
https://Hostname:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /opt/oracle/product/11.2.0.3/hostname_dbname/sysman/log
> emctl stop dbconsoleOracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
https://hostname:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...
 ...  Stopped.

 2. Now you can either shutdown the listener before shutdown the database or you can simply issue the shutdown database via srvctl. srvctl will shutdown the listener if it's up before shutting the database. In this exmaple, we choose to shutdown the listener separately.

 > srvctl status listener   --> Check the listener status
Listener LISTENER_NAME is enabled
Listener LISTENER_NAME is running on node(s): nodename
> srvctl stop listner   --> shutdown the listener
> srvctl status listener
Listener LISTENER_NAME is enabled
Listener LISTENER_NAME is not running
>

3. Nowwe stop the instance/database
> srvctl stop database -d database_name  
--> we need to specify the option -d with the database name to shutdown
> srvctl status database -d database_nameDatabase is not running.
So, we are sure the database has been shutdown successfully now.

4. Bring back up the instance/database in archivelog mode.
    (note: in our example the database was in 'noarchivelog' mode before we shut it down)
> srvctl start database -d database_name -o mount 
--> we startup the database in 'mout' mode. Notice that the option of '-o' is where you can specify what mode your want to bring your instance to.
5. Open another session and login to the instance through sqlplus. And issue the 'alter database archivelog' command to turn the archivelog mode on.
> sqlplus
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 22 21:26:25 2011
Copyright (c) 1982, 2011, Oracle.  All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning and Automatic Storage Management options
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> exit

(5b. Turn on the database through srvctl, if no need to alter database archivelog)
> srvctl start database -d database_name -o open
--> the '-s' option is to specify the start option. In this case, we want to turn the database to be open, and which is the default, other options includes 'mount', 'nomoun'.
One thing to notice is that, once you use srvctl to start the database in 'nomount', 'mount' mode, you cannnot alter that database through srvctl directly to the 'open' mode. You need to shutdown the database first and then use 'srvctl start database -d database_name -o open' to bring it back on.

6. Check the database status and make sure listner is running.
> srvctl status database -d database_name
Database is running.
> srvctl status listener
Listener LISTENER_NAME is enabled
Listener LISTENER_NAME is running on node(s): node_name
8. Turn on the dbconsole
> emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.3.0
Copyright (c) 1996, 2011 Oracle Corporation.  All rights reserved.
https://hostname:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control .... started.
------------------------------------------------------------------
Logs are generated in directory /opt/oracle/product/11.2.0.3/hostname_dbname/sysman/log
The srvctl also has the -h option to help you find out the option available to use, which is a pretty handy tool to help you utilizing the srvctl.

ex:
> srvctl status listener -h
Displays the current state of the listener.
Usage: srvctl status listener [-l <lsnr_name>] [-v]
    -l <lsnr_name>           Listener name
    -v                       Verbose output
    -h                       Print usage

Sar command in Linux, retrieving cpu, memory information

'sar' command in linux is a command to return CPU usage and memory usage information.

You can issue repeated option to keep it running returning the stats with specified times.

Ex: >sar 5 150

this will return the result every 5 seconds for 150 times.

some example out put is below:

21:00:56          CPU     %user     %nice   %system   %iowait    %steal     %idle
21:01:11          all         2.21        0.00      0.85          6.13          0.00         90.81
21:01:26          all         3.71        0.00      1.35          3.03          0.00         91.91
21:01:41          all         0.93        0.00      0.68          4.33          0.00         94.05
21:01:56          all         0.88        0.00      0.77          4.41          0.00         93.94
21:02:11          all         0.83        0.00      0.63          4.15          0.00         94.39
Average:          all       15.29        0.00      4.77        32.46          0.00         47.48

Here is what you can do to extract specific time interval from sar data file:

Sar puts its data in daily logs in /var/log/sa. So for instance, if you want to extract from /var/log/sa/sa16 file, use the –f option and then –s for start and –e for end in hh:mm:ss format:

> sar -f sa16 -s 19:00:01  -e 20:27:01

It will display to standard out and the last line is always the Average. If you need to span across two files, you can either append one to the other making a new file (watch for any extraneous lines for averages), or you can get data from each file and average them together. Hope this helps.

Oracle Fundamental I - Chapter 9: Storage Structure and Relationships

In this chapter, we are discussing the logical structure of the database, and segment types for their uses. The storage management is one important factor for DBA to manage the database and administrate the space usage in a efficient way.

The logical storage unit of Oracle database is like below
Tablespaces --> Segments --> Extents --> Blocks

Segments:
Table segment: A table is the most common means of storing data within a database. A table segment stores that data for a table that is neither clustered  nor partitioned. Data within a table segment is sotred in no particular order, and the DBA has very little control over the location of rows within the lbocks in a table. All the data in a table segment must be stored in one tablespace.

Table Partition segment: Scalability and availability are major concerns when there is a table in a database with high concurrent usage. In such cases, data within a table may be stored in several partitions, each of which resides in a different tablespace. The Oracle server currently supports partitioning by a range of key values, by a hashing algorithm, and by a lit of values. If a table is partitioned, each partition is a segment, and the storage parameters can be specified to control them independently. Using this type of segment requires the partitioning option within the Oracle enterprise edition.

Cluster segment: A cluster, like a table, is a type of data segment. Rows in a cluster are stored based on key column values. A cluster may contain one or more tables. TAbles in a cluster belong to the same segment and share the same storage characteristics. The rows in a clustered table can be accessed with an index or hashing algorithm.

Index segment: All the entries for a particular index are stored within one index segment. If a table has three indexes, three index segments are used. the purpose of this segment is to look up the location of rows in a table cased on a specified key.

Index-Organized Table segment: In an index-organied table, data is stored within the index based on the key value. An IOT does not need a table lookup, because all the data can be retrieved directly from the index tree.

Index-Partition segment: An index can be partitioned and spread across several tablespace. In this case, each partition in the index corresponds to a segment and cannot span multiple tablespaces. The primary use of a partitioned index is to minimize contention by spreading index input/output (I/O). Use of this type of segment requires the partitioning option within the Oracle9i Enterprise Edition.

Undo segment: An undo segment is used by a transaction that is making changes to a database. Before changing the data or index blocks, the old value is tored in the undo segment. This allows a user to undo changes made.

Temporary segment:
When a user executes commands such as CREATE INDEX, SELECT DISTINCT, and SELECT GROUP BY, the Oracle server tries to perform sorts in memory. When a sort needs more space than the space available in memory, intermecdiate results are written to the disk. Temporary segments are used to streo these intermediate results.

LOB segment:
One or more columns in a table can be used to store large objects (LOBs) such as text documents, images, or videos. If the column is large, the Oracle server stores these values in separate segmentsknown as LOB segments. The table contains only a locator or a pointer to the location of the corresponding LOB data.

Nested Table segment:
A column in a table may be made up of a user-defined table as in the case of items within an order. In such cases, the inner table, which is known as a nested table, is stored as a separate segment.

Bootstrap segment:
A bootstrap segment, also known as a cache segment, is created by the sql.bsq script when a database is created. This segment helps to initialize the data dictionary cache when the database isopened by an insance. The bootstrap segment cannot be queried or updated and does not require any maintenance by the database administrator.

A storage clause can be specified at the segment level to control how extents are allocated to a segment.

• Any storage parameter specified at the segment level overrides the corresponding option set at the tablespace level, except for the MINIMUM EXTENT or UNIFORM SIZE tablespace parameter.
• When storage parameters are not set explicitly at the segment level, they default to those at the tablespace level.
• When storage parameters are not set explicitly at the tablespace level, the Oracle server system defaults are used.
• If storage parameters are altered, the new options apply only to the extents not yet allocated.

Extent Allocation and Deallocation

• An extent is a chunk of space used by a segment within a tablespace
• An extent is allocated when the segment is:
• Created
• Exteneded
• Altered
• An extent is deallocated when the segment is:
• Dropped
• Altered
• Truncated

Used and Free Exxtents
When a tablespace is created, the data files in the tablespace contains a header, which is the first block or blocks in the file.
As segments are created, they are allocated space from the free extents in a tablespace. Continuous space used by a segment is referred to as a used extent. When segments release space, the extents that are released are added to the pool of free extents available in the tablespace.


Database Block

• Minimum unit of I/O
• Consists of one or more operating system blocks. 
• Set at tablespace creation
• DB_BLOCK_SIZE is the default block size 

Since Oracle 9i, Oracle support multiple block sizes.

• A database can be created with a standard block size and up to four nonstandard block sizes.
• Block sies can have any power-of-two value between 2KB and 32KB.
• Current block sizes supported includes 2, 4, 8, 16, 32KB.
• The block size of the SYSTEM tablespace is termed the standard block size.

Standard Block Size

• Set at database creation using the DB_BLOCK_SIZE parameter: cannot be changed without recreating the database.
• Used for SYSTEM and TEMPORARY TABLESPACES
• db_CACHE_SIZE specifies the size of the DEFAULT buffer cache for standard block size:
• Minimum size = one granule (4MB or 16 MB)
• Default value = 48MB

Nonstandard Block Size

• Configure additional caches with the following dynamic parameters:
• DB_2K_CACHE_SIZE for 2KB blocks
• DB_4K_CACHE_SIZE for 4KB blocks
• DB_8K_CACHE_SIZE for 8KB blocks
• DB_16K_CACHE_SIZE for 16KB blocks
• DB_32K_CACHE_SIZE for 32KB blocks
• DB_nK_CACHE_SIZE is not allowed if nK is the standard block sie
• Minimum size for each cache isone granule

Creating Non-standard block size tablespace:
Ex:

CREATE TABLESPACE data01 DATAFILE ‘/oradata/u01/data01.dbf’ SIZE 10m BLOCKSIZE 4K;

Some rules of multiple block sizing:

• All partitions of a partitioned object must reside in tablespaces of the same block size.
• All temporary tablespaces, including the permanent ones that are being used as default temporary tablespaces, must be of standard block size.
• Index-organized table overflow and out-of-line LOB segments can be stored in a tablespace with a block size different from teh base table.

• Block header: The header contains the data block address, table directory, row directory, and transaction slots that are used when transactions make changes to rows in the block. Block headers grow from the top down.
• Data space: Row data is inserted into the block from the bottom up.
• Free space: The free space in a block is in the middle of the block. Thus both the deader and the row data space can grow when necessary. The free space in a block is contiguousinitially. However, deletions and upddates may fragment the free space in the block. The free space in the block is coalesced by the Oracle server when necessary.

Block space utilization paramters:
Some space utilization parameters like INITRANS, MAXTRANS, PCTFREE and PCTUSED.

INITRANS and MAXTRANS: Specify the initial and the maximum number of transaction slots that are created in an index or a data block.

PCTFREE: Specify for a data segment, the percentage of space in each data block that is reserved for growth resulting from updates to rows in the block. The default for PCTFREE is 10%

PCTUSED: For a data segment, this parameter represents the minimum percentage of used space that the Oracle server tries to maintain for each data block of the table. The default is $40%.


Data Block management:
There are two ways to managing data blocks:
Automatic segment-space management

• It is a method of managing free space inside database segments
• Tracking in-segment free and used space is done using bitmaps as opposed to using free lists
• This method provides:
• Ease of management
• Better space utiliation
• Better performance for concurrent INSERT operations
• Bitmap segments contain a bitmap that describes the status of each blocks in the segment with respect to its available space.
• The map is contained in a separate set of blocks referred to as bitmapped blocks (BMBs).
• When inserting a new row, the server searches the map for a block with sufficient space.
• As the amount of space available in a block changes, its new state is reflected in the bitmap.
• Automatic segment-space management can be enabled at the tablespace level only, for locally managed tablespaces

EX:

CREATE TABLESPACE data01 DATAFILE ‘/oradata/u01/data01.dbf’ SIZE 10M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 64K SEGMENT SPACE MANAGEMENT AUTO;

Manual Data Block management:

• Allows you to configure data blocks manually using parameters such as:
• PCTFREE
• PCTUSED
• FREELIST

The example makes it easier to understand how PCTFREE and PCTUSED to work:
PCTFREE=20%, PCTUSED=40%
1. Rows are inserted into block until the free space in the block is equal to or less than 20%. The block is no longer available for inserts when rows occupy 80% (100 - PCTFREE) or more of the available data space in the block.
2. The remaining 20% can be used when the size of a row increases. For example, a column that was originally NULL is updated to be assigned a value. Thus block utiliation may be in excess of 80% as a result of updates.
3. If rows are deleted in the block or if rows decrease in sie as a result of udpates, block utilization may fall below 80%. HOwever, a block is not used for inserts until the utilization falls below PCTUSED, which in this example, is 40%
4. When the utiliation falls below PCTUSED, the block is available for inserts. As rows are inserted into the bloc, the utiliation of the block increases and the cycle repeats starting with step 1.

Some views are very useful for obtaining storage inforamtion:

• DBA_EXTENTS
• DBA_SEGMENETS
• DBATABLESPACES
• DBA_DATA_FILES
• DBA_FREE_SPACE

Ex: DBA_SEGMENTS
SQL> select segment_name, tablespace_name, extents, blocks from dba_segments;
SEGMENT_NAME   TABLESPACE_NAME      EXTENTS   BLOCKS
------------------------ ------------------------------- -------------  ----------
REGIONS                  SAMPLE                              79               8192
LOCATIONS             SAMPLE                              79               8192
JOBS                          SAMPLE                              79               8192