Oracle DBMS_QOPATCH
Version 21c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
Purpose 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
build_header VARCHAR2(200) '$Header: rdbms/admin/dbmsqopi.sql /main/16 2017/10/25 10:57:00 sspulava Exp $'
xslt XMLType NULL
Data Types sys.qopatch_list VARRAY(64) OF VARCHAR2(128);
Dependencies
DBA_DIRECTORIES DUAL PLITBLM
DBA_REGISTRY_SQLPATCH GV$INSTANCE QOPATCH_LIST
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_SQL_PATCHES V_$ACTIVE_INSTANCES
DBMS_STANDARD OPATCH_XINV_TAB XMLTYPE
DBMS_SYSTEM OPATCH_XML_INV XQSEQUENCE
DBMS_UTILITY    
Documented Yes: Packages and Types Reference
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
Security Model Owned by SYS with EXECUTE granted to the DATAPATCH_ROLE role
Source {ORACLE_HOME}/rdbms/admin/dbmsqopi.sql
Subprograms
 
ADD_OINV_JOB
Create job on newly added node dbms_qopatch.add_oinv_job(
nname IN VARCHAR2,
iname IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
BODY_BUILD_HEADER
Undocumented but the file header contains "-- 25425451: For intelligent bootstrap" dbms_qopatch.body_build_header RETURN VARCHAR2;
SELECT dbms_qopatch.body_build_header
FROM dual;

BODY_BUILD_HEADER
-----------------------------------------------------------------------------------------------------------
$Header: rdbms/src/client/tools/qpinv/prvtqopi.sql /st_rdbms_18.3.0.0.0dbru/1 2018/07/27 23:36:43 sspulava Exp $
 
CHECK_PATCH_INSTALLED
Checks whether a patch is already installed dbms_qopatch.check_patch_installed(bugs IN qopatch_list)
RETURN VARCHAR2;
TBD
 
CLEAN_METADATA
API metadata cleanup in the event of an inconsistency dbms_qopatch.clean_metadata;
exec dbms_qopatch.clean_metadata;
 
DROP_OINV_JOB
Drop a job on a deleted node dbms_qopatch.drop_oinv_job(
nname IN VARCHAR2,
iname IN VARCHAR2)
RETURN BOOLEAN;
TBD
 
GET_OPATCH_BUGS
Returns 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="https://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
Returns 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
Returns 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="https://www.w3.org/2001/XMLSchema-instance">17352756</patchID>
  <bugs xmlns:xsi="https://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="https://www.w3.org/2001/XMLSchema-instance">7 Sep 2016, 06:10:08 hrs PST8PDT</creationDate>
  <appliedDate xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance">2017-01-22T15:09:40-08:00</appliedDate>
  <sqlPatch xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance">true</sqlPatch>
  <sqlPatchDatabaseStartupMode xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance">normal</sqlPatchDatabaseStartupMode>
</patchInfo>
 
GET_OPATCH_FILES
Returns 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="https://www.w3.org/2001/XMLSchema-instance">17352756</patchID>
  <files xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance">
    <file>execqopi.sql</file>
    <file>17352756_apply.sql</file>
    <file>17352756_rollback.sql</file>
  </files>
</patchFiles>
 
GET_OPATCH_IMAGE
Returns the inventory image dbms_qopatch.get_opatch_image(filename IN VARCHAR2)
RETURN XMLTYPE;
 
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
Returns a list of installed patches 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
Returns 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="https://www.w3.org/2001/XMLSchema-instance">17352756</patchID>
  <overlayPatches xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance"/>
</prePatches>
 
GET_OPATCH_PREQS
Returns 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="https://www.w3.org/2001/XMLSchema-instance">17352756</patchID>
  <prereqPatches xmlns:xsi="https://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_PATCH_DETAILS
Returns patch detail information dbms_qopatch.get_patch_details (patch IN VARCHAR2)
RETURN XMLTYPE;
TBD
 
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="https://www.w3.org/2001/XMLSchema-instance">17352756</patchID>
  <appliedDate xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance">2017-01-22T15:09:40-08:00</appliedDate>
  <sqlPatch xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance">true</sqlPatch>
  <sqlPatchDatabaseStartupMode xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance">normal</sqlPatchDatabaseStartupMode>
</patchInfo>
 
LOAD_SQL_PATCHES
Returns the patches count dbms_qopatch.load_sql_patches(patch_count OUT NUMBER);
DECLARE
 retVal NUMBER;
BEGIN
  dbms_qopatch.load_sql_patches(retVal);
  dbms_output.put_line(retVal);
END;
/
0
 
OPATCH_COMPARE_CURRENT
Compares the list of bugs with the connected node dbms_qopatch.opatch_compare_current(bugs IN qopatch_list)
RETURN VARCHAR2;
TBD
 
OPATCH_COMPARE_GOLD_IMAGE
Performs a comparison with the gold image dbms_qopatch.opatch_compare_gold_image(gold_image_file IN VARCHAR2)
RETURN VARCHAR2;
SELECT dbms_qopatch.opatch_compare_gold_image('OPATCH_LOG_DIR')
FROM dual;

DBMS_QOPATCH.OPATCH_COMPARE_GOLD_IMAGE('OPATCH_LOG_DIR')
---------------------------------------------------------
Patch(es) Not found in Gold-Image

Patch(es) Not found in connected Database

Patch(es) which are common
Patch 28267731
Patch 27908644
Patch 27783303
 
OPATCH_COMPARE_NODES
Compares all RAC nodes or specific RAC nodes dbms_qopatch.opatch_compare_nodes(
node IN VARCHAR2 DEFAULT NULL,
inst IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2;
TBD
 
OPATCH_CREATE_IMAGE
Creates an inventory image dbms_qopatch.opatch_create_image(filename IN VARCHAR2);
SELECT directory_name, directory_path
FROM dba_directories
WHERE directory_name LIKE 'OPATCH%';

DIRECTORY_NAME     DIRECTORY_PATH
-----------------  -------------------
OPATCH_INST_DIR    /u01/orahome18/OPatch
OPATCH_LOG_DIR     /u01/orahome18/rdbms/log
OPATCH_SCRIPT_DIR  /u01/orahome18/QOpatch

exec dbms_qopatch.opatch_create_image('OPATCH_LOG_DIR');

SQL> host

$ cd $ORACLE_HOME/rdbms/log
$ ls
 
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;
 
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
 
REPLACE_DIRS_INT
Replaces log and script directories with correct path dbms_qopatch.replace_dirs_int(pf_id IN 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=>'orabase');
 
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
Built-in Functions
Built-in Packages
DBMS_ROLLING
OPatch
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx