Runbook6 steps

After patching

Confirm datapatch ran, every component is VALID, nothing was left invalid or unusable, PDBs opened cleanly and the alert log is quiet.

Applied patches (datapatch history)Release updates and one-off patches applied through datapatch, newest first. Confirms the SQL side of a patch actually ran.
Installed components and their statusEverything in DBA_REGISTRY. Anything not VALID after a patch or upgrade needs attention before you hand the database back.
Invalid objectsEvery invalid object by owner and type, typically left behind by a deployment or patch. The recompile script fixes most of them.
Unusable indexes and index partitionsIndexes marked UNUSABLE, typically after a partition operation or a direct-path load. Queries that need them will either fail or fall back to full scans.
Unresolved plug-in violationsWhy a PDB opened in restricted mode: patch mismatches, parameter conflicts and missing options, with Oracle's suggested fix in the message.
Alert log errors in the last 24 hoursORA- and TNS- errors plus checkpoint warnings from the alert log, read through SQL. It can be slow when the alert log is very large.

Open in denrepo

The whole runbook as one file: SQL> @patching

patching.sql

1-- denrepo runbook: After patching2-- Confirm datapatch ran, every component is VALID, nothing was left invalid or unusable, PDBs opened cleanly and the alert log is quiet.3-- Source: https://denrepo.com/runbooks/patching/4 5SET ECHO OFF6 7-- Not yet verified8PROMPT9PROMPT ==== 1 of 6: Applied patches (datapatch history) ====10CLEAR COLUMNS11SET LINESIZE 180 PAGESIZE 100 TRIMOUT ON TAB OFF12COLUMN patch_id    HEADING "PATCH"   FORMAT 999999999913COLUMN action      FORMAT A1014COLUMN status      FORMAT A1015COLUMN applied     FORMAT A1616COLUMN description FORMAT A70 TRUNCATE17 18SELECT patch_id, action, status,19       TO_CHAR(action_time, 'YYYY-MM-DD HH24:MI') AS applied,20       description21FROM dba_registry_sqlpatch22ORDER BY action_time DESC;23 24-- Not yet verified25PROMPT26PROMPT ==== 2 of 6: Installed components and their status ====27CLEAR COLUMNS28SET LINESIZE 140 PAGESIZE 100 TRIMOUT ON TAB OFF29COLUMN comp_id   HEADING "ID"        FORMAT A1030COLUMN comp_name HEADING "COMPONENT" FORMAT A4531COLUMN version   FORMAT A1532COLUMN status    FORMAT A1033 34SELECT comp_id, comp_name, version, status35FROM dba_registry36ORDER BY comp_id;37 38-- Tested on 12c, 19c · verified Sep 202639PROMPT40PROMPT ==== 3 of 6: Invalid objects ====41CLEAR COLUMNS42SET LINESIZE 150 PAGESIZE 200 TRIMOUT ON TAB OFF43COLUMN owner FORMAT A2044COLUMN object_type FORMAT A2045COLUMN object_name FORMAT A3046SELECT owner,47       object_type,48       object_name,49       status50FROM   dba_objects51WHERE  status = 'INVALID'52ORDER BY owner, object_type, object_name;53 54-- Not yet verified55PROMPT56PROMPT ==== 4 of 6: Unusable indexes and index partitions ====57CLEAR COLUMNS58SET LINESIZE 120 PAGESIZE 100 TRIMOUT ON TAB OFF59COLUMN owner      FORMAT A2060COLUMN index_name HEADING "INDEX"     FORMAT A3061COLUMN part_name  HEADING "PARTITION" FORMAT A3062COLUMN status     FORMAT A863 64SELECT owner, index_name, NULL AS part_name, status65FROM dba_indexes WHERE status = 'UNUSABLE'66UNION ALL67SELECT index_owner, index_name, partition_name, status68FROM dba_ind_partitions WHERE status = 'UNUSABLE'69UNION ALL70SELECT index_owner, index_name, subpartition_name, status71FROM dba_ind_subpartitions WHERE status = 'UNUSABLE'72ORDER BY 1, 2, 3;73 74-- Not yet verified75PROMPT76PROMPT ==== 5 of 6: Unresolved plug-in violations ====77PROMPT (run on CDB root)78CLEAR COLUMNS79SET LINESIZE 180 PAGESIZE 100 TRIMOUT ON TAB OFF80COLUMN name    HEADING "PDB" FORMAT A2081COLUMN type    FORMAT A982COLUMN status  FORMAT A1083COLUMN message FORMAT A100 WORD_WRAPPED84 85SELECT name, type, status, message86FROM pdb_plug_in_violations87WHERE status <> 'RESOLVED'88ORDER BY name, time;89 90-- Not yet verified91PROMPT92PROMPT ==== 6 of 6: Alert log errors in the last 24 hours ====93CLEAR COLUMNS94SET LINESIZE 200 PAGESIZE 200 TRIMOUT ON TAB OFF95COLUMN at_time HEADING "TIME" FORMAT A1996COLUMN message FORMAT A150 WORD_WRAPPED97 98SELECT TO_CHAR(originating_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS at_time,99       SUBSTR(message_text, 1, 300) AS message100FROM v$diag_alert_ext101WHERE originating_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY102  AND (message_text LIKE '%ORA-%'103       OR message_text LIKE '%TNS-%'104       OR message_text LIKE '%Checkpoint not complete%')105ORDER BY originating_timestamp DESC;106

