Another reason for verify_queryable_inventory returned ORA-20001: Latest xml inventory is not loaded into table

In another post I wrote some time ago, I ran into the error ORA-20001 during the datapatch of a 19c upgrade. I got the same error but with a different reason this time: temp data files missing due to OS changes.

So we moved a 12c datatabase from Oracle Linux 6 to Oracle Linux 8 for a 19c upgrade and the OS partition layout has also changed. All data files have been restored to the correct locations except temp tablespace data files. That caused 19c upgrade failed during the datapatch.

As always, ORA-20001 is just a general error, it asks you to refer back to MOS note 1609718.1.

Error: prereq checks failed!
verify_queryable_inventory returned ORA-20001: Latest xml inventory is not loaded into table
Prereq check failed, exiting without installing any patches.

Please refer to MOS Note 1609718.1 and/or the invocation log
/opt/oracle/cfgtoollogs/sqlpatch/sqlpatch_187447_2022_10_19_08_40_55/sqlpatch_invocation.log
for information on how to resolve the above errors.

The first thing to check is to check dbms_sqlpatch.verify_queryable_inventory and OPATCH_XML_INV. I was lucky that error from OPATCH_XML_INV revealed the issue:

SQL> select dbms_sqlpatch.verify_queryable_inventory from dual;

VERIFY_QUERYABLE_INVENTORY
-----------------------------------------------------------------------
ORA-20001: Latest xml inventory is not loaded into table

SQL> select * from OPATCH_XML_INV ;
ERROR:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-01157: cannot identify/lock data file 1026 - see DBWR trace file
ORA-01110: data file 1026: '/dbase/FRA/PROD/temp2.dbf'

The next thing was easy, I just dropped non-existing temp data files and upgrade succeeded.

alter database tempfile '/dbase/FRA/PROD/temp2.dbf' drop;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s