Recently I got this error when “datapatch”ing an Oracle instance that had been cloned (didn’t know that it had been cloned at the time).
$ ./datapatch -verbose
SQL Patching tool version 12.1.0.1.0 on Thu Feb 9 17:51:40 2017
Copyright (c) 2014, Oracle. All rights reserved.
Connecting to database…OK
Determining current state…
Currently installed SQL Patches: 19769486,20299016,20831107,21068523
DBD::Oracle::st execute failed: ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: at “SYS.DBMS_QOPATCH”, line 1011
ORA-06512: at line 4 (DBD ERROR: OCIStmtExecute) [for Statement “DECLARE
Why? Turns out the DB had been cloned from another installation that used a different $ORACLE_HOME. The actual path was different.
So why would that break datapatch? Well, datapatch now uses a new set of Oracle directories (naming below) and an external table name SYS.OPATCH_XML_INV.
The external table uses the Oracle directories and the directory path in the Oracle directories and that directory path still had the old clone home in it… remember it is different?
Here is how we know that.
SQL> !echo $ORACLE_HOME
/u01/home/oracle/product/12.1.0/dbhome_1
SQL> select directory_name, directory_path from dba_directories where directory_name like ‘OPATCH%’;
OPATCH_LOG_DIR
/u01/home/oracle/product/12.1.0.1/QOpatch
OPATCH_SCRIPT_DIR
/u01/home/oracle/product/12.1.0.1/QOpatch
Notice the ORACLE_HOME is 12.1.0 and the Oracle directory paths are 12.1.0.1? There’s the problem.
So here is the fix…
drop directory OPATCH_SCRIPT_DIR;
drop directory OPATCH_LOG_DIR;
drop directory OPATCH_INST_DIR;
create directory OPATCH_SCRIPT_DIR as ‘/u01/home/oracle/product/12.1.0/dbhome_1/QOpatch’;
create directory OPATCH_LOG_DIR as ‘/u01/home/oracle/product/12.1.0/dbhome_1/QOpatch’;
create directory OPATCH_INST_DIR as ‘/u01/oracle/product/12.1.0/dbhome_1/OPatch’;
You don’t need to touch this external table that I’m about to show you – fixing the Oracle directories should be enough, but for your reference, here is what the table looks like.
CREATE TABLE opatch_xml_inv
(
xml_inventory CLOB
)
ORGANIZATION EXTERNAL
(
TYPE oracle_loader
DEFAULT DIRECTORY opatch_script_dir
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
READSIZE 67108864
preprocessor opatch_script_dir:’qopiprep.bat’
BADFILE opatch_script_dir:’qopatch_bad.bad’
LOGFILE opatch_log_dir:’qopatch_log.log’
FIELDS TERMINATED BY ‘UIJSVTBOEIZBEFFQBL’
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
xml_inventory CHAR(100000000)
)
)
LOCATION(opatch_script_dir:’qopiprep.bat’)
)
PARALLEL 1
REJECT LIMIT UNLIMITED;
Here is some more of the gory detail behind datapatch and 12c enhancements to it.
External table (patch_xml_inv) created by catqitab.sql.
As mentioned the new 12c utility datapath uses sqlloader as an Oracle external table (opatch_script_dir —>$ORACLE_HOME/QOpatch/qopiprep.bat)
cd $ORACLE_HOME/QOpatch
[oracle@oel66-noDB QOpatch]$ ls
qopatch.log qopatch_log.log qopiprep.bat
You can use SQL to query the interface dbms_qopatch, dbmsqopi.sql – details omitted.
The dbms_opatch package contains the following procedures/functions : get_patch, get_patch_lsinventory, get_sqlpatch_status
BTW – here’s some other things (mostly for my reference) you can do with datapatch – in this case – I did not need to. datapatch -apply <patch_id> -force -bundle_series PSU e.g. datapatch -apply 18031528 -force -bundle_series PSU
references: