Oracle DB Patching: Key Steps

ADVERTISEMENT

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

1. OS Check with Bit Information (OS Level)

-- Check OS version and kernel info
uname -a                

-- Check OS bit architecture (32/64-bit)
isainfo -kv

2. OS Release Check (OS Level)

-- Confirm OS release version
cat /etc/release

3. Database Status

-- Set line width and page size for better output formatting
set lines 200 pages 200

-- Format columns for readability
col name for a15
col db_unique_name for a15
col open_mode for a15
col log_mode for a15
col logins for a15
col instance_name for a15
col HOST_NAME for a15

-- Set date format for session output
alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';

-- Query database and instance details
select name, db_unique_name, created, open_mode, log_mode, logins, instance_name, database_role, host_name, startup_time 
from v$instance, v$database;

-- Show current OS date and time
!date

4. Database Registry Status Check

-- Format columns for output readability
COL COMP_NAME FOR a40;
COL VERSION FOR a15;
COL STATUS FOR a12;

-- Check database components registry status
SELECT COMP_NAME, VERSION, STATUS FROM dba_registry;

5. Invalid Object Check

-- Format columns for better readability
COL owner FOR a15;
COL object_type FOR a20;
COL object_name FOR a20;

-- List invalid objects in the database
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

-- Format columns for patch history output
COL ACTION FOR a20;
COL VERSION FOR a25;
COL BUNDLE_SERIES FOR a15;
COL COMMENTS FOR a60;
COL ACTION_TIME FOR a30;

-- Retrieve patch and PSU application history
SELECT ACTION_TIME, ACTION, VERSION, BUNDLE_SERIES, COMMENTS FROM registry$history;

7. Checking Opatch Version

# Check OPatch version
opatch version

# If above fails, set OPatch path and check version again
export PATH=$ORACLE_HOME/OPatch:$PATH
opatch version

# Alternative method: navigate to OPatch directory and check version
cd $ORACLE_HOME/OPatch
./opatch version

8. Opatch Inventory Check

# List applied patches and Oracle inventory
opatch lsinventory

# If above fails, set OPatch path and retry
export PATH=$ORACLE_HOME/OPatch:$PATH
opatch lsinventory

# Alternative method: navigate to OPatch directory and list inventory
cd $ORACLE_HOME/OPatch
./opatch lsinventory

9. Copy Patch to Directory

# Extract patch files to the specified directory
unzip -d <PATCH_TOP_DIR> <downloaded zip>.zip

10. Check for Patch Conflicts

# Change to patch directory
cd <PATCH_TOP_DIR>/<new patch number>

# Verify patch conflicts before applying
opatch prereq CheckConflictAgainstOHWithDetail -ph ./

# Expected output:
# Prereq "checkConflictAgainstOHWithDetail" passed.

# Notes:
# Subset patch conflicts: Proceed; patch auto-rollbacks obsolete patches.
# Conflicts with applied patch: Stop and contact Oracle Support.

11. Pre-Patch Service Checks

# Check PMON process running
ps -ef | grep pmon

# Check TNS processes running
ps -ef | grep tns

# Verify listener status
lsnrctl status <listener>

12. Patch Installation Steps

Pre-Installation Checks

-- Set column formats for better readability
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;

-- Check active sessions before shutdown
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';

-- Switch logfile before shutdown
ALTER SYSTEM SWITCH LOGFILE;

-- Shutdown DB safely
shutdown immediate;

-- Stop listener
lsnrctl stop <listener>;

-- Verify no Oracle background services running
which fuser;
<path to fuser> /$ORACLE_HOME/bin/oracle;

-- Backup Oracle Home before patching
cd /path/to/oracle_home;
tar -cvf /path/to/backup/backup.tar .

Installation

cd <PATCH_TOP_DIR>/<patch number>

opatch apply               -- Apply patch

opatch lsinventory         -- Verify patch applied successfully

Restart Oracle Services

# Start listener and database after patching
lsnrctl start <listener>
sqlplus / as sysdba
startup

Rollback (if needed)

# Rollback patch if issues occur
opatch rollback -id <patch number>

# Verify rollback successful
opatch lsinventory

13. Post-Patch Checks

# Connect to SQL*Plus without login
sqlplus /nolog

# Connect as SYSDBA
CONNECT / as sysdba

# Start the database after patching
startup

# Change directory to OPatch folder
cd $ORACLE_HOME/OPatch

# Run datapatch to apply SQL patches post-binary patching
./datapatch -verbose

# Revalidate invalid objects in the database
SQL> @?/rdbms/admin/utlprp.sql 0

Important Notes & Tips

  • Always take a full backup of your database and Oracle Home before starting the patch process.
  • Test patches on a non-production environment first to avoid unexpected downtime.
  • Check for active sessions before shutdown to avoid interrupting users unexpectedly.
  • Use opatch prereq to detect conflicts before applying patches; resolve issues before proceeding.
  • Ensure listener and database are properly stopped before patching.
  • Monitor logs (opatch logs, database alert logs) during patching for errors.
  • Follow Oracle Support and README patch instructions precisely for your Oracle version and platform.
  • Use the rollback command if patching fails, but investigate the root cause before retrying.
  • Document patching steps and results for audit and troubleshooting.

ADVERTISEMENT