- SQL> select patch_id, patch_uid, version, action, action_time, status, description from dba_registry_sqlpatch;
- no rows selected
The expected output should be (from another CDB in the same home):
- PATCH_ID PATCH_UID VERSION ACTION ACTION_TIME STATUS DESCRIPTION
- ———- ———- ——————– ————— ————————————————————————— ————— —————————————————————————————————-
- 24917972 20791781 188.8.131.52 APPLY 37-APR-17 184.108.40.206261 AM SUCCESS Database PSU 220.127.116.11.170117, Oracle JavaVM Component (JAN2017)
- 24732082 20904347 18.104.22.168 APPLY 17-APR-17 22.214.171.1242985 AM SUCCESS DATABASE PATCH SET UPDATE 126.96.36.199.170117
- 24917972 20791781 188.8.131.52 ROLLBACK 29-NOV-17 08.35.57.888426 PM SUCCESS Database PSU 184.108.40.206.170117, Oracle JavaVM Component (JAN2017)
- 26635845 21564421 220.127.116.11 APPLY 29-NOV-17 08.35.57.890421 PM SUCCESS Database PSU 18.104.22.168.171017, Oracle JavaVM Component (OCT2017)
- 26713565 21602269 22.214.171.124 APPLY 29-NOV-17 08.35.57.956378 PM SUCCESS DATABASE PATCH SET UPDATE 126.96.36.199.171017
- 27338041 22036385 188.8.131.52 APPLY 12-JUN-18 01.45.24.163558 PM SUCCESS DATABASE PATCH SET UPDATE 184.108.40.206.180417
The result is basically the same if querying cdb_registry_sqlpatch.
The problem is that it applies to 12.1 and it is caused by a bug in OPatch version 220.127.116.11.6, but the OPatch version is 18.104.22.168.8.
- $ORACLE_HOME/OPatch/opatch version
- OPatch Version: 22.214.171.124.8
If this is a match for you, the proposed solution to note is:
- 1. Download and use latest opatch version 126.96.36.199.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, I 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 if you think it is suspect.
And it was a match for me. It seems the client used to have problems with opatchauto in the past and had to run the ‘datapatch -verbose’ manually.
The solution? Run this for every CDB contained in the cluster. The registry$sqlpatch table is now reporting the correct patch history for all CDBs.
Hope this helps!