Monthly Archives: May 2016

Killing QOPATCH zombies

3 Replies

I recently had various cases where functions/procedures of DBMS_QOPATCH raised “ORA-20001: Latest xml inventory is not loaded into table” on Windows:

  • Platform: Windows Server 2012 R2
  • Oracle Version: 12.1.0.2.11

The issue can best be seen from SQL*Plus:

SQL> select dbms_qopatch.get_opatch_install_info from dual;
ERROR:
ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: in "SYS.DBMS_QOPATCH", line 1937
ORA-06512: in "SYS.DBMS_QOPATCH", line 133

Another symptom of the issue can be found during instance startup when there is no patch information dumped to the alert.log, even though you know there are patches installed.
Extract from alert.log:

===========================================================
Dumping current patch information
===========================================================
No patches have been applied
===========================================================

Trust me, this database has patches installed 🙂
So, what’s going on…

DBMS_QOPATCH writes a log in %ORACLE_HOME%\QOpatch\qopatch_log.log. Here’s what it says:

 LOG file opened at 05/02/16 21:54:17

KUP-05004:   Warning: Intra source concurrency disabled because parallel select was not requested.

KUP-05007:   Warning: Intra source concurrency disabled because the preprocessor option is being used.

Field Definitions for table OPATCH_XML_INV
  Record format DELIMITED BY NEWLINE
  Data in file has same endianness as the platform
  Reject rows with all null fields

  Fields in Data Source: 

	XML_INVENTORY                   CHAR (100000000)
	  Terminated by "UIJSVTBOEIZBEFFQBL"
	  Trim whitespace same as SQL Loader
KUP-04004: error while reading file C:\app\oracle\product\ora1210211\QOpatch\qopiprep.bat
KUP-04017: OS message: The operation completed successfully.
KUP-04017: OS message: Argument(s) Error... Cannot use file "C:\app\oracle\product\ora1210211\QOpatch\xml_file.xml" to generate XML output.
Specify path/filename and make sure filena
KUP-04118: operation "read_pipe", location "skudmir"

Alright, DBMS_QOPATCH calls “qopiprep.bat” as a pre-processor which in turn tries to write (and at the very end delete) a file named “xml_file.xml”. So, this file is used temporarily and should not exist when there’s currently no call to DBMS_QOPATCH running. When I checked the file was there and Process Explorer revealed that there were multiple “cmd.exe” processes having an open file handle, thus locking the file from deletion.

ps-file-handes

Most of these “cmd.exe” processes were spawned by “oracle.exe” and were not doing any work anymore. I went and killed all of them, one after another. Just be careful and make sure these processes are actually “zombies”. After killing these “cmd.exe” processes there were no locks on “xml_file.xml” anymore and DBMS_QOPATCH worked again as expected.

SQL> select dbms_qopatch.get_opatch_install_info from dual;

GET_OPATCH_INSTALL_INFO
--------------------------------------------------------------------------------
<oracleHome><UId>OracleHome-6f58d48c-880c-45ab-88b4-5831abc60f31</UId><targetTyp

Unfortunately, I haven’t been able to reproduce the issue at will. For the moment I’m fine having a solution.

Remember, datapatch/sqlpatch uses the same funcionality of DBMS_QOPATCH…so applying a patch could fail as well.