With Database 12c, Oracle has provided a handy feature, Queryable Patch Inventory, which allows for a SQL and PL/SQL interface to the product patch inventory. Previously, this information was only accessible through the OS command: OPatch. Now, with the DBMS_QOPATCH package, you can monitor distributed Opatch repositories from a central location. OPatch now supports inventory in XML format and inventory data is stored in database tables. So as data is stored in database tables, you can view installed database patches performing interactive queries using SQL*Plus. Oracle database 12c provides DBMS_QOPATCH package to manage this interface. It is installed by default during instance creation or by executing "dbmsqopi.sql" SQL script.
Few considerations
This package will work only if the database is OPEN (write mode).
Verify that the following database directories exist in database:
SQL> SELECT directory_name, directory_path
2 FROM dba_directories
3 WHERE directory_name like 'OPATCH%';
DIRECTORY_NAME DIRECTORY_PATH
------------------------------ -----------------------------------------------
OPATCH_LOG_DIR /u01/app/oracle/product/12.1.0.1/db_1/QOpatch
OPATCH_SCRIPT_DIR /u01/app/oracle/product/12.1.0.1/db_1/QOpatchValidate status for database objects related to Queryable Patch Inventory utility:
SQL> COL owner FORMAT a5
SQL> COL object_name FORMAT a25
SQL> COL object_type FORMAT a15
SQL> COL status FORMAT a6
SQL> SELECT owner, object_name, object_type, status
2 FROM dba_objects
3 WHERE object_name like '%OPATCH%';
OWNER OBJECT_NAME OBJECT_TYPE STATUS
----- ------------------------- --------------- ------
SYS OPATCH_LOG_DIR DIRECTORY VALID
SYS OPATCH_SCRIPT_DIR DIRECTORY VALID
SYS OPATCH_XML_INV TABLE VALID
SYS OPATCH_XINV_TAB TABLE VALID
SYS OPATCH_INST_JOB TABLE VALID
SYS OPATCH_INST_PATCH TABLE VALID
SYS OPATCH_NODE_ARRAY TYPE VALID
SYS DBMS_QOPATCH PACKAGE VALID
SYS DBMS_QOPATCH PACKAGE BODY VALID
SYS LOAD_OPATCH_INVENTORY JOB VALID
10 rows selected.DBMS_QOPATCH execution output is returned in XML format. You will have to transform XML format into text output in order to make this output more "friendly". You can perform this task using GET_OPATCH_XSLT function as argument. This function returns the style-sheet for the opatch XML inventory presentation. GET_OPATH_XLST function is provided by Oracle as a component of DBMS_QOPATCH package. You can use this function as your default XSLT sheet or you can write your own XSLT sheet too.
USEFUL FUNCTIONS
You can start playing with the following functions to understand how this package works:
GET_OPATCH_INSTALL_INFO function
This function returns the ORACLE_HOME details, such as patch and inventory location:
SQL> SET pagesize 1000
SQL> SET long 50000
SQL> SELECT
2 xmltransform(dbms_qopatch.GET_OPATCH_INSTALL_INFO,
3 dbms_qopatch.GET_OPATCH_XSLT)
4 as "ORACLE HOME DETAILS"
5 FROM dual;
ORACLE HOME DETAILS
-----------------------------------------------------------------------
Oracle Home : /u01/app/oracle/product/12.1.0.1/db_1
Inventory : /u01/app/oraInventoryGET_OPATCH_BUGS Function
This function lists all bugs applied in the Opatch repository:
SQL> SET long 50000
SQL> SET pagesize 10000
SQL> SELECT
2 xmltransform(dbms_qopatch.GET_OPATCH_BUGS,
3 dbms_qopatch.GET_OPATCH_XSLT)
4 as "BUGS FIXED"
5 FROM dual;
BUGS FIXED
-----------------------------------------------------------------------
Bugs fixed:
17716305 17034172 16694728 16855202 17439871 16320173 17462687
7082983 16313881 16715647 17362796 17777061 16450169 16392068 17761775
6977973 14197853 16712618 17552800 17922172 17441661 16524071 16856570
...
7610418 17465741 17171530 16523150 16212405 16741246 16930325 16443657GET_OPATCH_LIST Function
This function provides a very useful list of patches installed, expanding the previous example not only including patch description and bugs fixed listing but also including files modified by each fix.
SQL> SELECT
2 xmltransform(dbms_qopatch.get_opatch_list(),
3 dbms_qopatch.GET_OPATCH_XSLT())
4 as "OPATCH LIST"
5 FROM dual;
OPATCH LIST
-----------------------------------------------------------------------
Patch Details:
Patch(sqlpatch) 18031528: applied on 2014-04-17T16:04:49-03:00
Unique Patch ID: 17262469
Patch Description: Database Patch Set Update : 12.1.0.1.3 (18031528)
Created on : 18 Mar 2014, 14:50:40 hrs PST8PDT
Bugs fixed:
17716305 17034172 16694728 16855202 17439871 16320173 17462687
7082983 16313881 16715647 17362796 17777061 16450169 16392068 17761775
...
...
7610418 17465741 17171530 16523150 16212405 16741246 16930325 16443657
Files Touched:
/nae.o
ins_net_client.mk
/osslib.o
/jox.o
/kcb.o
...
...
ins_rdbms.mk
ins_precomp.mkIS_PATCH_INSTALLED Function
You can query if a specific patch ID is installed in your system as follows:
SQL> SELECT
2 xmltransform(dbms_qopatch.is_patch_installed(18031528),
3 dbms_qopatch.GET_OPATCH_XSLT())
4 as "PATCH INSTALLED"
5 FROM dual;
PATCH INSTALLED
-----------------------------------------------------------------------
Patch Information:
18031528: applied on 2014-04-17T16:04:49-03:00Writing your own XSLT sheet
You can write your own XLST sheet instead of using GET_OPATCH_XSLT() function.
Following the previous example, let's take a look at the IS_PATCH_INSTALLED function output:
SQL> SET linesize 60
SQL> SELECT dbms_qopatch.is_patch_installed(18031528)
2 FROM dual;
DBMS_QOPATCH.IS_PATCH_INSTALLED(18031528)
------------------------------------------------------------
18031528 2014-04-17T16:04:49-03:00 true normal We can create our new custom output providing a new XLST sheet as follows:
SQL> SELECT xmltransform(dbms_qopatch.is_patch_installed(18031528),
2 '
5
6 Patch Id=
7 Applied:
8
9 ') as "CUSTOM XLST SHEET"
10 FROM dual;
CUSTOM XLST SHEET
------------------------------------------------------------
Patch Id= 18031528 Applied: true