Patch failing because of Datapatch bug [SQL_PATCH=true]
Again I lost too many hours trying to solve an issue I got during upgrade of one 19c non-CDB database and plugging it into 21c CDB.
When I execute the noncdb_to_pdb.sql script, I got following error:
select cause, type, status, message from pdb_plug_in_violations where name='NINJAPDB';
| Non-CDB to PDB | ERROR | RESOLVED | PDB plugged in is a non-CDB, requires noncdb_to_pdb.sql be run. |
| SQL Patch | ERROR | PENDING | No release updates are installed in the CDB but ‘19.9.0.0.0 Release_Update 2010200930′ is installed in the PDB |
I tried to apply that patch (Patch 31719903 – Windows Database Bundle Patch 19.9.0.0.201020), but at the end, when I executed the Datapatch, I got an error:
Error: prereq checks failed!
patch 32738356: XML descriptor does not exist in either the file system or SQL registry
Prereq check failed, exiting without installing any patches.
That patch 32738356 has been applied a couple months ago as an interim patch and it is causing an error now?
After I couldn’t find any solution via Google or on My Oracle Support, I made a rollback of that patch, and then Datapatch finished successfully.
But, then I was interested why that happened? Why is suddenly this simple patch causing any issue, because it contains only a single .dll file – no SQL scripts, no changes inside database!?
After some digging within Oracle Inventory and data dictionary, I found the issue – I assume, in this case it’s a bug made by some developer, who prepared this patch for deployment.
This query shows us some details:
with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
select x.patch_id, x.patch_uid, x.description, x.sql_patch
from a,
xmltable('InventoryInstance/patches/*'
passing a.patch_output
columns
patch_id number path 'patchID',
patch_uid number path 'uniquePatchID',
description varchar2(80) path 'patchDescription',
sql_patch varchar2(8) path 'sqlPatch'
) x;
| PATCH_ID | PATCH_UID | DESCRIPTION | SQL_PATCH |
| 32738356 | 24188129 | 32738356 – KBHSHTRECEIVE GETTING CORRUPTED DATA ON WINDOWS | true |
| 31719903 | 23815960 | Windows Database Bundle Patch : 19.9.0.0.201020 (31719903) | true |
Please take a look at the column SQL_PATCH – it shows the value “true“! But why, when there is no database change, only a single .dll file!? It should be set to “false”, and Datapatch would work okay!
The workaround is to change the inventory file %ORACLE_HOME%\inventory\ContentsXML\oui-patch.xml and to replace the value from “true” to “false” for this patch.
Hope it helps you to save your nerves and time.