DBMS_QOPATCH How to List the Patches Applied Without Connecting to the Database Server
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
