Since 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
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
David,
sorry for the inconvenience – you’ll have to execute a "set serverout on" first – I added it to the blog post.
Cheers
Mike
Ah, Thx a lot! 😀
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
Thanks Martin – if you don’t mind I have added your valuable information to the blog post as well 🙂
Cheers!
Mike
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.
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
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
Thanks for your comment, Jeannette!!!
Mike