Thursday, October 20, 2011

Roll Foward a Physical Standby on 11gR2

Previous posts showed how to do roll forward 10gR2 and 11gR1 physical standby databases.
This shows how to roll forward a 11gR2 physical standby that's in RAC configuration setup earlier.

In this case archive log files were deleted on primary and there's no other way to bring the standby up to date with the primary.
Current dataguard configuration is
DGMGRL> show configuration

Configuration - rac11g2_dgb

  Protection Mode: MaxPerformance
  Databases:
    rac11g2  - Primary database
    rac11g2s - Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
SUCCESS
1. Identify the archive gap
SQL> select * from v$archive_gap;

   THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
         2           901            901
2. Stop all but one standby RAC instances
srvctl stop instance -d rac11g2s -i rac11g2s2
3. Stop log apply on the standby
DGMGRL> edit database rac11g2s set state='APPLY-OFF';
Succeeded.
4. Find the current SCN on the standby
SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
   45716408
5. Using the SCN identified in the above step create an incrimental backup in the primary database
RMAN> BACKUP INCREMENTAL FROM SCN 45716408 DATABASE FORMAT '/home/oracle/forstandby%U' tag 'forstandby';

Starting backup at 20-OCT-11

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=46 instance=rac11g21 device type=DISK
backup will be obsolete on date 27-OCT-11
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00002 name=+DATA/rac11g2/datafile/sysaux.257.740770047
input datafile file number=00001 name=+DATA/rac11g2/datafile/system.256.740770045
input datafile file number=00006 name=+DATA/rac11g2/datafile/undotbs3.276.745423577
input datafile file number=00005 name=+DATA/rac11g2/datafile/undotbs2.264.740770355
input datafile file number=00003 name=+DATA/rac11g2/datafile/abc.280.755611691
input datafile file number=00004 name=+DATA/rac11g2/datafile/users.259.740770049
channel ORA_DISK_1: starting piece 1 at 20-OCT-11
channel ORA_DISK_1: finished piece 1 at 20-OCT-11
piece handle=/home/oracle/forstandby2fmpj6s7_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35

using channel ORA_DISK_1
backup will be obsolete on date 27-OCT-11
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 20-OCT-11
channel ORA_DISK_1: finished piece 1 at 20-OCT-11
piece handle=/home/oracle/forstandby2gmpj6tb_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 20-OCT-11
6. Copy the created backup files to standby using scp, ftp or any other file transfer utility.

7. Catalog backup files on standby
RMAN> catalog start with '/home/oracle/forstand';

using target database control file instead of recovery catalog
searching for all files that match the pattern /home/oracle/forstand

List of Files Unknown to the Database
=====================================
File Name: /home/oracle/forstandby2fmpj6s7_1_1
File Name: /home/oracle/forstandby2gmpj6tb_1_1

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /home/oracle/forstandby2fmpj6s7_1_1
File Name: /home/oracle/forstandby2gmpj6tb_1_1
8. Data guard concept and administration guide states "Connect to the standby database as the RMAN target and execute the REPORT SCHEMA statement to ensure that the standby database site is automatically registered and that the files names at the standby site are displayed". Doesn't say registered in what, but safe to assume talking about the recovery catalog. In this case recovery catalog is not used but run the command and make a note of the values as these will be used later on.
RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name RAC11G2S

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    720      SYSTEM               ***     +DATA/rac11g2s/datafile/system.258.754586581
2    1290     SYSAUX               ***     +DATA/rac11g2s/datafile/sysaux.259.754586467
3    10       ABC                  ***     +DATA/rac11g2s/datafile/abc.287.755615113
4    5        USERS                ***     +DATA/rac11g2s/datafile/users.276.754586727
5    150      UNDOTBS2             ***     +DATA/rac11g2s/datafile/undotbs2.261.754586713
6    600      UNDOTBS3             ***     +DATA/rac11g2s/datafile/undotbs3.262.754586657

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       +DATA/rac11g2s/tempfile/temp.288.755615251
9. Restore the standby controlfile using the backup file (shown in bold on the backup output earlier) and mount the database
RMAN> restore standby controlfile from '/home/oracle/forstandby2gmpj6tb_1_1';

Starting restore at 20-OCT-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=17 instance=rac11g2s1 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:05
output file name=+DATA/rac11g2s/controlfile/current.257.754586439
output file name=+FLASH/rac11g2s/controlfile/current.482.754586439
Finished restore at 20-OCT-11

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1
10. Run a report schema again and see that datafile names reported are the ones of the primary databae
RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name RAC11G2S

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    0        SYSTEM               ***     +DATA/rac11g2s/datafile/system.256.740770045
2    0        SYSAUX               ***     +DATA/rac11g2s/datafile/sysaux.257.740770047
3    0        ABC                  ***     +DATA/rac11g2s/datafile/abc.280.755611691
4    0        USERS                ***     +DATA/rac11g2s/datafile/users.259.740770049
5    0        UNDOTBS2             ***     +DATA/rac11g2s/datafile/undotbs2.264.740770355
6    0        UNDOTBS3             ***     +DATA/rac11g2s/datafile/undotbs3.276.745423577

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       +DATA/rac11g2s/tempfile/temp.263.740770177
11. Catalog the datafiles to the original standby datafile names
RMAN> catalog start with '+DATA/rac11g2s/datafile';

searching for all files that match the pattern +DATA/rac11g2s/datafile

List of Files Unknown to the Database
=====================================
File Name: +data/RAC11G2S/DATAFILE/SYSAUX.259.754586467
File Name: +data/RAC11G2S/DATAFILE/SYSTEM.258.754586581
File Name: +data/RAC11G2S/DATAFILE/UNDOTBS3.262.754586657
File Name: +data/RAC11G2S/DATAFILE/UNDOTBS2.261.754586713
File Name: +data/RAC11G2S/DATAFILE/USERS.276.754586727
File Name: +data/RAC11G2S/DATAFILE/ABC.287.755615113

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: +data/RAC11G2S/DATAFILE/SYSAUX.259.754586467
File Name: +data/RAC11G2S/DATAFILE/SYSTEM.258.754586581
File Name: +data/RAC11G2S/DATAFILE/UNDOTBS3.262.754586657
File Name: +data/RAC11G2S/DATAFILE/UNDOTBS2.261.754586713
File Name: +data/RAC11G2S/DATAFILE/USERS.276.754586727
File Name: +data/RAC11G2S/DATAFILE/ABC.287.755615113
12. Run switch database to copy to update the control file with the changes
RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DATA/rac11g2s/datafile/system.258.754586581"
datafile 2 switched to datafile copy "+DATA/rac11g2s/datafile/sysaux.259.754586467"
datafile 3 switched to datafile copy "+DATA/rac11g2s/datafile/abc.287.755615113"
datafile 4 switched to datafile copy "+DATA/rac11g2s/datafile/users.276.754586727"
datafile 5 switched to datafile copy "+DATA/rac11g2s/datafile/undotbs2.261.754586713"
datafile 6 switched to datafile copy "+DATA/rac11g2s/datafile/undotbs3.262.754586657"
13. Recover the database with no redo
RMAN> recover database noredo;

Starting recover at 20-OCT-11
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATA/rac11g2s/datafile/system.258.754586581
destination for restore of datafile 00002: +DATA/rac11g2s/datafile/sysaux.259.754586467
destination for restore of datafile 00003: +DATA/rac11g2s/datafile/abc.287.755615113
destination for restore of datafile 00004: +DATA/rac11g2s/datafile/users.276.754586727
destination for restore of datafile 00005: +DATA/rac11g2s/datafile/undotbs2.261.754586713
destination for restore of datafile 00006: +DATA/rac11g2s/datafile/undotbs3.262.754586657
channel ORA_DISK_1: reading from backup piece /home/oracle/forstandby2fmpj6s7_1_1
channel ORA_DISK_1: piece handle=/home/oracle/forstandby2fmpj6s7_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07

Finished recover at 20-OCT-11
14. At this stage should have cleared the log files as in step 10 herebut still without clearing the old logs when log apply is enabled new log files will be created automatically. Old log files will remain in the ASM taking up space and could be manually removed using asmcmd rm.

15. Enable log apply on the standby and verify log files are getting applied
DGMGRL> edit database rac11g2s set state='APPLY-ON';

   THREAD#  SEQUENCE# APPLIED
---------- ---------- ---------
         2        907 YES
         2        908 YES
         2        909 YES
         2        910 YES
         2        910 YES


Friday, October 14, 2011

Upgrading from 11.2.0.2 to 11.2.0.3

RAC Databases with data guard configuration see
Upgrading from 11.2.0.2 to 11.2.0.3 with Physical Standby - 1 (Standby site upgrade)
Upgrading from 11.2.0.2 to 11.2.0.3 with Physical Standby - 2 (Primary site upgrade)

Upgrading from 11.2.0.2 to 11.2.0.3 is not much different from upgrading from 11.2.0.1 to 11.2.0.2 (part 2). Same as before (with upgrading to 11.2.0.2)  there's some pre-reqs that need to be done. Continuing the tradition started with 11gR2 Oracle doesn't give a single read me file with all the necessary information for upgrade. One has to read several user guides and metalink notes to get the full picture. Below are some extracts taken from upgrade guide, asm guide and grid infrastructure installation guide. 

Oracle Clusterware Upgrade Configuration Force Feature
If nodes become unreachable in the middle of an upgrade, starting with release 11.2.0.3, you can run the rootupgrade.sh script with the -force flag to force an upgrade to complete.


Starting with Oracle Grid Infrastructure 11g release 2 (11.2.0.3) and later, you can use the CVU healthcheck command option to check your Oracle Clusterware and Oracle Database installations for their compliance with mandatory requirements and best practices guidelines, and to check to ensure that they are functioning properly.

Known Issue with the Deinstallation Tool for This Release
Cause: After upgrading from 11.2.0.1 or 11.2.0.2 to 11.2.0.3, deinstallation of the Oracle home in the earlier release of Oracle Database may result in the deletion of the old Oracle base that was associated with it. This may also result in the deletion of data files, audit files, etc., which are stored under the old Oracle base.
Action: Before deinstalling the Oracle home in the earlier release, edit the orabase_cleanup.lst file found in the $Oracle_Home/utl directory and remove the "oradata" and "admin" entries. Then, deinstall the Oracle home using the 11.2.0.3 deinstallation tool.


To upgrade existing Oracle Grid Infrastructure installations from 11.2.0.2 to a later release, you must apply patch 11.2.0.2.1 (11.2.0.2 PSU 1) or later.
 To upgrade existing 11.2.0.1 Oracle Grid Infrastructure installations to Oracle Grid Infrastructure to any later version (11.2.0.2 or 11.2.0.3), you must patch the release 11.2.0.1 Oracle Grid Infrastructure home  (11.2.0.1.0) with the 9706490 patch.

To upgrade existing 11.1 Oracle Clusterware installations to Oracle Grid Infrastructure 11.2.0.3 or later, you must patch the release 11.1 Oracle Clusterware home with the patch for bug 7308467.

Oracle recommends that you leave Oracle RAC instances running. When you start the root script on each node, that node's instances are shut down and then started up again by the rootupgrade.sh script. If you upgrade from release 11.2.0.1 to any later version (11.2.0.2 or 11.2.0.3), then all nodes are selected by
default. You cannot select or de-select the nodes. For single instance Oracle Databases on the cluster, only those that use Oracle ASM need to be shut down. Listeners do not need to be shut down.


From metalink notes


Actions For DST Updates When Upgrading To Or Applying The 11.2.0.3 Patchset [ID 1358166.1]

If your current RDBMS time timezone version is 14 , install 11.2.0.3 in a new home and update the 11.2.0.2 or 11.2.0.1 database to 11.2.0.2.
You can skip any DST related sections in the patchset documentation , there is no need to apply DST patches or check for DST issues for the update to 11.2.0.3

If your current RDBMS time timezone version is  lower than 14 ,  install 11.2.0.3 in a new home and update the 11.2.0.2 or 11.2.0.1 database to 11.2.0.3
You can skip any DST related sections in the patchset documentation , there is no need to apply DST patches or check for DST issues for the update to 11.2.0.3

If your current RDBMS time timezone version is higher than 14, then there's some work to be done read the above metalink note.


In this case database was 11.2.0.2
SQL> SELECT version FROM v$timezone_file;

   VERSION
----------
        14
Before starting the upgrade using cluster verification tool it's now possible to validate the readiness to upgrade.
./runcluvfy.sh stage -pre crsinst -upgrade -n hpc3 -rolling -src_crshome /flash/11.2.0/grid11.2.0.2 -dest_crshome /flash/11.2.0/grid11.2.0.3 -dest_version 11.2.0.3.0 -fixup -fixupdir /home/oracle/fixupscript -verbose
Prior to upgrading to 11.2.0.3 a patch should be applied to 11.2.0.2 (similar to 11.2.0.2) only difference is this patch number wasn't mentioned in any of the documents referenced (which means some more reading is required to get the complete picture) but luckily the pre-req check flags this.



 Applying patch 12539000
