Oracle Database Administration

RMAN: Archivelogs lost

2011-02-10T17:12:00.002+05:30

Problem: I have lost some of the archivelog files without taking backup. If I run the rman to backup available archive logs, it throws error that the archivelog_seq# is not available.

Solution: run the following command.

RMAN> change archivelog all validate;

Now you run the backup archivelog command. RMAN will backup the available archivelogs successfully.

Thanks

Drop Database in Oracle 10g

2010-07-17T18:29:00.002+05:30

It has become easier to drop a database from Oracle 10g. Use the DROP DATABASE command to drop the database. It removes the datafiles, redologs, controlfiles and init parameter files.

$ sqlplus / as sysdba
SQL> Shutdown immediate;
SQL> Startup Mount Exclusive Restrict;
SQL> Drop Database;

Thanks

Block Change Tracking

2010-03-01T15:08:00.020+05:30

RMAN Incremental Backups backup only the blocks that were changed since the lastest base incremental backups. But RMAN had to scan the whole database to find the changed blocks. Hence the incremental backups read the whole database and writes only the changed blocks. Thus the incremental backups saves space but the reduction in the time is fairly neglegible.

Block Change Tracking (BCT) is a new feature in Oracle 10g. BCT enables RMAN to read only the blocks that were changed since the lastest base incremental backups. Hence by enabling BCT, RMAN reads only the changed blocks and writes only the changed blocks.

Without BCT, RMAN has to read every block in the database and compare the SCN in the block with the SCN in the base backup. If the block's SCN is greater than the SCN in the base backup then the block is a candidate for the new incremental backup. Usually only few blocks are changed between backups and the RMAN has to do unncessary work of reading the whole database.

BCT Stores the information about the blocks being changed inthe BlockChange Tracking File. The background process that does this logging is Change Tracking Writer (CWTR).

BlockChange Tracking File

BCT File is one per database and in the case RAC, it is shared among all the instances. BCT File is created in the location defined by the parameter DB_CREATE_FILE_DEST as OMF file.

To enable BCT

SQL> Alter Database Enable Block Change Tracking;

To disable BCT

SQL> Alter Database Disable Block Change Tracking;

To specify the BCT file location

SQL> Alter Database enable Block Change Tracking using File '/Backup/BCT/bct.ora';

A useful query,

SQL> Select Completion_time, datafile_blocks, blocks_read, blocks, used_change_tracking
From v$backup_datafile
where to_char(completion_time, 'dd/mon/yy') = to_char(sysdate, 'dd/mon/yy');

Where,
datafile_blocks is the total number of blocks in the datafile.
blocks_read is the total number of blocks read by RMAN
blocks is the total number of blocks backed up by the RMAN.
used_change_tracking if yes BCT is used, if no BCT is not used.

Thanks

RMAN Commands

2010-02-25T01:14:00.001+05:30

The following are the commands you can type from the RMAN prompt.

