How do I use the DBMS_QOPATCH on an Amazon RDS instance that's running Oracle 19c?

Short description

Oracle 19c 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 can retrieve software inventory information only by using the Oracle OPatch utility. Because Amazon RDS restricts access to the underlying operating system (OS), previous versions can't run the OPatch utility. In Oracle 19c 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 turned on for Oracle instances released in 2020 or later. If your Oracle instances were released in 2020 or later, then it's a best practice to use this feature. 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/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_SQL_PATCHES
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. 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/QOpatch

The 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
----------   ---------- ---------------------------------------------------------------
33613833     24537804   DSTV37 UPDATE - TZDATA2021E - NEED OJVM FIX
33613829     24529874   RDBMS - DSTV37 UPDATE - TZDATA2021E
32327201     24049836   RDBMS - DSTV36 UPDATE - TZDATA2020E
31335037     23600477   RDBMS - DSTV35 UPDATE - TZDATA2020A
29997937     23062124   RDBMS - DSTV34 UPDATE - TZDATA2019B
28852325     23061696   RDBMS - DSTV33 UPDATE - TZDATA2018G
29213893     24595383   DBMS_STATS FAILING WITH ERROR ORA-01422 WHEN GATHERING STATS FOR USER$ TABLE
28730253     23062304   SUPPORT NEW ERA REIWA FOR JAPANESE IMPERIAL CALENDAR
33561310     24538862   OJVM RELEASE UPDATE: 19.14.0.0.220118 (33561310)
33515361     24589353   Database Release Update : 19.14.0.0.220118 (33515361)
29585399     22840393   OCW RELEASE UPDATE 19.3.0.0.0 (29585399)

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):
19.0.0.0.0
Installed Products ( 128)
Oracle Database 19c 19.0.0.0.0
Java Development Kit 1.8.0.201.0
oracle.swd.oui.core.min 12.2.0.7.0
Installer SDK Component 12.2.0.7.0
Oracle One-Off Patch Installer 12.2.0.1.15
Oracle Universal Installer 12.2.0.7.0
oracle.swd.commonlogging 13.3.0.0.0
Trace File Analyzer for DB 19.0.0.0.0
Oracle USM Deconfiguration 19.0.0.0.0
Oracle DBCA Deconfiguration 19.0.0.0.0
...
...
Oracle Advanced Security 19.0.0.0.0
Oracle Internet Directory Client 19.0.0.0.0
Oracle Net Listener 19.0.0.0.0
HAS Files for DB 19.0.0.0.0
Oracle Database Provider for DRDA 19.0.0.0.0
Oracle Text 19.0.0.0.0
Oracle Net Services 19.0.0.0.0
Oracle Database 19c 19.0.0.0.0
Oracle OLAP 19.0.0.0.0
Oracle Spatial and Graph 19.0.0.0.0
Oracle Partitioning 19.0.0.0.0
Enterprise Edition Options 19.0.0.0.0

Interim patches:

Patch 33613833: applied on 2022-02-07T08:53:35Z
Unique Patch ID: 24537804
Patch Description: DSTV37 UPDATE - TZDATA2021E - NEED OJVM FIX
Created on : 9 Dec 2021, 01:32:48 hrs PST8PDT
Bugs fixed:
33613833 
Files Touched:

tzdb.dat
fixTZa.sql
fixTZb.sql

...	
...

The DBMS_QOPATCH package also provides the following procedures and functions. Run the following to verify if a specific patch is installed:

select xmltransform(dbms_qopatch.is_patch_installed('<patch number>'), dbms_qopatch.get_opatch_xslt) from dual;

Run the following to get a list of bugs fixed by the installed patches:

select xmltransform(dbms_qopatch.get_opatch_bugs, dbms_qopatch.get_opatch_xslt) from dual;

Run the following to list the bugs fixed by a specific patch number:

select xmltransform(dbms_qopatch.get_opatch_bugs(<patch number>), dbms_qopatch.get_opatch_xslt) from dual;

Related information

Administering your Oracle DB instance