Ran into a different issue with Oracle 19c datapatch again. The error was:

[oracle@joedb OPatch]$ ./datapatch -verbose
SQL Patching tool version 19.14.0.0.0 Production on Thu Jun 2 19:35:49 2022
Copyright (c) 2012, 2021, Oracle. All rights reserved.


Log file for this invocation: /opt/oracle/cfgtoollogs/sqlpatch/sqlpatch_74090_2022_06_02_19_35_49/sqlpatch_invocation.log

Connecting to database...OK
Gathering database info...done
Bootstrapping registry and package to current versions...done
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_74090_2022_06_02_19_35_49/sqlpatch_invocation.log
for information on how to resolve the above errors.

So datapatch failed with the prerequisite check. When talking about verify_queryable_inventory, there are two queries we could run. But both gave us errors:

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-29400: data cartridge error
KUP-04095: preprocessor command /opt/oracle/190000/QOpatch/qopiprep.bat encountered error "pipe read timeout"

The first query called the method of the dbms_sqlpatch package, but the error didn’t give too much information. The second query was about time out and it seemed that the file qopiprep.bat was called and somehow timed out.

Looking at the file qopiprep.bat itself, it’s interesting to see it’s a shell script. All it does just invoke opatch to list inventory and output as the xml format. Below are the snippets from the script.

DBSID=$ORACLE_SID
PD=$$
ORABASE=`$ORACLE_HOME/bin/orabasehome`

rm -rf $ORABASE/rdbms/log/xml_file_${DBSID}_${PD}.xml
$ORACLE_HOME/OPatch/opatch lsinventory -customLogDir $ORABASE/rdbms/log -xml  $ORABASE/rdbms/log/xml_file_${DBSID}_${PD}.xml -retry 0 -invPtrLoc $ORACLE_HOME/oraInst.loc >> $ORABASE/rdbms/log/stout_${DBSID}_${PD}.txt
RETVAL=$?;
if [ $RETVAL -ne 0 ] ; then
  # if execution was not successfull, then exit at this point.
  exit $RETVAL;
fi;
unset RETVAL ;

Manually exectued the shell script and it took quite a few minutes to finish. Obviously it worked but too long. It looked like datapatch has a step to call this script and timed out during this step.

Fortunately there are two hidden parameters can increase the timeout values:

*._bug27355984_xt_preproc_timeout=1000
*._enable_ptime_update_for_sys=TRUE

We then created a pfile and added two parameters, then created a spfile from the pfile. datapatch succeeded. And another interesting thing was two parameters were gone after the patch.

References:

1. Queryable Patch Inventory – Issues/Solutions for ORA-20001: Latest xml inventory is not loaded into table (Doc ID 1602089.1)
2. KUP-04095: Preprocessor command encountered error “pipe Read Timeout (Doc ID 2641386.1)

• Oracle