OPatch … oh OPatch … why is datapatch so stubborn?

Well, it is Friday night – and I’d rather should sleep or do something cool people tend to do on Friday night’s. But I try to verify a strange issue with DBMS_OPTIM_BUNDLE and the October 2018 Update for Oracle 12.2.0.1. I’m patching. Can you think of anything better on a Friday night than patching?

Of course I know that I’m not alone. I know at least two customers I work with right now who do some maintenance jobs tonight as well. And I’m struggling with OPatch … oh OPatch … why is datapatch so stubborn?

My test setup

I have a very simple setup:

• Oracle 12.2.0.1 vanilla install
• Oracle 18.4.0
• April 2018 Update for Oracle 12.2.0.1
• October 2018 Update for Oracle 12.2.0.1
• The newest OPatch in my Oracle 18.4.0 home
• Two 12.2.0.1 databases, DB12 and CDB1

Patching

Then I patch my 12.2.0.1 environment with the April 2018 Update. This is simple and done within minutes:

cd /media/sf_TEMP/p27674384_122010_Linux-x86-64/27674384
$OH18/OPatch/opatch apply

Done.

datapatch -verbose … ups

And now I’d like to invoke datapatch -verbose to apply the SQL changes to the database.

$OH18/OPatch/datapatch -verbose

But this fails with a lovely error message:

SQL Patching tool version 12.2.0.1.0 Production on Fri Dec 14 23:43:19 2018
Copyright (c) 2012, 2018, Oracle.  All rights reserved.

Log file for this invocation: /u01/app/oracle/cfgtoollogs/sqlpatch/sqlpatch_26838_2018_12_14_16_43_19/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_26838_2018_12_14_23_43_19/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_26838_2018_12_14_23_43_19/sqlpatch_invocation.log
for information on how to resolve the above errors.

SQL Patching tool complete on Fri Dec 14 23:43:24 2018

Ok, let me check the log for hopefully more information:

Bootstrapping registry and package to current versions...done
verify_queryable_inventory returned ORA-20001: Latest xml inventory is not loaded into table

Queryable inventory could not determine the current opatch status.
Execute 'select dbms_sqlpatch.verify_queryable_inventory from dual'

Executing the query gives me the exact same error message.

Solution possible?

Let me ask the ‘oracle’ … pardon … MOS – and MOS returns this note as first result:

• MOS Note:1602089.1 – Queryable Patch Inventory – Issues/Solutions for ORA-20001: Latest xml inventory is not loaded into table

I quickly browse through the note and it dawns me (even though dawn is still some hours away) that I may not the only one seeing this error from time to time – and there are multiple causes for it.

But what should I do? I just randomly picked the first possible query and executed it:

select * from OPATCH_XML_INV;

And here I have my error sequence:

ERROR:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04095: preprocessor command
/u01/app/oracle/product/12.2.0.1/QOpatch/qopiprep.bat encountered error
"Inventory load failed... OPatch cannot load inventory for the given Oracle
Home.
LsInventorySession failed: Unable to create patchObject
Possible causes are:
"



no rows selected

Well, I especially like the “Possible causes are:” followed by empty lines. But luckily there’s this note showing 18 different symptoms.

Minutes later I got stuck with symptom 16 / cause:

1. Using older version of opatch utility.

2. OPatch folder is not in the default location

I can definitely exclude 1. as I have the newest OPatch (or at least a much newer one than the April Update). But yes, I call datapatch from my 18c home. The OPatch is exactly identical between 12.2.0.1 and 18c.

But could this be the case?

I’m trying:

rm -rf $ORACLE_HOME/OPatch
cp -R $OH18/OPatch $ORACLE_HOME

and then I call datapatch again:

$ $ORACLE_HOME/OPatch/datapatch -verbose
SQL Patching tool version 12.2.0.1.0 Production on Fri Dec 14 23:57:02 2018
Copyright (c) 2012, 2018, Oracle.  All rights reserved.

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

Connecting to database...OK
Bootstrapping registry and package to current versions...done
Determining current state...done

Current state of SQL patches:
Bundle series DBRU:
  ID 180417 in the binary registry and not installed in the SQL registry

Adding patches to installation queue and performing prereq checks...
Installation queue:
  Nothing to roll back
  The following patches will be applied:
    27674384 (DATABASE APR 2018 RELEASE UPDATE 12.2.0.1.180417)

Installing patches...
Patch installation complete.  Total patches installed: 1

Validating logfiles...
Patch 27674384 apply: SUCCESS
  logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/27674384/22098633/27674384_apply_DB12_2018Dec14_23_57_11.log (no errors)
SQL Patching tool complete on Fri Dec 14 23:58:26 2018

Wow! That was the cause. I called datapatch from a different directory. And I simply could copy the entire OPatch directory to my current home – and then it worked.

I hope I don’t dream of datapatch tonight … and I still wonder why the tool couldn’t tell me straight away the reason for the error – or why datapatch does care from where it is called while opatch itself does not.

Why are you so stubborn, datapatch??

Goodnight! And MOS is sometimes pretty helpful – even on a Friday night.

More Information

• Why you should use the most recent version of OPatch (May 14, 2018)
• How to find out if a PSU (or BP or RU or RUR) has been applied? (Feb 2, 2016)
• July 2018 patches – delays and other issues (Jul 20, 2018)
• Patching my database with the July 2018 PSU, BP and RU (Jul 19, 2018)
• The magic OPatch patch number to download opatch: 6880880.

–Mike