One of a cool new feature in Oracle 12c is queryable Opatch. Oracle offers a set of tables and a PL/SQL package to query the Oracle Inventory.
With this feature, you can query the inventory to know if a one off patch has been deployed, which components have been deployed etc.
Tables related to this feature are:
- OPATCH_XML_INV
- OPATCH_XINV_TAB
- OPATCH_INST_JOB
- OPATCH_INST_PATCH
The OPATCH_XINV_TAB table is in fact external table preprocessed with specific script :
SQL> select dbms_metadata.get_ddl('TABLE','OPATCH_XML_INV','SYS') from dual;
DBMS_METADATA.GET_DDL('TABLE','OPATCH_XML_INV','SYS')
--------------------------------------------------------------------------------
CREATE TABLE "SYS"."OPATCH_XML_INV"
( "XML_INVENTORY" CLOB
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "OPATCH_SCRIPT_DIR"
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
READSIZE 67108864
preprocessor opatch_script_dir:'qopiprep.bat'
BADFILE opatch_script_dir:'qopatch_bad.bad'
LOGFILE opatch_log_dir:'qopatch_log.log'
FIELDS TERMINATED BY 'UIJSVTBOEIZBEFFQBL'
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
xml_inventory CHAR(100000000)
)
)
LOCATION
( "OPATCH_SCRIPT_DIR":'qopiprep.bat'
)
)
REJECT LIMIT UNLIMITED
The script is located in the path pointed by oracle directory OPATCH_SCRIPT_DIR.
On my server:
[oracle@oel63 ~]$ find $ORACLE_HOME -name "qopiprep.bat"
/u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/qopiprep.bat
SQL> select * from dba_directories where directory_name='OPATCH_SCRIPT_DIR';
OWNER DIRECTORY_NAME DIRECTORY_PATH ORIGIN_CON_ID
---------- ------------------------------ -------------------------------------------------- -------------
SYS OPATCH_SCRIPT_DIR /u01/app/oracle/product/12.1.0/dbhome_1/QOpatch 1
The content of this script is pasted below:
#!/bin/sh
cd $ORACLE_HOME
PATH=/bin:/usr/bin
export PATH
$ORACLE_HOME/OPatch/opatch lsinventory -xml $ORACLE_HOME/QOpatch/xml_file.xml -retry 0 -invPtrLoc $ORACLE_HOME/oraInst.loc >> $ORACLE_HOME/QOpatch/stout.txt
`echo "UIJSVTBOEIZBEFFQBL" >> $ORACLE_HOME/QOpatch/xml_file.xml`
echo `cat $ORACLE_HOME/QOpatch/xml_file.xml`
rm $ORACLE_HOME/QOpatch/xml_file.xml
rm $ORACLE_HOME/QOpatch/stout.txt
This is the base of the feature which is accessed through a PL/SQL package DBMS_QOPATCH.
This package has many functions, for example:
- IS_PATCH_INSTALLED
- GET_OPATCH_LIST
- GET_OPATCH_FILES
…
For example, if we execute the GET_OPATCH_BUGS (which provides bugs list in a specific patch or for all patches), the result is a XML output:
SQL> select dbms_qopatch.GET_OPATCH_BUGS from dual;
GET_OPATCH_BUGS
------------------------------------------------------------------------------------------------------------------------------------------------------
<bugInfo><bugs xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><bug id="9448798"><UId>FlexibleDataType-957ebac1-ef13-462b-9157-b1e0cb711840</UId
><description>11202 dummy one-off for testing</description></bug></bugs></bugInfo>
This is not very user friendly even with a lightweight output … try to execute DBMS_QOPATCH.GET_OPATCH_LSINVENTORY function, there will be more stuff to analyze ;).
That’s why Oracle delivered a XSLT sheet to transform these outputs, you can get this sheet by executing the DBMS_QOPATCH.GET_OPATCH_XSLT function:
SQL> select xmltransform(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY, DBMS_QOPATCH.GET_OPATCH_XSLT) from dual;
XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY,DBMS_QOPATCH.GET_OPATCH_XSLT)
----------------------------------------------------------------------------------------------------
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):
Oracle Database 12c 12.1.0.1.0
Installed Products ( 131)
Oracle Database 12c 12.1.0.1.0
Sun JDK 1.6.0.37.0
oracle.swd.oui.core.min 12.1.0.1.0
Installer SDK Component 12.1.0.1.0
Oracle One-Off Patch Installer 12.1.0.1.0
.../...
Or with my example (bugs corrected by a specific or all OneOff patches) :
SQL> select xmltransform(DBMS_QOPATCH.GET_OPATCH_BUGS, DBMS_QOPATCH.GET_OPATCH_XSLT) from dual;
XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_BUGS,DBMS_QOPATCH.GET_OPATCH_XSLT)
----------------------------------------------------------------------------------------------------
Bugs fixed:
9448798
Of course, you can write your own XSLT sheet, if you want more information. For example, I want the patch Id and the description of this one:
SQL> select xmltransform(dbms_qopatch.GET_OPATCH_BUGS,
2 '<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
3 <xsl:template match="bug">
4 <xsl:text>Patch Id= </xsl:text><xsl:value-of select="@id"/>
5 <xsl:text> Description: </xsl:text> <xsl:value-of select="description"/>
6 </xsl:template>
7 </xsl:stylesheet>')
8 from dual
9 /
XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_BUGS,'<XSL:STYLESHEETVERSION="1.0"XMLNS:XSL="HTTP://WWW.W3.ORG/
----------------------------------------------------------------------------------------------------
Patch Id= 9448798 Description: 11202 dummy one-off for testing
Like this:
Like Loading...
Related
Data ... as usual 
thank you!