Oracle Database

Solving an unusual Oracle upgrade issue

2 min read

Jan 27, 2020 12:00:00 AM

Background

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

Output

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

Scenario

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!