Oracle 12c Zero downtime Patching on Grid Infrastructure
In this article we will throw some light on patching 12c grid and database using newly opatchauto .
Also Oracle has introduced Grid Infrastructure Out of Place ( OOP ) Patching using opatchauto .
Starting 12.2 , there is a new feature introduced with the latest version of opatchauto.
You can perform out of place ( OOP ) Patching .
This is helpful when the current Oracle Homes do not have sufficient space left and there is a need to shift the Oracle Homes to a new location .
This operation generally requires cloning of the Oracle Homes followed by Patching and then switching the CRS and the Database , including all the services to newly cloned Homes respectively .
All these operations can be performed at a single stretch by making use of OOP Patching .
However i am not covering OOP patching in this article because it is very nicely and in-detailed explained in Oracle (Doc ID 2419319.1)
#################### 12c Pre and Post Patching ####################
1) If data guard is used , remember to defer log shipping and re-enable log shipping after patch is applied
2) If you are using acl or sticky bit on database home , do remember to take backup of home permission and reapply after patch is completed
3) Take backup of database and oracle / grid homes
4) Compare database invalid objects pre and post patch
############# 12c Zero downtime grid and database Patching ##########
Zero Downtime patching provides a process and mechanism for rolling out a patch across a domain while allowing applications to continue to service requests.
You can apply a ZDT patch using OPatchAuto, which rolls out the change to one node at a time and allows a load balancer to redirect incoming traffic to the remaining nodes until the change is complete.
The Opatch utility has automated the patch application for the Oracle Grid Infrastructure (GI) home and the Oracle RAC database homes. It operates by querying existing configurations and automating the steps required for patching each Oracle RAC database home of same version and the GI home.
The utility must be executed by an operating system (OS) user with root privileges, and it must be executed on each node in the cluster if the GI home or Oracle RAC database home is in nonshared storage. The utility should not be run in parallel on the cluster nodes.
Depending on command line options specified, one invocation of opatchauto can patch the GI home, Oracle RAC database homes, or both GI and Oracle RAC database homes of the same Oracle release version as the patch. You can also roll back the patch with the same selectivity.
The current node and the DBs on it will be in the open state. The opatchauto utility will take them down automatically while patching .
Before running actual patch we can analyze patch
/oragridpx/app/12.1.0/grid/OPatch/opatchauto apply -analyze
Please note only to export CRS_HOME parameter .
cd /oratmp/patches/Jan2020/30463691/30464119export CRS_HOME=/oragridpx/app/12.1.0/grid
/oragridpx/app/12.1.0/grid/OPatch/opatchauto apply
In our case we take full downtime so I personally apply 2nd node at same time while first node is going on due to shortage of time . On 2nd node we can initiate patch while first node patch is ongoing mentioning nonrolling option
/oragridpx/app/12.1.0/grid/OPatch/opatchauto apply -nonrolling
Please note that If database has java been used you need downtime to apply java patch as below with oracle user from any one of node
Also if we are using acfs we might have to apply acfs patch separately just like java patch
opatch apply
srvctl start database -d Dbname
alter system set cluster_database=false scope=spfile ;
startup upgrade
$ORACLE_HOME/OPatch/datapatch -verbose
sqlplus "/ as sysdba"
alter system set cluster_database=true scope=spfile ;
srvctl start database -d Dbname
@?/rdbms/admin/utlrp.sql
select count(*) , owner from dba_objects where status !='VALID' group by owner ;
select * from dba_registry_sqlpatch ;
--> verify grid services are up
crs_stat | awk -F= '/NAME=/{n=$2}/TYPE=/{t=$2}/TARGET=/{g=$2}/STATE=/{s=$2; printf("%-45s%-15s%-10s%-30s\n", n,t,g,s)}'
############# Resume from failed Psu Patch ##########
In 12c, we have the patching sessions with their configuration in JSON files.
So go to directory $grid_home/OPatch/auto/dbsessioninfo/
Run below with Grid user
cd /oratmp/patches/Jan2020/30463691/30464119
export CRS_HOME=/oragridpx/app/12.1.0/grid
/oragridpx/app/12.1.0/grid/OPatch/opatchauto resume with session id "DZSQ"
############# How to get Clusterware state out of "Rolling Upgrade" ##########
At time of patching or Upgrade at time we see below error . We need to remove server from rolling upgrade/patch more .
PRVG-13410 : The state of Oracle Clusterware is not consistent to allow upgrade. [Expected = "NORMAL" ; Found = "ROLLING UPGRADE"].
Checks Done :
crsctl query crs releasepatch
crsctl query crs softwarepatch
crsctl query crs activeversion -f
crsctl query crs softwareversion
ASMCMD [+] > showversion
ASMCMD [+] > showclusterstate
+ASM1:>SELECT SYS_CONTEXT('sys_cluster_properties', 'cluster_state') as "SYSTEM_STATE" FROM dual;
kfod op=patchlvl
kfod op=patches
Fix Tried :
ASMCMD> showclusterstate
$crsctl stop rollingpatch
crsctl stop rollingupgrade
+ASM1:>ALTER SYSTEM STOP ROLLING PATCH;
+ASM1:>ALTER SYSTEM STOP ROLLING MIGRATION;
$GI_HOME/crs/install/rootcrs.pl -prepatch
$GI_HOME/bin/clscfg -patch
$GI_HOME/crs/install/rootcrs.pl -postpatch
Known Issue :
BUG 30140462 - CLUSTERWARE STATE IS STILL ROLLING UPGRADE AFTER PERFORMING OFFLINE DOWNGRADE
Bug 25197395 - Command 'crsctl startupgrade' hangs having many Databases (Doc ID 25197395.8)
rootupgrade.sh Fails with CRS-1136: Rejecting the rolling upgrade mode change because the cluster is being patched (Doc ID 2494827.1)
For some reason, patches are available from “opatch lsinventory”, but they are missing from kfod output:
############# Rollback Psu and DB Java patch ##########
export CRS_HOME=/oragridpx/app/12.1.0/grid
/oragridpx/app/12.1.0/grid/OPatch/opatchauto rollback
/oragridpx/app/12.1.0/grid/OPatch/opatch lspatches
To rollback database Java patch . From binary rollback on both nodes however on database only from any 1 node with oracle user
cd /oratmp/patches/Jan2020/30463691/30502041
opatch rollback -id 27105253
srvctl start database -d Dbname
alter system set cluster_database=false scope=spfile ;
startup upgrade
$ORACLE_HOME/OPatch/datapatch -verbose
sqlplus "/ as sysdba"
alter system set cluster_database=true scope=spfile ;
srvctl start database -d Dbname
@?/rdbms/admin/utlrp.sql
select count(*) , owner from dba_objects where status !='VALID' group by owner ;
select * from dba_registry_sqlpatch ;
For troubleshooting reference :
1) https://docs.oracle.com/cd/E24628_01/doc.121/e39376/troubleshooting_opatchauto.htm#BJFBIDEF
############# Oracle 12c Patching Not using opatchauto ##########
-- as super user
$GRID_HOME/crs/install/rootcrs.sh -prepatch
-- as grid owner,
$GRID_HOME/bin/patchgen commit -pi 12345678
$GRID_HOME/bin/patchgen commit -pi 23456789
-- as super user
$GRID_HOME/crs/install/rootcrs.sh -postpatch
############# Rollback Psu and DB Java patch ##########
To rollback Psu please note only to export CRS_HOME parameter .and run below with root
cd /oratmp/patches/Jan2020/30463691/30464119export CRS_HOME=/oragridpx/app/12.1.0/grid
/oragridpx/app/12.1.0/grid/OPatch/opatchauto rollback
/oragridpx/app/12.1.0/grid/OPatch/opatch lspatches
To rollback database Java patch . From binary rollback on both nodes however on database only from any 1 node with oracle user
cd /oratmp/patches/Jan2020/30463691/30502041
opatch rollback -id 27105253
srvctl start database -d Dbname
alter system set cluster_database=false scope=spfile ;
startup upgrade
$ORACLE_HOME/OPatch/datapatch -verbose
sqlplus "/ as sysdba"
alter system set cluster_database=true scope=spfile ;
srvctl start database -d Dbname
@?/rdbms/admin/utlrp.sql
select count(*) , owner from dba_objects where status !='VALID' group by owner ;
select * from dba_registry_sqlpatch ;
For troubleshooting reference :
1) https://docs.oracle.com/cd/E24628_01/doc.121/e39376/troubleshooting_opatchauto.htm#BJFBIDEF
############# Oracle 12c Patching Not using opatchauto ##########
Download and copy both OCT2020PSU and bugfix PSU and extract under /u01/src/
==================================================
For Grid Infrastructure Home, as home user:
opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/src/31750108/31771877
opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/src/31750108/31772784
opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/src/31750108/31773437
opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/src/31750108/31780966
For Database home, as home user:
opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/src/31750108/31771877
opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /u01/src/31750108/31772784
==================================================
Run the pre root script.
If this is a GI Home, as the root user execute:
/u01/app/19.0.0/grid/crs/install/rootcrs.sh -prepatch
==================================================
3) from GI home as oracle user.
GRID_HOME=/u01/app/19.0.0/grid
export PATH=$GRID_HOME/bin:$GRID_HOME/OPatch:$PATH:/usr/css/bin
INcluding Bugfix
opatch apply -local /u01/src/32242453/32242453 -oh /u01/app/19.0.0/grid
opatch apply -local /u01/src/31750108/31773437 -oh /u01/app/19.0.0/grid
opatch apply -local /u01/src/31750108/31771877 -oh /u01/app/19.0.0/grid
opatch apply -local /u01/src/31750108/31780966 -oh /u01/app/19.0.0/grid
4. Apply On DB home.
ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:/usr/css/bin
/u01/src/31750108/31772784/custom/scripts/prepatch.sh -dbhome /u01/app/oracle/product/19.0.0/dbhome_1
opatch apply -local /u01/src/32242453/32242453 -oh /u01/app/oracle/product/19.0.0/dbhome_1
opatch apply -local /u01/src/31750108/31771877 -oh /u01/app/oracle/product/19.0.0/dbhome_1
/u01/src/31750108/31772784/custom/scripts/postpatch.sh -dbhome /u01/app/oracle/product/19.0.0/dbhome_1
==================================================
Run the pre root script.
If this is a GI Home, as the root user execute:
/u01/app/19.0.0/grid/crs/install/rootcrs.sh -postpatch
################################
Know Issue : Crs Home Not Stopped
################################
Please check the below steps to fix that issue(Doc ID 2703720.1) It happened today in cbem db1a uat server.
1) As grid user , execute :
/u01/app/19.0.0/grid/bin/cluutil -ckpt -oraclebase $ORACLE_BASE -writeckpt -name ROOTCRS_PREPATCH -state START
2) Verify:
/u01/app/19.0.0/grid/bin/cluutil -ckpt -oraclebase $ORACLE_BASE -chkckpt -name ROOTCRS_PREPATCH -status
START
3) Check for all nodes of the cluster and perform the above actions where required.
4) Re-execute opatchauto apply command or 'rootcrs.sh -prepatch' as required
" Bug 33036568 : HAIP FLIP CAUSING NODE REBOOT DURING 19.11 GI RU PATCHING ON IBM AIX",
ASM process is terminated by LMON process during postpatch on grid home 19.11
############# Issues commonly faced ##########
1) While applying datapatch at dataabase we face ORA-20001 for which below metalink document cant be referred
Queryable Patch Inventory - Issues/Solutions for ORA-20001: Latest xml inventory is not loaded into table (Doc ID 1602089.1)
verify_queryable_inventory returned ORA-20001: Latest xml inventory is not loaded into table
select dbms_sqlpatch.verify_
VERIFY_QUERYABLE_INVENTORY
------------------------------
ORA-20001: Latest xml inventory is not loaded into table
alter system set "_bug27355984_xt_preproc_
alter system set "_enable_ptime_update_for_sys"
After patching :
alter system reset "_bug27355984_xt_preproc_
alter system reset "_enable_ptime_update_for_sys"
Similarly if ORA-20008: is reported we need use below trace
>> Before patching
SQL: alter session set events '18219841 trace name context forever';
>> After patching
alter session set events '18219841 trace name context off';
2) Session disconnect during opatchauto .
We can rerun opatchauto again . Oracle will internally determine missing patch and apply same
3) Due to failed opatchauto patching it happens sometimes that crs does not start on node where it failed . When we try to rerun opatchauto it shows crs is not configured
i personally followed below
A) Stop crs on 2nd node where we are not patching to avoid mismatch of crs version between nodes
B) ran below on node where we getting error . It was part of post patching till 11g
/optware/grid/11.2.0.4/crs/install/rootcrs.pl -patch
C) rerun opatchauto on node where it failed
4) oracle home space issue during and after patching
Before patching we can initiate below to ensure prevoius backups are removed. Please refer to 550522.1 to troubleshoot space issues during patching
$ opatch util cleanup
How To Avoid Disk Full Issues Because OPatch Backups Take Big Amount Of Disk Space. (Doc ID 550522.1)
############# Old 11g approach of applying GI patch ##########
In case resume approach of above doesnt help we must have clarity on traditional way of GI patch . Seeing logs which step patch failed we need to proceed accordingly
Hence below old steps are shared only for backup purpose
APPLY PATCH TO GRID HOME => /optware/grid/11.2.0.4
************************
1. Run the pre root script. If this is a GI Home, as the root user execute
***********************************************************************
/optware/grid/11.2.0.4/crs/install/rootcrs.pl -unlock
2. As the GI home owner execute, apply the OCW patch
**************************************************
/optware/grid/11.2.0.4/OPatch/opatch napply -oh /u01/app/11204 -local /u02/patch/22191577/21948348
3. As the GI home owner execute, apply the ACFS patch
**************************************************
/optware/grid/11.2.0.4/OPatch/opatch napply -oh /u01/app/11204 -local /u02/patch/22191577/21948355
4. As the GI home owner execute, apply the DB PSU Patch
*****************************************************
/optware/grid/11.2.0.4/OPatch/opatch apply -oh /u01/app/11204 -local /u02/patch/22191577/21948347
5. Run the post script. As the root user execute:
**********************************************
/optware/grid/11.2.0.4/rdbms/install/rootadd_rdbms.sh
6. If this is a GI Home, as the root user execute:
***********************************************
/optware/grid/11.2.0.4/crs/install/rootcrs.pl -patch
