How do I use the DBMS_QOPATCH on an Amazon RDS instance that is running Oracle 12c?

Last updated: 2019-06-18

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 that 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 execute the DBMS_QOPATCH package can retrieve Oracle software inventory information.

Resolution

The queryable patch inventory uses existing features like XML support and external tables. See the following examples to see 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/OPatch

In 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_JOB

The 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 UNLIMITED

The implementation is done by the PREPROCESSOR script, qopiprep.bat, that 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/QOpatch

The qopiprep.bat script runs the queryable patch inventory and 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 only 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;

Did this article help you?

Anything we could improve?


Need more help?