Tuesday, June 26, 2012

Upgrade 11.2.0.2 RAC Database to 11.2.0.3 manually

How to upgrade 11.2.0.2 ORACLE RAC Database to 11.2.0.3 manually


System detail:

2 server with Red hat Enterprise Linux 5.5
Oracle RAC database + ASM is installed 11.2.0.2 with no PSU applied. (11.2.0.2.0)
GoldenGate is used with this system to another 11.2.0.3 RAC database



I upgrade the 2 node Oracle RAC system from 11.2.0.2 to 11.2.0.3 in 4 main steps:

1.  Apply patch 11.2.0.2.6 PSU to both GI (Grid Infrastructure) and database.
2.  Upgrade GI to 11.2.0.3 and apply 11.2.0.3.2 PSU with software only mode.
3.  Upgrade database to 11.2.0.3.
4.  Apply 11.2.0.3.2 PSU to db only.



1. Apply patch 11.2.0.2.6 PSU to both GI (Grid Infrastructure) and database.

Actually applying PSU to 11.2.0.2  is not a prerequisite  for 11.2.0.3 upgrade. However there are many bugs related to upgrade process and I want to play it safe.

The patch 13696242 includes both GI and database PSU.

If you use GoldenGate in this system, first of all, you have to disable ddl_trigger and stop the GoldenGate services 



  • List all services:



[oraem@mrctst1 gg]$ crsctl stat res -t
--------------------------------------------------------------------------------
NAME           TARGET  STATE        SERVER                   STATE_DETAILS       
--------------------------------------------------------------------------------
Local Resources
--------------------------------------------------------------------------------
ora.DATA.dg
               ONLINE  ONLINE       mrctst1                                    
               ONLINE  ONLINE       mrctst2                                    
ora.FRALOG.dg
               ONLINE  ONLINE       mrctst1                                    
               ONLINE  ONLINE       mrctst2                                    
ora.LISTENER.lsnr
               ONLINE  ONLINE       mrctst1                                    
               ONLINE  ONLINE       mrctst2                                    
ora.VOTING.dg
               ONLINE  ONLINE       mrctst1                                    
               ONLINE  ONLINE       mrctst2                                    
ora.asm
               ONLINE  ONLINE       mrctst1                Started             
               ONLINE  ONLINE       mrctst2                                    
ora.gsd
               OFFLINE OFFLINE      mrctst1                                    
               OFFLINE OFFLINE      mrctst2                                    
ora.net1.network
               ONLINE  ONLINE       mrctst1                                    
               ONLINE  ONLINE       mrctst2                                    
ora.ons
               ONLINE  ONLINE       mrctst1                                    
               ONLINE  ONLINE       mrctst2                                    
ora.registry.acfs
               ONLINE  ONLINE       mrctst1                                    
               ONLINE  ONLINE       mrctst2                                    
--------------------------------------------------------------------------------
Cluster Resources
--------------------------------------------------------------------------------
ggateapp
      1        ONLINE  ONLINE       mrctst1                                                                                  
mvggatevip
      1         ONLINE  ONLINE      mrctst1                                    
ora.LISTENER_SCAN1.lsnr
      1        ONLINE  ONLINE       mrctst2                                    
ora.LISTENER_SCAN2.lsnr
      1        ONLINE  ONLINE       mrctst1                                    
ora.LISTENER_SCAN3.lsnr
      1        ONLINE  ONLINE       mrctst1                                    
ora.cvu
      1        ONLINE  ONLINE       mrctst1                                    
ora.mrctst.db
      1        ONLINE  ONLINE       mrctst1                Open                
      2        ONLINE  ONLINE       mrctst2                Open                
ora.mrctst1.vip
      1        ONLINE  ONLINE       mrctst1                                    
ora.mrctst2.vip
      1        ONLINE  ONLINE       mrctst2                                    
ora.oc4j
      1        ONLINE  ONLINE       mrctst1                                    
ora.scan1.vip
      1        ONLINE  ONLINE       mrctst2                                    
ora.scan2.vip
      1        ONLINE  ONLINE       mrctst1                                    
ora.scan3.vip
      1        ONLINE  ONLINE       mrctst1              




  • Stop GoldenGate services 
crsctl stop res ggateapp
crsctl stop res mvggatevip


  • Disable ddl trigger:
This is must for applying patches to oracle database if you are using GoldenGate:

$ sqlplus / as sysdba

sql> @ddl_disable 



  • Stop Grid agent or dbconsole:

This is also necessary for applying patches to Oracle database. It may cause fail to apply patch.

$ emctl stop agent or dbconsole



  • Unzip latest opatch to both GI and Db homes:

Opatch version must be 11.2.0.1.5 or later.



  • Unzip PSU zip file:

Unzip p13696242_112020_Linux-x86-64.zip on both nodes.



  • Validation of oracle Inventory:

./opatch lsinventory -detail -oh $GI_HOME
./opatch lsinventory -detail -oh $DB_HOME



  • Check for any conflict: 

opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./13696242
opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./13696224


  • Create an OCM respose file:

Patch asks for this response file at the beginning in order to continue patching process. It is a stupid, nonsense step but you have to create this file:

$GI_HOME/OPatch/ocm/bin/emocmrsp

answer the stupid questions and it creates a file named: ocm.rsp


  • Apply patch on every node with root user:

# export PATH=$PATH:$GI_HOME/OPatch

# opatch auto PATCH_DIRECTORY


enter the path of stupid ocm.rsp file, when it finishes, apply on second node.


  • Check whith "opatch lsinventory" command to see applied psu.

Sample "opatch lsinventory" output after applying PSU


[oracle@ractst1 OPatch]$ ./opatch lsinventory
Oracle Interim Patch Installer version 11.2.0.3.0
Copyright (c) 2012, Oracle Corporation.  All rights reserved.


Oracle Home       : /u01/app/oracle/product/11.2.0/dbhome_1
Central Inventory : /u01/app/oraInventory
   from           : /u01/app/oracle/product/11.2.0/dbhome_1/oraInst.loc
OPatch version    : 11.2.0.3.0
OUI version       : 11.2.0.2.0
Log file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/opatch2012-04-25_13-46-30PM_1.log

Lsinventory Output file location : /u01/app/oracle/product/11.2.0/dbhome_1/cfgtoollogs/opatch/lsinv/lsinventory2012-04-25_13-46-30PM.txt

--------------------------------------------------------------------------------
Installed Top-level Products (1):

Oracle Database 11g                                                  11.2.0.2.0
There are 1 products installed in this Oracle Home.


Interim patches (2) :