{
allocate
alter
backup
beginline
blockrecover
catalog
change
connect
copy
create
crosscheck
configure
duplicate
debug
delete
drop
exit
endinline
host
library
list
mount
open
print
quit
recover
register
release
replace
report
renormalize
reset
restore
resync
rman
run
rpctest
set
setlimit
sql
spool
startup
shutdown
send
show
test
upgrade
validate

Thanks

RMAN List Command

2010-02-25T00:43:00.021+05:30

RMAN List Command queries the target control file or the recovery catalog for the historical information on the backups. The various options you can use with List command and their uses are discussed below,

Listing Incarnations

A new incarnation of the database is created when you perform the open resetlogs operation. To view the list of database incarnations,

RMAN> List incarnation;

List Summary of Backups

The summary of backups include backupset key, the status, device type, completion time etc,

RMAN> List Backup Summary;
RMAN> List expired Backup of archivelog all summary;
RMAN> List Backup of tablespace Test summary;

List Backups of various files

It provides the summary of the backups available for each datafile, controlfile, archivelog file and spfile.

RMAN> List Backup By File;

Detailed Report

If you want the detailed report on the backups, then issue the following command.

RMAN> List Backup;

It lists the all available information about the backups.

Backups used for Recovery

To list the backups used for restore and recovery,

RMAN> list recoverable backup;

Expired Backups

The list backup shows both available and expired backups. To view only the expired backups,

RMAN> List expired Backup;
RMAN> List expired Backup summary;
RMAN> List expired Backup of Archivelog all;
RMAN> List expired Backup of datafile 10;

Listing Tablespace and Datafile Backups

RMAN> List Backup of Tablespace Test;
RMAN> List Backup of Datafile 4;

Listing Archivelog Backups

RMAN> List Archivelog all;
RMAN> List Archivelog all backedup 2 times to device type sbt;

Listing Controlfile and Spfile Backups

RMAN> List Backup of Controlfile;
RMAN> List Backup of Spfile;

The above list commands displayed information about the backusets. If you have performed Image copy backups then you must use the list copy command as shown below,

RMAN> List Copy;
RMAN> List Copy of database;
RMAN> List Copy of tablespace test;
RMAN> List Copy of archivelog all;
RMAN> List Copy of archivelog from sequence 12345;
RMAN> List Copy of archivelog from sequence 1000 until sequence 1010;
RMAN> List Copy of Controlfile;
RMAN> List Copy of Spfile;

Thanks

Connecting to RMAN

2010-01-20T03:16:00.008+05:30

Step 1: Set the following environment variables

ORACLE_SID
ORACLE_HOME
NLS_DATE_FORMAT

Step 2: Access to privileged OS account or user with Sysdba privilege. Only users with Sysdba privilege can connect to RMAN. Hence no need to specify as sysdba while connecting to RMAN.

$ cd $ORACLE_HOME/bin
$ rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Wed Jan 20 03:19:22 2010
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: IRIS (DBID=1460454352)

If you are using password file, then specify the username and password

$ rman target sys/syspassword

If you are accessing the target database via oracle net then use connection string as follows

$ rman target sys/syspassword@

You can also connect to RMAN in two steps first by invoking the rman

$ rman

The RMAN prompt appears

RMAN>

To connect to the target datbase..

RMAN> Connect target /
RMAN> Connect target sys/syspassword
RMAN> Connect target sys/syspassword@

Once you have connected to RMAN, you can start entering the commands. The commands can be typed in single line or in multiple lines and ends with a semicolon.

RMAN> Backup
2 database;

To add comments use the # symbol. Text after the # symbol in a line is interpreted as comments

RMAN> # Backup database
Backup database # this command will backup the database
include current controlfile; # backup control file along with database

Thanks

Remote_login_passwordfile

2010-01-20T02:15:00.002+05:30

The password file is used to authenticate the users connecting to the database as Sysdba or Sysoper. The password file is created with the orapwd utility as follows,

$ cd $ORACLE_HOME/dbs
$ orapwd file=orapw password= entries=

Where,
file = the filename where the password is stored.
password = the password for sys user
entries = the number of users that can connect as sys user at a time. This parameter is optional.

Once the password file is created you need to set the init parameter remote_login_passwordfile for the database to use it.

The possible values for the parameter is

EXCLUSIVE - Only one database can use the password file. Multiple users can exist in the password file.

SHARED - More than one database can use the password file. However only Sys users allowed to have sysdba privilege. You will receive ORA-01999 error when you grant sysdba privilege to other users.

NONE - The password file is not used.

Thanks

ASM Views

2010-01-08T18:51:00.005+05:30

The following V$views are used for managing ASM.

View Name	Description	Instance
V$ASM_DISKGROUP	Diskgroups discovered by ASM	DB
V$ASM_DISK	Disks discovered by ASM including disks that are not part of any Diskgroup.	DB
V$ASM_FILE	ASM files mounted by ASM instance	ASM
V$ASM_ALIAS	Filename alias of the ASM Files	ASM
V$ASM_OPERATION	Long running ASM operations	ASM
V$ASM_TEMPLATE	Template present in diskgroup	DB
V$ASM_CLIENT	Lists one row per each ASM instance for which the database instance has any open ASM files	DB

Thanks

Drop a file from diskgroup

2010-01-08T18:48:00.001+05:30

Syntax

ALTER DISKGROUP diskgroup_name DROP FILE ‘filename’, ‘filename’..;

Note: ASM drops all the aliases associated with the filename.

Example 1

SQL> Alter Diskgroup ASMDATA DROP FILE ‘+ASMDB/gcprod/datafile/users.259.661355087’ , ‘+ASMDB/gcprod/datafile/sysaux.257.661355085’;

Thanks

Manage ASM Directories

2010-01-05T03:11:00.000+05:30

In order to use ASM filename aliases, you need to create Directories.

Syntax:

To add directory
ALTER DISKGROUP diskgroup_name ADD DIRECTORY ‘filename’, ‘filename’..;

To rename directory
ALTER DISKGROUP diskgroup_name RENAME DIRECTORY ‘old_dirname’ TO ‘new_dirname’, ‘old_dirname’ TO ‘new_dirname’..;

To drop directory
ALTER DISKGROUP diskgroup_name DROP DIRECTORY ‘filename’ [FORCE or NOFORCE], ‘filename’ [FORCE or NOFORCE]..;

Where,

FORCE – To drop the directory even if it contains any alias definitions.
NOFORCE – It will not drop the directory if it contains any alias definitions

Example 1 (Add directory)

SQL> Alter Diskgroup asmdata ADD DIRECTORY ‘+ASMDATA/datafile’;

Example 2 (Rename directory)

SQL> Alter Diskgroup asmdata RENAME DIRECTORY ‘+ASMDATA/datafile’ TO ‘+ASMDATA/indexfile’;

EXAMPLE 3 (Drop directory)

SQL> Alter Diskgroup asmdata DROP DIRECTORY ‘+ASMDATA/indexfile’ FORCE;

Thanks

Mounting and Dismounting a Diskgroup

2010-01-05T03:09:00.001+05:30

Diskgroups are automatically mounted at ASM instance startup and dismounted at ASM instance shutdown. To manually mount and dismount the diskgroups use the following statements,

Syntax:

ALTER DISKGROUP ALL or diskgroup_name MOUNT or DISMOUNT;

Example 1 (Mount a specific diskgroup)

SQL> Alter Diskgroup ASMIND Mount;

Example 2 (Dismount all diskgroups)

SQL> Alter Disgroup ALL DisMount;

Note

In the case of High and Normal Redundancy diskgroups you need all set of extents available for the diskgroups to mount. The unavailable disks may be used for redundant copies and you may mount the diskgroups without those disks. If there are sufficient disks available for the diskgroups to mount you can use the force option to mount it.

Alter Diskgroup diskgroup_name Mount Force;

The unavailable disks are taken offline. ASM drops those disks after the DISK_REPAIR_TIME expires.

In RAC ASM environments, you cannot MOUNT FORCE a disk group that is already mounted by another instance because the disks have been accessed by another instance and the disks are not locally accessible.

Thanks

Checking a Diskgroup

2010-01-05T03:07:00.000+05:30

It checks the internal consistency of the diskgroup metadata. If any errors are found they are reported in the alert log.

Instance: ASM Instance
Privilege: Sysdba

Syntax

ALTER DISKGROUP diskgroup_name CHECK check_diskgroup_clause [REPAIR or NOREPAIR]

Where,

Check_dikgroup_clause= [ALL] or [DISK diskname] or [DISKS IN FAILGROUP failgroup_name] [FILE filename]

REPAIR = ASM attempts to repair any errors.
NOREPAIR= ASM reports the error in the alert log and does not attempt to repair it.

Example 1 (checks all the disks in the diskgroup)

SQL> Alter Diskgroup ASMDATA CHECK ALL;

Example 2 (checks only specific disks in the diskgroup)

SQL> Alter Diskgroup ASMIND CHECK
DISK data_05,
DISK data_06 NOREPAIR;

Example 3 (Checks all the disks in the failure group).

SQL> Alter Diskgroup ASMDATA CHECK
DISKS IN FAILGROUP ctrl_1;

Example 4 (checks specific files in the diskgroup)

SQL> Alter Diskgroup ASMIND CHECK
FILE ‘+ASMDB/gcprod/datafile/users.259.661355087’,
FILE ‘+ASMDB/gcprod/datafile/sysaux.257.661355085’;

Thanks

Rebalance a Diskgroup

2010-01-05T03:06:00.000+05:30

Instance: ASM Instance
Privilege: Sysdba

Syntax

ALTER DISKGROUP diskgroup_name rebalance_diskgroup_clause

Where,

Rebalance_diskgroup_clause = REBALANCE POWER integer WAIT/NOWAIT
Integer – 0 to 11. If you omit ASM takes the value from ASM_POWER_LIMIT init parameter.
WAIT – The control is not returned to the user until the rebalance operation completes.
NOWAIT – The control immediately returns to the user and the rebalance operation takes place at the background and user can continue to do other operation.

Example 1

SQL> Alter Diskgroup ASMDATA REBALANCE POWER 10 wait;

Thanks

Resize a Disk

2010-01-05T03:05:00.000+05:30

Note: Resizing disks in a diskgroup invokes implicit rebalancing of all the disks within the diskgroup.

Instance: ASM Instance
Privilege: Sysdba

Syntax

ALTER DISKGROUP diskgroup_name RESIZE resize_disk_clause rebalance_diskgroup_clause

Where

resize_disk_clause =[ALL SIZE size_clause] or [DISK diskname SIZE size_clause] or [DISKS IN FAILGROUP failgroup_name SIZE size_clause]

Note: If you omit the size clause then ASM uses the size returned by the OS.

Rebalance_diskgroup_clause = REBALANCE POWER integer WAIT/NOWAIT
Integer – 0 to 11. If you omit ASM takes the value from ASM_POWER_LIMIT init parameter.
WAIT – The control is not returned to the user until the rebalance operation completes.
NOWAIT – The control immediately returns to the user and the rebalance operation takes place at the background and user can continue to do other operation.

Example 1 (resizes all the disks in the diskgroup)

SQL> Alter Diskgroup ASMDATA RESIZE ALL SIZE 100G;

Example 2 (resizes only specific disks in the diskgroup)

SQL> Alter Diskgroup ASMIND RESIZE
DISK data_05 SIZE 100G,
DISK data_06
REBALANCE POWER 5;

Example 3 (resizes only specific disks in the failure group).

SQL> Alter Diskgroup ASMDATA RESIZE
DISKS IN FAILGROUP ctrl_1 SIZE 100G;

Thanks

Undrop Disk from Diskgroup

2010-01-05T03:03:00.001+05:30

Note: To undrop a disk, the header_status from v$asm_disk view must be dropping. You cannot undrop a completely dropped disk.

Instance: ASM Instance
Privilege: Sysdba

Syntax

ALTER DISKGROUP [ALL/diskgroupname, diskgroupname..] UNDROP DISKS

Example 1

SQL> Alter Diskgroup asmdata undrop disks;

Example 2

SQL> Alter Diskgroup ALL undrop disks;

Thanks

Add Disk to Diskgroup

2009-12-19T17:41:00.001+05:30

Note: Adding new disks to diskgroup invokes implicit rebalancing of all the disks within the diskgroup.

Instance: ASM Instance
Privilege: Sysdba

Syntax

ALTER DISKGROUP diskgroup_name ADD [FAILGROUP failgroup_name] DISK add_disk_clause rebalance_diskgroup_clause

Where

Add_disk_clause = search_string [NAME disk_name] [SIZE size_clause] [FORCE/NOFORCE]
Force – Add disk to the diskgroup even if the disk is part of some other diskgroup.
NoForce – ASM returns error if the disk is part of some other diskgroup.

Rebalance_diskgroup_clause = REBALANCE POWER integer WAIT/NOWAIT
Integer – 0 to 11. If you omit ASM takes the value from ASM_POWER_LIMIT init parameter.
WAIT – The control is not returned to the user until the rebalance operation completes.
NOWAIT – The control immediately returns to the user and the rebalance operation takes place at the background and user can continue to do other operation.

Example 1

SQL> Alter Diskgroup ASMDATA ADD
FAILGROUP ctrl_1
DISK
'/u01/asmdisks/disk1' NAME data_05 NOFORCE,
'/u01/asmdisks/disk2' NAME data_06 NOFORCE;

Example 2

SQL> Alter Diskgroup ASMIND ADD
DISK '/dev/rhdisk33'
REBALANCE POWER 5 ;

Thanks

Dropping a Diskgroup in ASM

2009-12-19T16:15:00.000+05:30

Instance: ASM Instance
Privilege: Sysdba
Syntax:

DROP DISKGROUP diskgroup_name INCLUDING/EXCLUDING CONTENTS;

Including Contents – Drops all the files associated with the disk group.
Excluding Contents – Drops the disk group only when it is empty. Default.

Example 1

SQL> Drop Diskgroup ASMDATA including contents;

Example 2

SQL> Drop Diskgroup ASMIND;

Thanks

Create a new Diskgroup in ASM

2009-12-19T15:44:00.005+05:30

Instance: ASM Instance.
Privilege: Sysdba
Pre-requisite: Disks must be formatted. The path of the disk must be in the ASM_DISKSTRING. Oracle DB user must have read/write permission on the disk.

Syntax:

CREATE DISKGROUP diskgroup_name redundancy_level REDUNDANCY [FAILGROUP failgroup_name]DISK disk_clause

Where,
redundancy_level = HIGH or NORMAL or EXTERNAL
disk_clause = search_string [NAME disk_name] [SIZE size_clause] [FORCE/NOFORCE]

Force – Add disk to the diskgroup even if the disk is part of some other diskgroup.
NoForce – ASM returns error if the disk is part of some other diskgroup.

Example 1

CREATE DISKGROUP asm_data
NORMAL REDUNDANCY
FAILGROUP ctrl_1
DISK
'/u01/asmdisks/disk1' NAME data_01 NOFORCE
,'/u01/asmdisks/disk2' NAME data_02 NOFORCE
FAILGROUP ctrl_2
DISK
'/u03/asmdisks/disk3' NAME data_3 NOFORCE
,'/u03/asmdisks/disk4' NAME data_4 NOFORCE;

If the size clause is omitted the full disk is assigned.

Example 2

SQL> CREATE DISKGROUP ASMIND External REDUNDANCY DISK
'/dev/rhdisk6' SIZE 51200M ,
'/dev/rhdisk7' SIZE 51200M ,
'/dev/rhdisk8' SIZE 51200M ;

If the name clause is omitted the disks are given a system generated name.

Thanks

Install Oracle 10g on Windows XP - Part 3

2009-12-10T16:06:00.006+05:30

In the last part2 we discussed ASM installation. In this part we will see the RDBMS installation.

Step 1: Invoke the OUI. Select the Advanced installation option and then click Next.

Step 2: Select the installation type for eg., Enterprise, standard edition etc.

Step 3: Specify the Oracle Home details. Choose a different path from ASM home.

Step 4: In the next page select the option to create the database.

Step 5: Specify a Database Name. Choose the character set.

Step 6: Select Database control for Database Management.

Step 7: In the select database storage option page, select ASM.

Step 8: In the next page click the option that best matches your requirement.

Step 9: Select the ASM Disk Group.

If the space available is not enough it will show the following screen.

Select the disks so that the required space is available. Click Next

Step 10: Click Install.

Click Exit.

The oracle RDBMS is installed.

Click here for Part 2

Click here for Part 1

Thanks

Install Oracle 10g on Windows XP - Part 2

2009-12-10T11:20:00.011+05:30

In this part we will see the ASM installation and ASM instance Creation.

Step 1: Invoke the Oracle Universal Installer. In the Welcome Screen select Advanced Installation and click Next button.

Step 2: In the Select Installation Type, select the appropriate choice. In our case we select Standard Edition.

Step 3: Specify the ASM Home details.

Click Next.

Step 4: Check for any errors and correct it if any. Click Next.

Step 5: Choose the Configure Automatic Storage Management option and specify the SYS password. Click Next.

Step 6: In the next screen do the following,

Specify the Disk Group name.
Choose the redundancy type.
Select the candidate disks you want to assign for this disk group.

Note: You must have stamped the disks before to make Oracle recognize the disks. If you have not stamped the disks already, click the stamp disks button to invoke the asmtool utility. You can then stamp the disks as shown in the Stamp disks section in the part 1.

Click Next

Step 7: Click Install.

Click the Exit button. The ASM installation is completed.

Note: You can use the DBCA to create addition disk groups.

Click Here for Part 1

Click Here for Part 3

Thanks

Install Oracle 10g on Windows XP using ASM - Part 1

2009-12-09T17:06:00.012+05:30

Hardware Requirements

RAM - 256MB Minimum. 512 MB Recommended

Virtual Memory - 512 MB to 1 GB

Disk Space - 2.5 GB Minimum

Software Requirements

System Architecture - Processor: Intel X86, AMD 64 and Intel EM64T

Operating System - Windows XP Professional, Windows 2000

Install Loopback Adapter

If you are installing Oracle Database on a DHCP computer or on a Non-Networked Computer, then you may want to install Loopback Adapter. The Loopback adapter and the local IP address simulate a networked computer. If the Computer is connected to a network then this step may be ignored.

Click Here for the steps to install loopback adapter.

Preparing Diskgroups for Automatic Storage Management

Step 1: Create Disk Partitions

For this sample installation I use only one Disk and create logical partitions on it. For production implementation create a primary partition on each disk.

To create partitions,

C:\> Diskpart

DISKPART> list disk

DISKPART> Select disk 0

To create a logical partition, the size is in MB.

DISKPART> Create partition logical size=1024

To create a primary partition , the size is in MB

DISKPART> Create partition primary size=1024

To view the partitions created

DISKPART> List partition

Repeat for other disks

The created volumes must be unformatted, that is, the Fs column must be blank in the following command. In the following output NTFS indicates NT File system and hence cannot be used as ASM disk.

Stamp ASM Disks

To use ASM, the disks must be stamped with a header. Use ASMTOOL to stamp each partition with an ASM label so that oracle can recognize these partitions as candidate disk. Navigate the oracle media to find the asmtool folder. Click the asmtoolg.exe.

Select the Add or change label dialog and press Next button.

Select the Candidate Disk and assign a asmdisk group name. Click Next.

Click Next.

Click Finish.

Repeat the process for each disk group.

Click Here for Part 2

Click Here for Part 3

Thanks

ASMCMD

2009-11-25T18:12:00.010+05:30

In this post I will provide you with some commonly used ASM commands and how to use it to perform some day to day activities.

Before invoking the ASM command, set the following environmental variables,

$ Export ORACLE_SID=+ASM1
$ Export ORACLE_HOME=/oracle/app/oracle/product/10.2.0.1/db_1

To invoke the ASMCMD

$ asmcmd –p
If the –p option is used, it displays the path in the prompt.

To navigate the directories (same as in Unix) case insensitive

To Create a File alias

$ asmcmd
ASMCMD> cd asmdb/gcprod/datafile
ASMCMD> mkalias +asmdb/gcprod/datafile/USERS.259.661355087 users_01.dbf

To remove a file alias

ASMCMD> rmalias +asmdb/gcprod/datafile/users_01.dbf

To check space usage

du – displays disk usage in MB. Similar to du command in unix.

lsdg – lists the disk group usage.

To find the RDBMS instances connected to ASM
lsct – lists the ASM Client information

To create and delete directories and files

ASMCMD> mkdir dummydir
ASMCMD> rm dummydir
ASMCMD> rm +asmdb/gcprod/datafile/USERS.259.661355087

Note: The file must be closed before deleting it otherwise the rm command will throw error.

Searching through the ASM directories

Thanks

ASMLIB

2009-11-06T17:08:00.001+05:30

ASMLIB is a storage management interface. ASMLIB is not required for ASM to function; it is an add-on library that simplifies the tasks of managing and discovering the disks.

Function of ASMLIB

Disk Discovery: It provides more information about the disk attributes enabling easy discovery of disks. Avoids disks being added to one node and not been discovered by other nodes in the cluster.

IO Processing: Enables more efficient IO.

Thanks

ASM Rebalance

2009-11-06T16:09:00.002+05:30

The Rebalance operation provides an even distribution of file extents across all disks in the diskgroup. The rebalance is done on each file to ensure balanced I/O load.

The RBAL background process manages the rebalance activity. It examines the extent map for each file and redistributes the extents to new storage configuration. The RBAL process will calculate estimation time and the work required to perform the rebalance activity and then message the ARBx processes to actually perform the task. The number of ARBx process starts is determined by the parameter ASM_POWER_LIMIT.

There will be one I/O for each ARBx process at a time. Hence the impact of physical movement of file extents will be low. The asm_power_limit parameter determines the speed of the rebalance activity. It can have values between 0 and 11. If the value is 0 no rebalance occurs. If the value is 11 the rebalance takes place at full speed. The power value can also be set for specific rebalance activity using Alter Diskgroup statement.

The rebalance operation has various states, they are

WAIT: No operations are running for the group.
RUN: A rebalance operation is running for the group.
HALT: The DBA has halted the operation.
ERROR: The operation has halted due to errors.

You can query the V$ASM_OPERATION to view the status of rebalance activity.

The rebalance activity is an asynchronous operation, i.e., the operation runs in the background while the users can perform other tasks. In certain situation you need the rebalance activity to finish successfully before performing the other tasks. To make the operation synchronous you add a keyword WAIT while performing the rebalance as shown below.

SQL> Alter diskgroup ASMDB Add Disk ‘/dev/sdc4’ Rebalance power 4 WAIT;

The above statement will not return the control to the user unless the rebalance operation ends.

Thanks

ASM redundancy and Failure Groups

2009-11-04T20:51:00.001+05:30

A Failure group is a collection of disks that can become unavailable due to failure of one of its associated components (controllers, HBAs, Fiber channel switches, Disks, Entire arrays etc).

A diskgroup is divided in to failure groups. Each disks in the diskgroup is part of one failure group. The disks in different failure groups does not share the same failure component (HBAs, controllers etc).

ASM uses Mirroring for redundancy. Three types of redundancy are supported by ASM. They are,

Normal Redundancy: 2-way mirrored. At least two failure groups are needed.
High Redundancy: 3-way mirrored. At least three failure groups are needed.
External Redundancy: No ASM mirroring. 3rd party redundancy mechanism like RAID is used.

After creating a diskgroup you cannot change the redundancy level. If you want to change it then create a separate diskgroup and move the files to that diskgroup (using RMAN restore or DBMS_FILE_TRANSFER).

ASM mirrors extents instead of disks. The first copy of the extent is called the primary extent and its mirrored extent is called secondary extent. For high redundancy there are two secondary extents. The primary and secondary extents together are known as extent set. An extent set always contains same data. For read/write operations, each extent in an extent set is written in parallel and only primary extent is read.

Note: Disk group meta data is always triple mirrored with normal or high redundancy.

Thanks

ASM Templates

2009-11-03T01:02:00.003+05:30

A template is a collection of file attributes that are applied to the ASM files. It is associated with a Disk Group. Whenever a file is created the file attributes specific to the template are applied to the files. For eg., if we want to create a users tablespace that contains data file we use the DATAFILE template.

Default Template: When you create a disk group, Oracle creates system default template for the disk group. It includes default attributes for various file types like control files or data files. The DBA can mention whether the files created via the template should be 2-way or 3-way mirrored and Coarse or fine striped.

Default templates and their attributes.

Ref: http://www.dbasupport.com/oracle/ora10g/ASM0301.shtml

Some of the attributes of default templates can be modified, but cannot be deleted. The redundancy and striping cannot be modified. You can also create user defined templates.

The drawback of ASM file templates is if you want to modify any of the file attributes after it is created, then you must use RMAN to copy the file to a new file with new settings.

To view the ASM template details use the following query.

SQL> Select * From V$ASM_TEMPLATE;

Thanks

ASM Files

2009-11-03T00:16:00.005+05:30

ASM Files are named using OFA standard and employs OMF for managing files for example when you drop a tablespace ASM will automatically deletes the files associated with the tablespace. Also I need not specify a file name while creating a data file, ASM will automatically assign a unique name.

A typical ASM file looks like this
+ASMDB/gcprod/datafile/users.259.661355087

The ASM file has 5 components, they are

ASM Disk Group : +ASMDB
Database name : gcprod
File type : datafile
Tag : Specific information about the file. In this case it is the tablespace name, users
file.incarnation : A number pair that insure uniqueness

ASM File Alias

Alias are used to represent the files using user friendly names.

To create an alias

SQL> Alter Diskgroup ASMDB add alias '+ASMDB/gcprod/datafile/users1.dbf' for '+ASMDB/gcprod/datafile/users.259.661355087' ;

Instead of using full name you can also use only the disk group name and the numeric identifier as shown below,

SQL> Alter Diskgroup ASMDB add alias '+ASMDB/gcprod/datafile/users1.dbf' for '+ASMDB.259.661355087';

To rename an alias

SQL> Alter Diskgroup ASMDB rename alias '+ASMDB/gcprod/datafile/users1.dbf' to '+ASMDB/gcprod/datafile/users2.dbf';

To delete an alias

SQL> Alter Diskgroup ASMDB delete alias '+ASMDB/gcprod/datafile/users1.dbf';

Note: Once you create an alias the files will not become OMF. Hence the files will not be deleted automatically when the tablespace is dropped. You have to delete the files manually as shown below,

Drop file using alias

SQL> Alter Diskgroup ASMDB drop file '+ASMDB/gcprod/datafile/users1.dbf';

Drop file using numeric name

SQL> Alter Diskgroup ASMDB drop file '+ASMDB.259.661355087';

Drop file using fully qualified name

SQL> Alter Diskgroup ASMDB drop file '+ASMDB/gcprod/datafile/users.259.661355087';

Thanks

ASM Background Processes

2009-11-02T17:46:00.002+05:30

Like normal database instances ASM instance too have the usual background processes like SMON, PMON, DBWr, CKPT and LGWr. In addition to that the ASM instance also have the following background processes,

RABL- Rebalancer: It opens all the device files as part of disk discovery and coordinates the ARB processes for rebalance activity.

ARBx - Actual Rebalancer: They perform the actual rebalancing activities. The number of ARBx processes depends on the ASM_POWER_LIMIT init parameter.

ASMB - ASM Bridge: This process is used to provide information to and from the Cluster Synchronization Service (CSS) used by ASM to manage the disk resources. It is also used to update statistics and provide a heartbeat mechanism.

Thanks

ASM Instance

2009-11-01T02:50:00.003+05:30

ASM instance is a special kind of Oracle Instance which has only SGA and Background process and no Physical Files except parameter file. It manages the disk group activity. Each server has an ASM instance that manages the disk groups for that server. If the server has more than one database a single ASM instance can manage the disk group for all the databases. In an RAC configuration each node has a separate ASM instance that manages disk groups for that node.

ASM breaks the files into multiple extents and spreads the extents across all of the disks available in the disk group. Usually the extent is 1MB in size. The ASM instance creates an extent map, which has a pointer to each 1MB extent. When the database instance wants to create or open the datafile, it messages the ASM instance and ASM instance returns the extent map for that file. The database instance then performs the I/O directly with the datafile.

ASM instance must be started before the database instance starts and must be running as long as the database instance runs.

Initialization Parameters for ASM instance

Unlike database instance, ASM instance does not have physical files and can be started with few initialization parameters. The following are the required parameters.

*.instance_type=asm
*.cluster_database=TRUE
*.instance_number=1
*.remote_login_passwordfile=EXCLUSIVE
*.asm_diskgroups=ASMIND,ASMDB,ASMFLASH
*.background_dump_dest=’/…/’
*.core_dump_dest=’/…/’
*.user_dump_dest=’/…/’

The following size is used for most configurations

*.Shared_pool_size=128MB
*.Large_pool_size= 12MB
*.Db_cache_size=64MB

From Oracle 11g, there is no need to set bdump,cdump,udump. Instead set the following Automatic Diagnostic Repository parameter,

*.diagnostic_dest=’/…/’

Starting ASM instance

There are four modes to start asm instance they are,

SQL> Startup Nomount; -- starts the asm instance.
SQL> Startup Mount; -- mounts the disk groups
SQL> Startup Force; -- shuts down asm and restarts.
SQL> Startup Restrict; -- prevents oracle database from connecting to asm.

Stopping ASM instance

SQL> Shutdown;

Shutting down the ASM instance shuts down any oracle databases connected to that ASM instance in the same mode. When you issue shutdown normal, immediate or transactional ASM instance waits for the corresponding oracle databases to finish all SQL operations before shutting down.

Thanks

If you like the post vote for me by clicking the following button

Automatic Storage Management

2009-11-01T00:46:00.005+05:30

What is Automatic Storage Management ?

ASM is a special kind of volume manager specifically designed for Oracle Database Files. You give all the disks to Oracle and it will manage it for you. ASM groups the disks in the storage system as one or more disk groups and automates the placement of files within these disk groups. This results in better utilization, performance and high availability.

Advantages of ASM

1. Automatic Load Balancing: The data is spread over all the available disks and hence provide equal distribution of I/O loads across all the disks.

2. Automatic Rebalancing of data: When a disk is added or removed the data is automatically rebalanced with the remaining disks without affecting the performance.

3. Fault Tolerance : ASM provides fault tolerance through 2 to 3 levels of mirroring.

4. ASM can be used in single instance as well as RAC environment.

5. ASM files can co-exist with non-asm files in the same database.

Disadvantage of ASM

ASM cannot support some files such as alert log files, trace files, external table files etc.

Types of files stored in ASM

Data Files, Control files, Redo Log files, Archived log files, Temp files, Parameter files, Backup pieces, Flashback logs, Data pump sets.

Thanks

If you like the post vote for me by clicking the following button.

Force Log Switches

2009-10-11T03:54:00.005+05:30

You can force the log switches using the following statements,

SQL> Alter System Switch LogFile;
SQL> Alter System Archive Log Current;

The difference between the two statements is that the first statement issues a log switch and the control immediately returns to the user. The user can perform other activities while the log file is being archived at the background. However the second statement will not return the control to the user until the current logfile is archived. It issues a log switch and the user session hangs until the log is archived.

A number of DBAs also confuse with the following statement,

SQL> Alter System Archive Log all;

The above statement will not issue a log switch. It will archive all the non-archived log files. If you have no logfiles that are unarchived, then issuing the above statement returns the error message,

ORA-00271 there are no logs that need archiving.

Thanks
If you like the post vote for me by clicking the following button

Log File Switch Completion (Checkpoint Incomplete)

2009-10-11T03:45:00.003+05:30

The cause for this wait event is same as mentioned here. When you see the log file switch completion wait event you will most likely see the checkpoint incomplete wait event. During the log switch a checkpoint occurs. The checkpoint signals the DBWr to write the dirty buffers to the data files.

The difference between log file switch completion and log file switch completion (Checkpoint Incomplete) wait event is, in the case of former wait event the users wait for the Log writer background process (LGWr) to complete its work (log switch). In the case of latter wait event the users wait for the Database Writer background process (DBWr) to complete its work (checkpoint).

Tuning option

Increase the size of redo log files. Increase the number of redo log groups.

Thanks
If you like the post vote for me by clicking the following button

log file switch completion

2009-10-11T02:45:00.002+05:30

Whatever changes you make in the database, the changes are recorded in the online redo log files. These are important files for recovery in the event of a crash. There must be atleast two redo log groups in the database. The online redo log files are used in a circular fashion, i.e., when the current redo log file is full the changes are recorded in the next available online redo log file. When the last redo log file is full the changes are then recorded in the first redo log file overwriting the already available redo changes. If you have enabled the archiving then the online redo log file must have been archived before it is overwritten.

A log switch occurs when the current online redo log file is full. It enables the LGWr process to close the current redo log file and open the next available redo log file and start writing the changes in that file. A checkpoint occurs during the log switch which enables the DBWr to write the dirty buffers to be flushed to the data files.

When the redo log file size is small then the log file gets filled frequently causing log switches to occur more frequently. When a user session waits for log file switch completion wait event, it means the LGWr has not completed its work.

Tuning Option

Increase the size of the online redo log file. Check out the v$log_history view to see how often the log switch has taken place. Size your log files so that the log switch occurs every 30 minutes. For eg., if your current log file size is 50MB and log switch occurs every 5 minutes then increase the file size to 300MB.

Thanks

Free Buffer Waits

2009-09-29T16:03:00.004+05:30

When a user session requires free buffers, the server process scans the LRU list to a get a free buffer space. After scanning the LRU list up to a threshold, if the server process could not get free space, it requests the DBWr to write the dirty buffer from the LRU list to disk. While the DBWr process writes the dirty buffers the session waits on 'Free Buffer Waits'.

Tuning Options

Poor SQL Statements

Query the V$SQL view for statements that have high DISK_READS. Tune the statements to reduce the physical reads. The poorly written SQL Statements are the main cause of this wait event.

DBWr Processes

Increase the DBWr processes (or)
Decrease the Buffer Cache (or)
Decrease the FAST_START_MTTR_TARGET parameter.

Delayed Block Cleanout

The delayed block cleanout will cause the free buffer wait events. To avoid delayed block cleanout perform a full table scan on a table that has been loaded with a lot of rows before it is released to the application.

Small Buffer Cache

Increase the size of Buffer Cache if you feel that the buffer cache is under sized and check for the wait event.

Slow IO

Thanks
Vinod D

log file sync wait

2009-09-17T10:14:00.003+05:30

The log file sync event occurs when a user issues Commit or Rollback. Click here for the difference between log file sync and log parallel write wait event.

When a user issues a commit or rollback command, the redo data in the redo buffer is written to online redo log file. This write is known as sync write. During this synchronization process the user process waits in log file sync event, while the LGWr waits on log file parallel write event.

The log file sync event is very fast and usually unnoticed by the end users. However you may notice that there are very high time waited for this wait event in certain cases. The main cause for such high wait for this event is as follows,

Too many commits

If you notice high waits at session level then it may be due to running batch processes there are commits within a loop. If that is the case then the application logic can be changed by eliminating unneccessary commits and reduce commit frequency.

If you notice high waits at system level then it may be due to short transactions. OLTP databases usually have short transactions and have high log file sync wait events. Only thing you can do to improve the performance, in this case, is to use faster IO subsystem, rawdevices.

Large Log buffer

The redo entries from buffer to log files take place either through sync writes as explained earlier or through background writes (Such as 1/3 full, 1MB redo etc). When redo log buffer is large then more redo data are accumulated in the buffer. The background writes (i.e., when redo becomes 1/3 full ) are limited or delayed. When a user issues a commit or roll back then the sync writes will take more time.

Thanks

Buffer Busy Waits

2009-09-14T13:16:00.004+05:30

The 'Buffer Busy Waits' Event occurs due to the following reasons,

1. A user wants to access a data block for read or write operation. The block is present in the Buffer Cache but locked by another session. The user has to wait till the other session releases the lock on that block.

2. A user wants to access a data block for read or write operation. The block is not present in the Buffer Cache. The block has to be read from data files into Buffer Cache. But the same block is being read by another session. Hence the user patiently waits for the IO of the other session to complete. Prior to oracle 10g, this wait is referred to as Buffer busy wait, but from oracle 10g this wait event is referred to as 'read by other session' wait.

Tuning Options,

Run the following query to find whether any block or range of blocks are always responsible for buffer busy waits,

SQL> select p1 "File #", p2 "Block #", p3 "Reason Code" From v$session_wait
where event = 'buffer busy waits';

Use the following query to find the segment the block belongs to,

SQL> select owner,segment_name,segment_type From dba_extents
where file_id = &file#
and &block# between block_id and block_id + blocks -1;

Once the segment name is identified use the V$Segment_Statistics view to monitor the statistics of the segment.

SQL>select * from v$segment_statistics
where owner like 'RACFIN'
and statistic_name like 'buffer busy waits'
and object_name like 'IBM_PARTY_BRANCH' ;

Use the following query to find what kind of contention is causing the buffer busy waits.

SQL> Select * from v$waitstat;

The output shows the sum and total time of all waits for particular class of block such as data block, segment header, undo header block etc.

To avoid the buffer busy waits,

1. Increase the PCTFREE and PCTUSED values to reduce the number of rows per block to avoid data block contention.
2. Increase the INITRANS value to avoid data block contention.
3. Increase the FREELIST and FREELIST GROUPS value to avoid freelist block contention and segment header block contention.

Thanks

Enqueue

2009-09-14T11:37:00.006+05:30

Enqueue waits are locking mechanisms that control the access to shared resources. There are various modes of enqueues.

The following query gives you the detail of Sessions holding the lock, the lock type, mode.

SQL> select DECODE(request,0,'Holder: ','Waiter: ')sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request ;

The most common enqueue waits are discussed below,

TYPE: TM (Table Lock)
LMODE: 3
CAUSE: Unindexed Foreign Key
SOLUTION: The holder has to issue commit or rollback. To avoid this kind of lock in first place create indexes on the foreign key columns. You can do this by taking the ID1 column value in v$lock. This ID1 value is the object ID of the child table. Use dba_objects dictionary table and get the object name. Create the index on the foreign key column.

TYPE: TX (Row level lock)
LMODE: 6
CAUSE: Updating or deleting rows that are currently locked by another transaction.
SOLUTION: Application issue. The lock is released when the holding session issues a commit or rollback. Killing the holding session will rollback the transaction.
RESOURCE LOCKED: Issue the following query to find the resource that is locked.

SQL> select c.sid waiter_sid, a.object_name, a.object_typefrom dba_objects a, v$session b, v$session_wait cwhere (a.object_id = b.row_wait_obj# or a.data_object_id = b.row_wait_obj#)and b.sid = c.sidand chr(bitand(c.P1,-16777216)/16777215) chr(bitand(c.P1,16711680)/65535) = ’TX’and c.event = ’enqueue’;

TYPE: TX (ITL Shortage)
LMODE: 4
CAUSE: i) ITL (Interested Transaction List) Shortage. ii) Unique Key Enforcement. iii) Bitmap index Entry.
SOLUTION: To see whether the wait is due to ITL shortage dump the data block and see how many ITL slots are being used.

