Queryable Opatch and Datapatch

One of my Oracle Support Buddies mentioned to me a cool feature called Query-able Opatch.  This new feature of 12c Oracle Database provides the capability to store, in-database, and query the patch inventory.  Note this feature is specific to Database Home, it does fit into the for Grid Infrastructure or other Oracle Homes.

I wasn't quite sure what problem this feature was trying to solve or what big value it was attempting to bring on.  Regardless, I thought I'd investigate and see what this feature was about.  Mind you I didn't do any in-depth analysis, but enough to shed light on the topic.  I'll followup later was detailed analysis.    We will also touch on the new Datapatch feature as well. 

Query-able Opatch
In versions prior to 12c, the typical stack flow of getting Oracle patch inventory information was :  
opatch lsinventory —> oraInventory_loc  —> Central Inventory (OBase) —> local inventory (OHome) 

Now, In 12c, the stack flow is as follows, if you implement and configure Queryable Opatch feature: 
opatch lsinvenroty (XML) —> queryable patch interface (qpi) —{XML} —> Inventory data (in database) 
The key ingredient here is the queryable patch interface (qpi).  
QPI consists of 
	•	      External table (patch_xml_inv) created by catqitab.sql. 
	•	      Uses oracle_load and and prepocessor (opatch_script_dir —>qop)
	•	      SQL interface dbms_qopatch, dbmsqopi.sql, used as plsql interface to query. The dbms_opatch package contains the following procedures/functions : get_patch, 
			  get_patch_lsinventory, get_sqlpatch_status

Once the external table is created using the catqitab.sql script, you can then execute the load and instantiation of the Opatch Registry data.  

Process of instantiation of Opatch Registry data: 


1. Select against the opatch_xml_inv (external table) 2. Execution of opatch lsinventory -xml (pre-processor program) 3. Load inventory data into table(s)
SQL> SELECT directory_name, directory_path FROM dba_directories WHERE directory_name like 'OPATCH%'; DIRECTORY_NAME DIRECTORY_PATH ------------------------------ ----------------------------------------------- OPATCH_LOG_DIR /u01/app/oracle/product/12.1/db_home1/QOpatch OPATCH_SCRIPT_DIR /u01/app/oracle/product/12.1/db_home1/QOpatch NewImage

Note, that the DBMS_QOPATCH returns XML format, thus you'll need to transform the XML format into something more readable; e.g., using a stylesheet (XSLT). Luckily Oracle provides a default XSLT, GET_OPATCH_XSLT. GET_OPATCH_XSLT is function of DBMS_QPOPATCH. You can use this function or you can build your own XSLT sheet. NewImage

DataPatch is also a new sub-feature of Queryable Patch Inventory.  DataPatch is a driver script that automates the post-patch SQL actions for database patches 
It is applicable only to database home (not GI home) and for patches that have a SQL changes. 
When the  binary patch is successfully applied, Datapatch updates the SQL Patch Registry in the database (table) —> dba_registry_history/dba_registry_sqlpatch 
Note, DataPatch has to be executed per database.  Also, YOU STILL INSTALL PATCHES using Opatch first !!! 
Without DataPatch you could never tell if the database had the SQL part of the patch applied.