Oracle | DBMS

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	GET_OPATCH_LIST	OPATCH_COMPARE_GOLD_IMAGE
BODY_BUILD_HEADER	GET_OPATCH_LSINVENTORY	OPATCH_COMPARE_NODES
CHECK_PATCH_INSTALLED	GET_OPATCH_OLAYS	OPATCH_CREATE_IMAGE
CLEAN_METADATA	GET_OPATCH_PREQS	OPATCH_INV_REFRESH_JOB
DROP_OINV_JOB	GET_OPATCH_XSLT	PATCH_CONFLICT_DETECTION
GET_OPATCH_BUGS	GET_PATCH_DETAILS	REPLACE_DIRS_INT
GET_OPATCH_COUNT	GET_PENDING_ACTIVITY	REPLACE_LOGSCRPT_DIRS
GET_OPATCH_DATA	GET_SQLPATCH_STATUS	SET_CURRENT_OPINST
GET_OPATCH_FILES	IS_PATCH_INSTALLED	SET_DEBUG
GET_OPATCH_IMAGE	LOAD_SQL_PATCHES	SKIP_SANITY_CHECK
GET_OPATCH_INSTALL_INFO	OPATCH_COMPARE_CURRENT

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);