Patch  13696224     : applied on Wed Apr 25 13:34:48 EEST 2012
Unique Patch ID:  14617280
Patch description:  "Database Patch Set Update : 11.2.0.2.6 (13696224)"
   Created on 3 Apr 2012, 01:07:52 hrs PST8PDT
   Bugs fixed:
     13632725, 10031806, 13769501, 13769502, 13769503, 13769504, 9744252
     13769505, 9956713, 13769506, 13769507, 9972680, 13769508, 13769509
     11853815, 10635701, 9591812, 10127360, 11723722, 9443361, 12846268
     12846269, 9707965, 10245086, 9401552, 10039731, 11689702, 13769510
     12366627, 10077191, 9829397, 11785938, 10258337, 10264680, 10094823
     10209232, 10284570, 8672862, 9672816, 12830339, 9881076, 10621169
     10048701, 12569482, 9078442, 11057263, 10322959, 12780098, 12976376
     12340939, 11788856, 8223165, 10264696, 10142909, 11800959, 13476583
     10052956, 10285022, 10329146, 10332589, 9895207, 9869401, 12828071
     9285259, 10229719, 11724984, 10411618, 11670161, 9724970, 10113990
     10312847, 11893621, 10200390, 10084145, 10367188, 10285394, 10190642
     12586486, 12586487, 10129643, 12586488, 12917230, 12586489, 11866952
     10232083, 9715581, 10302581, 11690639, 12423475, 11889177, 10126094
     10396041, 10269503, 9970255, 9436324, 12400751, 12589039, 11785390
     12586490, 12586491, 12586492, 9795214, 12586493, 10142788, 12586494
     12586495, 9905049, 12586496, 11674898, 10419984, 6892311, 11815753
     10358019, 12431716, 9906422, 10422126, 13343244, 11937253, 9965655
     11890804, 11651810, 9382956, 11067567, 11716621, 10126822, 9869287
     9375300, 10155605, 10356782, 10326338, 10165083, 10051315, 13696224
     10218814, 13554409, 11076894, 10278773, 11707302, 10230571, 12419321
     9966609, 12633340, 12546006, 10137324, 11894889, 10061015, 9572787
     10284838, 10073683, 12639234, 9578670, 9748749, 10022980, 10237773
     10089333, 12419331, 11674485, 12685431, 10187168, 10648873, 10158965
     11061775, 12635537, 9746210, 10204358, 10356513, 10378005, 10170431
     12639177, 10222719, 10384285, 10035737, 12345717, 9873405, 11069199
     12670165, 10159846, 13257247, 10205230, 10052141, 11818335, 12371955
     12655433, 10040921, 11827088, 10219576, 12408350, 13343424, 11707699
     12370722, 11695333, 11841309, 11924400, 12737666, 12797765, 10281887
     10278372, 10013177, 13503598, 12543639, 10157249, 12531263, 9735237
     10317487, 10219583, 9727147, 10310299, 10636231, 11065646, 10055063
     10368698, 10079168, 11695416, 10233732, 10314582, 9953542, 10080579
     11699057, 12620422, 10427260, 11666137, 10110863, 10363186, 10417716
     10019218, 10388660, 12748240, 9539440, 10373381, 10239480, 10158493
     11842991, 10399808, 10417216, 11695285, 11800170, 10157402, 9651350
     10299224, 10151017, 11724916, 9564886, 9847634, 10018789, 10248523
     11694127, 10630870, 9770451, 10425676, 9683047, 10180307, 9835264
     10132870, 10094201, 10193846, 11664046, 10324294, 9414040, 9819805
     11830776, 11830777, 11830778, 11683713, 10200404, 10102506, 12827726
     11733179, 10229886, 10040531, 10082277, 9788588, 12326246, 12397410
     10622001, 13468884, 13386082, 10040035, 12539000, 11867127, 9842573
     9771278, 10013431, 10228151, 10324526, 12417369, 10238786, 10217802
     10332111, 10227288, 10623249, 9943960, 10021022, 9824435, 11664719
     12950644, 9735282, 11800854, 10097711, 11858315, 6523037, 10053725, 8685446

Patch  13696242     : applied on Wed Apr 25 13:31:18 EEST 2012
Unique Patch ID:  14617280
Patch description:  "Grid Infrastructure Patch Set Update : 11.2.0.2.6 (13696242)"
   Created on 4 Apr 2012, 05:37:11 hrs PST8PDT
   Bugs fixed:
     13696242, 10157506, 10178670, 10425672, 12311357, 12419353, 12827731
     13343447, 13653086, 9959110, 10374874, 10272615, 10314123, 10014392
     10089120, 10057296, 11692271, 9864003, 11775080, 13635562, 9916145
     12695202, 12593451, 10044622, 11705438, 10365625, 12767231, 9935308
     12399977, 11882456, 12421404, 12862012, 11744343, 12340700, 10056713
     10637741, 9939306, 9795321, 9902536, 10007185, 10376847, 10038791
     11741224, 12823479, 11655840, 10048487, 10322157, 11768599, 10260251
     10052721, 10028235, 10027079, 12412303, 10357258, 11664015, 11871469
     10045436, 10231906, 12722095, 10622973, 11724464, 9891341, 10072474
     10036834, 10029900, 9974223, 10161846, 10016083, 12597463, 9918485
     11781515, 10040647, 13332363, 12651595, 12345151, 11925641, 10069541
     10029119, 12709476, 10233159, 12332919, 9812956, 10036193, 10015210
     12424479, 12340501, 10621175, 9686152, 12556028, 12605690, 11877079
     8906163, 10111010, 12366642, 11777648, 10115514, 10104377, 10121931
     10057680, 10280665, 10078086, 9944948, 10146768, 10052529, 10011084
     10012319, 10073075, 10233811, 12720728, 10299006, 12426375, 10248739
     10236074, 10128191, 11071429, 10019726, 9975837, 10253630, 12615394
     10420872, 9949676, 11936945, 11820787, 10637483, 13036424, 13396479
     10379703, 12677816, 10157622, 11698552, 10385838, 10053985, 12718811
     10229297, 10425674, 9812970, 11828633, 12538907, 12359585, 11899801
     10083789, 12728585, 9876201, 10073372, 9963327, 10428946, 11077756
     10375649, 12398492, 9336825, 11682409, 10062301, 12767563, 12546712
     10018215, 10105195, 10419987, 13355549, 10071992, 11807012, 10634513
     9926027, 10103954, 12743755, 12406573, 10028343, 12425730, 11768055
     11866171, 10065216, 9907089, 9897335, 10190153, 11744313, 10175855
     12600343, 12422324, 10284828, 10028637, 10361177, 12800501, 9979706
     12950415, 10324594, 10015460, 11818524, 9971646, 11782423, 11654726
     9978765, 10398810, 11904778, 10397652, 13768473, 9915329, 10107380
     10110969, 10305361, 10331452, 10083009, 10631693, 10008467, 10048027
     10040109, 9944978, 10370797, 10033106, 12700217, 11840629, 10042143
     9978195, 10284693, 12639016, 10638381, 11839004, 9679401, 11663339
     10075643, 10057719, 11834289, 9458055, 10205290, 10124517, 12680491
     13334158, 11069614, 10165314, 9593552, 10168006



