Friday, November 16, 2012

Applying Patch Set using Standby-First

According to metalink note 278641.1 before applying a patch to a physical standby environment log transport must be stopped on the primary site. Although the note says to shutdown all standby instance, it should still be possible to do a rolling patch on the standby environment if patch allows it. However recovery may need to be stopped before patching and since redo transport is stopped, there won't be anything to recover anyway.
From 11.2.0.1 onwards if the patch set says it's applicable in a standby-first manner then these patches could be applied on standby without stopping redo transport nor recovery on the standby database.
This post shows the highlights of applying GI PSU 11.2.0.3.4 (which is a rolling and Standby-First applicable patch) to a physical data guard environment.
Before the patch apply both standby and primary are at the same patch level.primary
rac5 psu]$ /opt/app/oracle/product/11.2.0/db_2/OPatch/opatch lsinventory -local | grep "Database Patch Set Update"
Patch description:  "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)" 
Standby
rac5b psu]$ /opt/app/oracle/product/11.2.0/db_2/OPatch/opatch lsinventory -local | grep "Database Patch Set Update"
Patch description:  "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)"
Identify the apply instance and start patch applying on the none apply instance first
show database rac11g2s

Database - rac11g2s

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    rac11g2s1 (apply instance)
    rac11g2s2

Database Status:
SUCCESS
In this case apply instance was rac11g2s1 running on rac4b hence patching will being on rac5b.
Once patched rac5b will be at a higher patch level than rac4b and all nodes on primary
rac5b psu]$ /opt/app/oracle/product/11.2.0/db_2/OPatch/opatch lsinventory -local | grep "Database Patch Set Update"
Patch description:  "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)"
Switch the apply instance to patched node and patch the remaining nodes as well
DGMGRL> edit database rac11g2s set state ='APPLY-ON' with apply instance = 'rac11g2s2';
Succeeded.
DGMGRL> show database rac11g2s
Database - rac11g2s

  Role:            PHYSICAL STANDBY
  Intended State:  APPLY-ON
  Transport Lag:   0 seconds
  Apply Lag:       0 seconds
  Real Time Query: OFF
  Instance(s):
    rac11g2s1
    rac11g2s2 (apply instance)

Database Status:
SUCCESS

rac4b ~]$ /opt/app/oracle/product/11.2.0/db_2/OPatch/opatch lsinventory -local | grep "Database Patch Set Update"
Patch description:  "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)"
At this stage all Oracle binaries in standby environment are patched.
It is possible to run the data guard configuration like this with primary and standby at different patch level but there are limitations to how long this configuration will be supported (less than 1 month) and these are explained in 1265700.1. This gives time to evaluate the patch either by creating a snapshot standby or with role transition.


Once patch is evaluated on standby environment apply it on the primary site as well. Since this patch is applicable in a rolling fashion log transport will be active at all times during the patch application.
DGMGRL> show database rac11g2

Database - rac11g2

  Role:            PRIMARY
  Intended State:  TRANSPORT-ON
  Instance(s):
    rac11g21
    rac11g22

Database Status:
SUCCESS
After patching rac5
rac5 ~]$ /opt/app/oracle/product/11.2.0/db_2/OPatch/opatch lsinventory -local | grep "Database Patch Set Update"
Patch description:  "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)"
Shutdown the remaining instances and apply the patch (rac4 in this case) and at the same time execute the post-installation tasks on the already patched instance.
After patching rac4
rac4 psu]$ /opt/app/oracle/product/11.2.0/db_2/OPatch/opatch lsinventory -local | grep "Database Patch Set Update"
Patch description:  "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)"

Useful metalink notes
Oracle Patch Assurance - Data Guard Standby-First Patch Apply [ID 1265700.1]
Mixed Oracle Version support with Data Guard Redo Transport Services [ID 785347.1]
How do you apply a Patchset,PSU or CPU in a Data Guard Physical Standby configuration [ID 278641.1]