Samstag, 10. Oktober 2015

12c datapatch can fail with manual out of place patching

datapatch can fail if doing manual out of place patching, when the new ORACLE_HOME is not cloned from previous one but a fresh installation. The problem is a one-of patch in the old ORACLE_HOME, which is included in something else (like a PSU) in the new ORACLE_HOME.
In my specific situation the old O_H had PSU 12.1.0.2.3 + some one-off + Patch:21355879.
This Patch is not needed anymore in my new O_H (12.1.0.2.4 + some other one-off) - I checked on My Oracle Support - Patches & Updates:

So no need (and no simple way) to add this patch to the new O_H at all.

But when running datapatch within the new O_H, it throws this error and refuses to continue:
Error: prereq checks failed!
  patch 21355879: rollback script /appl/oracle/product/rdbms_121024Jc/sqlpatch/21355879/19095143/21355879_rollback.sql does not exist
Prereq check failed, exiting without installing any patches.

It's obvious the patch  21355879 is not installed within the new O_H. But somehow ( I did not check the exact internal function) datapatch checks the view DBA_REGISTRY_SQLPATCH and finds PATCH_ID:21355879. With a statement similar to
SELECT XMLSerialize(CONTENT dbms_sqlpatch.opatch_registry_state INDENT) from dual;
it finds the patchuid 19095143. The base table seems to be OPATCH_XINV_TAB.  (the other patches within 21355879 are not in this XML and doesn't require a rollback script).

So the database repository says a rollback script should be present, but it's not installed in the new O_H as Oracle calls it not required anymore.

The clean solution would be to patch every new O_H in the exact sequence of all it predecessors - this can be time consuming and also challenging when different branches with conflicting one-offs needs to be merged - even all these conflicting one-offs are merged in a later PSU and not needed anymore.

We decided to do the easier way and just copy all patch directories from old onto new O_H.

12c datapatch - take care of parallel patching

datapatch is a nice new feature in recent Oracle database installations. It helps to ensure the databases objects match the binaries after any kind of patching and so avoid situations which can be an operational and support nightmare - and very hard to identify.

Problem


Unfortunately it has some drawbacks as well.
One of those I hit recently when running datapatch on 2 instances which uses the same ORACLE_HOME.
At some time in it's progress datapatch uses dbms_qopatch.get_opatch_lsinventory, which uses the external table SYS.OPATCH_XML_INV. This has the preprocessor $ORACLE_HOME/QOPatch/qopiprep.bat. This script executes
$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
Maybe you see already what will happen when 2 different instances execute this step at the same time:
2 different opatch lsinventory -xml instances are writing into the same file. A very efficient was to corrupt the xml file, as they write to the same file $ORACLEHOME/QPatch/xml_file.xml

in one db I got this error:
Determining current state...DBD::Oracle::st execute failed: ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: at "SYS.DBMS_QOPATCH", line 1937
ORA-06512: at "SYS.DBMS_QOPATCH", line 1259 (DBD ERROR: error possibly near <*> indicator at char 143 in 'SELECT description, startup_mode
               FROM XMLTable('/InventoryInstance/patches/patch[patchID=21573304]'
                    PASSING <*>dbms_qopatch.get_opatch_lsinventory
                    COLUMNS description VARCHAR2(100) PATH 'patchDescription',
                            startup_mode VARCHAR2(7) PATH 'sqlPatchDatabaseStartupMode')') [for Statement "SELECT description, startup_mode
               FROM XMLTable('/InventoryInstance/patches/patch[patchID=21573304]'
                    PASSING dbms_qopatch.get_opatch_lsinventory
                    COLUMNS description VARCHAR2(100) PATH 'patchDescription',
                            startup_mode VARCHAR2(7) PATH 'sqlPatchDatabaseStartupMode')"] at /appl/oracle/product/rdbms_121024Jc/sqlpatch/sqlpatch.pm line 1368, <LOGFILE> line 73.

in the other
verify_queryable_inventory returned ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00229: input source is empty
ORA-06512: at "SYS.XMLTYPE", line 272
ORA-06512: at line 1

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

But there might be different errors possible as well - all based on the concurrency issue here.

Workaround

In our environment the it's quite simple to workaround such issues: We jave scripts which do all the steps during patching, so the "apply datapatch" module just needs a small extension which ckecks for a "latch" (existence of a file in our case) and only continues if it can grab this file. Otherwise it sleeps for a short time.

Solution

Oracle could easily use a filename like  $ORACLEHOME/QPatch/xml_file.$$.xml instead. I'm not sure if it's worth the effort to fight through the perimeters in MOS.

Update

(2015-12-28)
I found 12.1:Parallel Execution Of Datapatch during Patching or Manual upgrade fails with error " Queryable inventory could not determine the current opatch status " (Doc ID 2054286.1) today which describes exactly this behavior.
Patch:19215058 solves the issue by implementing
DBSID=$ORACLE_SID
...
$ORACLE_HOME/QOpatch/xml_file_$DBSID.xml
...