Oracle … as usual

Oracle by Laurent Leturgez

Oracle 12c: queryable Opatch

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
Advertisements

One response to “Oracle 12c: queryable Opatch

  1. Santosh Loke July 15, 2016 at 8:57 AM

    thank you!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: