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?

OPatch ... oh OPatch ... why is datapatch so stubborn?

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:

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

–Mike

2 thoughts on “OPatch … oh OPatch … why is datapatch so stubborn?

  1. Hi Mike,

    Thanks for sharing this.

    i faced the same issue when applying July PSU for Oracle 12cR1 (12.1.0.2), i even blogged about it here:

    https://geodatamaster.com/2018/07/19/oracle-psu-july-2018-error-ora-20001-latest-xml-inventory-is-not-loaded-into-table/

    I think this kind of changes should be reflected in the HTML file that comes with the downloaded patches (where the procedure of patching is documented)…..unfortunately this is not the case.

    Best Regards,
    Emad

    • Hi Emad,

      thanks for your feedback – and sorry to hear that you’ve had the same “lovely” experience I’ve had.
      From the twitter stream regarding my post I can see that most people package it together when they roll out their new homes and thus don’t hit this.

      And I agree that such things need to be documented. But I stopped asking for changes in the readmes …

      Thanks!
      Mike

Leave a Reply

Your email address will not be published. Required fields are marked *

* Checkbox to comply with GDPR is required

*

I agree

This site uses Akismet to reduce spam. Learn how your comment data is processed.