Troubleshooting a datapatch error in Oracle
As part of patching preparation, the following command was executed and failed:
Verifying SQL patch applicability on home /u01/app/oracle/product/12.1.0/db
Following step failed during analysis: /bin/sh -c 'cd /u01/app/oracle/product/12.1.0/db; ORACLE_HOME=/u01/app/oracle/product/12.1.0/db ORACLE_SID=cdb /u01/app/oracle/product/12.1.0/db/OPatch/datapatch -prereq -verbose' Let's run datapatch -prereq -verbose manually.
$ORACLE_HOME/OPatch/datapatch -prereq -verbose SQL Patching tool version 12.1.0.2.0 Production on Thu Oct 11 11:06:07 2018 Copyright (c) 2012, 2017, Oracle. All rights reserved.
Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_21425_2018_10_11_11_06_07/sqlpatch_invocation.log
Connecting to database...OK Note: Datapatch will only apply or rollback SQL fixes for PDBs that are in an open state, no patches will be applied to closed PDBs. Please refer to Note: Datapatch: Database 12c Post Patch SQL Automation (Doc ID 1585822.1) Bootstrapping registry and package to current versions...done
Queryable inventory could not determine the current opatch status. Execute 'select dbms_sqlpatch.verify_queryable_inventory from dual' and/or check the invocation log /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_21425_2018_10_11_11_06_07/sqlpatch_invocation.log for the complete error. Prereq check failed, exiting without installing any patches.
Please refer to MOS Note 1609718.1 and/or the invocation log /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_21425_2018_10_11_11_06_07/sqlpatch_invocation.log for information on how to resolve the above errors.
SQL Patching tool complete on Thu Oct 11 11:06:15 2018 The documentation provided in Doc ID 1585822.1 did not help resolve the issue. Instead, running the SQL from the above output helped to identify the issue.
SQL> select dbms_sqlpatch.verify_queryable_inventory from dual;
VERIFY_QUERYABLE_INVENTORY -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ORA-20013: DBMS_QOPATCH ran mostly in non install area
SQL> Here is the referenced document: Datapatch fails with "ORA-20009: Job Load_opatch_inventory_1" "
ORA-20013: DBMS_QOPATCH ran mostly in non install area" (Doc ID 2033620.1) The following DIRECTORY_NAME, DIRECTORY_PATH from dba_directories was pointing to incorrect Oracle Home: OPATCH_SCRIPT_DIR, OPATCH_LOG_DIR, OPATCH_INST_DIR. This occurred because database was cloned from source where source and target had different Oracle Home. In conclusion, when cloning databases verify if Oracle Home is identical between source and target and if not, then change database configuration at target accordingly.
