Oracle Database Patching: Common Steps

Introduction

This document outlines the essential steps for Oracle Database patching. It includes pre-checks, patch installation instructions, and post-patching validation steps. Always refer to the README file included with the patch for specific details

1. OS Check with Bit Information – OS Level

uname -a; 
isainfo -kv

2. OS Release Check – OS Level

cat /etc/release

3. Database Status

SET pages 9999 lines 300;
COL DB_NAME FOR a10;
COL HOST_NAME FOR a15;
COL DATABASE_ROLE FOR a15;
COL DB_VERSION FOR a15;
COL LOGINS FOR a15;
COL DB_UP_TIME FOR a22;
COL OPEN_MODE FOR a15;
COL log_mode FOR a15;
SELECT name DB_NAME, HOST_NAME, DATABASE_ROLE, OPEN_MODE, version DB_VERSION, LOGINS, 
  TO_CHAR(STARTUP_TIME,'DD-MON-YYYY HH24:MI:SS') "DB_UP_TIME", log_mode 
FROM v$database, gv$instance;

!date

4. Database Registry Status Check

COL COMP_NAME FOR a40;
COL VERSION FOR a15;
COL STATUS FOR a12;
SELECT COMP_NAME, VERSION, STATUS FROM dba_registry;

5. Invalid Object Check

COL owner FOR a15;
COL object_type FOR a20;
COL object_name FOR a20;
SELECT owner, object_type, object_name, status 
FROM dba_objects WHERE status != 'VALID' ORDER BY owner, object_type;

6. Already Applied PSU and Patch Details

COL ACTION FOR a20;
COL VERSION FOR a25;
COL BUNDLE_SERIES FOR a15;
COL COMMENTS FOR a60;
COL ACTION_TIME FOR a30;
SELECT ACTION_TIME, ACTION, VERSION, BUNDLE_SERIES, COMMENTS FROM registry$history;

7. Checking Opatch Version

opatch version

# If the above command does not work, set the following path
export PATH=$ORACLE_HOME/OPatch:$PATH
opatch version

# Alternative way
cd $ORACLE_HOME/OPatch
./opatch version

8. Opatch Inventory Check

opatch lsinventory

# If the above command does not work, set the following path
export PATH=$ORACLE_HOME/OPatch:$PATH
opatch lsinventory

# Alternative way
cd $ORACLE_HOME/OPatch
./opatch lsinventory

9. Copy Patch to a Directory

unzip -d <PATCH_TOP_DIR> <downloaded zip>.zip

10. Check for Patch Conflicts

cd <PATCH_TOP_DIR>/<new patch number>
opatch prereq CheckConflictAgainstOHWithDetail -ph ./

# Expected output:
# Prereq "checkConflictAgainstOHWithDetail" passed.

# Refer if failed:
# "Failed to apply PSU due to CheckActiveFilesAndExecutables check failure (Doc ID 2506432.1)"

# Notes:
# OPatch categorizes conflicts into two types:
# 1. Subset patch conflicts: Continue with the patch installation. The subset patch will automatically be rolled back.
# 2. Conflicts with an already applied patch: Stop installation and contact Oracle Support Services.

11. Pre-Patch Service Checks

ps -ef | grep pmon
ps -ef | grep tns
lsnrctl status <listener name>

12. Patch Installation Steps

Pre-Installation

1. Check Active Sessions

COL LOGON FOR a22;
COL USERNAME FOR a13;
COL STATUS FOR a10;
COL OSUSER FOR a12;
COL PROCESS FOR a10;
COL MACHINE FOR a25;
COL SPID FOR a10;
COL PROGRAM FOR a35;
SELECT TO_CHAR(a.logon_time,'DD-MON-YYYY HH24:MI:SS') logon, a.USERNAME, a.SID, a.SERIAL#, a.status,
  a.OSUSER, b.spid, a.PROCESS, a.MACHINE, a.PROGRAM, a.SQL_HASH_VALUE, a.sql_id, a.SQL_CHILD_NUMBER sql_child
FROM v$session a, v$process b
WHERE a.paddr = b.addr AND a.status = 'ACTIVE';

2. Switch Logfile

ALTER SYSTEM SWITCH LOGFILE;

3. Shutdown Database and Listener

shutdown immediate
lsnrctl stop <listener name>

4. Verify No Background Oracle Services Are Running

which fuser
<path to fuser> /$ORACLE_HOME/bin/oracle

5. Backup Oracle Home

cd /path/to/oracle_home
tar -cvf /path/to/backup/backup.tar .

Installation

1. Apply Patch

cd <PATCH_TOP_DIR>/<patch number>
opatch apply

2. Verify Patch Installation

opatch lsinventory

3. Restart Oracle Services

Deinstallation

In case any issue after patching you can rollback the patch.

1. Rollback Patch

opatch rollback -id <patch number>

2. Verify Oracle Inventory

opatch lsinventory

13. Post-Patch Checks

1. Restart Database and Run Datapatch

sqlplus /nolog
CONNECT / as sysdba
startup
cd $ORACLE_HOME/OPatch
./datapatch -verbose

2. Revalidate Invalid Objects

SQL> @?/rdbms/admin/utlprp.sql 0