Steps to apply Database patches 4Q-2020 (Oct 2020 Release Update 12.2.0.1.201020)

Please find the instructions below to apply db patches to Grid_Home and Db_Home for Q4-2020.

Steps also include a bug fix patch which has been rolled-back and re-applied.

##################################################

## Instructions to apply Database patches 4Q-2020
##################################################
-- ------------------------------------------
-- Summary of patches to be installed
-- ------------------------------------------

Patch No --- Patch Description ---------------------------------------------------------------------------- Which Home to Patch -----------------------
31741641 Database Oct 2020 Release Update 12.2.0.1.201020       Only DB home for Oracle Non-RAC setup.
31802727 OCW Oct 2020 Release Update 12.2.0.1.201020            Both DB homes and Grid home
31749575 ACFS Oct 2020 Release Update 12.2.0.1.201020Footnote 2 Only Grid home
26839277 DBWLM Release Update 12.2.0.1.170913Footnote 2         Only Grid home
31805785 Tomcat Release Update 12.2.0.1.0Footnote 2             Only Grid home

-- ------------------------------------------
-- Check for patch s/w location
-- ------------------------------------------
-bash-4.1$ pwd
/tmp2/Patch_4Q-2020
-bash-4.1$ ls -ltr
drwxr-x--- 8 oracle dba       4096 Oct 10 14:48 31750094
drwxrwxr-x 4 oracle dba       4096 Oct 11 18:43 30910264
-rw-r----- 1 oracle dba     691159 Oct 30 03:33 p30910264_12201201020DBOCT2020RU_Linux-x86-64.zip
-rw-r----- 1 oracle dba 2220434531 Oct 30 03:34 p31750094_122010_Linux-x86-64.zip
-rw-r----- 1 oracle dba  118408624 Oct 30 03:34 p6880880_200000_Linux-x86-64.zip

-- ------------------------------------------
-- check for Grid_Home and Oracle_Home
-- ------------------------------------------
$ cat /etc/oratab | grep -v '^#\|^\s*$'

-- ------------------------------------------
-- Comment cron jobs
-- ------------------------------------------
:g/^/s/^/##db_patching##--/

-- ------------------------------------------
-- shut down db s/w
-- ------------------------------------------
. oraenv
+ASM
crsctl stop has

-- -------------------------------------------------------------------------------------
-- backup Grid_Home and Oracle_Home in compressed format
-- -------------------------------------------------------------------------------------
# tar -czvf grid_home_29102020.tar.gz /u02/app/oracle/product/12.2.0/grid --exclude='/u02/app/oracle/product/12.2.0/grid/rdbms/audit'
# tar -czvf db_home_29102020.tar.gz /u01/app/oracle/product/12.2.0/dbhome_1 --exclude='/u01/app/oracle/admin'
# tar -czvf inventory_29102020.tar.gz /u02/app/oraInventory

-- --------------------------------------------------------------------------------------------
-- check for
-- Opatch version: to check if its latest. if its not, please copy the latest opatch in Oracle Home
-- Oracle Home: to make sure you are patching correct Oracle Home
-- Central Inventory: to check the inventory location
-- --------------------------------------------------------------------------------------------
$ORACLE_HOME/OPatch/opatch lsinventory |grep 'OPatch version'|head -1
$ORACLE_HOME/OPatch/opatch lsinventory |grep 'Oracle Home'
$ORACLE_HOME/OPatch/opatch lsinventory |grep 'Central Inventory'

-- ------------------------------------------------------
-- unlock Grid Home with root user
-- ------------------------------------------------------
# /u02/app/oracle/product/12.2.0/grid/crs/install/roothas.pl -unlock

-- ------------------------------------------------------
-- Apply Grid patches
-- ------------------------------------------------------
[oracle@mydb OPatch]$
$ORACLE_HOME/OPatch/opatch apply -oh /u02/app/oracle/product/12.2.0/grid -local /tmp2/Patch_4Q-2020/31750094/31802727
$ORACLE_HOME/OPatch/opatch apply -oh /u02/app/oracle/product/12.2.0/grid -local /tmp2/Patch_4Q-2020/31750094/31749575
$ORACLE_HOME/OPatch/opatch apply -oh /u02/app/oracle/product/12.2.0/grid -local /tmp2/Patch_4Q-2020/31750094/26839277
$ORACLE_HOME/OPatch/opatch apply -oh /u02/app/oracle/product/12.2.0/grid -local /tmp2/Patch_4Q-2020/31750094/31805785
$ORACLE_HOME/OPatch/opatch lsinventory|grep "applied"

-- ------------------------------------------------------
-- lock Grid Home with root user
-- make sure all db s/w get started.
-- ------------------------------------------------------
# /u02/app/oracle/product/12.2.0/grid/crs/install/roothas.pl -postpatch

