Thursday, May 1, 2014

Queryable Patch Inventory or DBMS_QOPATCH

New in Oracle 12c is queryable Opatch. Oracle offers a set of tables and a PL/SQL packages to query the Oracle Inventory.Prior to Oracle 12c, it was not easy to List Oracle patches. Now from Oracle 12c and beyond, you can use the DBMS_QOPATCH package to list all patches.

Using DBMS_QOPATCH; Oracle Database 12c has a PL/SQL - SQL interface for viewing patch information using a select statement instead a OS ORACLE opach command. This new interface view all the patch information available as part of the ,OPatch lsinventory -xml command conform the the OS Oracle opatch command. The package DBMS_QOPATCH accesses the Oracle Universal Installer (OUI) patch inventory in real time to provide patch and patch meta information.

Using DBMS_QOPATCH feature, users can do:

  • Query what patches are installed from SQL*Plus. 
  • Write wrapper programs to create reports and do validation checks across multiple environments.
  • Check patches installed on Oracle RAC nodes from a single location instead of having to log onto each one in turn.

Objects related to the name OPATCH

select object_name , object_type, owner 
 from dba_objects where object_name like '%OPATCH%'

OBJECT_NAME   OBJECT_TYPE    OWNER
------------------------ ----------------- ------
OPATCH_LOG_DIR   DIRECTORY    SYS
OPATCH_SCRIPT_DIR  DIRECTORY    SYS
OPATCH_XML_INV   TABLE     SYS
OPATCH_XINV_TAB   TABLE     SYS
OPATCH_INST_JOB   TABLE     SYS
OPATCH_INST_PATCH  TABLE     SYS
OPATCH_NODE_ARRAY  TYPE      SYS
DBMS_QOPATCH   PACKAGE    SYS
DBMS_QOPATCH   PACKAGE BODY     SYS
LOAD_OPATCH_INVENTORY  JOB     SYS

DBMS_QOPATCH PACKAGE - APIs/functions/procedures


DBMS_QOPATCH

Function/procedure NameParametersComment
GET_OPATCH_INSTALL_INFONoneProvides ORACLE HOME details
SET_CURRENT_OPINSTNoneSet current oracle instance to get the inventory
GET_OPATCH_LISTNoneProvides list of patches installed
IS_PATCH_INSTALLEDpatch Number IN varchar2Provides information in the installed patch
GET_OPATCH_DATApatch Number IN varchar2Provides top level patch info for patch
GET_OPATCH_BUGSpatch Number DEFAULT NULL IN varchar2Provides bugs list in a patch or all the patches
GET_OPATCH_FILESpatch Number IN varchar2Provides list of files modified by a patch
GET_OPATCH_COUNTNoneProvides installed patches total count
GET_OPATCH_PREQSpatch Number IN varchar2Provides prerequisite patches for a patch
GET_OPATCH_OLAYSpatch Number IN varchar2Provides overlay patches for a patch
PATCH_CONFLICT_DETECTIONfileName IN varchar2Detects patch conflicts for given file
GET_PENDING_ACTIVITYNoneGets sql patch status of RAC/non-RAC
GET_OPATCH_XSLTNoneGets the style-sheet for presentation same appearance as opatch text output command line
GET_OPATCH_LSINVENTORYNoneGets the inventory in xml format
GET_SQLPATCH_STATUSpatch Number DEFAULT NULL IN varchar2Displays the sql patch status

Link : Summary of DBMS_QOPATCH Subprograms

"OPATCH_XINV_TAB" External table with prerprocessed

Information about the external table with the external table prerprocessed module "OPATCH_XINV_TAB"
select 
 dbms_metadata.get_ddl('TABLE','OPATCH_XML_INV','SYS') "OPATCH_XML_INV" 
 from dual;

OPATCH_XML_INV
-----------------------------------------------------------------
  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

"qopiprep.bat" script on Operation System inside the Oracle Software tree

select * from dba_directories 
 where directory_name like '%OPATCH%';
OWNER  DIRECTORY_NAME   DIRECTORY_PATH                 ORIGIN_CON_ID
------ ------------------ ------------------------------------------------ -------------
SYS    OPATCH_LOG_DIR   /u02/app/oracle/product/12.1.0/dbhome_1/QOpatch              1
SYS    OPATCH_SCRIPT_DIR  /u02/app/oracle/product/12.1.0/dbhome_1/QOpatch              1
HOST cat /u02/app/oracle/product/12.1.0/dbhome_1/QOpatch/qopiprep.bat
#!/bin/sh
#
# $Header: rdbms/src/client/tools/qpinv/qopiprep.bat /st_rdbms_12.1.0.1/1 2013/01/26 05:16:37 tbhukya Exp $
#
# qopiprep.bat
#
# Copyright (c) 2012, 2013, Oracle and/or its affiliates. All rights reserved. 
#
#    NAME
#      qopiprep.bat - bat file for preprocessor
#
#    DESCRIPTION
#      bat file for external table preprocessor.
#
#    NOTES
#      .
#
#    MODIFIED   (MM/DD/YY)
#    tbhukya     01/25/12 - Bug 16076845
#    tbhukya     09/23/12 - Creation 
#
cd $ORACLE_HOME
PATH=/bin:/usr/bin
export PATH

# Option: "-retry 0" avoids retries in case of locked inventory.

# Option: "-invPtrLoc" is required for non-central-inventory
# locations. $OPATCH_PREP_LSINV_OPTS which may set by users
# in the environment to configure special OPatch options 
# ("-jdk" is another good candidate that may require configuration!).

# Option: "-all" gives information on all Oracle Homes
# installed in the central inventory.  With that information, the
# patches of non-RDBMS homes could be fetched. 


$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

Using the package module DBMS_QOPATCH

SQL> select DBMS_QOPATCH.GET_OPATCH_INSTALL_INFO from dual;
GET_OPATCH_INSTALL_INFO
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------<oracleHome><UId>OracleHome-609f621a-71a9-42c9-814f-beed236fe198</UId><targetTypeId>oracle_home</targetTypeId><patchingModel>oneoff</patchingModel><path>/u02/app/oracle/product/12.1.0/dbhome_1</path>
<targetTypeId>oracle_home</targetTypeId><inventoryLocation>/u02/app/oraInventory</inventoryLocation><isShared>false</isShared></oracleHome>

Now in a SQL friendly way with xmltransform and functionDBMS_QOPATCH.GET_OPATCH_XSLT XMLTransform takes as arguments an XMLType instance and an XSL style sheet, which is itself a form of XMLType instance. It applies the style sheet to the instance and returns an XMLType.
SQL> select xmltransform(DBMS_QOPATCH.GET_OPATCH_INSTALL_INFO, DBMS_QOPATCH.GET_OPATCH_XSLT) from dual;
XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_INSTALL_INFO,DBMS_QOPATCH.GET_OPATCH_XSLT)
-----------------------------------------------------------------------------------------------------------------------------

Oracle Home   : /u02/app/oracle/product/12.1.0/dbhome_1
Inventory   : /u02/app/oraInventory
SQL> select xmltransform(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY, DBMS_QOPATCH.GET_OPATCH_XSLT) from dual;

LINK : XMLTRANSFORM
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   : /u02/app/oracle/product/12.1.0/dbhome_1
Inventory   : /u02/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
Oracle Universal Installer        12.1.0.1.0
Oracle USM Deconfiguration        12.1.0.1.0
Oracle Configuration Manager Deconfiguration      10.3.1.0.0
Oracle RAC Deconfiguration        12.1.0.1.0
...
Oracle Database 12c         12.1.0.1.0
Oracle OLAP          12.1.0.1.0
Oracle Spatial and Graph        12.1.0.1.0
Oracle Partitioning         12.1.0.1.0
Enterprise Edition Options        12.1.0.1.0


Interim patches:

Important Note on Security

Note that any DBA, as well as any user with the CREATE ANY DIRECTORY or DROP ANY DIRECTORY or grant any object privilege also has full access to all directory objects. Therefore, those privileges should be used sparingly, if at all. DBMS_ADVISOR privilege does not require DB DIRECTORY privileges to WRITE directly to the OS user dbsnmp has this rights.
LINK : Preprocess clause External Tables - ORACLE_LOADER Access Driver