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

Posted on February 2, 2016 by Patch Recommendation 10

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:

DBMS_QOPATCH does not work in PDBs (right now)

–Mike

Share this:

Tags:

You may also like...

10 Responses

  1. David says:
    February 2, 2016 at 13:22

    Hi Mike,

    thants for the article, but when I execute exec dbms_qopatch.get_sqlpatch_status; at one patched database I get nothing. Just a

    SQL> exec dbms_qopatch.get_sqlpatch_status;

    PL/SQL procedure successfully completed.

    What’s wrong here?

    Thanks and regards,
    David

    Reply
  2. Mike says:
    February 2, 2016 at 14:08

    David,

    sorry for the inconvenience – you’ll have to execute a "set serverout on" first – I added it to the blog post.

    Cheers
    Mike

    Reply
  3. David says:
    February 2, 2016 at 14:16

    Ah, Thx a lot! 😀

    Reply
  4. Martin Berger says:
    February 3, 2016 at 07:17

    Hi Mike,

    Thank you for the nice article, dbms_qopatch is really helpful to keep larger environments consistent.
    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.

    hth,
    Martin

    Reply
  5. Mike says:
    February 3, 2016 at 11:10

    Thanks Martin – if you don’t mind I have added your valuable information to the blog post as well 🙂

    Cheers!
    Mike

    Reply
  6. Murthy says:
    October 7, 2016 at 11:07

    Hi Mike,

    we are facing a scenario in oracle 12.2.02 multi tenant architecture,when we are querying at CDB level we are getting the result of all the patches installed,but when we are trying to extract the same from a PDB we are not getting the patch list,can you please let us know the reason behind it.

    Thanks in Advance.

    Reply
  7. Mike says:
    October 7, 2016 at 12:07

    Murty,

    please open an SR – I can’t diagnose such things via the blog. But I agree with your opinion that the same information should be visible in PDBs.
    My first assumption would have been that the SQL changes hadn’t been applied by datapatch or the PDBs were not open and so the information couldn’t get updated. But just blindly guessing …

    Cheers
    Mike

    Reply
  8. Jeannette says:
    February 2, 2017 at 08:57

    Hi Mike

    Just fyi, and in relation to Murthy’s comment that dbms_quopatch only returns list of patches if run in CDB root. We created a SR, which has resulted in enhancement bug: BUG 24977464 – QOPATCH COMMAND EXECUTION AT PDB LEVEL. The answer from support was that “QOpatch is designed the way as stated earlier taking security reasons into consideration. DBMS_QOPATCH access is for the CDB Root.”, why I take it the bug is an enhancement request.

    Kr Jeannette

    Reply
  9. Mike says:
    February 8, 2017 at 10:49

    Thanks for your comment, Jeannette!!!

    Mike

    Reply
  1. Applying the first RU for Oracle Database 12.2.0.1 - Download and apply it
    July 20, 2017

    […] Quick exercise as this can be done easily with DBMS_QOPATCH: […]

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Mike Dietrich
Oracle Senior Director of Product Manager for Database Upgrade, Migrations and Patching writing just another blog.

Opinions certainly are my own as is this page.

Archives

Categories

Upgrade @YouTube

Subscribe via Email

Join 7,330 other subscribers

Follow me on Twitter