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