-- denrepo runbook: After patching
-- Confirm datapatch ran, every component is VALID, nothing was left invalid or unusable, PDBs opened cleanly and the alert log is quiet.
-- Source: https://denrepo.com/runbooks/patching/

SET ECHO OFF

-- Not yet verified
PROMPT
PROMPT ==== 1 of 6: Applied patches (datapatch history) ====
CLEAR COLUMNS
SET LINESIZE 180 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN patch_id    HEADING "PATCH"   FORMAT 9999999999
COLUMN action      FORMAT A10
COLUMN status      FORMAT A10
COLUMN applied     FORMAT A16
COLUMN description FORMAT A70 TRUNCATE

SELECT patch_id, action, status,
       TO_CHAR(action_time, 'YYYY-MM-DD HH24:MI') AS applied,
       description
FROM dba_registry_sqlpatch
ORDER BY action_time DESC;

-- Not yet verified
PROMPT
PROMPT ==== 2 of 6: Installed components and their status ====
CLEAR COLUMNS
SET LINESIZE 140 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN comp_id   HEADING "ID"        FORMAT A10
COLUMN comp_name HEADING "COMPONENT" FORMAT A45
COLUMN version   FORMAT A15
COLUMN status    FORMAT A10

SELECT comp_id, comp_name, version, status
FROM dba_registry
ORDER BY comp_id;

-- Tested on 12c, 19c · verified Sep 2026
PROMPT
PROMPT ==== 3 of 6: Invalid objects ====
CLEAR COLUMNS
SET LINESIZE 150 PAGESIZE 200 TRIMOUT ON TAB OFF
COLUMN owner FORMAT A20
COLUMN object_type FORMAT A20
COLUMN object_name FORMAT A30
SELECT owner,
       object_type,
       object_name,
       status
FROM   dba_objects
WHERE  status = 'INVALID'
ORDER BY owner, object_type, object_name;

-- Not yet verified
PROMPT
PROMPT ==== 4 of 6: Unusable indexes and index partitions ====
CLEAR COLUMNS
SET LINESIZE 120 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN owner      FORMAT A20
COLUMN index_name HEADING "INDEX"     FORMAT A30
COLUMN part_name  HEADING "PARTITION" FORMAT A30
COLUMN status     FORMAT A8

SELECT owner, index_name, NULL AS part_name, status
FROM dba_indexes WHERE status = 'UNUSABLE'
UNION ALL
SELECT index_owner, index_name, partition_name, status
FROM dba_ind_partitions WHERE status = 'UNUSABLE'
UNION ALL
SELECT index_owner, index_name, subpartition_name, status
FROM dba_ind_subpartitions WHERE status = 'UNUSABLE'
ORDER BY 1, 2, 3;

-- Not yet verified
PROMPT
PROMPT ==== 5 of 6: Unresolved plug-in violations ====
PROMPT (run on CDB root)
CLEAR COLUMNS
SET LINESIZE 180 PAGESIZE 100 TRIMOUT ON TAB OFF
COLUMN name    HEADING "PDB" FORMAT A20
COLUMN type    FORMAT A9
COLUMN status  FORMAT A10
COLUMN message FORMAT A100 WORD_WRAPPED

SELECT name, type, status, message
FROM pdb_plug_in_violations
WHERE status <> 'RESOLVED'
ORDER BY name, time;

-- Not yet verified
PROMPT
PROMPT ==== 6 of 6: Alert log errors in the last 24 hours ====
CLEAR COLUMNS
SET LINESIZE 200 PAGESIZE 200 TRIMOUT ON TAB OFF
COLUMN at_time HEADING "TIME" FORMAT A19
COLUMN message FORMAT A150 WORD_WRAPPED

SELECT TO_CHAR(originating_timestamp, 'YYYY-MM-DD HH24:MI:SS') AS at_time,
       SUBSTR(message_text, 1, 300) AS message
FROM v$diag_alert_ext
WHERE originating_timestamp > SYSTIMESTAMP - INTERVAL '1' DAY
  AND (message_text LIKE '%ORA-%'
       OR message_text LIKE '%TNS-%'
       OR message_text LIKE '%Checkpoint not complete%')
ORDER BY originating_timestamp DESC;