Verify SQL patch history, component status and invalid objects

Command

-- 1) datapatch history (most recent at the bottom):
SELECT patch_id, patch_type, action, status, source_version, target_version,
       TO_CHAR(action_time,'YYYY-MM-DD HH24:MI') AS action_time, description
FROM   dba_registry_sqlpatch
ORDER  BY action_time;

-- Failed entries and their log files:
SELECT install_id, patch_id, action, status, logfile
FROM   dba_registry_sqlpatch
WHERE  status <> 'SUCCESS';

-- In a CDB, for all PDBs (from root):
SELECT con_id, patch_id, action, status
FROM   cdb_registry_sqlpatch ORDER BY con_id, action_time;

-- 2) Components must be VALID:
SELECT comp_id, comp_name, version_full, status
FROM   dba_registry ORDER BY comp_id;

-- 3) Invalid object summary (compare before/after utlrp):
SELECT owner, object_type, COUNT(*) AS invalid_cnt
FROM   dba_objects
WHERE  status = 'INVALID'
GROUP  BY owner, object_type
ORDER  BY invalid_cnt DESC;

What it does

Shows at a glance whether the SQL layer was really updated after patching (dictionary matching the binaries), which patch failed in which PDB, whether dictionary components are VALID, and the number of invalid objects.

When to use it

As the verification step after every datapatch, to capture a baseline before patching or upgrading, and when a mismatch is suspected after plugging in a PDB.

Caution

Read-only; no license required. SOURCE_VERSION/TARGET_VERSION are populated in the 18c+ RU model. If STATUS='WITH ERRORS', fix the error in LOGFILE and rerun datapatch. Some components (e.g. unused XDB/JAVA) may show INVALID/REMOVED; compare with the pre-patch state.