DBMS_QOPATCH How to List the Patches Applied Without Connecting to the Database Server

DBMS_QOPATCH How to List the Patches Applied Without Connecting to the Database Server

August 3, 2020 0 By dianarobete

Ever wondered how to check what patches are installed for a database home, without actually having access to the database server?

In 12.1 Oracle introduced a package DBMS_QOPATCH that allows you to check what patches have been applied to the Oracle Home the database is running from.
This package accesses the OUI (Oracle Universal Installer) patch inventory to provide you with the information.

The package has lots of subprograms, which you can check out here:

What we are interested in today, is to check the patches installed in the database, without actually being connected to the database server.

set pagesize 0
spool installed_patches.out
 
select xmltransform(dbms_qopatch.get_opatch_lsinventory,
dbms_qopatch.get_opatch_xslt) as Patch_Info from dual;

spool off

Sample output:
Oracle Querayable Patch Interface 1.0
--------------------------------------------------------------------------------

Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
Inventory         : /u01/app/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
...

Patch(sqlpatch) 31219897:   applied on 2020-07-22T13:34:07-06:00
Unique Patch ID: 23619699
  Patch Description: OJVM RELEASE UPDATE: 19.8.0.0.200714 (31219897)
  Created on     : 8 Jul 2020, 01:57:16 hrs PST8PDT
  Bugs fixed:
 29540327  29254623  29445548  29774362  30134746  30160625  29942275
 30534662  29512125  30855101  31306261  31359215
  Files Touched:

...

Patch(sqlpatch) 31281355:   applied on 2020-07-22T13:27:28-06:00
Unique Patch ID: 23688465
  Patch Description: Database Release Update : 19.8.0.0.200714 (31281355)
  Created on     : 6 Jul 2020, 11:18:02 hrs PST8PDT
  Bugs fixed:

...

Make sure you spool the content to a file, as there is a lot detail in the output, same as running opatch lsinventory -detail.

Another useful command of the same package is to find out the Oracle Home and the inventory location:

set pagesize 0
set long 1000000 

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

Oracle Home       : /u01/app/oracle/product/19.0.0/dbhome_1
Inventory         : /u01/app/oraInventory

One more thing to check for the Oracle home and the database is, if the patch has been applied in the database, aka was datapatch run in the database, after the home was patched. To find the answer for this, you need to query: DBA_REGISTRY_SQLPATCH view.

SQL> select patch_id, action, description, action_time 
     from dba_registry_sqlpatch order by action_time;

  PATCH_ID ACTION   DESCRIPTION                                                  ACTION_TIME
---------- -------- ------------------------------------------------------------ ----------------------------
  30869156 APPLY    Database Release Update : 19.7.0.0.200414 (30869156)         09-JUL-20 10.25.59.281935 AM
  30805684 APPLY    OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)              09-JUL-20 10.25.59.286150 AM
  31281355 APPLY    Database Release Update : 19.8.0.0.200714 (31281355)         22-JUL-20 01.30.47.736379 PM
  30805684 ROLLBACK OJVM RELEASE UPDATE: 19.7.0.0.200414 (30805684)              22-JUL-20 01.36.22.283160 PM
  31219897 APPLY    OJVM RELEASE UPDATE: 19.8.0.0.200714 (31219897)              22-JUL-20 01.36.22.287535 PM

With this information at your fingertips, you can create a script that tells you everything: the patches applied to the Oracle Home and the patches applied in the database, without connecting to the database server!

If you enjoyed this article, and would like to learn more about databases, please sign up to my weekly email, and you will receive my FREE guide: 7 Questions to Ask When Troubleshooting Database Performance Problems!

If you are interested in improving your Oracle Tuning skills, check out my course theultimatesqltuningformula.com