SQL> Alter system dump datafile block

If it is indeed due to ITL shortage, then increase the INITRANS value of the object. Also increase the PCTFREE value of the objects.

ii) If the Wait is due to the Unique key Enforcement (i.e, if more than one session inserts the same value that has unique or primary key then the insert will not succeed). If the first session that inserted the value commits then the waiting session will receive the unique constraint violation error. If the first session rollsback then the second session succeeds.

iii) Bitmap Index Entry: A bitmap entry covers a range of ROWIDs. When a bitmap entry is locked all the ROWIDs that correspond to the bitmap entry are locked. When multiple users attempt to delete or update different rows that have the same bitmap entry then a wait for TX in mode 4 will occur.

It is difficult to find whether the lock was due to unique key enforcement or bitmap index entry by merely looking in to the V$Lock view. You have to capture the SQL statements that holder and waiter have issued. If the statement is an insert then wait is due to the unique key enforcement. If the statement is update or delete then the wait is due to the bitmap index entry.

Thanks

control file parallel write

2009-09-13T09:06:00.003+05:30

The control file parallel write wait event occurs due to some operations that caused the control file to be updated, such as

1. log switches by LGWR process.
2. adding a datafile.
3. removing a datafiles.
4. checkpoint information by CKPT process.
5. archive log information by ARCH process.

To find which sessions cause transactions to controlfile, issue the following statement.

SQL> select a.sid,decode(a.type, 'BACKGROUND', 'BACKGROUND-' || substr
(a.program,instr(a.program,'(',1,1)), 'FOREGROUND') type, b.time_waited,
round(b.time_waited/b.total_waits,4) average_wait, round((sysdate - a.logon_time)*24) hours_connected
from v$session_event b, v$session a
where a.sid = b.sid
and b.event = 'control file parallel write'
order by type, time_waited;

The output of the above statement shows which background process is writing to control file frequently, For eg., if LGWr has more time_waited then it implies that the log switches are more. If the foreground process have more time_waited then it implies that there are more changes to database that requires to update the SCN in control file.

Thanks

log file sync

2009-09-13T06:28:00.002+05:30

When a user issues a commit or rollback then the redo data in the redo buffer is written to the online redo log file. The user session waits for this event to finish before continuing with other processing. This wait time is represented as log file sync wait event.

A number of people have asked the question as what is the difference between log file parallel write and log file sync.

The difference is....

log file parallel write occurs when LGWR writes redo records from redo buffer to online redo log file. This may take place very frequently when it meets any one of the following condition,

1. Once in every three seconds.
2. _LOG_IO_SIZE threshold is met.
3. 1MB worth of redo entries are buffered.
4. Commit.
5. Rollback.
6. When DBWr requests.

The user sessions will never experience the log file parallel write wait event.

When the user session issue commit or rollback then it leads to log file sync wait event, which the user will experience by response time.

Thanks

log file parallel write

2009-09-13T05:40:00.004+05:30

The 'log file parallel write' event is caused by the Log writer (LGWR) process. The LGWR writes the redo buffer to the online redo log files . It issues a series of write calls to the system IO. The LGWR waits for the writes to complete on log file parallel write. A slow LGWR process can introduce log file sync waits which makes the user to experience wait times during commit or rollback. The log file parallel write and log file sync wait events are interrelated and must be dealt simultaneously.

If the average_wait time is high (above 10 milliseconds) it indicates that the system IO throughput is slow. To improve the average_wait time follow the same techniques used in db file parallel write wait event.

Tuning options:

1. Avoid running hot backups during peak hours.
2. Check for high commit sessions and try to change the application logic to commit less frequently. Use the following queries to find high commit sessions,

SQL> select sid, value from v$sesstat
where statistic# = select statistic# from v$statname where name = 'user commits') order by 2 desc;

A high redo wastage also indicates high frequency commits

SQL> select b.name, a.value, round(sysdate - c.startup_time) days_old
from v$sysstat a, v$statname b, v$instance c
where a.statistic# = b.statistic#
and b.name in ('redo wastage','redo size');

Thanks

AIX-VMM Tuning Parameters

2009-09-03T08:38:00.001+05:30

AIX - Virtual Memory Tuning

In AIX, about 80% of the memory is used to buffer file I/O and only 20% is used for oracle processes and programs. This results in the double buffering of file I/O as Oracle SGA is already buffering the file I/O. It causes more Page in and Page out. To avoid this situation you must tune the Virtual Memory Parameters

To see the virtual memory Parameters use the following command.

# vmo -a

The important parameters are

strict_maxperm

strict_maxclient

lru_file_repage

maxperm%

minperm%

v_pinshm

maxpin%

Check whether the parameters are set to the following values, if not set it to the following values

strict_maxperm=0 (default)

strict_maxclient=1 (default)

lru_file_repage=0

maxperm%=20-30

minperm%=5 (RAM <32>32 GB but <64>64 GB)

v_pinshm=1

maxpin%=80

Thanks

Wait Events Parameter

2009-08-20T23:57:00.003+05:30

The parameters (p1, p2, p3) for some of the most common wait events are given below.

Thanks

Controlfile Sequential Read/ Parallel Write

2009-08-17T19:17:00.003+05:30

'controlfile sequential read' occurs while reading control file (backup, share information from controlfile between instances etc). The parameters in V$session_wait are as follows,

P1 - The file# of control file from which the session is reading.
P2 – The block# from which the session starts reading.
P3 – The no. of blocks the session is trying to read.

'controlfile parallel write' occurs while writing to all the control files. The parameters in V$session_wait are as follows,

P1 – No. of control files being updated.
P2 – No. of blocks that are being updated.
P3 – No. of IO requests.

Tuning Options: Use Asynchronous IO if possible. Move the controlfile to a different disk or use faster disk.

Thanks

db file parallel read/write

2009-08-17T18:10:00.006+05:30

'db file parallel read' occurs during recovery. The datablocks that need to be changed are read from various datafiles and are placed in non-contiguous buffer blocks. The server process waits till all the blocks are read in to the buffer.

Tuning options - same as db file sequential read.

'db file parallel write' occurs when database writer (DBWr) is performing parallel write to files and blocks. Check the average_wait in V$SYSTEM_EVENT, if it is greater than 10 milliseconds then it signals a slow IO throughput.

Tuning options - The main blocker for this wait event is the OS I/O sub systems. Hence use OS monitoring tools (sar -d, iostat) to check the write performance. To improve the average_wait time you can consider the following,

If the data files reside on raw devices use asynchronous writes. However if the data files reside on cooked file systems use synchronous writes with direct IO.

Note: If the average_wait time for db file parallel write is high then you may see that the system waits on free buffer waits event.

Thanks

Objects and Blocks in waits

2009-08-17T16:55:00.003+05:30

When you find a session waiting for either sequential read or scattered read, it might be useful to find which object is being accessed for further tuning.

To find the object and the block number the session is accessing,

SQL> Select SID, Event, P1 File#, p2 Block#, p3 “Blocks Fetched”,
wait_time, seconds_in_wait, state
From V$Session_Wait
Where Sid in (Select Sid From V$Session where osuser != ‘oracle’
and status = ‘ACTIVE’);

From the above query get the file# and the block#.

To find the name of the file, issue the following query.

SQL> SELECT tablespace_name, file_name FROM dba_data_files
WHERE file_id = &File#;

To find the object, issue the following query.

SQL> SELECT owner , segment_name , segment_type, partition_name
FROM dba_extents
WHERE file_id = &File#
AND &Block# BETWEEN block_id AND block_id + blocks -1 ;

Thanks

db file scattered read

2009-08-17T16:45:00.006+05:30

'db file scattered read' occurs during multiblock read (Full table Scan, Index Fast Full Scans).

Tuning Options

1. Check for SQL that performs Full scans. Tune for optimal plans.
2. If the multiblock scans are due to optimal plans then increase the init parameter DB_FILE_MULTIBLOCK_READ_COUNT (up to 9i). Set this parameter to 0 (automatic tuning) in 10g.
3. Use Partitions if possible.

Thanks

db file sequential read

2009-08-17T16:23:00.004+05:30

'db file sequential read' occurs during single block read (Reading index blocks, row fetch by row id).

Tuning Options

1. Find the top SQL with high physical reads (AWR or Statspack).
Analyze the objects for better Execution plans.
Use more selective index.
Rebuild the indexes if it is fragmented.
Use Partition if possible.

2. Find the I/O Statistics
Check hot disks using V$filestat.
Move datafiles to avoid contention to a single disk.

3. Try to increase the Buffer Cache
In 9i, use buffer cache advisory and in 10g use ASSM to determine the optimal size for buffer cache.
Check for hot segments and place it in the Keep Pool.

Thanks

Optimizer Statistics - Views

2009-08-12T17:34:00.002+05:30

To Check when the object's statistics was last gathered

SQL> Select last_analyzed, table_name, owner, num_rows, sample_size
From dba_tables
Order By last_analyzed;

To check the statistics gathered on the tables

SQL> Select * From DBA_TAB_STATISTICS;

To check the statistics gathered on Columns

SQL> Select * From DBA_TAB_COL_STATISTICS;

To check the amount of DML operations on tables

SQL> Select * From SYS.DBA_TAB_MODIFICATIONS;

Thanks

Restore old statistics

2009-08-12T17:10:00.002+05:30

When you collect new statistics, oracle stores the old statistics in the data dictionary. If the new statistics lead to sub optimal plans then you can restore the old statistics.

To view the history of statistics gathering

SQL> Select * From DBA_OPTSTAT_OPERATIONS;
SQL> Select * From DBA_TAB_STATS_HISTORY;

The old statistics are purged automatically after the retention period (default 31 days). Hence you can restore the statistics to anytime in last 31 days.

To check the current retention period

SQL> Select DBMS_STATS.GET_STATS_HISTORY_RETENTION from dual;

To change the retention period

SQL> Exec DBMS_STATS.ALTER_STATS_HISTORY_RETENTION (45)

To manually purge the old statistics

SQL> Exec dbms_stats.purge_stats(Timestamp);

To Restore the statistics

SQL> Exec dbms_stats.restore_database_stats(Sysdate -1);
SQL> Exec dbms_stats.restore_dictionary_stats(Sysdate -1);
SQL> Exec dbms_stats.restore_system_stats(‘Sysdate -1’);
SQL> Exec dbms_stats.restore_schema_stats(‘SCOTT’,’Sysdate -1’);
SQL> Exec dbms_stats.restore_table_stats(‘SCOTT’,’EMP’’Sysdate -1’);

Thanks

Gathering System Statistics

2009-08-12T16:57:00.005+05:30

The System Statistics enable optimizer to generate better execution plan. The Automatic statistic gathering feature of 10g does not gather the system statistics. You have to manually collect System Statistics. There are two options for collecting System Statistics, they are

1. Workload Statistics
2. No Workload Statistics

Workload Statistics: This type of statistics are taken during normal working period. It will not generate any workload. The execution plans are generated based on the workload at the time of submitting the query. For eg. if the system is I/O intensive then the CBO choses an execution plan that is less intensive on I/O.

To gather Workload Statistics

SQL> Exec dbms_stats.gather_system_stats('start')
SQL> Exec dbms_stats.gather_system_stats('stop')
Or
SQL> Exec dbms_stats.gather_system_stats('interval', interval=>N)

where N is the number of minutes when statistics gathering will be stopped automatically.

To delete system statistics and reset to the default noworkload statistics

SQL> Exec dbms_stats.delete_system_stats().

NoWorkload Statistics: Noworkload statistics gather data by submitting random reads against all data files. It gathers I/O transfer speed, I/O seek time and CPU speed. There will be some overhead while gathering statistics. By default oracle uses noworkload statistics. If workload statistics are available oracle ignores noworkload statistics.

To gather no workload statistics

SQL> Exec dbms_stats.gather_system_stats();

Thanks

Sampling in Statistics Gathering

2009-08-11T04:31:00.004+05:30

Gathering statistics on any object involves a full table scan and sorts which uses more resources. To reduce the resource spent for gathering statistics use sampling.

The ESTIMATE_PERCENT attribute of DBMS_STATS package is used to set the sample size. Oracle recommends to set the value to be AUTO_SAMPLE_SIZE. This value lets oracle to decide the best sample size.

SQL> DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT', DBMS_STATS.AUTO_SAMPLE_SIZE);

Thanks

Manual Statistics Gathering

2009-08-11T04:28:00.002+05:30

1. To Gather Statistics on System Schemas (SYS, SYSTEM, etc)

SQL> Exec DBMS_STATS.GATHER_DICTIONARY_STATS
SQL> Exec DBMS_STATS.DELETE_DICTIONARY_STATS

2. To Gather Statistics on all database objects including System schemas.

SQL> Exec DBMS_STATS.GATHER_DATABASE_STATS
SQL> Exec DBMS_STATS.DELETE_ DATABASE _STATS

3. To Gather Statistics on schemas other than System schemas

SQL> Exec DBMS_STATS.GATHER_SCHEMA_STATS(‘Ownername’);
SQL> Exec DBMS_STATS.DELETE_SCHEMA_STATS(‘Ownername’);

4. To Gather Statistics on individual tables

SQL> Exec DBMS_STATS.GATHER_TABLE_STATS(‘Ownername’,’Tablename’);
SQL> Exec DBMS_STATS.DELETE_TABLE_STATS(‘Ownername’,’Tablename’);

5. To Gather Statistics on indexes

SQL> Exec DBMS_STATS.GATHER_INDEX_STATS(‘Ownername’,’Indexname’);
SQL> Exec DBMS_STATS.DELETE_INDEX_STATS(‘Ownername’, ’Indexname’);

6. To Gather Statistics on individual columns of tables

SQL> Exec DBMS_STATS.GET_COLUMN_STATS(‘Ownername’,’Tablename’,’Column’);
SQL> Exec DBMS_STATS.DELETE_ COLUMN _STATS(‘Owner’,’Tablename’,’column’);

Note: Oracle invalidates the parsed SQL statements when new statistics are updated.

Thanks

Automatic Statistic Gathering

2009-08-11T03:40:00.019+05:30

From Oracle 10g, any DML operations performed on any objects are monitored by oracle. The information is stored in the view SYS.DBA_TAB_MODIFICATIONS. Based on the amount of changes, oracle decides whether to gather new statistics for an object.

When you create the database using DBCA, oracle automatically creates a Job, GATHER_STATS_JOB, automatically and is scheduled to run during the maintenance window.

The following query is used to find whether the job is running,

SQL> Select Job_name from dba_scheduler_jobs where job_name like 'GATHER_STAT%';

Maintenance Windows: Oracle Scheduler have two Maintenance windows,

Weeknight window : Between 10 pm and 6 am (monday - friday)
Weekend window: Between 12 am saturday and 12 am sunday

The job is closed when the maintenance window closes even if the job has not finished.

To enable statistics collection to continue even after the maintenance window closes, set the stop_on_window_close attribute to false.

SQL> Exec DBMS_SCHEDULER.SET_ATTRIBUTE (‘GATHER_STATS_JOB’, ‘STOP_ON_WINDOW_CLOSE’, ‘FALSE’);

Candidates for Statistics Gathering: The job gathers statistics for objects with

Missing Statistics : Objects without statistics
Stale Statistics : If more than 10% of the rows in an object is modified, its statistics are considered stale.

Note: System Statistics and the Fixed table (X$ tables) statistics are not gathered automatically.

To Disable Automatic Statistic Gathering

SQL> Exec DBMS_SCHEDULER.DISABLE(‘GATHER_STATS_JOB’);

To get the details about the maintenance window

SQL> Select window_name, repeat_interval, duration, next_start_date
From dba_scheduler_windows;

To change the period of Maintenance Windows

SQL> Exec DBMS_SCHEDULER.SET_ATTRIBUTE(‘WEEKNIGHT_WINDOW’, ‘repeat_interval’, 'freq=daily;byday=MON, TUE, WED, THU, FRI;byhour=0; byminute=0; bysecond=0');

Lock Statistics: If you dont want to gather statistics for certain object or schema then you can lock it as follows,

SQL> Exec DBMS_STATS.LOCK_SCHEMA_STATS(‘SCOTT’);
SQL> Exec DBMS_STATS.LOCK_TABLE_STATS(‘SCOTT’, ‘EMP’);

To unlock

SQL> Exec DBMS_STATS.UNLOCK_SCHEMA_STATS(‘SCOTT’);
SQL> Exec DBMS_STATS.UNLOCK_TABLE_STATS(‘SCOTT’, ‘EMP’);

Note: When you perform bulk loads on tables gather statistics on those tables immediately.

Thanks

Recompile Invalid Objects

2009-08-11T03:06:00.004+05:30

Script to find the invalid objects in the database

SQL> Select * From dba_objects where status = 'INVALID';

To Recompile Invalid Objects

Method 1: With access to SYS user

