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.
[[email protected] ~]# 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
[[email protected] 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.
[[email protected] 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.
[[email protected] StgOraBackup]$ id uid=401(grid) gid=400(oinstall) groups=400(oinstall),401(dba),402(oper) [[email protected] 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.
[[email protected] ~]# id uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel) [[email protected] ~]#export GI_HOME=/u01/app/grid/CRS [[email protected] ~]#export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 [[email protected] ~]#export PATH=$GI_HOME/OPatch:$GI_HOME/bin:$ORACLE_HOME/bin:$PATH [[email protected] ~]# which make /usr/bin/make [[email protected] ~]# which ar /usr/bin/ar [[email protected] ~]# which ld /usr/bin/ld [[email protected] ~]# which nm /usr/bin/nm [[email protected] ~]# cd $GI_HOME/OPatch [[email protected] 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> [[email protected] ~]# id uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel) [[email protected] ~]#export GI_HOME=/u01/app/grid/CRS [[email protected] ~]#export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 [[email protected] ~]#export PATH=$GI_HOME/OPatch:$GI_HOME/bin:$ORACLE_HOME/bin:$PATH [[email protected] ~]# which make /usr/bin/make [[email protected] ~]# which ar /usr/bin/ar [[email protected] ~]# which ld /usr/bin/ld [[email protected] ~]# which nm /usr/bin/nm [[email protected] ~]# cd $GI_HOME/OPatch [[email protected] 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.
[[email protected] 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> [[email protected] 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.
[email protected]> 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. [email protected]> select host_name,instance_name from v$instance; HOST_NAME ---------------------------------------------------------------- INSTANCE_NAME ---------------- oracleenespanol3 TESTSTBY1 1 row selected. [email protected]> shutdown immediate [email protected]> 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.
[[email protected] ~]# id uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel) [[email protected] ~]#export GI_HOME=/u01/app/grid/CRS [[email protected] ~]#export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 [[email protected] ~]#export PATH=$GI_HOME/OPatch:$GI_HOME/bin:$ORACLE_HOME/bin:$PATH [[email protected] ~]# which make /usr/bin/make [[email protected] ~]# which ar /usr/bin/ar [[email protected] ~]# which ld /usr/bin/ld [[email protected] ~]# which nm /usr/bin/nm [[email protected] ~]# cd $GI_HOME/OPatch [[email protected] 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> [[email protected] ~]# id uid=0(root) gid=0(root) groups=0(root),1(bin),2(daemon),3(sys),4(adm),6(disk),10(wheel) [[email protected] ~]#export GI_HOME=/u01/app/grid/CRS [[email protected]enespanol2 ~]#export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1 [[email protected] ~]#export PATH=$GI_HOME/OPatch:$GI_HOME/bin:$ORACLE_HOME/bin:$PATH [[email protected] ~]# which make /usr/bin/make [[email protected] ~]# which ar /usr/bin/ar [[email protected] ~]# which ld /usr/bin/ld [[email protected] ~]# which nm /usr/bin/nm [[email protected] ~]# cd $GI_HOME/OPatch [[email protected] 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.
[email protected]> 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 [email protected]> HOST_NAME INSTANCE_NAME ---------------------------------------------------------------- ---------------- oracleenespanol1 TEST1 [email protected]> 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 [email protected]> 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 [email protected]> HOST_NAME INSTANCE_NAME ---------------------------------------------------------------- ---------------- oracleenespanol1 TEST1 [email protected]> 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.
[email protected]> 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> [email protected]> ALTER SYSTEM ARCHIVE LOG CURRENT; [email protected]> 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.
[email protected]> select OPEN_MODE,DATAGUARD_BROKER,GUARD_STATUS,DATABASE_ROLE from v$database; OPEN_MODE DATAGUAR GUARD_S DATABASE_ROLE -------------------- -------- ------- ---------------- MOUNTED DISABLED NONE PHYSICAL STANDBY [email protected]> alter database open; Database altered. [email protected]> select OPEN_MODE,DATAGUARD_BROKER,GUARD_STATUS,DATABASE_ROLE from v$database; OPEN_MODE GUARD_S DATABASE_ROLE -------------------- ------- ---------------- READ ONLY NONE PHYSICAL STANDBY [email protected]> select process , status from v$managed_standby where process like '%MRP%'; no rows selected [email protected]> 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 [email protected]> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; [email protected]> select process , status from v$managed_standby where process ='MRP0'; PROCESS STATUS --------- ------------ MRP0 APPLYING_LOG [email protected]> 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 [email protected]> 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.
[email protected]> 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 [email protected]> 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 [email protected]> 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 :)
7 Comments. Leave new
Nice work, Rene, Happy new year, hope you have great 2014.
Thank you Jason, wish you the same
Thanks. Very helpful
Thanks Khan :)
hi, Mr. Antunez,
thank you for the kindness to share this info. Just a quick question. In the standby database patching, don’t you need to shut down the redo log apply before applying the patching?
If so, when should you turn the redo log apply back on? before patching the primary databae or after patching the primary database? thank you so much.
regards
Ryan
Hi Ryan
When patching the Standby Binaries, no there is no need.
On the primary, it all depends if its RAC rolling or not. When patching the primary, we need to verify that is in Read Only Mode if the patch Readme advises Patches that are listed as RAC Rolling Installable in the patch README can be applied on the primary with the standby performing recovery in read only mode.
However, patches that are not RAC Rolling Installable must stop read only recovery on the standby,
bring the standby database to the mount state, and restart recovery after applying the patch to the primary database.
Regards
René
thanks very much, those this work with 12c too…