The patch itself has some issues when it comes to applying.
1. Applying using opatch auto, which is suppse to apply to both GI Home and Oracle Home only applied to Oracle Home.
2. Read me says to run the /12539000/custom/server/12539000/custom/scripts/prepatch.sh but this directory structure and the file is missing.
3. So finally had to apply the patch with opatch auto but one home at a time. (opatch auto PATH_TO_PATCH_DIRECTORY -oh GI_HOME or OH

Another issue was shmmni was flaged as not configured (this already had an upgrade to 11.2.0.2 and value was set from the begining). Running the fixup script gives the output below which doesn't modify but makes the warning disappear
# /tmp/CVU_11.2.0.3.0_oracle/runfixup.sh
Response file being used is :/tmp/CVU_11.2.0.3.0_oracle/fixup.response
Enable file being used is :/tmp/CVU_11.2.0.3.0_oracle/fixup.enable
Log file location: /tmp/CVU_11.2.0.3.0_oracle/orarun.log
Setting Kernel Parameters...
The value for shmmni in response file is not greater than value of shmmni for current session. Hence not changing it.

Apart from these upgrade is straightforward.

By default all the nodes will be selected for upgrade (this cluster only has one node). Oracle recommends to upgrade both GI and ASM at the same time. This option has been selected for this upgrade.

SCAN IP is set on the /etc/hosts but the pre-req checks if this IP could be resolved using nslookup and flags a warning when cannot. This could be ignored and proceeded. Metalink note 1212703.1 mentions the multicast requirements. In this upgrade all these were ignored (on production environments should resolve these issues before proceeding)



When prompted run the rootupgrade.
# /flash/11.2.0/grid11.2.0.3/rootupgrade.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /flash/11.2.0/grid11.2.0.3

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Using configuration parameter file: /flash/11.2.0/grid11.2.0.3/crs/install/crsconfig_params
Creating trace directory
User ignored Prerequisites during installation

ASM upgrade has started on first node.

CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'hpc3'
CRS-2673: Attempting to stop 'ora.crsd' on 'hpc3'
CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'hpc3'
CRS-2673: Attempting to stop 'ora.CLUSTERDG.dg' on 'hpc3'
CRS-2673: Attempting to stop 'ora.registry.acfs' on 'hpc3'
CRS-2673: Attempting to stop 'ora.clusdb.db' on 'hpc3'
CRS-2673: Attempting to stop 'ora.LISTENER_SCAN1.lsnr' on 'hpc3'
CRS-2673: Attempting to stop 'ora.oc4j' on 'hpc3'
CRS-2673: Attempting to stop 'ora.LISTENER.lsnr' on 'hpc3'
CRS-2673: Attempting to stop 'ora.cvu' on 'hpc3'
CRS-2677: Stop of 'ora.LISTENER.lsnr' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.hpc3.vip' on 'hpc3'
CRS-2677: Stop of 'ora.LISTENER_SCAN1.lsnr' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.scan1.vip' on 'hpc3'
CRS-2677: Stop of 'ora.scan1.vip' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.hpc3.vip' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.registry.acfs' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.clusdb.db' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.DATA.dg' on 'hpc3'
CRS-2673: Attempting to stop 'ora.FLASH.dg' on 'hpc3'
CRS-2677: Stop of 'ora.DATA.dg' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.FLASH.dg' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.oc4j' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.cvu' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.CLUSTERDG.dg' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.asm' on 'hpc3'
CRS-2677: Stop of 'ora.asm' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.ons' on 'hpc3'
CRS-2677: Stop of 'ora.ons' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.net1.network' on 'hpc3'
CRS-2677: Stop of 'ora.net1.network' on 'hpc3' succeeded
CRS-2792: Shutdown of Cluster Ready Services-managed resources on 'hpc3' has completed
CRS-2677: Stop of 'ora.crsd' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.ctssd' on 'hpc3'
CRS-2673: Attempting to stop 'ora.evmd' on 'hpc3'
CRS-2673: Attempting to stop 'ora.asm' on 'hpc3'
CRS-2673: Attempting to stop 'ora.drivers.acfs' on 'hpc3'
CRS-2673: Attempting to stop 'ora.mdnsd' on 'hpc3'
CRS-2677: Stop of 'ora.asm' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.cluster_interconnect.haip' on 'hpc3'
CRS-2677: Stop of 'ora.drivers.acfs' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.evmd' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.cluster_interconnect.haip' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.mdnsd' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.ctssd' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.cssd' on 'hpc3'
CRS-2677: Stop of 'ora.cssd' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.diskmon' on 'hpc3'
CRS-2673: Attempting to stop 'ora.crf' on 'hpc3'
CRS-2677: Stop of 'ora.diskmon' on 'hpc3' succeeded
CRS-2677: Stop of 'ora.crf' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.gipcd' on 'hpc3'
CRS-2677: Stop of 'ora.gipcd' on 'hpc3' succeeded
CRS-2673: Attempting to stop 'ora.gpnpd' on 'hpc3'
CRS-2677: Stop of 'ora.gpnpd' on 'hpc3' succeeded
CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'hpc3' has completed
CRS-4133: Oracle High Availability Services has been stopped.
OLR initialization - successful
Replacing Clusterware entries in inittab
clscfg: EXISTING configuration version 5 detected.
clscfg: version 5 is 11g Release 2.
Successfully accumulated necessary OCR keys.
Creating OCR keys for user 'root', privgrp 'root'..
Operation successful.
Started to upgrade the Oracle Clusterware. This operation may take a few minutes.
Started to upgrade the CSS.
Started to upgrade the CRS.
The CRS was successfully upgraded.
Oracle Clusterware operating version was successfully set to 11.2.0.3.0

ASM upgrade has finished on last node.

PRKO-2116 : OC4J is already enabled
Configure Oracle Grid Infrastructure for a Cluster ... succeeded
Check the versions
crsctl query crs activeversion
Oracle Clusterware active version on the cluster is [11.2.0.3.0]

crsctl query crs softwareversion
Oracle Clusterware version on node [hpc3] is [11.2.0.3.0]

crsctl query crs releaseversion
Oracle High Availability Services release version on the local node is [11.2.0.3.0]
As mentioned in the begining of this blog cluvfy could be used to verify the compliance of clusterware and database.
./cluvfy comp healthcheck -collect cluster -bestpractice -deviations -html -save -savedir /home/oracle
This command will create a html file in /home/oracle listing the findings of the commands. There are serveral other options related to this and could use to verify the database as well. To verify the database must first run the $GI_HOME/cv/admin/cvusys.sql script to create the necessary user.

After rootupgrade continue rest of the configuration

This concludes the GI upgrade. Next is to upgrade the Oracle Home and the database. As with 11.2.0.2 upgrade this is done as an out of place upgrade. Both software and database is upgraded at the same time.


When prompted run the root.sh
/opt/app/oracle/product/11.2.0/clusdb_3/root.sh
Performing root user operation for Oracle 11g

The following environment variables are set as:
    ORACLE_OWNER= oracle
    ORACLE_HOME=  /opt/app/oracle/product/11.2.0/clusdb_3

Enter the full pathname of the local bin directory: [/usr/local/bin]:
The contents of "dbhome" have not changed. No need to overwrite.
The contents of "oraenv" have not changed. No need to overwrite.
The contents of "coraenv" have not changed. No need to overwrite.

Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root script.
Now product-specific root actions will be performed.
Finished product-specific root actions.
Summary will be shown once the upgrade is completed.
COMP_NAME                           STATUS   VERSION
----------------------------------- -------- ------------
OWB                                 VALID    11.2.0.1.0
Oracle Application Express          VALID    3.2.1.00.10
Oracle Enterprise Manager           VALID    11.2.0.3.0
OLAP Catalog                        VALID    11.2.0.3.0
Spatial                             VALID    11.2.0.3.0
Oracle Multimedia                   VALID    11.2.0.3.0
Oracle XML Database                 VALID    11.2.0.3.0
Oracle Text                         VALID    11.2.0.3.0
Oracle Expression Filter            VALID    11.2.0.3.0
Oracle Rules Manager                VALID    11.2.0.3.0
Oracle Workspace Manager            VALID    11.2.0.3.0
Oracle Database Catalog Views       VALID    11.2.0.3.0
Oracle Database Packages and Types  VALID    11.2.0.3.0
JServer JAVA Virtual Machine        VALID    11.2.0.3.0
Oracle XDK                          VALID    11.2.0.3.0
Oracle Database Java Packages       VALID    11.2.0.3.0
OLAP Analytic Workspace             VALID    11.2.0.3.0
Oracle OLAP API                     VALID    11.2.0.3.0
Oracle Real Application Clusters    VALID    11.2.0.3.0
From patch history (using ADMon)

This concludes the upgrade to 11.2.0.3.

If automatic shared memory managment (ASMM) is used then read the post Multiple Shared Memory Segments Created by Default on 11.2.0.3

Useful metalink note
Things to Consider Before Upgrading to 11.2.0.3 Grid Infrastructure/ASM [ID 1363369.1]
Oracle Clusterware (CRS or GI) Rolling Upgrades [ID 338706.1]

Upgrading from 11.2.0.1 to 11.2.0.2 - Part 2

Metalink notes related to 11.2.0.1 to 11.2.0.2 upgrade

Complete checklist for out-of-place manual upgrade from 11.2.0.1 to 11.2.0.2 [ID 1276368.1]
Important Changes to Oracle Database Patch Sets Starting With 11.2.0.2 [ID 1189783.1]
Things to Consider Before Upgrading to 11.2.0.2 Grid Infrastructure [ID 1312225.1]
ASM 11gR2: How To Upgrade An ASM Instance To Release 11gR2 (11.2)? [ID 950200.1]
Oracle Grid Infrastructure 11.2.0.2 Installation or Upgrade may fail due to Multicasting Requirement [ID 1212703.1]
Oracle Database Upgrade Path Reference List [ID 730365.1]
Upgrade Advisor: Database from 10.2 to 11.2 [ID 251.1]
Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1]
RACcheck 11.2.0.3 Upgrade Readiness Assessment [ID 1457357.1]
Things to Consider Before Upgrading to 11.2.0.3 Grid Infrastructure/ASM [ID 1363369.1]


Wednesday, October 5, 2011

Latch Free Waits

A test development running 11gR1 (11.1.0.7) started to show high number of latch free waits during a load test.
Looking at the AWR report it could be seen that latch free waits is part of the top 5 events.
Going through the latch activity section of the AWR report it could seen Result Cache: Latch and SQL memory manager latch having the large wait times compared to other latch types.
Result Cache latch is used for controlling access to the result cache.

Used GV$RESULT_CACHE_OBJECTS to identify the objects that are being cached. In this case there was only one object which was a function. Remove the caching option from the function and ran the load test again.

In the second test the latch free waits were less and concurrency (cache buffer chains) were predominant.

Top 5 wait events shows that latch free events have decreased compared to previous time.

The high number of cache buffer chains are as a result of multiple sessions running the same sql and accessing same set of blocks (hot blocks). It is this sql that was inside the function that had used result cached feature. Since result caching was removed from the function sql was getting executed rather than result being served from the cache.

Predominant latch in the second period was cache buffer chain.

Comparison AWR report shows DB Time being reduced while DB CPU time increasing.

Wait Event comparison captures the reduction of the latch free and increase of cache buffer chain.

Latch wait time comparison


Mutex Sleep Summary in test 1.

Mutex Sleep Summary in test 2.

Comparison of the above two summaries shows during the second period mutex were obtained much quicker and far less sleeps to get the mutex.

Take home point is even though result cache is expected to reduce the load on the database by preventing the execution of the SQL and serving sessions using the cache, it could have other effects/bottlenecks/issues etc that could negate the gains.

Another event that was predominant in these test was SQL Memory Manager latch. Metalink note 9732503.8 mentions Bug 9732503 with regard to this latch in certain platforms and Oracle versions. More importantly it also states

This issue is often caused when the value of pga_aggregate_target is set too low, ie. when the statistic "over allocation count" is non zero in v$pgastat. When this is the case, increase pga_aggregate_target such that it is set to at least "maximum PGA allocated" in v$pgastat. You can find more information on tuning pga_aggregate_target in the Oracle tuning guide.

Metalink notes useful in diagnosing latch free wait events

WAITEVENT: "latch free" Reference Note [ID 34576.1]
How to Identify Which Latch is Associated with a "latch free" wait [ID 413942.1]