SQL>@$ORACLE_HOME/rdbms/admin/utlrp.sql

Method 2: Without access to SYS user (Cannot validate SYS objects)

SQL> Exec DBMS_UTILITY.compile_schema('SYSTEM');

Method 3: Manual Script

SQL> select 'ALTER ' OBJECT_TYPE ' ' OWNER '.' OBJECT_NAME
' COMPILE;'from dba_objects where status = 'INVALID'and object_type in ('PACKAGE','FUNCTION','PROCEDURE', 'VIEW', 'TRIGGER', 'SYNONYM');

SQL> Select 'ALTER PACKAGE' ' ' OWNER '.' OBJECT_NAME ' COMPILE BODY;' From dba_objects where status = 'INVALID'
And object_type in ('PACKAGE BODY');

SQL> Select 'ALTER MATERIALIZED VIEW' ' ' OWNER '.' OBJECT_NAME ' COMPILE;' From dba_objects where status = 'INVALID'
and object_type in ('UNDEFINED');

Thanks

TkProf

2009-07-30T00:47:00.002+05:30

TKPROF (Transient Kernel Profiler) is an utility to translate the trace files in to a readable format.

Syntax

$ TKPROF variable_list

Where variable_list include

Tracefile - The name of the trace file in the udump.
Output_file - The name of the output file in the udump.
Explain=user/pass - Run Explain plan on user’s SQL in trace file.
Sys= Yes or No - Display Recursive SQL statements in output file.
Waits= Yes or No - Record summary of for any waits.
Aggregate = Yes or No - If no, then SQL text is shown once if executed more than once.

Sort=parameters - A number of sorting options are available. Most common are:
FCHCPU (Cpu time fetch), FCHDSK (Disk reads for fetch),
FCHCU and FCHQRY (Memory Reads for fetch), FCHROW(no. of rows fetched),
EXEDSK( Disk reads during execute), EXECU, EXEQRY, EXEROW, EXECPU,
PRSCPU(Parse CPU) and PRSCNT (times parsed).

Table= schema.table - Table in which tkprof temporarily put execution plans before writing them to output file.
Insert=filename - Creates a script to create a table and store the trace file statistics for each SQL stmt traced.
Record=filename - Produces a file of all the user’s SQL stmt.
print= number - The no. of statements in the output file. Default is all statement.

Example:

$ cd $ORACLE_BASE/admin/gcuat/udump
$ tkprof gcuat_ora_mytrace_704678.trc output.txt explain=system/manager aggregate=no sys=no waits=yes sort=(FCHCPU,EXECPU,PRSCPU)

Thanks

