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

SQL>

The expected output should be (from another CDB in the 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 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 12.1.0.1.6, but the 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 you, the proposed solution to 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, 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!

email

Authors

Interested in working with Matheus? Schedule a tech call.

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 *