Oracle DBMS_QOPATCH
Version 12.1.0.2

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 12.1.0.1 to 12.1.0.2. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.

Interested in Oracle GoldenGate? Check out the IGGOUG,
Purpose Provides an interface to view installed database patches. If not patches have been applied you will not be able to run these demos.
AUTHID DEFINER
Constants
Name Data Type Value
xslt XMLType NULL
Dependencies
DBA_REGISTRY_SQLPATCH DBMS_UTILITY REGISTRY$SQLPATCH
DBA_SCHEDULER_JOBS GV$PARAMETER UTL_FILE
DBMS_LOB GV$QPX_INVENTORY V$DATABASE
DBMS_LOCK OPATCH_INST_JOB V$INSTANCE
DBMS_OUTPUT OPATCH_INST_PATCH V$PARAMETER
DBMS_SCHEDULER OPATCH_NODE_ARRAY V$QPX_INVENTORY
DBMS_SQLPATCH OPATCH_XINV_TAB V_$ACTIVE_INSTANCES
DBMS_STANDARD OPATCH_XML_INV XMLTYPE
DBMS_SYSTEM PLITBLM XQSEQUENCE
Documented Yes
Exceptions
Error Code Reason
ORA-20001 Latest XML inventory is not loaded into table
ORA-20002 Directory creation failed
ORA-20003 Configuration of a job on a node failed
ORA-20004 Job configuration failed as node is inactive
ORA-20005 Job is not configured with given node, instance name
ORA-20006 Number of RAC active instances and opatch jobs configured are not same
ORA-20007 Job configuration failed as node or instance is not active
ORA-20008 Timed out - job execution time is more than 120Secs
ORA-20009 Job execution failed
ORA-20010 Node is inactive and job cannot be executed
ORA-20011 Job name is NULL and inventory cannot be loaded
ORA-20012 JOB_QUEUE_PROCESSES is set to zero and the inventory can not be loaded
First Available 12.1.0.1
Security Model Owned by SYS with no privileges granted
Source {ORACLE_HOME}/rdbms/admin/dbmsqopi.sql
Subprograms
 
ADD_OINV_JOB
Create job on newly added node dbms_qopatch.add_oinv_job(
nname VARCHAR2,
iname VARCHAR2)
RETURN BOOLEAN;
TBD
 
CONFIG_OINV_JOBS
Create jobs on RAC instances dbms_qopatch.config_oinv_jobs;
exec dbms_qopatch.config_oinv_jobs;
 
DROP_OINV_JOB
Drop a job on a deleted node dbms_qopatch.drop_oinv_job(
nname VARCHAR2,
iname VARCHAR2)
RETURN BOOLEAN;
TBD
 
GET_OPATCH_BUGS
Provides a bugs list for a patch in XML format if the patch number is given. If patch is not given then it lists all the bugs installed in all the patches in XML format. dbms_qopatch.get_opatch_bugs(pnum IN VARCHAR2 DEFAULT NULL) RETURN XMLYPE;
SELECT dbms_qopatch.get_opatch_bugs
FROM dual;

<bugInfo>
  <bugs xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <bug id="17352756">
      <UId>FlexibleDataType-7b5f507b-b2cf-4321-bb00-e39aab61cea4</UId>
      <description>QPATCH DIRECTORIES - OPATCH_LOG_DIR &amp;amp; OPATCH_SCRIPT_DIR INCORRECTLY DEFINED.</description>
    </bug>
  </bugs>
</bugInfo>
 
GET_OPATCH_COUNT
Provides the total number of installed patches in XML format dbms_qopatch.get_opatch_count(pnum IN VARCHAR2 DEFAULT NULL) RETURN XMLYPE;
SELECT dbms_qopatch.get_opatch_count
FROM dual;

GET_OPATCH_COUNT
----------------------------------
<patchCountInfo>4</patchCountInfo>
 
GET_OPATCH_DATA
Provides top level patch information for the patch (such as Patch ID, patch creation time) in the XML element dbms_qopatch.get_opatch_data(pnum IN VARCHAR2) RETURN XMLYPE;
SELECT dbms_qopatch.get_opatch_data('17352756')
FROM dual;

DBMS_QOPATCH.GET_OPATCH_DATA('17352756')
------------------------------------------------------------
<patchInfo>
  <patchID xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">17352756</patchID>
  <bugs xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <bug id="17352756">
      <UId>FlexibleDataType-52cd2f67-a389-4b63-80ea-00b67aacac42</UId>
      <description>QPATCH DIRECTORIES - OPATCH_LOG_DIR &amp;amp; OPATCH_SCRIPT_DIR INCORRECTLY DEFINED.</description>
    </bug>
  </bugs>
  <creationDate xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">7 Sep 2013, 06:10:08 hrs PST8PDT</creationDate>
  <appliedDate xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">2014-01-22T15:09:40-08:00</appliedDate>
  <sqlPatch xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">true</sqlPatch>
  <sqlPatchDatabaseStartupMode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">normal</sqlPatchDatabaseStartupMode>
</patchInfo>
 
GET_OPATCH_FILES
Provides the list of files modified in the given patch number in XML format dbms_qopatch.get_opatch_files(pnum IN VARCHAR2) RETURN XMLYPE;
SELECT dbms_qopatch.get_opatch_files('17352756')
FROM dual;

DBMS_QOPATCH.GET_OPATCH_FILES('17352756')
-------------------------------------------------------------
<patchFiles>
  <patchID xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">17352756</patchID>
  <files xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <file>execqopi.sql</file>
    <file>17352756_apply.sql</file>
    <file>17352756_rollback.sql</file>
  </files>