Rac system comprising of multiple nodes
  Local node = ractst1
  Remote node = ractst2

--------------------------------------------------------------------------------

OPatch succeeded.


  • Post patch:

$ cd $ORACLE_HOME/rdbms/admin

$ sqlplus / as sysdba

SQL> @catbundle.sql psu apply





OK finished, now we can upgrade GI...




2.  Upgrade GI to 11.2.0.3 and apply 11.2.0.3.2 PSU with software only mode. 

  • All the upgades on GI and DB homes should be done with out-of-place upgrade. This means you have to create the new GI and DB home directories for upgrade.
  • Normally you don't have to apply 11.2.0.3.2 PSU while upgrading GI home. However if you give an eye to note   1367631.1  Top 5 CRS/Grid Infrastructure Install issues , at Issue #2, it states that:  Frequently GI installs fail due to known issues for which fixes already exist. It is therefore a good practice to check for and potentially install the latest GI PSU - embedded in the initial GI installation.  I will explain how it will installed while upgrading GI home.


  • Validate for Oracle Cluster upgrade
./runcluvfy.sh stage -pre crsinst -upgrade -n mrc1,mrc2 -rolling -src_crshome $GI_HOME -dest_crshome  $NEW_GI_HOME -dest_version 11.2.0.3.0 -fixup -fixupdir /home/oramrc/fixup -verbose




  • Unset environments


$ unset ORA_CRS_HOME
$ unset ORACLE_HOME
$ unset ORA_NLS10
$ unset TNS_ADMIN
$ unset ORACLE_BASE
$ unset ORACLE_SID
$ unset CRS_HOME


  • Create new GI_HOME directories
$ mkdir NEW_GI_HOME







  • Start runInstaller with oracle user:


 
 
 
 
 
 
 
 
 

  • When it asks to run rootupgrade.sh install 11.2.0.3.2 PSU with software only mode: ( Patch 13696251 )

** unzip opatch to new GI home
** set new GI_HOME in the environment



** Install the PSU with software only mode on each node:



$ <GI_HOME>/OPatch/opatch napply -oh <GI_HOME> -local <UNZIPPED_PATCH_LOCATION>/13696251

$ <GI_HOME>/OPatch/opatch apply -oh <GI_HOME> -local <UNZIPPED_PATCH_LOCATION>/13696216



  • After installing 11.2.0.3.2 PSU run rootupgrade.sh for each node as runInstaller wants.

# /u01/app/11.2.0/grid11203/rootupgrade.sh 



  • Click continue on runInstaller and finish it.


  • Check for GI version
$ crsctl query crs activeversion



And GI upgarde is finished with PSU installed (11.2.0.3.2), continue to next section db upgarde...


3.  Upgrade database to 11.2.0.3 manually .


** It is not recommended to use dbua while upgrading a business critical database. So I upgrade the database manually



  • create new DB_HOME directories:

$ mkdir NEW_DB_HOME





  • Launch runInstaller and install database as "software only":















  • Use pre-upgrade information tool:

This is not an optional section for manual db upgrade, it is a must.

Download latest version of this script and run: Note 884522.1

SQL> SPOOL upgrade_info.log

SQL> @$11g_ORACLE_HOME/rdbms/admin/utlu112i.sql

SQL> SPOOL OFF




sample run:


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 XXXX has 2 INVALID objects.
.... USER XX has 2 INVALID objects.
.... USER XXX has 26 INVALID objects.
WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.
.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.
.... USER APEX_030200 has dependent objects.
.
**********************************************************************
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.



Analyze every item and fix the warnings.


  • Apply the following scripts:

SQL> SELECT * FROM v$recover_file;

no rows selected

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

no rows selected

SQL> SELECT * FROM dba_2pc_pending;

no rows selected

SQL> PURGE dba_recyclebin;

DBA Recyclebin purged.



  • Prepare the new home:


  • create pfile from spfile and remove cluster parameters:

SQL> create pfile='/u01/app/oracle/product/11.2.0/pfilerac_orj.ora' from spfile;

set cluster database parameter to false and change the paths to new DB_HOME directories.




  • Copy parameter files from old_db_home to new_db_home

Copy password file and tnsnames.ora and other needed parameter files to new ORACLE_HOME on both nodes.



  • stop databases runing from  old home:

srvctl stop database -d ractest


  • Check the environment ORACLE_HOME for pointing to new DB_HOME.


  • startup the database with upgrade mode from new home:

SQL> startup upgrade pfile='/u01/app/oracle/product/11.2.0/pfilerac.ora';



  • run catalog upgrade script: (Before start, check the archive log location for enough free space)

SQL> SPOOL upgrade2.log

SQL> @?/rdbms/admin/catupgrd.sql
.....
SQL> SPOOL OFF




It took 40 minutes to upgrade my 600 Gb database.




  • After upgrade create spfile with original pfile;



  • Run post-upgrade status tool:

SQL> @utlu112s.sql



  • Run post-Upgrade Scripts
SQL> catuppst.sql




  • Recompile Invalid Packages

SQL> @utlrp.sql




  • Update /etc/oratab

Update oratab with new database home



  • upgrade the database configuration in Oracle Clusterware 
srvctl upgrade database -d  rac -o /u01/app/oracle/product/11.2.0/dbhome_11203



This finishes upgrading database, now move on to last step to appy 11.2.0.3.2 PSU to db_home





4.  Apply 11.2.0.3.2 PSU to db only.


  • Unzip latest opatch to new db_homes again and again and again !!!!!

  • Create ocm.rsp file
$ /u01/app/oracle/product/11.2.0/dbhome_1/OPatch/ocm/bin/emocmrsp

  • Check for validation and conflict detection

./opatch lsinventory -detail -oh /u01/app/oracle/product/11.2.0/db_11203

opatch prereq CheckConflictAgainstOHWithDetail -ph ./13696216 -oh /u01/app/oracle/product/11.2.0/db_11203
opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./13696251/custom/server/13696251 -oh /u01/app/oracle/product/11.2.0/db_11203


  • start opatch on each node(with root user )

opatch auto PATCH_DIRACTORY -oh /u01/app/oracle/product/11.2.0/dbhome_11203 -ocmrf /u01/app/oracle/product/11.2.0/dbhome_11203/OPatch/ocm/bin/ocm.rsp



  • Post patch
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql psu apply
SQL> QUIT


  • catalog upgrade

$ rman catalog rman/rman@CATALOG_DB
RMAN> upgrade catalog;

recovery catalog owner is RMAN
enter UPGRADE CATALOG command again to confirm catalog upgrade

RMAN> upgrade catalog

recovery catalog upgraded to version 11.02.00.02
DBMS_RCVMAN package upgraded to version 11.02.00.02
DBMS_RCVCAT package upgraded to version 11.02.00.02

  • If you use GoldenGate, enable ddl_trigger and start services

$ sqlplus / as sysdba

SQL>  @ddl_enable


$ crsctl start res ggateapp

$ crsctl start res mvggatevip




Finished !! Both GI and DB versions are upgraded to 11.2.0.3.2.