How do I use the DBMS_QOPATCH on an Amazon RDS instance that is running Oracle 12c?
Last updated: 2022-06-09
I have an Amazon Relational Database Service (Amazon RDS) instance that is running Oracle 12c. How can I use the queryable patch inventory (DBMS_QOPATCH) feature on Amazon RDS for Oracle?
Short description
Oracle 12c includes a queryable patch inventory feature. The feature allows users to retrieve Oracle software inventory information from within the database by using the DBMS_QOPATCH package. For more information, see the Oracle documentation for DBMS_QOPATCH.
In previous versions of Oracle, users could retrieve software inventory information only by using the Oracle OPatch utility. Because Amazon RDS restricts access to the underlying operating system (OS), users couldn't run the OPatch utility. When you use Oracle 12c with Amazon RDS, any users with privileges to run the DBMS_QOPATCH package can retrieve Oracle software inventory information.
Note: Amazon RDS now includes a feature that allows you to access OPatch files from the log file. This feature is enabled for Oracle instances released in 2020 or later. For more information, see Accessing OPatch files.
Resolution
The queryable patch inventory uses existing features like XML support and external tables. These examples show how the queryable patch inventory works.
In this example, the OPATCH query is run on three new directory objects:
SQL> select owner,directory_name,directory_path from dba_directories where directory_name like 'OPATCH%'This is an example output from the query:
OWNER DIRECTORY_NAME DIRECTORY_PATH
---------- -------------------- --------------------------------------------------
SYS OPATCH_SCRIPT_DIR /rdsdbbin/oracle/QOpatch
SYS OPATCH_LOG_DIR /rdsdbbin/oracle/QOpatch
SYS OPATCH_INST_DIR /rdsdbbin/oracle/OPatchIn this example, the OPATCH query is run on four new system tables:
SQL> select owner,table_name from dba_tables where table_name like 'OPATCH%';This is an example output from the query:
OWNER TABLE_NAME
--------------- ------------------------------
SYS OPATCH_XML_INV
SYS OPATCH_XINV_TAB
SYS OPATCH_INST_PATCH
SYS OPATCH_INST_JOBThe main table is OPATCH_XML_INV, which is a full extract of the inventory in XML format. The table is implemented as an external table that reads from the inventory:
SQL> select dbms_metadata.get_ddl('TABLE','OPATCH_XML_INV','SYS') from dual;This is an example output from the query:
DBMS_METADATA.GET_DDL('TABLE','OPATCH_XML_INV','SYS')
--------------------------------------------------------------------------------
CREATE TABLE "SYS"."OPATCH_XML_INV"
( "XML_INVENTORY" CLOB
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "OPATCH_SCRIPT_DIR"
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE CHARACTERSET UTF8
DISABLE_DIRECTORY_LINK_CHECK
READSIZE 8388608
preprocessor opatch_script_dir:'qopiprep.bat'
BADFILE opatch_script_dir:'qopatch_bad.bad'
LOGFILE opatch_log_dir:'qopatch_log.log'
FIELDS TERMINATED BY 'UIJSVTBOEIZBEFFQBL'
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
xml_inventory CHAR(100000000)
)
)
LOCATION
( "OPATCH_SCRIPT_DIR":'qopiprep.bat'
)
)
REJECT LIMIT UNLIMITEDThe implementation is done by the PREPROCESSOR script, qopiprep.bat. This script is located on the directory that is pointed to by OPATCH_SCRIPT_DIR. For more information, see the Oracle documentation for PREPROCESSOR. See the following example query that uses the OPATCH_SCRIPT_DIR:
SQL> select owner,directory_name,directory_path from dba_directories where directory_name='OPATCH_SCRIPT_DIR';This is an example output from the query:
OWNER DIRECTORY_NAME DIRECTORY_PATH
--------------- --------------------------------- ----------------------------------------
SYS OPATCH_SCRIPT_DIR /rdsdbbin/oracle/QOpatchThe qopiprep.bat script runs the queryable patch inventory. It then generates the output in XML so that the output can be used as the input to the external table, OPATCH_XML_INV. Then, the package and functions provided by DBMS_QOPATCH can be used to extract Oracle inventory information from the table. To see the functions and procedures provided by the DBMS_QOPATCH package, see the Oracle documentation for the Summary of DBMS_QOPATCH subprograms.
Run the following query to list all patches installed:
with a as (select dbms_qopatch.get_opatch_lsinventory patch_output from dual)
select x.patch_id, x.patch_uid, x.description from a,
xmltable('InventoryInstance/patches/*' passing a.patch_output columns
patch_id number path 'patchID',
patch_uid number path 'uniquePatchID',
description varchar2(80) path 'patchDescription') x;This is an example output from the query:
PATCH_ID PATCH_UID DESCRIPTION
---------- ---------- ------------------------------------------------------------------------
17969866 20266857
18307021 17688457
22873666 20307375
22873635 20173886
22037014 19477445
20875898 18862169
19396455 18018276
18759211 18703381
17432124 16852639
16799735 18509762
23177536 20400035 Database PSU 12.1.0.2.160719, Oracle JavaVM Component (JUL2016)
23054246 20464632 Database Patch Set Update : 12.1.0.2.160719 (23054246)
22291127 19694308 Database Patch Set Update : 12.1.0.2.160419 (22291127)
21948354 19553095 Database Patch Set Update : 12.1.0.2.160119 (21948354)
21359755 19194568 Database Patch Set Update : 12.1.0.2.5 (21359755)
20831110 18977826 Database Patch Set Update : 12.1.0.2.4 (20831110)
20299023 18703022 Database Patch Set Update : 12.1.0.2.3 (20299023)
19769480 18350083 Database Patch Set Update : 12.1.0.2.2 (19769480)To get a more detailed output in a format similar to opatch lsinventory -detail, run the following:
set long 200000 pages 0 lines 200
select xmltransform(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY, DBMS_QOPATCH.GET_OPATCH_XSLT) from dual;This is an example output from the query:
Oracle Querayable Patch Interface 1.0
--------------------------------------------------------------------------------
Oracle Home : /rdsdbbin/oracle
Inventory : /rdsdbbin/oraInventory
--------------------------------------------------------------------------------Installed Top-level Products (1):
Oracle Database 12c 12.1.0.2.0
Installed Products ( 135)
Oracle Database 12c 12.1.0.2.0
Java Development Kit 1.6.0.75.0
oracle.swd.oui.core.min 12.1.0.2.0
Installer SDK Component 12.1.0.2.0
Oracle One-Off Patch Installer 12.1.0.1.2
Oracle Universal Installer 12.1.0.2.0
Oracle USM Deconfiguration 12.1.0.2.0
Oracle Configuration Manager Deconfiguration 10.3.1.0.0
Oracle RAC Deconfiguration 12.1.0.2.0
Oracle DBCA Deconfiguration 12.1.0.2.0
...
...
Oracle OLAP 12.1.0.2.0
Oracle Spatial and Graph 12.1.0.2.0
Oracle Partitioning 12.1.0.2.0
Enterprise Edition Options 12.1.0.2.0
Interim patches:
Patch 17969866: applied on 2016-08-12T08:33:09Z
Unique Patch ID: 20266857
Patch Description:
Created on : 9 Jun 2016, 10:44:53 hrs PST8PDT
Bugs fixed:
17969866
Files Touched:
/qctox.o
/qmxtgx.o
/qmxtxa.o
/qmudx.o
ins_net_client.mk
/koks2.o
/qmxstr.o
ins_rdbms.mk
ins_rdbms.mk
...
...The DBMS_QOPATCH package also provides the following procedures and functions. To verify if a specific patch is installed, run the following:
select xmltransform(dbms_qopatch.is_patch_installed('<patch number>'), dbms_qopatch.get_opatch_xslt) from dual;To get a list of bugs fixed by the installed patches, run the following:
select xmltransform(dbms_qopatch.get_opatch_bugs, dbms_qopatch.get_opatch_xslt) from dual;To list the bugs fixed by a specific patch number, run the following:
select xmltransform(dbms_qopatch.get_opatch_bugs(<patch number>), dbms_qopatch.get_opatch_xslt) from dual;Related information
Did this article help?
Do you need billing or technical support?