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 22.214.171.124. 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 126.96.36.199 vanilla install
- Oracle 18.4.0
- April 2018 Update for Oracle 188.8.131.52
- October 2018 Update for Oracle 184.108.40.206
- The newest OPatch in my Oracle 18.4.0 home
- Two 220.127.116.11 databases, DB12 and CDB1
Then I patch my 18.104.22.168 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
datapatch -verbose … ups
And now I’d like to invoke
datapatch -verbose to apply the SQL changes to the database.
But this fails with a lovely error message:
SQL Patching tool version 22.214.171.124.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.
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/126.96.36.199/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 188.8.131.52 and 18c.
But could this be the case?
rm -rf $ORACLE_HOME/OPatch cp -R $OH18/OPatch $ORACLE_HOME
and then I call
$ $ORACLE_HOME/OPatch/datapatch -verbose SQL Patching tool version 184.108.40.206.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 220.127.116.11.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,
Goodnight! And MOS is sometimes pretty helpful – even on a Friday night.
- 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.