datapatch failed with error ORA-20009: Job Load_opatch_inventory_1execution failed

Today we have hit with issue while applying the OCT-2017 PSU. The Cluster and RDBMS software patched successfuly but while running the datapatch automation failed with below error.

ERROR:

Can't locate XML/Simple.pm in @INC (@INC contains: /u01/app/oracle/product/12.1.0.2/db_1/sqlpatch /u01/app/oracle/product/12.1.0.2/db_1/rdbms/admin /u01/app/oracle/product/12.1.0.2/db_1/sqlpatch/lib /u01/app/oracle/product/12.1.0.2/db_1/perl/lib/site_perl/5.14.1/x86_64-linux-thread-multi /u01/app/oracle/product/12.1.0.2/db_1/perl/lib/site_perl/5.14.1 /u01/app/oracle/product/12.1.0.2/db_1/perl/lib/5.14.1/x86_64-linux-thread-multi /u01/app/oracle/product/12.1.0.2/db_1/perl/lib/5.14.1 .) at /u01/app/oracle/product/12.1.0.2/db_1/sqlpatch/sqlpatch.pm line 173.

BEGIN failed--compilation aborted at /u01/app/oracle/product/12.1.0.2/db_1/sqlpatch/sqlpatch.pm line 173.

Compilation failed in require at /u01/app/oracle/product/12.1.0.2/db_1/sqlpatch/sqlpatch.pl line 64.

BEGIN failed--compilation aborted at /u01/app/oracle/product/12.1.0.2/db_1/sqlpatch/sqlpatch.pl line 64.

2017-11-16 00:52:01,264 INFO  [493] com.oracle.glcm.patch.auto.db.product.executor.PatchingStepExecutor -

Failure in command execution:

/bin/sh -c 'ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1 ORACLE_SID=ORCL_2 /u01/app/oracle/product/12.1.0.2/db_1/OPatch/datapatch'

2017-11-16 00:52:01,264 INFO  [493] com.oracle.cie.common.util.reporting.CommonReporter - Reporting console output : Message{id='null', message='"/bin/sh -c 'ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1 ORACLE_SID=ORCL_2 /u01/app/oracle/product/12.1.0.2/db_1/OPatch/datapatch'" command failed with errors. Please refer to logs for more details. SQL changes, if any, can be applied by manually retrying the same command.

'}

2017-11-16 00:52:01,276 INFO  [493] com.oracle.cie.common.util.reporting.CommonReporter - Reporting console output : Message{id='null', message='SQL patch applied successfully on home /u01/app/oracle/product/12.1.0.2/db_1

We have decided to run the datapatch manually in prereq mode to see the issue and executed as below but unfortunately its failed with the error.

$ ./datapatch -prereq -verbose

SQL Patching tool version 12.1.0.2.0 Production on Thu Nov 16 03:13:59 2017

Copyright (c) 2012, 2016, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_12298_2017_11_16_03_13_59/sqlpatch_invocation.log

Connecting to database...OK

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_12298_2017_11_16_03_13_59/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_12298_2017_11_16_03_13_59/sqlpatch_invocation.log

for information on how to resolve the above errors.

SQL Patching tool complete on Thu Nov 16 03:14:09 2017

From this point we have started our investigation from this point.

1.) Validate the inventory is queryable. This should return "OK", if not then the inventory is not accessible.

SQL> select dbms_sqlpatch.verify_queryable_inventory from dual;

VERIFY_QUERYABLE_INVENTORY

--------------------------------------------------------------------------------

ORA-20009: Job Load_opatch_inventory_1execution failed

Solution:

1.) Validate all the directories are correct and in place as below. if any of the QOpatch and OPatch directories are not correct, recreate them as below. (If this is RAC, validate all the nodes). Execute the inventory is queryable or not. If its return "OK". go ahead and rerun the datapatch -verbose

SQL> create or replace directory OPATCH_LOG_DIR as '/u01/app/oracle/product/12.1.0.2/db_1/QOpatch';

SQL> create or replace directory OPATCH_SCRIPT_DIR as '/u01/app/oracle/product/12.1.0.2/db_1/QOpatch';

SQL> create or replace directory OPATCH_INST_DIR as '/u01/app/oracle/product/12.1.0.2/db_1/OPatch';

2.) Try the below option, If the above solution didn't not worked. Datapatch generate and loads the inventory from the shell script located in $ORACLE_HOME/QOpatch

i) Make the copy of scripts.

   $ cp -p qopiprep.bat qopiprep.bat.sav

ii) Open the qopiprep.bat and run the commands in the file:

     $ORACLE_HOME/OPatch/opatch lsinventory -xml $ORACLE_HOME/QOpatch/xml_file.xml -retry 0 -invPtrLoc $ORACLE_HOME/oraInst.loc >> $ORACLE_HOME/QOpatch/stout.txt

iii) Add the delimiter string onto the end of the XML file. You can also look for delimiter in the log file generated in QOpatch directory. Add the delimiter at the end of xml_file.xml.

echo "UIJSVTBOEIZBEFFQBL" >> $ORACLE_HOME/QOpatch/xml_file.

iii) Edit the sciprt (qopiprep.bat) and remove the bottom 5 commands EXCEPT the “echo `cat $ORACLE_HOME/QOpatch/xml_file.xml`” bit, which returns the output to the calling code:

# Option: "-all" gives information on all Oracle Homes installed in the central inventory. With that information, the patches of non-RDBMS homes could be fetched.

$vi qopiprep.bat

# $ORACLE_HOME/OPatch/opatch lsinventory -xml $ORACLE_HOME/QOpatch/xml_file.xml -retry 0 -invPtrLoc $ORACLE_HOME/oraInst.loc >> $ORACLE_HOME/QOpatch/stout.txt

#`echo "UIJSVTBOEIZBEFFQBL" >> $ORACLE_HOME/QOpatch/xml_file.xml`

echo `cat $ORACLE_HOME/QOpatch/xml_file.xml`

#rm $ORACLE_HOME/QOpatch/xml_file.xml

#rm $ORACLE_HOME/QOpatch/stout.txt

iv) Now when you run, the dbms_sqlpatch command, you have taken the opatch lsinventory slow aspect of the preprocessor out of the equation:

SQL > select dbms_sqlpatch.verify_queryable_inventory from dual;

VERIFY_QUERYABLE_INVENTORY

--------------------------

OK

v) Run the datapatch again and this time its successfull without any issue.

$$ORACLE_HOME/OPatch/datapatch -verbose