</patchFiles>
 
GET_OPATCH_INSTALL_INFO
Returns the XML element containing the ORACLE_HOME details such as patch and inventory location dbms_qopatch.get_opatch_install_info RETURN XMLTYPE;
SELECT dbms_qopatch.get_opatch_install_info
FROM dual;

SELECT *
FROM opatch_xinv_tab;

SELECT *
FROM opatch_xml_inv;
 
GET_OPATCH_LIST
Provides list of patches installed dbms_qopatch.get_opatch_list RETURN XMLTYPE;
SELECT dbms_qopatch.get_opatch_list
FROM dual;
 
GET_OPATCH_LSINVENTORY
Returns the complete opatch inventory dbms_qopatch.get_opatch_lsInventory RETURN XMLTYPE;
SELECT dbms_qopatch.get_opatch_lsInventory
FROM dual;
 
GET_OPATCH_OLAYS
Provides overlay patches for a given patch dbms_qopatch.get_opatch_olays(pnum IN VARCHAR2) RETURN XMLTYPE;
SELECT dbms_qopatch.get_opatch_olays('17352756')
FROM dual;

DBMS_QOPATCH.GET_OPATCH_OLAYS('17352756')
--------------------------------------------------------------
<prePatches>
  <patchID xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">17352756</patchID>
  <overlayPatches xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
</prePatches>
 
GET_OPATCH_PREQS
Provides prerequisite patches for a given patch as XML element dbms_qopatch.get_opatch_preqs(pnum IN VARCHAR2) RETURN XMLTYPE;
SELECT dbms_qopatch.get_opatch_preqs('17352756')
FROM dual;

DBMS_QOPATCH.GET_OPATCH_PREQS('17352756')
-------------------------------------------------------------
<prePatches>
  <patchID xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">17352756</patchID>
  <prereqPatches xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
</prePatches>
 
GET_OPATCH_XSLT
Returns the style-sheet for the opatch XML inventory presentation dbms_qopatch.get_opatch_xslt RETURN XMLTYPE;
SELECT dbms_qopatch.get_opatch_xslt
FROM dual;
 
GET_PENDING_ACTIVITY
Get patch status of all RAC instances dbms_qopatch.get_pending_activity RETURN XMLTYPE;
SELECT dbms_qopatch.get_pending_activity
FROM dual;
 
GET_SQLPATCH_STATUS
Displays the SQL patch status by querying from SQL patch registry to produce complete patch level information dbms_qopatch.get_sqlpatch_status(pnum VARCHAR2 DEFAULT NULL);
exec dbms_qopatch.get_sqlpatch_status;
 
IS_PATCH_INSTALLED
Returns a list of patches installed dbms_qopatch.is_patch_installed(pnum IN VARCHAR2) RETURN XMLTYPE;
SELECT dbms_qopatch.is_patch_installed('17352756')
FROM dual;

DBMS_QOPATCH.IS_PATCH_INSTALLED('17352756')
---------------------------------------------------------------
<patchInfo>
  <patchID xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">17352756</patchID>
  <appliedDate xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">2014-01-22T15:09:40-08:00</appliedDate>
  <sqlPatch xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">true</sqlPatch>
  <sqlPatchDatabaseStartupMode xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">normal</sqlPatchDatabaseStartupMode>
</patchInfo>
 
OPATCH_INV_REFRESH_JOB
Call an opatch job to refresh the inventory dbms_qopatch.opatch_inv_refresh_job;
exec dbms_qopatch.opatch_inv_refresh_job;
 
OPATCH_RUN_JOB
Enable and run the opatch ob dbms_qopatch.opatch_run_job;
exec dbms_qopatch.opatch_run_job;
 
PATCH_CONFLICT_DETECTION
Returns the conflicting patch for a given file, if it conflicts with an existing patch dbms_qopatch.patch_conflict_detection(fileName IN VARCHAR2) RETURN XMLTYPE;
SELECT dbms_qopatch.patch_conflict_detection('c:\stage\p17352756_121010_Generic.zip')
FROM dual;

-- this demo doesn't fail but I am quite convinced it isn't what is intended either
 
REFRESH_OPATCH_DATA
Refresh the opatch inventory dbms_qopatch.refresh_opatch_data;
exec dbms_qopatch.refresh_opatch_data;
 
REPLACE_DIRS_INT
Replaces log and script directories with correct path dbms_qopatch.replace_dirs_int(pf_id NUMBER);
TBD
 
REPLACE_LOGSCRPT_DIRS
Replaces log and script directories with correct path dbms_qopatch.replace_logscrpt_dirs;
exec dbms_qopatch.replace_logscrpt_dirs;
 
SET_CURRENT_OPINST
Sets the node name and instance to get the inventory details specific to it in an Oracle Real Application Clusters (RAC) environment dbms_qopatch.set_current_opinst(
node_name IN VARCHAR2 DEFAULT NULL,
inst_name IN VARCHAR2 DEFAULT NULL);
exec dbms_qopatch.set_current_opinst(inst_name=>'orabase2');
 
SET_DEBUG
Toggle debug mode on/off dbms_qopatch.set_debug(debug IN BOOLEAN);
exec dbms_qopatch.set_debug(TRUE);
 
SKIP_SANITY_CHECK
Internal testing flag dbms_qopatch.skip_sanity_check(skip IN BOOLEAN);
exec dbms_qopatch.skip_sanity_check(TRUE);

Related Topics
OPatch
DBMS_ROLLING
Packages

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved