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

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.

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 :)

Interested in working with Rene? Schedule a tech call.