Hi all!
So, I was checking a new environment and noticed the dba_registry_sqlpatch was empty, when it actually shouldn’t:
SQL> select patch_id, patch_uid, version, action, action_time, status, description from dba_registry_sqlpatch; no rows selected SQL>
The expected output should be (from another CDB in same home):
PATCH_ID PATCH_UID VERSION ACTION ACTION_TIME STATUS DESCRIPTION ---------- ---------- -------------------- --------------- --------------------------------------------------------------------------- --------------- ---------------------------------------------------------------------------------------------------- 24917972 20791781 12.1.0.2 APPLY 37-APR-17 11.19.49.103261 AM SUCCESS Database PSU 12.1.0.2.170117, Oracle JavaVM Component (JAN2017) 24732082 20904347 12.1.0.2 APPLY 17-APR-17 11.19.49.322985 AM SUCCESS DATABASE PATCH SET UPDATE 12.1.0.2.170117 24917972 20791781 12.1.0.2 ROLLBACK 29-NOV-17 08.35.57.888426 PM SUCCESS Database PSU 12.1.0.2.170117, Oracle JavaVM Component (JAN2017) 26635845 21564421 12.1.0.2 APPLY 29-NOV-17 08.35.57.890421 PM SUCCESS Database PSU 12.1.0.2.171017, Oracle JavaVM Component (OCT2017) 26713565 21602269 12.1.0.2 APPLY 29-NOV-17 08.35.57.956378 PM SUCCESS DATABASE PATCH SET UPDATE 12.1.0.2.171017 27338041 22036385 12.1.0.2 APPLY 12-JUN-18 01.45.24.163558 PM SUCCESS DATABASE PATCH SET UPDATE 12.1.0.2.180417
The result is basically the same if quering cdb_registry_sqlpatch.
Fist found the MOS dba_registry_sqlpatch or registry$sqlpatch View Is Not Reflecting the Complete Updated Information after Patching (Doc ID 2039738.1).
Problem is that is applies to 12.1 and it is caused by a bug in opatch version 12.1.0.1.6, but OPatch version is 12.2.0.1.8.
$ORACLE_HOME/OPatch/opatch version OPatch Version: 12.2.0.1.8
If this is a match for your, the proposed solution in that note is:
1. Download and use latest opatch version 12.1.0.1.8. (Patch 6880880)
2. Take the backup & delete the contents of dba_registry_sqlpatch to remove the invalid entries:
SQL>delete
3. Re-run the datapatch
But what was my problem then?
Well, after a while noticed the MOS Note Datapatch may skip the application of SQL payload for certain patches included in a given bundle in a RAC environment. (Doc ID 2069046.1).
It includes a PLSQL validation script, by the way. Have a look in case it’s a suspect.
And it was a match to me, seems the client used to had problems with opatchauto in the past and had to run the ‘datapatch -verbose’ manually.
The solution? To run this for every CDB contained in the cluster. The Registry$sqlpatch table is now reporting the correct patch history for all CDBs.
Hope it helps!
