Business Insights Technical Track Pythian Life

How to Apply a Standby-First PSU Patch in a 2 node RAC environment

by
December 24, 2013
Posted in: Technical Track

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

  1. Take a backup of your GI_HOME/ORACLE_HOME  and inventory
  2. Verify you have a valid backup of your Primary DB
  3. Run against your binaries in your primary environment opatch prereq CheckConflictAgainstOHWithDetail as we did with the standby environment.
  4. Make sure that you have your ocm.rsp file created
  5. 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 :)

email

Author

Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Currently I am an Oracle ACE ; Speaker at Oracle Open World, Oracle Developers Day, OTN Tour Latin America and APAC region and IOUG Collaborate ; Co-President of ORAMEX (Mexico Oracle User Group); At the moment I am an Oracle Project Engineer at Pythian. In my free time I like to say that I'm Movie Fanatic, Music Lover and bringing the best from México (Mexihtli) to the rest of the world and in the process photographing it ;)

7 Comments. Leave new

Jason
January 1, 2014 5:13 am

Nice work, Rene, Happy new year, hope you have great 2014.

Reply
Rene Antunez
February 4, 2014 3:11 pm

Thank you Jason, wish you the same

Reply
Khan Man
February 4, 2014 2:08 pm

Thanks. Very helpful

Reply
Rene Antunez
February 4, 2014 3:11 pm

Thanks Khan :)

Reply
Ryan
November 25, 2014 10:52 am

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

Reply
René Antunez
November 26, 2014 12:41 am

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é

Reply
nelnado
October 18, 2019 12:18 pm

thanks very much, those this work with 12c too…

Reply

Leave a Reply

Your email address will not be published.

  • dba-cloud-services

    DBA Cloud Services
  • Annual Big Data

    • Contact Us