Event 10046 Tracing (Other's session)

2009-07-29T05:34:00.003+05:30

Method 1: Using oradebug utility

Get the SPID of the session you want to trace.
SQL> Select p.spid, p.pid From v$session s, v$processs p
Where s.paddr = p.addr
And s.sid = 149 And s.serial#= 3643

$ sqlplus / as sysdba
SQL> oradebug setospid 12345;
-- 12345 is the p.spid value from the above query.

SQL> oradebug unlimit;
SQL> oradebug event 10046 trace name context forever, level 8;

-- To turn off tracing
SQL> oradebug event 10046 trace name context off;

Method 2: Using DBMS_MONITOR package in 10g or DBMS_SUPPORT package

SQL> Exec DBMS_MONITOR.session_trace_enable(149,3643, TRUE, TRUE)
SQL> Exec DBMS_MONITOR session_trace_disable(149,3643)

or

SQL> Exec DBMS_SUPPORT.START_TRACE_IN_SESSION (149,3643, TRUE, TRUE);
SQL> Exec DBMS_SUPPORT.STOP_TRACE_IN_SESSION (149,3643);

Thanks

Event 10046 Tracing

2009-07-29T05:31:00.003+05:30

To enable and disable event 10046 tracing for the current session,

SQL> Alter session set events ‘10046 TRACE NAME CONTEXT FOREVER, LEVEL 12’;
SQL> Alter session set events ‘10046 TRACE NAME CONTEXT OFF’;

To enable and disable event 10046 tracing for the entire instance

SQL> Alter system set events ‘10046 TRACE NAME CONTEXT FOREVER, LEVEL 12’;
SQL> Alter system set events ‘10046 TRACE NAME CONTEXT OFF’;

Thanks

Event 10046 Levels

2009-07-29T05:28:00.002+05:30

Event 10046 enables us to collect extended SQL trace data. There are four levels in event 10046 tracing.

Level 1: Standard tracing with parse, fetch, execute, Explain plan.
Level 2: Level 1 + Bind Variables
Level 3: Level 1 + Waits
Level 4: Level 1 + Bind Variables + Waits

Thanks

Tracing other's session

2009-07-29T05:21:00.002+05:30

To trace other user's session prior to Oracle 10g use DBMS_SUPPORT package.

If the package is not available, run the following script.

SQL>@$ORACLE_HOME/rdbms/admin/dbmssupp.sql

Syntax

DBMS_SUPPORT.START_TRACE_IN_SESSION(sid, serial#, wait, bind)
DBMS_SUPPORT.STOP_TRACE_IN_SESSION(sid, serial#)

To start tracing

SQL> Exec DBMS_SUPPORT.START_TRACE_IN_SESSION (149,3643, TRUE, TRUE);

To stop tracing

SQL> Exec DBMS_SUPPORT.STOP_TRACE_IN_SESSION (149,3643);

Thanks

Tracing other's session in 10g

2009-07-29T05:14:00.002+05:30

To trace the other user's session in Oracle 10g,

Step 1: Set the init parameter.

SQL> Alter system set TIMED_STATISTICS = TRUE;
SQL> Alter system set MAX_DUMP_SIZE = UNLIMITED;
SQL> Alter system set TRACEFILE_IDENTIFIER = ‘mytrace’;

Step 2: Enable Tracing

SQL> Exec DBMS_MONITOR.session_trace_enable(149,3643, TRUE, TRUE);
Where
1st Parameter: Session ID. (null means current session’s ID)
2nd Parameter: Serial # (null means current session’s serial#)
3rd Parameter: Trace Waits (Default True)
4th Parameter: Bind Variables (Default False)

Step 3: Disable Tracing

SQL> Exec DBMS_MONITOR.SESSION_TRACE_DISABLE(149,3643);

Thanks

Tracing own session

2009-07-29T04:03:00.002+05:30

To trace your own session follow the steps below,

Step 1: Set the init parameters

SQL> Alter session set TIMED_STATISTICS = TRUE;
SQL> Alter session set MAX_DUMP_SIZE = UNLIMITED;
SQL> Alter session set TRACEFILE_IDENTIFIER = ‘mytrace’;

Timed_statistics – Allows tracing to occur on the system.
Max_dump_size – Maximum size of the trace file in OS level blocks.
Tracefile_identifier - To identify the trace file generated for a particular session in the udump.

Step 2: Enable the tracing

SQL> Alter session set SQL_TRACE='TRUE';

or in 10g

SQL> Execute DBMS_MONITOR.SESSION_TRACE_ENABLE(NULL,NULL, TRUE, TRUE);

Note: In Oracle 10g SQL_TRACE parameter has been deprecated.

Step 3: Disable the Tracing

SQL> Alter session set SQL_TRACE= False;
Or
SQL> Execute DBMS_MONITOR.SESSION_TRACE_DISABLE;
Or
SQL> Exit;

Thanks

Autotrace

2009-07-13T04:07:00.002+05:30

Autotrace facility enables you to produce the EXPLAIN PLAN and Execution statistics for SQL Statement in SQL*Plus.

Requirements to use Autotrace

1. You must have a plan table to generate Explain plan report. If you don't have it then execute the following script to create it.

SQL> @$ORACLE_HOME/rdbms/admin/utlxplan.sql

2. You must have PLUSTRACE role. If you don't have it, then run the following script as a SYS user and grant the role.

$ sqlplus / as sysdba
SQL> @$ORACLE_HOME/sqlplus/admin/plustrce.sql
SQL> Grant plustrace to scott;

Turning on Autotrace

SQL> Set Autotrace ON explain -- Generates only Explain plan report
SQL> Set Autotrace ON Statistics -- Generates only Statistics report
SQL> Set Autotrace ON -- Generates both Explain plan and Statistics.

SQL> Set Autotrace OFF -- Turns off tracing

Sample Output

SQL> Select * from test;

no rows selected

Execution Plan

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2 Card=1 Bytes=22)
1 0 TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=2 Card=1 Bytes=22)

Statistics

314 recursive calls
0 db block gets
55 consistent gets
5 physical reads
0 redo size
143 bytes sent via SQL*Net to client
210 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
0 rows processed

Thanks

Hit Ratio in Oracle 10g

2009-07-06T17:15:00.002+05:30

From Oracle 10g, there is no need to run huge scripts to get the hit ratios. You can query the v$sysmetric view to get the hit ratios.

SQL> select * from gv$sysmetric
where metric_name like '%Ratio%'
and intsize_csec in (Select max(intsize_csec) from gv$sysmetric where inst_id =&id)
and inst_id = &&id;

Thanks

Rollback Segment Wait to Get Ratio

2009-07-05T20:00:00.003+05:30

The ratio of sum of WAITS to the sum of GETS in v$rollstat should be less than 5%.

The V$WAITSTAT view is used to find the contention in rollback segment header block.
A value over zero in the column UNDO_HEADER means there is contention.

Run hitratio script to get the ratio.

Thanks

Database Wait Time to CPU Time Ratio

2009-07-05T19:58:00.002+05:30

This ratio is available from Oracle 10g.

CPU Time Ratio - Percentage of total time used in performing user requests.
Wait Time Ratio- Percentage of total time used in waiting for resources.

When Wait time ratio is higher than the CPU time ratio, perform the Wait event analysis.

Run hitratio script to get the ratio.

Thanks

Chained Row Ratio

2009-07-05T19:53:00.003+05:30

Chained rows occurs as a result of row migration or row chaining.

Row chaining When the size of a row is more than the size of the data block, then the row is spread across multiple blocks. This is called Row Chaining.

Row migration When an update increases the row size and there is no free space available in the data block to accommodate the row, the entire row will be moved to a new block leaving only a pointer in the original block. A row migration can also lead to a row chain if the row becomes large enough.

Row chaining affects index reads and full table scans. You cannot do much to avoid row chaining, one way is to calculate the length of the row and put it in a tablespace that has large block size.

Row migration does not affect full table scans but affects index reads. Increase the PCTFREE to allow more space for future updates. Re-organize or recreate the tables and indexes to avoid row migration.

Run hitratio script to get the ratio.

Thanks

Latch Hit Ratio

2009-07-05T19:38:00.003+05:30

It gives the ratio of the total number of latch misses to the number of latch gets for all latches. If the latch hit ratio is below 99 percent, it indicates a latching problem. A latch free wait event in the Top 5 wait events section of the statspack or AWR report confirms that. You must refer to the Latch section of the report for further analysis.

Run the hitratio script to get the ratio.

Thanks

Execute to Parse Ratio

2009-07-05T19:30:00.003+05:30

This is an important ratio. Formula for finding this ratio:

Execute to Parse ratio = 100 - (Parse count/ Executions * 100)

If you parse once and execute more, then the ratio will be near to 100%.
If you parse every time you execute, then the ratio will be near to 1%.

When the value is too low, it means the SQL is not sharable.

You may need to set CURSOR_SHARING to FORCE. Set SESSION_CACHED_CURSORS init parameter. Increase OPEN_CURSORS init parameter.

Find out which SQL statement has parse count equal to execute count. These statements are contributing to inefficient cursor sharing.

Run hitratio script to get the ratio

Thanks

Parse CPU to Parse Elapsed

2009-07-05T19:18:00.004+05:30

Parse CPU means amount of CPU time used for parsing.
Parse Elapsed means amount of clock time used for parsing.

Parse Elapsed = Parse CPU + Wait time.

Parse CPU to Parse Elapsed ratio = 100 * parse CPU/ parse Elapsed.

Ideally Parse Elapsed must be equal to Parse CPU, i.e., only CPU time is used for parsing. In that case the ratio is 100%. If wait time is more then the ratio will be less.

Low Value for this ratio is an indicator of latching problem. Investigate the latch sections in AWR and Statspack report for contention on library cache and shared pool latches.

Run hitratio script to get the ratio.

Thanks

Soft Parse Ratio

2009-07-05T19:15:00.003+05:30

This ratio indicates the percentage of parses that were soft.

Soft-Parse Ratio = 100 * Soft Parse/(Hard + Soft Parse)

If the ratio is low, that is less than 80%, then set CURSOR_SHARING init parameter to FORCE.

Run hitratio script to get the ratio.

Thanks

Pin Hit Ratio

2009-07-05T17:49:00.003+05:30

The Pin Hit Ratio indicates how frequently the executed statements found the associated parsed SQL already in the Library Cache.

The value should be greater than 95%. Else increase shared pool size.

Run hitratio script to get the ratio.

Get Hit Ratio

2009-07-03T02:45:00.003+05:30

Get Hit Ratio = GETS/GETHITS

GETS = Statements that require parsing
GETHITS = Parsed statements already in the memory.

The value should be greater than 95. In 10g the get hit ratio will be too low, this is a bug.

Run hitratio script to get the ratio

Thanks

Dictionary Cache Hit Ratio

2009-07-03T02:39:00.002+05:30

It indicates the percentage of time the data dictionary objects are read from memory rather than on disk.

The value should be greater than 95%. If less then increase the shared pool (SHARED_POOL_SIZE init parameter).

Run hitratio script to get dictionary cache hit ratio.

A cache miss on Dictionary and Library cache is more expensive than a miss on buffer cache.

Thanks

Library Cache Reload Ratio

2009-07-03T02:32:00.003+05:30

When the Shared pool is not large enough, it cannot hold the parsed statement for a long time. When new statements are parsed the header of the old parsed statement is kept in the shared pool and the body is pushed out of the memory. Reloads occur when the body of previously parsed statements had to be loaded again.

If the value of this ratio is greater than 1% then increase the shared pool by increasing the SHARED_POOL_SIZE init parameter.

Run hitratio script for library cache reload ratio.

Thanks

Library Cache Hit Ratio

2009-07-03T02:23:00.004+05:30

This gives the percentage of Pin requests that result in pin hits.

PINS: No. of times an item in the library cache was executed.
PINHITS: No. of times an item was executed without reloads.

The value should be greater than 95%. If less then,

Increase the SHARED_POOL_SIZE init parameter.
CURSOR_SHARING may need to be set to FORCE.
SHARED_POOL_RESERVED_SIZE may be too small.
Inefficient sharing of SQL, PLSQL or JAVA code.
Insufficient use of bind variables.

Run hitratio scrip to get the Library hit ratio.

Thanks

In (PGA) Memory Sort Ratio

2009-07-03T02:16:00.004+05:30

Sorts occur either in memory or in disk.

Memory - PGA (Faster no I/O involved)
Disk - Temporary Tablespace (Slower I/O involved)

This ratio indicates the amount of sorts performed in the Memory rather than on Disk.

OLTP database should have value greater than 95%.

If the value is less than 95%, then

When not using Automatic PGA memory, then increase SORT_AREA_SIZE init parameter.
When using Automatic PGA memory, then increase PGA_AGGREGATE_TARGET init parameter.

Run hitratio script to get the memory-sort ratio.

Thanks

Buffer and Redo Nowait Ratio

2009-07-03T02:08:00.003+05:30

Buffer Nowait Ratio

If this ratio is greater than 99%, then it indicates that the server process is able to obtain the buffer immediately.

If this ratio is less than 99%, then it indicates that the server process has to wait for something before obtaining the buffer. You must investigate the Buffer Waits Section of Statspack/ AWR report.

Redo Nowait Ratio

It indicates the percentage of time the redo space is immediately available without having to wait. A low value indicates small redo log file and frequent redo switch. The value should be greater than 99%. If not then increase the redo log file size or use faster disks for redo log files.

Run the hitratio script to get the ratios.

Thanks

Buffer Cache Hit Ratio

2009-07-03T01:59:00.004+05:30

When the hit ratio is

Too Low (<50): Check for indexing. Consider increasing the buffer cache.

Too High (>99): Check for bad SQL or index that is causing a surge in the logical reads (High Buffer_gets SQL).

A high hit ratio is not an indication of good performance, but a low ratio is a sign of performance that can be improved.

Run the script hitratio script to get the buffer cache hit ratio.

Thanks

Hit Ratio Script

2009-07-01T05:15:00.002+05:30

Execute the following script for the various hit ratio

column Ratio format a30
column Value format a5
TTITLE '******Hit Ratio Report*****'
BTITLE '*****End of Report*****'

SELECT cur.inst_id, 'Buffer Cache Hit Ratio ' "Ratio", to_char(ROUND((1-(phy.value / (cur.value + con.value)))*100,2)) "Value"
FROM gv$sysstat cur, gv$sysstat con, gv$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
and phy.inst_id=1
and cur.inst_id=1
and con.inst_id=1
union all
SELECT cur.inst_id,'Buffer Cache Hit Ratio ' "Ratio", to_char(ROUND((1-(phy.value / (cur.value + con.value)))*100,2)) "Buffer Cache Hit Ratio"
FROM gv$sysstat cur, gv$sysstat con, gv$sysstat phy
WHERE cur.name = 'db block gets'
AND con.name = 'consistent gets'
AND phy.name = 'physical reads'
and phy.inst_id=2
and cur.inst_id=2
and con.inst_id=2
union
SELECT inst_id, 'Library Cache Hit Ratio ' "Ratio", to_char(Round(sum(pins) / (sum(pins)+sum(reloads)) * 100,2)) "Library Cache Hit Ratio"
FROM gv$librarycache group by inst_id
union
SELECT inst_id,'Dictionary Cache Hit Ratio ' "Ratio", to_char(ROUND ((1 - (SUM (getmisses) / SUM (gets))) * 100, 2)) "Percentage"
FROM gv$rowcache group by inst_id
union
Select inst_id, 'Get Hit Ratio ' "Ratio",to_char(round((sum(GETHITRATIO))*100,2)) "Get Hit"--, round((sum(PINHITRATIO))*100,2)"Pin Hit"
FROM GV$librarycache
where namespace in ('SQL AREA')
group by inst_id
union
Select inst_id, 'Pin Hit Ratio ' "Ratio", to_char(round((sum(PINHITRATIO))*100,2))"Pin Hit"
FROM GV$librarycache
where namespace in ('SQL AREA')
group by inst_id
union
select a.inst_id,'Soft-Parse Ratio ' "Ratio", to_char(round(100 * ((a.value - b.value) / a.value ),2)) "Soft-Parse Ratio"
from (select inst_id,value from gv$sysstat where name like 'parse count (total)') a,
(select inst_id, value from gv$sysstat where name like 'parse count (hard)') b
where a.inst_id = b.inst_id
union
select a.inst_id,'Execute Parse Ratio ' "Ratio", to_char(round(100 - ((a.value / b.value)* 100),2)) "Execute Parse Ratio"
from (Select inst_id, value from gv$sysstat where name like 'parse count (total)') a,
(select inst_id, value from gv$sysstat where name like 'execute count') b
where a.inst_id = b.inst_id
union
select a.inst_id,'Parse CPU to Elapsed Ratio ' "Ratio", to_char(round((a.value / b.value)* 100,2)) "Parse CPU to Elapsed Ratio"
from (Select inst_id, value from gv$sysstat where name like 'parse time cpu') a,
(select inst_id, value from gv$sysstat where name like 'parse time elapsed') b
where a.inst_id = b.inst_id
union
Select a.inst_id,'Chained Row Ratio ' "Ratio", to_char(round((a.val/b.val)*100,2)) "Chained Row Ratio"
from (SELECT inst_id, SUM(value) val FROM gV$SYSSTAT WHERE name = 'table fetch continued row' group by inst_id) a,
(SELECT inst_id, SUM(value) val FROM gV$SYSSTAT WHERE name IN ('table scan rows gotten', 'table fetch by rowid') group by inst_id) b
where a.inst_id = b.inst_id
union
Select inst_id,'Latch Hit Ratio ' "Ratio", to_char(round(((sum(gets) - sum(misses))/sum(gets))*100,2)) "Latch Hit Ratio"
from gv$latch
group by inst_id
/* Available from 10g
union
select inst_id, metric_name, to_char(value)
from gv$sysmetric
where metric_name in ( 'Database Wait Time Ratio', 'Database CPU Time Ratio')
and intsize_csec = (select max(intsize_csec) from gv$sysmetric)
order by inst_id
*/
/

Thanks

AWR Basic Info

2009-07-01T00:55:00.003+05:30

By default the Automatic Workload Repository (AWR) is populated every hour.

Tablespace: SYSAUX (Data is stored in this tablespace)
Background Process : MMON (Flushes data from Memory to Disk)

To find the space used by AWR query the V$SYSAUX_OCCUPANTS view.

SQL> Select * From V$SYSAUX_OCCUPANTS where occupant_name like '%AWR%';

The space_usage_kbytes column gives the amount of space used by AWR in kilobytes.

To find the oldest AWR information

SQL> Select dbms_stats.get_stats_history_availability From dual;

To find the retention period for AWR. Default is 7 days.

SQL> Select dbms_stats.get_stats_history_retention From dual;

To change the retention period for AWR

SQL> Exec dbms_stats.alter_stats_history_retention(10);

Thanks

Basic database Info

2009-07-01T00:47:00.002+05:30

To get the basic database information such as the database name, dbid, creation time, archiving information etc query V$DATABASE view.

SQL> Select * From V$DATABASE;

To view the oracle version query V$VERSION view.

SQL> Select * From V$VERSION;

DBA Views

2009-07-01T00:43:00.001+05:30

To get the underlying objects that make up the DBA_ views query the DBA_VIEWS view.

SQL> Select view_name, text From DBA_VIEWS where view_name like 'DBA%';

Thanks

V$ Views

2009-07-01T00:20:00.003+05:30

To get the list of all the V$ or GV$ Views, query the V$FIXED_TABLE view.

SQL> Select name From V$FIXED_TABLE where name like 'V%';

The difference between V$ and GV$ is GV$ includes inst_id column which is used in RAC instances.

V$ views are created from the X$ tables. To know the details of the underlying X$ tables from which the v$ views are created, query the V$_FIXED_VIEW_DEFINITION view.

SQL> Select * From V$FIXED_VIEW_DEFINITION where view_name like 'GV$SESSION';

Thanks

Top 10 Memory Abusers

2009-06-18T03:44:00.002+05:30

The following query will show the top 10 SQL statements using memory as a percentage of the entire system.

SQL> select sum(pct_bufgets)
from ( select rank() over ( order by buffer_gets desc ) as rank_bufgets,
to_char(100 * ratio_to_report(buffer_gets) over (), '999.99') pct_bufgets
from v$sqlarea )
where rank_bufgets < 11;

If the percentage is

< 5% then good performance
5 - 20% then average performance
> 25% then bad performance

Thanks

Top 10 Disk-Read Abusers

2009-06-18T03:12:00.002+05:30

The following query will show the top 10 heaviest disk reading statements as a percentage of the entire system.

SQL> select sum(pct_bufgets)
from ( select rank() over ( order by disk_reads desc ) as rank_bufgets,
to_char(100 * ratio_to_report(disk_reads) over (), '999.99') pct_bufgets
from v$sqlarea )
where rank_bufgets < 11;

If the percentage is

< 5% then good performance
5 - 20% then average performance
>25% then bad performance

Thanks

Segment Shrink Restrictions

2009-06-16T00:51:00.001+05:30

You cannot shrink:

• UNDO segments
• Temporary segments
• Clustered tables
• Tables with a colmn of datatype LONG
• LOB indexes
• IOT mapping tables and IOT overflow segments
• Tables with MVIEWS with ON COMMIT
• Tables with MVIEWS which are based on ROWIDs

Thanks

Segment Shrinking

2009-06-16T00:41:00.003+05:30

Starting with ORACLE 10gR1 we can use a new feature for adjusting the high watermark, it is called segment shrinking and is only possible for segments which use Automatic Segment Space Management and locally managed.

Steps to perform segment shrink.

Step 1: Enable Row movement for the segment

SQL> ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

Step 2: Recover space and amend the high water mark (HWM).

SQL> ALTER TABLE scott.emp SHRINK SPACE;

Step 2a: Recover space, but don't amend the high water mark (HWM).

SQL> ALTER TABLE scott.emp SHRINK SPACE COMPACT;

Step 3: Recover space for the object and all dependant objects.

SQL> ALTER TABLE scott.emp SHRINK SPACE CASCADE;

Note: The COMPACT option allows the shrink operation to be broken into two stages. First the rows are moved using the COMPACT option but the HWM is not adjusted so no parsed SQL statements are invalidated. The HWM can be adjusted at a later date by reissuing the statement without the COMPACT option. At this point any dependant SQL statements will need to be reparsed.

Thanks

ADDM Views

2009-06-16T00:26:00.002+05:30

The following views are useful for ADDM analysis.

DBA_ADVISOR_TASKS - Basic information about existing tasks
DBA_ADVISOR_LOG - Status information about existing tasks
DBA_ADVISOR_FINDINGS - Findings identified for an existing tasks
DBA_ADVISOR_RECOMMENDATIONS – Reco for problems identified by tasks
DBA_ADVISOR_ACTIONS - Actions to be taken for the existing tasks
DBA_ADVISOR_RATIONALE - Rationale for ADDM recommendations.

Thanks

ADDM Findings, Recos and Actions

2009-06-16T00:21:00.002+05:30

Query to find ADDM Findings, Recommendations and Actions

SQL> Select a.execution_end, b.type, b.impact, d.rank, d.type,
'Message:'b.message MESSAGE,
'Command To correct:'c.command COMMAND,
'Action Message:'c.message ACTION_MESSAGE
From dba_advisor_tasks a, dba_advisor_findings b,Dba_advisor_actions c, dba_advisor_recommendations d
Where a.owner=b.owner
and a.task_id=b.task_id
And b.task_id=d.task_id
and b.finding_id=d.finding_id
And a.task_id=c.task_id
and d.rec_id=c.rec_Id
And a.task_name like 'ADDM%'
and a.status='COMPLETED'
and to_char(execution_end,'dd/mm/yyyy')='12/06/2009'
Order by 3 desc;

Thanks

OEM dbconsole Start Failed

2009-05-28T21:00:00.002+05:30

Problem The dbconsole was working fine before the timezone setting (DST) was changed, but after the timezone was changed it stopped working.

solution When OEM dbconsole is started the agent timezone setting is recorded in the emd.properties. Everytime you start the dbconsole it will check the current timezone setting with the value in the emd.properties. When the two values mismatch the dbconsole will not start. We have to update the setting for the agent timezone.

To update follow the steps given below.

Check the timezone setting in /etc/environment.
The setting for TZ must be one of the supported timezone setting listed in the ORACLE_HOME/sysman/admin/supportedtzs.lst file

$ export ORACLE_SID=gcprod1
$ emctl config agent updateTZ
The output could be a different setting from the TZ in /etc/environment.

$ emctl stop dbconsole

$ emctl config agent updateTZ

$ emctl start dbconsole

Thanks
Vinod D

Bugs in Temp segments in RAC

2009-05-06T03:46:00.001+05:30

Bug 2934117 : ORA-1652:Can’t acquire free extents owned by other node.The scripts fail as a result of the error

Solution: Do not use default temporary tablespace for a RAC configuration.
Workaround
Step1: Create a small temporary tablespace def_temp.
Step2: Set it as default temporary tablespace.
Step3: Create other normal temporary tablespace temp.
Step4: For all users change the temporary tablespace to temp.
Step5: Shutdown immediate all instances.
Step6: Startup all instances
Step7: when new users are created set the temporary tablespace as temp.

Bug 2858082 : Even if the free extents are available, ORA-1652 occurs in Alert Log on RAC. The scripts do not fail but errors appear inside alert.log of RAC instances.

In this bug although scripts that need Temp tablespace space due to Sort Operations (order by, group by, join, etc) are successfull we may see this error message inside alert.log of RAC instances.

When a RAC instance fills its own sort pool of extents, instead of allocation new extents it may ask for free temp extents from the other RAC instances. During this operation the ORA-1652 may appear inside the alert.log although the operation has succeeded.

Solution: You may ignore this error.

Thanks

ORA-01652 (RAC)

2009-05-06T03:31:00.003+05:30

ORA-01652 Unable to extend temp segment by XX in tablespace tbs.

In an RAC environment this error occurs in the following situation

Out of space in temporary tablespace.

SQL> select sum(free_blocks) from gv$sort_segment where tablespace_name = '';

If the query returns 0 then increase the size of the tablespace.

Error occurs even though space is available in the tablespace

This situation occurs when we are requesting the sort segments from another instance. This should be considered as a warning to the DBA that there is instance contention for temporary tablespace.

Use the following query to monitor the instance wise temp space usage.

SQL> select inst_id, tablespace_name, total_blocks, used_blocks, free_blocks from gv$sort_segment;

Unlike non RAC environment, where the statement fails when this error occurs, the sort operations in RAC is successful.

Thanks

ORA-01652 (Non RAC)

2009-05-06T03:10:00.003+05:30

ORA-01652 Unable to extend temp segment by XX in tablespace tbs.

The space in the temporary tablespace is not enough to allocate for the temp segment.

A temp segment, in the temporary tablespace, is not always used for sorting. It is sometimes used for creating objects such as index, CTAS etc. At the end of the creation these temp segments are converted in to the real objects. Hence rebuilding/creating a large index requires large temp segments thereby resulting in ORA-01652.

Solution: Increase the size of the temp tablespace by using the statement

SQL> Alter tablespace add tempfile ‘path’ size ‘size’;

Thanks

Monitor Temporary Tablespace

2009-04-10T02:30:00.005+05:30

To monitor the temporary Segment Usage

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
( SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size ) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

To monitor the temporary storage usage per session

SELECT S.sid , S.serial#, S.username, S.osuser, P.spid,
S.module, P.program, SUM (T.blocks) * TBS.block_size / 1024 / 1024 mb_used,
T.tablespace, COUNT(*) statements
FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
WHERE T.session_addr = S.saddr
AND S.paddr = P.addr
AND T.tablespace = TBS.tablespace_name
GROUP BY S.sid, S.serial#, S.username, S.osuser, P.spid, S.module,
P.program, TBS.block_size, T.tablespace
ORDER BY sid, serial#;

To monitor the Temporary storage usage per statement

SELECT S.sid , S.serial#, S.username, Q.hash_value, Q.sql_text,
T.blocks * TBS.block_size / 1024 / 1024 mb_used, T.tablespace
FROM v$sort_usage T, v$session S, v$sqlarea Q, dba_tablespaces TBS
WHERE T.session_addr = S.saddrAND T.sqladdr = Q.address
AND T.tablespace = TBS.tablespace_name
ORDER BY S.sid;

Thanks

Switching Undo Tablepace in RAC

2009-04-09T05:02:00.000+05:30

Switching undo tablespace is similar to single instance. If you want to switch the tablespace for instance 1, then issue the following statement from that instance

SQL> Alter system set undo_tablespace = undotbs10 scope = both;

At the time of the switch, the instances maintain control of both the old and new undo tablespaces.The old undo tablespace is marked as pending-offline until all the transactions using it are completed.

Thanks

Undo Tablespace in RAC

2009-04-09T04:59:00.002+05:30

In RAC there must be one UNDO tablepsace for each instance. The entry in pfile must be (for 2 node instance prod1 and prod2)

prod1.undo_tablespace=UNDOTBS1

prod2.undo_tablespace=UNDOTBS2

Each instance can use only one undo tablespace at a time (store undo information for that instance only).

All instances can read block from any or all undo tablespaces for the purpose of Read consistency.

Undo management mode must be same for all instances. Either Auto or Manual.

Thanks

Undo Advisor

2009-04-09T01:38:00.002+05:30

Undo Advisor is used to do the following,

1. Set the low threshold value for undo retention.

2. Estimate the size of undo tablespace you'll need for various undo retention setting.

3. Use different analysis time periods to get the recommendations, in the form of graph, about the right undo tablespace size for varying undo retention length.

Thanks

Tuning Undo - Part 2

2009-04-09T01:18:00.004+05:30

The V$UNDOSTAT provides useful information in tuning the undo tablespace.

The V$UNDOSTAT view holds undo statistics for 10 minute intervals. This view represents statistics across instances, thus each begin time, end time, and statistics value will be a unique interval per instance.

Important columns

UNXPSTEALCNT - The number of attempts when unexpired blocks were stolen from other undo segments to satisfy space requests.

UNXPBLKRELCNT - The number of unexpired blocks removed from undo segments to be used by other transactions

UNXPBLKREUCNT - The number of unexpired undo blocks reused by transactions.

EXPSTEALCNT - The number of attempts when expired extents were stolen from other undo segments to satisfy a space requests

EXPBLKRELCNT - The number of expired extents stolen from other undo segments to satisfy a space request

EXPBLKREUCNT - The number of expired undo blocks reused within the same undo segments

SSOLDERRCNT - The number of ORA-1555 errors that occurred during the interval

NOSPACEERRCNT - The number of Out-of-Space errors

Inference:

1. When the columns UNXPSTEALCNT through EXPBLKREUCNT hold non-zero values, it is an indication of space pressure.

2. If the column SSOLDERRCNT is non-zero, then UNDO_RETENTION is not properly set.

3. If the column NOSPACEERRCNT is non-zero, then there is a serious space problem.

Thanks

Tuning Undo - Part 1

2009-04-09T01:09:00.003+05:30

Execute the following query,

SELECT inst_id, to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time, UNXPSTEALCNT "Unexpired Stolen", EXPSTEALCNT "Expired Reused",
SSOLDERRCNT "Snapshot err", NOSPACEERRCNT "Out of space err",
MAXQUERYLEN, tuned_undoretention
FROM gv$undostat
WHERE begin_time between to_date('','MM/DD/YYYY HH24:MI:SS')and to_date('','MM/DD/YYYY HH24:MI:SS')
ORDER BY inst_id, begin_time;

Problem 1: Out of space error and unexpired extents are stolen
Solution : Increase the undo Tablespace size.

Problem 2: Snapshot error and Expired extents are reused
Solution: Increase the undo Retention

Thanks

Undo Extents

2009-04-09T00:53:00.002+05:30

In Undo Segments there are three types of extents, they are

Unexpired Extents - Undo data whose age is less than the undo retention period.
Expired Extents - Undo data whose age is greater than the undo retention period.
Active Extents - Undo data that is part of the active transaction.

The sequence for using extents is as follows,

1. A new extent will be allocated from the undo tablespace when the requirement arises. As undo is being written to an undo segment, if the undo reaches the end of the current extent and the next extent contains expired undo then the new undo (generated by the current transaction) will wrap into that expired extent, in preference to grabbing a free extent from the undo tablespace free extent pool.

2. If this fails because of no available free extents and we cannot autoextend the datafile, then Oracle tries to steal an expired extent from another undo segment.

3. If it still fails because there are no extents with expired status then Oracle tries to reuse an unexpired extent from the current undo segment.

4. If even that fails, Oracle tries to steal an unexpired extent from another undo segment.

5. If all the above fails, an Out-Of-Space error will be reported.

Thanks