How to find out if a PSU has been applied? DBMS_QOPATCH

pflaster.jpgSince we change the PSU and BP patch numbering from Oracle Database 12.1.0.2.PSU6 to 12,1,0,2,160119 it is almost impossible to distinguish from the patch name only if you have applied a PSU or a BP.

But:
In Oracle Database 12c there’s a package available which is very useful to query plenty of information about patches from within the database: DBMS_QOPATCH.

Here are a few helpful examples which I created by checking in our DBaaS Cloud database.

Which patches have been applied (or rolled back)?

SQL> set serverout on

SQL> exec dbms_qopatch.get_sqlpatch_status;

Patch Id : 20415564
        Action : APPLY
        Action Time : 24-JUN-2015 06:19:23
        Description : Database PSU 12.1.0.2.3, Oracle JavaVM Component (Apr2015)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20415564/18617752/
                  20415564_apply_ORCL_CDBROOT_2015Jun24_06_18_09.log
        Status : SUCCESS

Patch Id : 20299023
        Action : APPLY
        Action Time : 24-JUN-2015 06:19:23
        Description : Database Patch Set Update : 12.1.0.2.3 (20299023)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20299023/18703022/
                  20299023_apply_ORCL_CDBROOT_2015Jun24_06_18_11.log
        Status : SUCCESS

Patch Id : 20848415
        Action : APPLY
        Action Time : 24-JUN-2015 06:19:23
        Description :
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20848415/18918227/
                  20848415_apply_ORCL_CDBROOT_2015Jun24_06_18_15.log
        Status : SUCCESS

Patch Id : 20848415
        Action : ROLLBACK
        Action Time : 24-JUN-2015 06:52:31
        Description :
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20848415/18918227/
                  20848415_rollback_ORCL_CDBROOT_2015Jun24_06_52_29.log
        Status : SUCCESS

Patch Id : 20618595
        Action : APPLY
        Action Time : 24-JUN-2015 13:52:13
        Description :
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20618595/18956621/
                  20618595_apply_ORCL_CDBROOT_2015Jun24_13_52_12.log
        Status : SUCCESS

Patch Id : 20618595
        Action : ROLLBACK
        Action Time : 24-JUN-2015 14:37:11
        Description :
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20618595/18956621/
                  20618595_rollback_ORCL_CDBROOT_2015Jun24_14_37_10.log
        Status : SUCCESS

Patch Id : 20415564
        Action : ROLLBACK
        Action Time : 27-JAN-2016 17:43:18
        Description : Database PSU 12.1.0.2.3, Oracle JavaVM Component (Apr2015)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/20415564/18617752/
                  20415564_rollback_MIKEDB_CDBROOT_2016Jan27_17_42_16.log
        Status : SUCCESS

Patch Id : 21555660
        Action : APPLY
        Action Time : 27-JAN-2016 17:43:18
        Description : Database PSU 12.1.0.2.5, Oracle JavaVM Component (Oct2015)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/21555660/19361790/
                  21555660_apply_MIKEDB_CDBROOT_2016Jan27_17_42_17.log
        Status : SUCCESS

Patch Id : 21359755
        Action : APPLY
        Action Time : 27-JAN-2016 17:43:18
        Description : Database Patch Set Update : 12.1.0.2.5 (21359755)
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/21359755/19194568/
                  21359755_apply_MIKEDB_CDBROOT_2016Jan27_17_42_18.log
        Status : SUCCESS

Patch Id : 21962590
        Action : APPLY
        Action Time : 27-JAN-2016 17:43:18
        Description :
        Logfile : /u01/app/oracle/cfgtoollogs/sqlpatch/21962590/19426224/
                  21962590_apply_MIKEDB_CDBROOT_2016Jan27_17_42_21.log
        Status : SUCCESS

PL/SQL procedure successfully completed.

Where’s my home and inventory?

SQL> set pagesize 0

SQL> set long 1000000 

SQL> select xmltransform(dbms_qopatch.get_opatch_install_info, dbms_qopatch.get_opatch_xslt) "Home and Inventory" from dual;

Home and Inventory
-------------------------------------------------------------

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

Has a specific patch been applied?

Lets check for the latest PSU.

SQL> select xmltransform(dbms_qopatch.is_patch_installed('21359755'), dbms_qopatch.get_opatch_xslt) "Patch installed?" from dual;

Patch installed?
-------------------------------------------------------

Patch Information:
         21359755:   applied on 2015-10-22T21:48:17Z

What’s tracked in my inventory?

The equivalent of opatch lsinventory -detail

SQL> select xmltransform(dbms_qopatch.get_opatch_lsinventory, dbms_qopatch.get_opatch_xslt) from dual; 

Oracle Querayable Patch Interface 1.0
----------------------------------------------------------------
Oracle Home       : /u01/app/oracle/product/12.1.0/dbhome_1
Inventory         : /u01/app/oraInventory
----------------------------------------------------------------

Installed Top-level Products (1):
12.1.0.2.0
Installed Products ( 135)
...

Additional Information and Patches

If you need more helpful examples you may check this excellent blog post by Simon Pane (Pythian):

And credits to Martin Berger for sending me this important information:

Just in case there are multiple DBs running from the same O_H, and someone
queries dbms_qopatch.get_opatch_lsinventory automated from all DBs (as in
automated monitoring/reporting scripts) I'd recommend Patch 20599273 -
otherwise there might be strange XM errors due to race conditions. 

DBMS_QOPATCH does not work in PDBs

Finally, as you can see from the comments section, some people commented that using DBMS_QOPATCH in a PDB does not work. But there’s a solution coming:

–Mike

Share this: