Solving an unusual Oracle upgrade issue

2 min read

Jan 27, 2020 12:00:00 AM

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!

On this page

Ready to unlock value from your data?

With Pythian, you can accomplish your data transformation goals and more.

Speak with Pythian consultants now →

What We Do

Solving an unusual Oracle upgrade issue

Share this

Share this

More resources

Oracle Database 12c: PSUs vs database proactive bundle patches

Oracle Database 12c Patching: DBMS_QOPATCH, OPATCH_XML_INV, and datapatch

How to patch an exadata (part 3) - grid and database OH patching

Ready to unlock value from your data?