Samstag, 10. Oktober 2015

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
...
Kommentar veröffentlichen