-- --------------------------------------------------------------
-- login to database and check for invalid SYS and SYSTEM objects
-- ---------------------------------------------------------------
set pages 9999 lines 160
col owner for a12
col object_name for a30
col object_type for a20
col comp_name for a40
col COMP_ID for a12
col VERSION for a16
select comp_name,comp_id,version,status from dba_registry;
select owner,object_name,object_type,status from  dba_objects where owner in ('SYS','SYSTEM') and status = 'INVALID' order by owner,object_type, object_name;

-- ------------------------------------------------------
-- for all db, set job_queue_process to 0
-- ------------------------------------------------------
set lines 200
select host_name,instance_name from v$instance;
show parameter job_queue
ALTER SYSTEM SET job_queue_processes=0;
show parameter job_queue

-- ------------------------------------------------------
-- shut down again db s/w
-- ------------------------------------------------------
. oraenv
+ASM
crsctl stop has

-- ------------------------------------------------------
-- Check conflict and apply sub-patch 31802727
-- ------------------------------------------------------
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp2/Patch_4Q-2020/31750094/31802727
$ORACLE_HOME/OPatch/opatch apply -oh /u01/app/oracle/product/12.2.0/dbhome_1 -local /tmp2/Patch_4Q-2020/31750094/31802727

-- ------------------------------------------------------
-- rollback bug fix patch 30910264
-- ------------------------------------------------------
$ORACLE_HOME/OPatch/opatch lsinventory|grep "30910264"
$ORACLE_HOME/OPatch/opatch rollback -id 30910264 -local
$ORACLE_HOME/OPatch/opatch lsinventory|grep "30910264"

-- ------------------------------------------------------
-- Check conflict and apply sub-patch 31741641
-- ------------------------------------------------------
$ORACLE_HOME/OPatch/opatch prereq  CheckConflictAgainstOHWithDetail  -phBaseDir /tmp2/Patch_4Q-2020/31750094/31741641
$ORACLE_HOME/OPatch/opatch apply -oh /u01/app/oracle/product/12.2.0/dbhome_1 -local /tmp2/Patch_4Q-2020/31750094/31741641

-- ------------------------------------------------------
-- apply bug fix patch 30910264
-- ------------------------------------------------------
$ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir /tmp2/Patch_4Q-2020/30910264
$ORACLE_HOME/OPatch/opatch apply -oh /u01/app/oracle/product/12.2.0/dbhome_1 -local /tmp2/Patch_4Q-2020/30910264

-- ------------------------------------------------------
-- check and make sure all patches get applied
-- ------------------------------------------------------
$ORACLE_HOME/OPatch/opatch lsinventory|grep "applied"

-- ------------------------------------------------------
-- postpatch to start all resources
-- make sure all db s/w get started.
-- ------------------------------------------------------
# /u02/app/oracle/product/12.2.0/grid/crs/install/roothas.pl -postpatch

crsctl stat res -t

-- ------------------------------------------------------
-- for all db, set job_queue_process back to original value
-- ------------------------------------------------------
set lines 200
select host_name,instance_name from v$instance;
show parameter job_queue
ALTER SYSTEM SET job_queue_processes=500;
show parameter job_queue

-- ------------------------------------------------------
-- run datapatch for each database
-- ------------------------------------------------------
cd $ORACLE_HOME/OPatch
./datapatch -verbose

-- ---------------------------------------------------------------------------------
-- check registry for each database to make sure new patches are visible in registry
-- ---------------------------------------------------------------------------------
set lines 500 pages 1000
set SERVEROUT ON
set long 2000000
col action_time for a12
col action for a10
col bundle_series for a4
col comments for a30
col description for a58
col namespace for a20
col status for a10
col version for a10
select TO_CHAR(action_time, 'YYYY-MM-DD') AS action_time, action, status, description, version, patch_id, bundle_series
 FROM   sys.dba_registry_sqlpatch ORDER by action_time;

-- ------------------------------------------------------
-- run utlrp for each db
-- ------------------------------------------------------
@?/rdbms/admin/utlrp.sql

-- --------------------------------------------------------------------------------------------
-- login to database and check for invalid SYS and SYSTEM objects
-- ---------------------------------------------------------------------------------------------
set pages 9999 lines 160
col owner for a12
col object_name for a30
col object_type for a20
col comp_name for a40
col COMP_ID for a12
col VERSION for a16
select comp_name,comp_id,version,status from dba_registry;
select owner,object_name,object_type,status from  dba_objects where owner in ('SYS','SYSTEM') and status = 'INVALID' order by owner,object_type, object_name;

-- ------------------------------------------
-- Un-comment cron jobs
-- ------------------------------------------

:%s/##db_patching##--//g