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 ‘
Release_Update 2010200930′ is installed in the PDB

I tried to apply that patch (Patch 31719903 – Windows Database Bundle Patch, 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,
passing a.patch_output
patch_id number path 'patchID',
patch_uid number path 'uniquePatchID',
description varchar2(80) path 'patchDescription',
sql_patch varchar2(8) path 'sqlPatch'
) x;
31719903 23815960 Windows Database Bundle Patch : (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.

Leave a Reply

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

This site uses Akismet to reduce spam. Learn how your comment data is processed.