Oracle 12c Zero downtime Patching on Grid Infrastructure

 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 . 

Below steps  is designed considering you are  using bundle patch of grid + database Psu   and DB jvm .    If we are not using  bundle patch we might have to   apply  psu separately on  Grid and Db home . 

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/30464119

export 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 

 cd  /oratmp/patches/Jan2020/30463691/30502041  
  
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:

-- 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/30464119

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    ########## 

 

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_queryable_inventory from dual;

VERIFY_QUERYABLE_INVENTORY

--------------------------------------------------------------------------------

ORA-20001: Latest xml inventory is not loaded into table

alter system set "_bug27355984_xt_preproc_timeout"=1000 scope=spfile ;

alter system set "_enable_ptime_update_for_sys"=TRUE   scope=spfile ;

After patching :

alter system reset  "_bug27355984_xt_preproc_timeout"  scope=spfile ;

alter system reset  "_enable_ptime_update_for_sys"   scope=spfile ;

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