Monday, September 9, 2013

Upgrading RAC from 11.2.0.3 to 11.2.0.4 - Database

After upgrading GI from 11.2.0.3 to 11.2.0.4 next process in upgrading the RAC is to upgrade the database. This include upgrading the database software and database itself. In this post an out-of-place upgrade is done for the database software. It is possible to do an in-place upgrade of the database software but is not recommended by Oracle.
Refer metalink note 1562142.1 for list of bug fixes for 11.2.0.4 patch set and 11.2.0.4 section of Readme document for additional information.
Though at the time of this post not updated to reflect any information related to 11.2.0.4, contents of 837570.1 and 1276368.1 are still valid for 11.2.0.3 to 11.2.0.4 upgrade.
Out-of-place upgrade is similar to software only installation. Since the current Oracle home is not affected, this doesn't require any database down time.
Before executing the runInstaller edit the cvu_config file to fix the pdksh issue. Once done start the database software installation for the upgrade by executing runInstaller.
The new home (11.2.0.4) is dbhome_1 and previous home (11.2.0.3) location is dbhome_2 (this is the same environment used for standard to enterprise conversion).
Summary page.

Once the software installation is complete (after root.sh is run) check the ownership of the oralce binaries. Since this is an environment that uses role separation the group ownership of the oracle binaries must be asmamdin. However these correct permissions were not set during the software installation. 11.2.0.3 home's oracle binaries
[oracle@rhel6m1 11.2.0]$ ls -l dbhome_2/bin/oracle*
-rwsr-s--x. 1 oracle asmadmin 232617534 Aug 15 10:35 dbhome_2/bin/oracle
-rwsr-s--x. 1 oracle asmadmin 232399041 Aug 14 15:45 dbhome_2/bin/oracleO
11.2.0.4 home's oracle binaries
[oracle@rhel6m1 11.2.0]$ ls -l dbhome_1/bin/oracle*
-rwsr-s--x. 1 oracle oinstall 239626641 Sep  9 15:08 dbhome_1/bin/oracle
-rwxr-x---. 1 oracle oinstall         0 Aug 24 11:43 dbhome_1/bin/oracleO
Therefore manual intervention is required to set the correct permissions. Run setasmgidwrap as grid user (on all the nodes)
[grid@rhel6m1 ~]$ $GI_HOME/bin/setasmgidwrap o=/opt/app/oracle/product/11.2.0/dbhome_1/bin/oracle
[oracle@rhel6m1 11.2.0]$ ls -l dbhome_1/bin/oracle*
-rwsr-s--x. 1 oracle asmadmin 239626641 Sep  9 15:08 dbhome_1/bin/oracle
If COST is setup copy the sqlnet.ora file from the 11.2.0.3 home to 11.2.0.4 home ($ORACLE_HOME/network/admin).
This conclude the database software upgrade. The next step is to upgrade the database and DBUA was used for this process.




Metalink note 1562142.1 list timezone related upgrade requirements. In this case the timezone was 14
SQL> SELECT version FROM v$timezone_file;

VERSION
-------
14
As such it doesn't require any steps to be carried out before or after the upgrade related to timezone. If the timezone version value is other than 14 then refer above metalink note.
Output generated by running utlu112i.sql is given below.
SQL> @utlu112i.sql
Oracle Database 11.2 Pre-Upgrade Information Tool 09-09-2013 15:31:51
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name:          STD11G2
--> version:       11.2.0.3.0
--> compatible:    11.2.0.0.0
--> blocksize:     8192
--> platform:      Linux x86 64-bit
--> timezone file: V14
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 716 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 673 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 MB
.
**********************************************************************
Flashback: OFF
**********************************************************************
**********************************************************************
Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]
Note: Pre-upgrade tool was run on a lower version 64-bit database.
**********************************************************************
--> If Target Oracle is 32-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.

--> If Target Oracle is 64-Bit, refer here for Update Parameters:
-- No update parameter changes are required.
.
**********************************************************************
Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No renamed parameters found. No changes are required.
.
**********************************************************************
Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]
**********************************************************************
-- No obsolete parameters found. No changes are required
.

**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views         [upgrade]  VALID
--> Oracle Packages and Types    [upgrade]  VALID
--> JServer JAVA Virtual Machine [upgrade]  VALID
--> Oracle XDK for Java          [upgrade]  VALID
--> Real Application Clusters    [upgrade]  VALID
--> Oracle Workspace Manager     [upgrade]  VALID
--> Oracle XML Database          [upgrade]  VALID
--> Oracle Java Packages         [upgrade]  VALID
--> Oracle Ultra Search          [upgrade]
--> Expression Filter            [upgrade]  VALID
--> Rule Manager                 [upgrade]  VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> The "cluster_database" parameter is currently "TRUE"
.... and must be set to "FALSE" prior to running a manual upgrade.
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER ASANGA has 4 INVALID objects.
WARNING: --> Your recycle bin is turned on and currently contains no objects.
.... Because it is REQUIRED that the recycle bin be empty prior to upgrading
.... and your recycle bin is turned on, you may need to execute the command:
.... prior to executing your upgrade to confirm the recycle bin is empty.
.
**********************************************************************
Recommendations
**********************************************************************
Oracle recommends gathering dictionary statistics prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:

    EXECUTE dbms_stats.gather_dictionary_stats;

**********************************************************************
Oracle recommends reviewing any defined events prior to upgrading.

To view existing non-default events execute the following commands
while connected AS SYSDBA:
  Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
      WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE'

  Trace Events:
    SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
      WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'

Changes will need to be made in the init.ora or spfile.

**********************************************************************
There are no invalid components before the upgrade. Noteably this environment doesn't have a enterprise manager console setup. If this is setup then backup emconsole using emdwgrd.
Take a full backup of the database before running DBUA. Once backup is complete start DBUA from the new home. Verify that DBUA invoked is from the new home (PATH variable may contain old home's dbua).
The following warning was shown during the upgrade. This is the same warning given when a similar system was upgraded to 12c. As the database started as a standard edition database and then got converted to enterprise, it doesn't have the text component installed. Same as the earlier incident with 12c this was ignored and upgrade completed successfully.
Upgrade summary.

After the upgrade the remote_listener parameter values goes back to scan-ip:port, even though before the upgrade this was changed to include individual scan IP values while setting up COST. Modify the remote_listener again as per 1340831.1 if COST is used. If the sole reason for implementing cost is preventing remote registration then use Valid Node Checking for Registration (VNCR) instead of setting up COST. VNCR is available from 11.2.0.4 onwards.
Remove the default listener created during the GI upgrade.
Deinstall the 11.2.0.3 Oracle home or detach it from inventory and delete
./runInstaller -silent -detachHome ORACLE_HOME=/opt/app/oracle/product/11.2.0/dbhome_2
rm -rf dbhome_2
11.2.0.4 has introduced a new value 11 for the OPTIMIZER_DYNAMIC_SAMPLING parameter which automatically lets the optimizer decide whether dynamic statistics (use to be called sampling) are useful and which level to use. This feature is enabled by setting OPTIMIZER_DYNAMIC_SAMPLING to 11. See performance tuning guide for more.

This conclude the upgrade of RAC from 11.2.0.3 to 11.2.0.4.

Useful metalink notes
Complete Checklist for Manual Upgrades to 11gR2 [837570.1]
11.2.0.4 Patch Set - List of Bug Fixes by Problem Type [1562142.1]
Actions For DST Updates When Upgrading To Or Applying The 11.2.0.4 Patchset [1579838.1]
Complete checklist for out-of-place manual upgrade from previous 11.2.0.N version to the latest 11.2.0.N patchset. [1276368.1]
Things to Consider Before Upgrading to 11.2.0.4 to Avoid Poor Performance or Wrong Results [ID 1645862.1]
Oracle 11gR2 Upgrade Companion [ID 785351.1]

Related Posts
Upgrading from 10.2.0.4 to 10.2.0.5 (Clusterware, RAC, ASM)
Upgrade from 10.2.0.5 to 11.2.0.3 (Clusterware, RAC, ASM)
Upgrade from 11.1.0.7 to 11.2.0.3 (Clusterware, ASM & RAC)
Upgrade from 11.1.0.7 to 11.2.0.4 (Clusterware, ASM & RAC)
Upgrading from 11.1.0.7 to 11.2.0.3 with Transient Logical Standby
Upgrading from 11.2.0.1 to 11.2.0.3 with in-place upgrade for RAC
In-place upgrade from 11.2.0.2 to 11.2.0.3
Upgrading from 11.2.0.2 to 11.2.0.3 with Physical Standby - 1
Upgrading from 11.2.0.2 to 11.2.0.3 with Physical Standby - 2
Upgrading from 11gR2 (11.2.0.3) to 12c (12.1.0.1) Grid Infrastructure