Solving an unusual Oracle upgrade issue

Posted in: Oracle, Technical Track

I recently came across a very unusual situation. During an upgraded review, I noticed the dba_registry_sqlpatch was empty when it shouldn’t be:

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):

---------- ---------- -------------------- --------------- --------------------------------------------------------------------------- --------------- ----------------------------------------------------------------------------------------------------
  24917972   20791781		   APPLY	   37-APR-17 AM 					       SUCCESS	       Database PSU, Oracle JavaVM Component (JAN2017)
  24732082   20904347		   APPLY	   17-APR-17 AM 					       SUCCESS	       DATABASE PATCH SET UPDATE
  24917972   20791781		   ROLLBACK	   29-NOV-17 PM 					       SUCCESS	       Database PSU, Oracle JavaVM Component (JAN2017)
  26635845   21564421		   APPLY	   29-NOV-17 PM 					       SUCCESS	       Database PSU, Oracle JavaVM Component (OCT2017)
  26713565   21602269		   APPLY	   29-NOV-17 PM 					       SUCCESS	       DATABASE PATCH SET UPDATE
  27338041   22036385		   APPLY	   12-JUN-18 PM 					       SUCCESS	       DATABASE PATCH SET UPDATE

The result is basically the same if querying cdb_registry_sqlpatch.

First, find the MOS dba_registry_sqlpatch or registry$sqlpatch View Is Not Reflecting the Complete Updated Information after Patching (Doc ID 2039738.1).

The problem is that it applies to 12.1 and it is caused by a bug in OPatch version, but the OPatch version is

$ORACLE_HOME/OPatch/opatch version
OPatch Version:

If this is a match for you, the proposed solution to note is:

1. Download and use latest opatch version (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!

Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Lead Database Consultant
Well known in the Oracle community in Latin America and Europe where he participates regularly in technology events, Matheus is actually the youngest Oracle ACE Director in the world. Lead Database Consultant at Pythian, Matheus is a Computer Scientist by PUCRS and has been working as an Oracle DBA for the last 10 years.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *