How to Apply a Standby-First PSU Patch in a 2 node RAC environment
Oracle provides since it’s 11.2.0.1 version a way to apply certain patches, to our standby environment first, without compromising the primary database, to allow to let it burn in the Standby for the time you deem appropriate and then apply the patch binaries to the primary BD ORACLE_HOME and these will cascade into our Standby DB.
The first thing you have to make sure before applying a patch like this, is that it’s certified in the MOS Patch note as “Standby-First”.
For this, I have the patch binaries in /u01/app/oracle/patches/11.2.0.3/PSUOct2013, and the databases that I will apply the patches are the primary DB TEST with oracleenespanol1/oracleenespanol2 nodes, and Standby TESTSTBY with oracleenespanol3 and oracleenespanol4 nodes. The PSU patch that I will apply is 17272731 which is 11.2.0.3.8.
Let’s start this long post in the Standby environment, so please be patient. The first thing I recommend is that you take a backup of your binaries, in a given case that there is an error, we can return to these without much problems. This is run as root. Similarly I recommend that before you start, to have the necessary backups of your database.
[root@oracleenespanol3 ~]# id uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel) export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 export GI_HOME=/u01/app/grid/CRS mkdir /orabackup/StgOraBackup/GI_RDBMS_OH_backup cd $ORACLE_HOME ##Verify that this is the correct ORACLE_HOME for TESTSTBY tar -cvf /orabackup/StgOraBackup/GI_RDBMS_OH_backup/rdbms_oh_bu_OCT2013.tar . cd $GI_HOME ##Verify that this is the correct GI_HOME for TESTSTBY tar -cvf /orabackup/StgOraBackup/GI_RDBMS_OH_backup/gi_oh_bu_OCT2013.tar . cat /etc/oraInst.loc inventory_loc=/u01/app/OraInventory inst_group=oinstall cd /u01/app/OraInventory tar -cvf /orabackup/StgOraBackup/OraInventory.tar .
Then with the oracle user, let’s make sure that there is no conflict with the patch we are to apply t the binaries that we have installed
[oracle@oracleenespanol3 StgOraBackup]$ id uid=400(oracle) gid=400(oinstall) groups=400(oinstall),401(dba),402(oper) export GI_HOME=/u01/app/grid/CRS export PATH=$GI_HOME/OPatch:$GI_HOME/bin:$PATH cd /u01/app/grid/CRS/OPatch ./opatch version ##The version output of the previous command should be 11.2.0.4.0 or later. ##If not download the latest Opatch from MOS Patch 6880880## <-- Verify this version against the PSU Patch Number in MOS 17272731 : OPatch Utility Information ./opatch prereq CheckConflictAgainstOHWithDetail -oh /u01/app/grid/CRS -phBaseDir /u01/app/oracle/patches/11.2.0.3/PSUOct2013/17076717 ./opatch prereq CheckConflictAgainstOHWithDetail -oh /u01/app/grid/CRS -phBaseDir /u01/app/oracle/patches/11.2.0.3/PSUOct2013/16902043 ./opatch prereq CheckConflictAgainstOHWithDetail -oh /u01/app/oracle/product/11.2.0/db_1 -phBaseDir /u01/app/oracle/patches/11.2.0.3/PSUOct2013/16902043
To continue, you must create as the oracle user, an OCM response file, this does not mean that we are going to install it, but this is necessary for a PSU. And remember that our directory where we have the patch binaries are shared, so we only have do it once.
[oracle@oracleenespanol3 StgOraBackup]$ id uid=400(oracle) gid=400(oinstall) groups=400(oinstall),401(dba),402(oper) $ORACLE_HOME/OPatch/ocm/bin/emocmrsp -no_banner -output /u01/app/oracle/patches/11.2.0.3/PSUOct2013/ocm.rsp
I like to ensure that all my services are up or even down on my RAC environment before making any changes, so that I can crosscheck when I finish. I use this script called crs_status.sh to verify and I run with my user grid, all you have to change the value of CRS_HOME, hopefully this will also serve you.
[grid@oracleenespanol3 StgOraBackup]$ id uid=401(grid) gid=400(oinstall) groups=400(oinstall),401(dba),402(oper) [grid@oracleenespanol3 antunez]$ ./crs_status.sh NAME TARGET STATE SERVER STATE_DETAILS ------------------------- ---------- ---------- ------------ ------------------ ora.LISTENER.lsnr ONLINE ONLINE oracleenespanol3 ora.LISTENER.lsnr ONLINE ONLINE oracleenespanol4 ora.asm ONLINE ONLINE oracleenespanol3 ora.asm ONLINE ONLINE oracleenespanol4 ora.teststby.db ONLINE ONLINE oracleenespanol3 Open,Readonly ora.teststby.db ONLINE ONLINE oracleenespanol4 Open,Readonly ... ora.cvu ONLINE ONLINE oracleenespanol3 ora.oc4j ONLINE ONLINE oracleenespanol4 ora.scan1.vip ONLINE ONLINE oracleenespanol4 ora.scan2.vip ONLINE ONLINE oracleenespanol3 ora.scan3.vip ONLINE ONLINE oracleenespanol3
Now we will apply the patch in our standby environment first, as the root user, make sure you run on it on the first node before moving to the second node.
[root@oracleenespanol3 ~]# id uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel) [root@oracleenespanol3 ~]#export GI_HOME=/u01/app/grid/CRS [root@oracleenespanol3 ~]#export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 [root@oracleenespanol3 ~]#export PATH=$GI_HOME/OPatch:$GI_HOME/bin:$ORACLE_HOME/bin:$PATH [root@oracleenespanol3 ~]# which make /usr/bin/make [root@oracleenespanol3 ~]# which ar /usr/bin/ar [root@oracleenespanol3 ~]# which ld /usr/bin/ld [root@oracleenespanol3 ~]# which nm /usr/bin/nm [root@oracleenespanol3 ~]# cd $GI_HOME/OPatch [root@oracleenespanol3 OPatch]# ./opatch auto /u01/app/oracle/patches/11.2.0.3/PSUOct2013 -ocmrf /u01/app/oracle/patches/11.2.0.3/PSUOct2013/ocm.rsp Executing /u01/app/grid/CRS/perl/bin/perl ./crs/patch11203.pl -patchdir /u01/app/oracle/patches/11.2.0.3 -patchn PSUOct2013 -ocmrf /u01/app/oracle/patches/11.2.0.3/PSUOct2013/ocm.rsp -paramfile /u01/app/grid/CRS/crs/install/crsconfig_params /u01/app/grid/CRS/crs/install/crsconfig_params /u01/app/grid/CRS/crs/install/s_crsconfig_defs This is the main log file: /u01/app/grid/CRS/cfgtoollogs/opatchauto2013-12-17_17-09-10.log This file will show your detected configuration and all the steps that opatchauto attempted to do on your system: /u01/app/grid/CRS/cfgtoollogs/opatchauto2013-12-17_17-09-10.report.log 2013-12-17 17:09:10: Starting Clusterware Patch Setup Using configuration parameter file: /u01/app/grid/CRS/crs/install/crsconfig_params patch /u01/app/oracle/patches/11.2.0.3/PSUOct2013/17076717/custom/server/17076717 apply successful for home /u01/app/oracle/product/11.2.0/db_1 patch /u01/app/oracle/patches/11.2.0.3/PSUOct2013/16902043 apply successful for home /u01/app/oracle/product/11.2.0/db_1 CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'oracleenespanol3' CRS-2673: Attempting to stop 'ora.crsd' on 'oracleenespanol3' CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'oracleenespanol3' ... CRS-2673: Attempting to stop 'ora.cssd' on 'oracleenespanol3' CRS-2677: Stop of 'ora.cssd' on 'oracleenespanol3' succeeded CRS-2673: Attempting to stop 'ora.gipcd' on 'oracleenespanol3' CRS-2677: Stop of 'ora.gipcd' on 'oracleenespanol3' succeeded CRS-2673: Attempting to stop 'ora.gpnpd' on 'oracleenespanol3' CRS-2677: Stop of 'ora.gpnpd' on 'oracleenespanol3' succeeded CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'oracleenespanol3' has completed CRS-4133: Oracle High Availability Services has been stopped. Successfully unlock /u01/app/grid/CRS patch /u01/app/oracle/patches/11.2.0.3/PSUOct2013/17076717 apply successful for home /u01/app/grid/CRS patch /u01/app/oracle/patches/11.2.0.3/PSUOct2013/16902043 apply successful for home /u01/app/grid/CRS CRS-4123: Oracle High Availability Services has been started. <span style="color: #ff0000;">###Once that oracleenespanol3 is complete,</span> <span style="color: #ff0000;">###As root in oracleenespanol4 and with the same variables for GI_HOME/ORACLE_HOME and PATH:</span> [root@oracleenespanol4 ~]# id uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel) [root@oracleenespanol4 ~]#export GI_HOME=/u01/app/grid/CRS [root@oracleenespanol4 ~]#export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 [root@oracleenespanol4 ~]#export PATH=$GI_HOME/OPatch:$GI_HOME/bin:$ORACLE_HOME/bin:$PATH [root@oracleenespanol4 ~]# which make /usr/bin/make [root@oracleenespanol4 ~]# which ar /usr/bin/ar [root@oracleenespanol4 ~]# which ld /usr/bin/ld [root@oracleenespanol4 ~]# which nm /usr/bin/nm [root@oracleenespanol4 ~]# cd $GI_HOME/OPatch [root@oracleenespanol4 OPatch]# ./opatch auto /u01/app/oracle/patches/11.2.0.3/PSUOct2013 -ocmrf /u01/app/oracle/patches/11.2.0.3/PSUOct2013/ocm.rsp Executing /u01/app/grid/CRS/perl/bin/perl ./crs/patch11203.pl -patchdir /u01/app/oracle/patches/11.2.0.3 -patchn PSUOct2013 -ocmrf /u01/app/oracle/patches/11.2.0.3/PSUOct2013/ocm.rsp -paramfile /u01/app/grid/CRS/crs/install/crsconfig_params /u01/app/grid/CRS/crs/install/crsconfig_params /u01/app/grid/CRS/crs/install/s_crsconfig_defs This is the main log file: /u01/app/grid/CRS/cfgtoollogs/opatchauto2013-12-17_17-46-32.log This file will show your detected configuration and all the steps that opatchauto attempted to do on your system: /u01/app/grid/CRS/cfgtoollogs/opatchauto2013-12-17_17-46-32.report.log 2013-12-17 17:46:32: Starting Clusterware Patch Setup Using configuration parameter file: /u01/app/grid/CRS/crs/install/crsconfig_params patch /u01/app/oracle/patches/11.2.0.3/PSUOct2013/17076717/custom/server/17076717 apply successful for home /u01/app/oracle/product/11.2.0/db_1 patch /u01/app/oracle/patches/11.2.0.3/PSUOct2013/16902043 apply successful for home /u01/app/oracle/product/11.2.0/db_1 CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'oracleenespanol4' CRS-2673: Attempting to stop 'ora.crsd' on 'oracleenespanol4' CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'oracleenespanol4' ... CRS-2673: Attempting to stop 'ora.cssd' on 'oracleenespanol4' CRS-2677: Stop of 'ora.cssd' on 'oracleenespanol4' succeeded CRS-2673: Attempting to stop 'ora.gipcd' on 'oracleenespanol4' CRS-2677: Stop of 'ora.gipcd' on 'oracleenespanol4' succeeded CRS-2673: Attempting to stop 'ora.gpnpd' on 'oracleenespanol4' CRS-2677: Stop of 'ora.gpnpd' on 'oracleenespanol4' succeeded CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'oracleenespanol4' has completed CRS-4133: Oracle High Availability Services has been stopped. Successfully unlock /u01/app/grid/CRS patch /u01/app/oracle/patches/11.2.0.3/PSUOct2013/17076717 apply successful for home /u01/app/grid/CRS patch /u01/app/oracle/patches/11.2.0.3/PSUOct2013/16902043 apply successful for home /u01/app/grid/CRS CRS-4123: Oracle High Availability Services has been started.
Once the patch has finished , we will verify that our environment is patched correctly.
[oracle@oracleenespanol4 StgOraBackup]$ id uid=400(oracle) gid=400(oinstall) groups=400(oinstall),401(dba),402(oper) export GI_HOME=/u01/app/grid/CRS export PATH=$GI_HOME/OPatch:$GI_HOME/bin:$PATH cd $GI_HOME/OPatch ./opatch lsinventory <span style="color: #ff0000;">##An expected result is below:</span> [oracle@oracleenespanol4 CRS]$ OPatch/opatch lsinventory Oracle Interim Patch Installer version 11.2.0.3.4 Copyright (c) 2012, Oracle Corporation. All rights reserved. Oracle Home : /u01/app/grid/CRS Central Inventory : /u01/app/OraInventory from : /u01/app/grid/CRS/oraInst.loc OPatch version : 11.2.0.3.4 OUI version : 11.2.0.3.0 Log file location : /u01/app/grid/CRS/cfgtoollogs/opatch/opatch2013-12-17_17-58-59PM_1.log Lsinventory Output file location : /u01/app/grid/CRS/cfgtoollogs/opatch/lsinv/lsinventory2013-12-17_17-58-59PM.txt -------------------------------------------------------------------------------- Installed Top-level Products (1): Oracle Grid Infrastructure 11.2.0.3.0 There are 1 products installed in this Oracle Home. Interim patches (2) : Patch 16902043 : applied on Tue Dec 17 17:46:32 EST 2013 Unique Patch ID: 16676143 Patch description: "Database Patch Set Update : 11.2.0.3.8 (16902043)" Created on 24 Sep 2013, 23:20:58 hrs PST8PDT Sub-patch 16619892; "Database Patch Set Update : 11.2.0.3.7 (16619892)" Sub-patch 16056266; "Database Patch Set Update : 11.2.0.3.6 (16056266)" Sub-patch 14727310; "Database Patch Set Update : 11.2.0.3.5 (14727310)" Sub-patch 14275605; "Database Patch Set Update : 11.2.0.3.4 (14275605)" Sub-patch 13923374; "Database Patch Set Update : 11.2.0.3.3 (13923374)" Sub-patch 13696216; "Database Patch Set Update : 11.2.0.3.2 (13696216)" Sub-patch 13343438; "Database Patch Set Update : 11.2.0.3.1 (13343438)" Bugs fixed: 13593999, 13566938, 10350832, 14138130, 12919564, 14198511, 13561951 ... 14063280, 12772404, 13011409 Patch 17076717 : applied on Tue Dec 17 17:50:01 EST 2013 Unique Patch ID: 16721032 Patch description: "Grid Infrastructure Patch Set Update : 11.2.0.3.8 (HAS Components)" Created on 11 Oct 2013, 02:52:50 hrs PST8PDT Bugs fixed: 17076717, 16619898, 16315641, 15876003, 14275572, 13919095, 13696251 ... 15936101, 14009845, 12827493, 13637590, 13068077 Rac system comprising of multiple nodes Local node = oracleenespanol4 Remote node = oracleenespanol3 -------------------------------------------------------------------------------- OPatch succeeded.
The only thing I would do is to use again the crs_status.sh script to check my RAC services are up/down. For this type of application, you have to know that there is no extra step in the Standby, as the catbundle will be applied in the primary BD not in Standby. As you could see, it is a long process, because we have just finished our standby environment, we will now proceed to our primary environment. Here is where you will wait the time that you deem necessary to see if there are any errors that can impact your primary database when you apply it there or make you rollback the patch.
Now we will move to the primary environment.
For the primary, I’m not going to go over the same steps that had to be done standby environments (oracleenespanol3/oracleenespanol4) that you will also have to make in the primary environment (oracleenespanol1/oracleenespanol2) , but I will do a summary of what has to be done in the primary nodes
- Take a backup of your GI_HOME/ORACLE_HOME and inventory
- Verify you have a valid backup of your Primary DB
- Run against your binaries in your primary environment opatch prereq CheckConflictAgainstOHWithDetail as we did with the standby environment.
- Make sure that you have your ocm.rsp file created
- Verify which services are up/down in your RAC environment
Once you’ve done the steps above, this is where the process of implementing the patch changes.
In oracleenespanol3/oracleenespanol4 nodes (Standby), let’s put TESTSTBY in mount mode.
SYS@TESTSTBY1> select OPEN_MODE,GUARD_STATUS,DATABASE_ROLE from v$database; OPEN_MODE GUARD_S DATABASE_ROLE -------------------- ------- ---------------- READ ONLY WITH APPLY NONE PHYSICAL STANDBY 1 row selected. SYS@TESTSTBY1> select host_name,instance_name from v$instance; HOST_NAME ---------------------------------------------------------------- INSTANCE_NAME ---------------- oracleenespanol3 TESTSTBY1 1 row selected. SYS@TESTSTBY1> shutdown immediate SYS@TESTSTBY1> startup mount
Now we return to oracleenespanol1 and oracleenespanol2 nodes (Primary), and we will apply the patch as root, make sure you run it and it completes on the first node, before moving to the second node.
[root@oracleenespanol1 ~]# id uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel) [root@oracleenespanol1 ~]#export GI_HOME=/u01/app/grid/CRS [root@oracleenespanol1 ~]#export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 [root@oracleenespanol1 ~]#export PATH=$GI_HOME/OPatch:$GI_HOME/bin:$ORACLE_HOME/bin:$PATH [root@oracleenespanol1 ~]# which make /usr/bin/make [root@oracleenespanol1 ~]# which ar /usr/bin/ar [root@oracleenespanol1 ~]# which ld /usr/bin/ld [root@oracleenespanol1 ~]# which nm /usr/bin/nm [root@oracleenespanol1 ~]# cd $GI_HOME/OPatch [root@oracleenespanol1 OPatch]# ./opatch auto /u01/app/oracle/patches/11.2.0.3/PSUOct2013 -ocmrf /u01/app/oracle/patches/11.2.0.3/PSUOct2013/ocm.rsp Executing /u01/app/grid/CRS/perl/bin/perl ./crs/patch11203.pl -patchdir /u01/app/oracle/patches/11.2.0.3 -patchn PSUOct2013 -ocmrf /u01/app/oracle/patches/11.2.0.3/PSUOct2013/ocm.rsp -paramfile /u01/app/grid/CRS/crs/install/crsconfig_params /u01/app/grid/CRS/crs/install/crsconfig_params /u01/app/grid/CRS/crs/install/s_crsconfig_defs This is the main log file: /u01/app/grid/CRS/cfgtoollogs/opatchauto2013-12-18_11-54-23.log This file will show your detected configuration and all the steps that opatchauto attempted to do on your system: /u01/app/grid/CRS/cfgtoollogs/opatchauto2013-12-18_11-54-23.report.log 2013-12-18 11:54:23: Starting Clusterware Patch Setup Using configuration parameter file: /u01/app/grid/CRS/crs/install/crsconfig_params patch /u01/app/oracle/patches/11.2.0.3/PSUOct2013/17076717/custom/server/17076717 apply successful for home /u01/app/oracle/product/11.2.0/db_1 patch /u01/app/oracle/patches/11.2.0.3/PSUOct2013/16902043 apply successful for home /u01/app/oracle/product/11.2.0/db_1 CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'oracleenespanol1' CRS-2673: Attempting to stop 'ora.crsd' on 'oracleenespanol1' CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'oracleenespanol1' ... CRS-2673: Attempting to stop 'ora.cssd' on 'oracleenespanol1' CRS-2677: Stop of 'ora.cssd' on 'oracleenespanol1' succeeded CRS-2673: Attempting to stop 'ora.gipcd' on 'oracleenespanol1' CRS-2677: Stop of 'ora.gipcd' on 'oracleenespanol1' succeeded CRS-2673: Attempting to stop 'ora.gpnpd' on 'oracleenespanol1' CRS-2677: Stop of 'ora.gpnpd' on 'oracleenespanol1' succeeded CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'oracleenespanol1' has completed CRS-4133: Oracle High Availability Services has been stopped. Successfully unlock /u01/app/grid/CRS patch /u01/app/oracle/patches/11.2.0.3/PSUOct2013/17076717 apply successful for home /u01/app/grid/CRS patch /u01/app/oracle/patches/11.2.0.3/PSUOct2013/16902043 apply successful for home /u01/app/grid/CRS CRS-4123: Oracle High Availability Services has been started. <span style="color: #ff0000;">###Once that oracleenespanol1 is complete,</span> <span style="color: #ff0000;">###As root in oracleenespanol2 and with the same variables for GI_HOME/ORACLE_HOME and PATH:</span> [root@oracleenespanol2 ~]# id uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel) [root@oracleenespanol2 ~]#export GI_HOME=/u01/app/grid/CRS [root@oracleenespanol2 ~]#export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 [root@oracleenespanol2 ~]#export PATH=$GI_HOME/OPatch:$GI_HOME/bin:$ORACLE_HOME/bin:$PATH [root@oracleenespanol2 ~]# which make /usr/bin/make [root@oracleenespanol2 ~]# which ar /usr/bin/ar [root@oracleenespanol2 ~]# which ld /usr/bin/ld [root@oracleenespanol2 ~]# which nm /usr/bin/nm [root@oracleenespanol2 ~]# cd $GI_HOME/OPatch [root@oracleenespanol2 OPatch]# ./opatch auto /u01/app/oracle/patches/11.2.0.3/PSUOct2013 -ocmrf /u01/app/oracle/patches/11.2.0.3/PSUOct2013/ocm.rsp Executing /u01/app/grid/CRS/perl/bin/perl ./crs/patch11203.pl -patchdir /u01/app/oracle/patches/11.2.0.3 -patchn PSUOct2013 -ocmrf /u01/app/oracle/patches/11.2.0.3/PSUOct2013/ocm.rsp -paramfile /u01/app/grid/CRS/crs/install/crsconfig_params /u01/app/grid/CRS/crs/install/crsconfig_params /u01/app/grid/CRS/crs/install/s_crsconfig_defs This is the main log file: /u01/app/grid/CRS/cfgtoollogs/opatchauto2013-12-18_12-24-10.log This file will show your detected configuration and all the steps that opatchauto attempted to do on your system: /u01/app/grid/CRS/cfgtoollogs/opatchauto2013-12-18_12-24-10.report.log 2013-12-18 12:24:10: Starting Clusterware Patch Setup Using configuration parameter file: /u01/app/grid/CRS/crs/install/crsconfig_params patch /u01/app/oracle/patches/11.2.0.3/PSUOct2013/17076717/custom/server/17076717 apply successful for home /u01/app/oracle/product/11.2.0/db_1 patch /u01/app/oracle/patches/11.2.0.3/PSUOct2013/16902043 apply successful for home /u01/app/oracle/product/11.2.0/db_1 CRS-2791: Starting shutdown of Oracle High Availability Services-managed resources on 'oracleenespanol2' CRS-2673: Attempting to stop 'ora.crsd' on 'oracleenespanol2' CRS-2790: Starting shutdown of Cluster Ready Services-managed resources on 'oracleenespanol2' ... CRS-2673: Attempting to stop 'ora.cssd' on 'oracleenespanol2' CRS-2677: Stop of 'ora.cssd' on 'oracleenespanol2' succeeded CRS-2673: Attempting to stop 'ora.gipcd' on 'oracleenespanol2' CRS-2677: Stop of 'ora.gipcd' on 'oracleenespanol2' succeeded CRS-2673: Attempting to stop 'ora.gpnpd' on 'oracleenespanol2' CRS-2677: Stop of 'ora.gpnpd' on 'oracleenespanol2' succeeded CRS-2793: Shutdown of Oracle High Availability Services-managed resources on 'oracleenespanol2' has completed CRS-4133: Oracle High Availability Services has been stopped. Successfully unlock /u01/app/grid/CRS patch /u01/app/oracle/patches/11.2.0.3/PSUOct2013/17076717 apply successful for home /u01/app/grid/CRS patch /u01/app/oracle/patches/11.2.0.3/PSUOct2013/16902043 apply successful for home /u01/app/grid/CRS CRS-4123: Oracle High Availability Services has been started.
We verify again as we did in the standby environment with the opatch lsinventory that the patch 11.2.0.3.8 is applied properly in our binaries and with the script crs_status.sh we crosscheck which services are up/down in our primary environment.
What we have to do next is run the catbundle.sql as the oracle user from $ORACLE_HOME/rdbms/admin in our primary database, this has to run on one node only, not both.
SYS@TEST> set pagesize 9999
col ACTION_TIME for a30
col COMMENTS for a30
col ACTION for a8
col NAMESPACE for a12
col VERSION for a10
col BUNDLE_SERIES for a30
set lines 200
select name from v$database;
select host_name,instance_name from v$instance;
select * from dba_registry_history;
NAME
---------
TEST
SYS@TEST>
HOST_NAME INSTANCE_NAME
---------------------------------------------------------------- ----------------
oracleenespanol1 TEST1
SQL@TEST>
ACTION_TIME ACTION NAMESPACE VERSION ID BUNDLE_SERIES COMMENTS
------------------------------ -------- ------------ ---------- ---------- ------------------------------ ------------------------------
17-SEP-11 10.21.11.595816 AM APPLY SERVER 11.2.0.3 0 PSU Patchset 11.2.0.2.0
24-MAY-12 01.56.22.270056 PM APPLY SERVER 11.2.0.3 0 PSU Patchset 11.2.0.2.0
03-JUL-12 09.43.20.177762 PM APPLY SERVER 11.2.0.3 2 PSU PSU 11.2.0.3.2
20-MAR-13 05.26.24.599603 PM APPLY SERVER 11.2.0.3 2 PSU PSU 11.2.0.3.2
14-AUG-13 04.48.25.893605 PM APPLY SERVER 11.2.0.3 7 PSU PSU 11.2.0.3.7
SQL> @catbundle.sql psu apply
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
Generating apply and rollback scripts...
Check the following file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_TEST_GENERATE_2013Dec18_18_08_13.log
Apply script: /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catbundle_PSU_TEST_APPLY.sql
Rollback script: /u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catbundle_PSU_TEST_ROLLBACK.sql
...
Updating registry...
SQL> INSERT INTO registry$history
2 (action_time, action,
3 namespace, version, id,
4 bundle_series, comments)
5 VALUES
6 (SYSTIMESTAMP, 'APPLY',
7 SYS_CONTEXT('REGISTRY$CTX','NAMESPACE'),
8 '11.2.0.3',
9 8,
10 'PSU',
11 'PSU 11.2.0.3.8');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SPOOL off
SQL> SET echo off
Check the following log file for errors:
/u01/app/oracle/cfgtoollogs/catbundle/catbundle_PSU_TEST_APPLY_2013Dec18_18_08_13.log
SYS@TEST> set pagesize 9999
col ACTION_TIME for a30
col COMMENTS for a30
col ACTION for a8
col NAMESPACE for a12
col VERSION for a10
col BUNDLE_SERIES for a30
set lines 200
select name from v$database;
select host_name,instance_name from v$instance;
select * from dba_registry_history;
NAME
---------
TEST
SYS@TEST>
HOST_NAME INSTANCE_NAME
---------------------------------------------------------------- ----------------
oracleenespanol1 TEST1
SQL@TEST>
ACTION_TIME ACTION NAMESPACE VERSION ID BUNDLE_SERIES COMMENTS
------------------------------ -------- ------------ ---------- ---------- ------------------------------ ------------------------------
17-SEP-11 10.21.11.595816 AM APPLY SERVER 11.2.0.3 0 PSU Patchset 11.2.0.2.0
24-MAY-12 01.56.22.270056 PM APPLY SERVER 11.2.0.3 0 PSU Patchset 11.2.0.2.0
03-JUL-12 09.43.20.177762 PM APPLY SERVER 11.2.0.3 2 PSU PSU 11.2.0.3.2
20-MAR-13 05.26.24.599603 PM APPLY SERVER 11.2.0.3 2 PSU PSU 11.2.0.3.2
14-AUG-13 04.48.25.893605 PM APPLY SERVER 11.2.0.3 7 PSU PSU 11.2.0.3.7
18-DEC-13 06.08.13.889118 PM APPLY SERVER 11.2.0.3 8 PSU PSU 11.2.0.3.8
Now the only thing left to do is to verify that the ARCHIVED REDO LOGS are being applied correctly on the standby database.
So we will first archive a REDO LOG in database TEST (primary), and we will see that the log 82197 is the one that will be archived.
SYS@TEST> ARCHIVE LOG LIST Database log mode Archive Mode Automatic archival Enabled Archive destination USE_DB_RECOVERY_FILE_DEST Oldest online log sequence 82190 Next log sequence to archive <strong>82197</strong> Current log sequence <strong>82197</strong> SYS@TEST> ALTER SYSTEM ARCHIVE LOG CURRENT; SYS@TEST> ALTER SYSTEM CHECKPOINT;
At this point, we’re done with the primary database and the primary nodes. In oracleenespanol3/oracleenespanol4 servers (Standby), let’s put TESTSTBY recovery more. In my case I’m using Active Dataguard, just be careful with that, because if you are not using it, is an extra licensable option for your Standby DB.
SYS@TESTSTBY1> select OPEN_MODE,DATAGUARD_BROKER,GUARD_STATUS,DATABASE_ROLE from v$database; OPEN_MODE DATAGUAR GUARD_S DATABASE_ROLE -------------------- -------- ------- ---------------- MOUNTED DISABLED NONE PHYSICAL STANDBY SYS@TESTSTBY1> alter database open; Database altered. SYS@TESTSTBY1> select OPEN_MODE,DATAGUARD_BROKER,GUARD_STATUS,DATABASE_ROLE from v$database; OPEN_MODE GUARD_S DATABASE_ROLE -------------------- ------- ---------------- READ ONLY NONE PHYSICAL STANDBY SYS@TESTSTBY1> select process , status from v$managed_standby where process like '%MRP%'; no rows selected SYS@TESTSTBY1> select process ,status , thread# , sequence# from gv$managed_standby; PROCESS STATUS THREAD# SEQUENCE# --------- ------------ ---------- ---------- ARCH CLOSING 1 82064 ARCH CONNECTED 0 0 ARCH CONNECTED 0 0 ARCH CONNECTED 0 0 ARCH CLOSING 2 81785 ARCH CLOSING 2 81797 ARCH CONNECTED 0 0 ARCH CLOSING 1 82074 ARCH CLOSING 2 81798 ARCH CLOSING 1 82075 RFS IDLE 0 0 RFS IDLE 0 0 RFS IDLE 2 81799 RFS IDLE 0 0 RFS IDLE 1 82076 RFS IDLE 0 0 RFS IDLE 0 0 RFS IDLE 0 0 RFS IDLE 0 0 RFS IDLE 0 0 SYS@TESTSTBY1> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; SYS@TESTSTBY1> select process , status from v$managed_standby where process ='MRP0'; PROCESS STATUS --------- ------------ MRP0 APPLYING_LOG SYS@TESTSTBY1> select process ,status , thread# , sequence# from gv$managed_standby; PROCESS STATUS THREAD# SEQUENCE# --------- ------------ ---------- ---------- ARCH CLOSING 1 82064 ARCH CONNECTED 0 0 ARCH CONNECTED 0 0 ARCH CONNECTED 0 0 ARCH CLOSING 2 81785 MRP0 APPLYING_LOG 2 81798 ARCH CLOSING 2 81797 ARCH CONNECTED 0 0 ARCH CLOSING 1 82074 ARCH CLOSING 2 81798 ARCH CLOSING 1 82075 RFS IDLE 0 0 RFS IDLE 0 0 RFS IDLE 2 81799 RFS IDLE 0 0 RFS IDLE 1 82076 RFS IDLE 0 0 RFS IDLE 0 0 RFS IDLE 0 0 RFS IDLE 0 0 RFS IDLE 0 0 SYS@TESTSTBY1> set lines 200 pages 9999 SELECT NAME,OPEN_MODE,PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE ROLE, SWITCHOVER_STATUS FROM V$DATABASE; NAME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL ROLE SWITCHOVER_STATUS --------- -------------------- -------------------- -------------------- ---------------- -------------------- TEST READ ONLY WITH APPLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED
And now there is nothing more to do, but to verify that all is well. Let’s check that the last ARCHIVED REDO LOG is applied, which was 82197, and the registry history of your Standby database and you’ll see that you’ll have the version 11.2.0.3.8.
SYS@TESTSTBY1> set lines 200 pages 9999 SELECT NAME,OPEN_MODE,PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE ROLE, SWITCHOVER_STATUS FROM V$DATABASE; NAME OPEN_MODE PROTECTION_MODE PROTECTION_LEVEL ROLE SWITCHOVER_STATUS --------- -------------------- -------------------- -------------------- ---------------- -------------------- TEST READ ONLY WITH APPLY MAXIMUM PERFORMANCE MAXIMUM PERFORMANCE PHYSICAL STANDBY NOT ALLOWED SYS@TESTSTBY1> SELECT 'Last Applied : ' Logs, TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,SEQUENCE# FROM v$archived_log WHERE sequence# = (SELECT MAX(sequence#) FROM v$archived_log WHERE applied='YES' ) UNION SELECT 'Last Received : ' Logs, TO_CHAR(next_time,'DD-MON-YY:HH24:MI:SS') TIME,SEQUENCE# FROM v$archived_log WHERE sequence# = (SELECT MAX(sequence#) FROM v$archived_log ); LOGS TIME SEQUENCE# ---------------- --------------------------- ---------- Last Applied : 18-DEC-13:18:23:58 82197 Last Received : 18-DEC-13:18:23:58 82197 SYS@TESTSTBY1> set pagesize 9999 col ACTION_TIME for a30 col COMMENTS for a30 col ACTION for a8 col NAMESPACE for a12 col VERSION for a10 col BUNDLE_SERIES for a30 set lines 200 select name from v$database; select host_name,instance_name from v$instance; select * from dba_registry_history; NAME --------- TEST HOST_NAME INSTANCE_NAME ---------------------------------------------------------------- ---------------- oracleenespanol3 TESTSTBY1 ACTION_TIME ACTION NAMESPACE VERSION ID BUNDLE_SERIESCOMMENTS ------------------------------ -------- ------------ ---------- ---------- ------------------------------ ------------------------------ 17-SEP-11 10.21.11.595816 AM APPLY SERVER 11.2.0.3 0 PSU Patchset 11.2.0.2.0 24-MAY-12 01.56.22.270056 PM APPLY SERVER 11.2.0.3 0 PSU Patchset 11.2.0.2.0 03-JUL-12 09.43.20.177762 PM APPLY SERVER 11.2.0.3 2 PSU PSU 11.2.0.3.2 20-MAR-13 05.26.24.599603 PM APPLY SERVER 11.2.0.3 2 PSU PSU 11.2.0.3.2 14-AUG-13 04.48.25.893605 PM APPLY SERVER 11.2.0.3 7 PSU PSU 11.2.0.3.7 18-DEC-13 06.08.13.889118 PM APPLY SERVER 11.2.0.3 8 PSU PSU 11.2.0.3.8
Conclusion
This is very useful for maintaining high availability in your environment while allowing you to verify that the patch that you apply does not harm your primary database. LEt me know if you had a use-case for this type of patching and if you think this is useful to you.
P.S.- Happy 2013 Holidays :)
