Monday, December 2, 2013

Oracle Database 12c–Query opatch status from SQL interface

1. How to query

SQL> set pages 999
SQL> set long 8000
SQL> set longc 2000

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
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 DBCA Deconfiguration 12.1.0.1.0
Oracle Database Plugin for Oracle Virtual Assembly Builder 12.1.0.1.0
Oracle Configuration Manager Client 10.3.2.1.0
Oracle Configuration Manager 10.3.7.0.3
Oracle ODBC Driverfor Instant Client 12.1.0.1.0
LDAP Required Support Files 12.1.0.1.0
Expat libraries 2.0.1.0.2
Oracle Bali Share 11.1.1.6.0
SSL Required Support Files for InstantClient 12.1.0.1.0
Oracle Net Required Support Files 12.1.0.1.0
Oracle R Enterprise Server Files 12.1.0.1.0
Oracle Real Application Testing 12.1.0.1.0
Oracle Label Security 12.1.0.1.0
Oracle Data Mining RDBMS Files 12.1.0.1.0
Oracle OLAP RDBMS Files 12.1.0.1.0
Oracle OLAP API 12.1.0.1.0
SQL*Plus Required Support Files 12.1.0.1.0
Oracle Database Vault option 12.1.0.1.0
XDK Required Support Files 12.1.0.1.0
Oracle Notification Service for Instant Client 12.1.0.1.0
Platform Required Support Files 12.1.0.1.0
Oracle RAC Required Support Files-HAS 12.1.0.1.0
Oracle JDBC Server Support Package 12.1.0.1.0
Oracle SQL Developer 12.1.0.1.0
Oracle Application Express 12.1.0.1.0
RDBMS Required Support Files Runtime 12.1.0.1.0
RDBMS Required Support Files for Instant Client 12.1.0.1.0
SQLJ Runtime 12.1.0.1.0
Database Workspace Manager 12.1.0.1.0
Precompiler Common Files for Core 12.1.0.1.0
Parser Generator Required Support Files 12.1.0.1.0
Oracle Globalization Support 12.1.0.1.0
Oracle Notification Service (eONS) 12.1.0.1.0
Oracle Text ATG Language Support Files 12.1.0.1.0
Oracle Text for Core 12.1.0.1.0
Oracle Text Required Support Files 12.1.0.1.0
Precompiler Required Support Files 12.1.0.1.0
Oracle Database 12c Multimedia Files 12.1.0.1.0
Oracle Multimedia Java Advanced Imaging 12.1.0.1.0
Oracle JDBC/OCI Instant Client 12.1.0.1.0
Oracle Multimedia Locator Java Required Support Files 12.1.0.1.0
Oracle Multimedia Locator RDBMS Files 12.1.0.1.0
Oracle Core Required Support Files for Core DB 12.1.0.1.0
Oracle Core Required Support Files 12.1.0.1.0
Sample Schema Data 12.1.0.1.0
Oracle Starter Database 12.1.0.1.0
Oracle Message Gateway Common Files 12.1.0.1.0
Oracle XML Query 12.1.0.1.0
Oracle Core XML Development Kit 12.1.0.1.0
XML Parser for Oracle JVM 12.1.0.1.0
Oracle JFC Extended Windowing Toolkit 11.1.1.6.0
Oracle Globalization Support For Core 12.1.0.1.0
Oracle Database Deconfiguration 12.1.0.1.0
Oracle Quality of Service Management (Client) 12.1.0.1.0
Oracle Locale Builder 12.1.0.1.0
JAccelerator (COMPANION) 12.1.0.1.0
Oracle Containers for Java 12.1.0.1.0
Oracle JVM For Core 12.1.0.1.0
Perl Modules 5.14.1.0.0
RDBMS Required Support Files 12.1.0.1.0
Secure Socket Layer 12.1.0.1.0
Oracle Universal Connection Pool 12.1.0.1.0
Oracle JDBC/THIN Interfaces 12.1.0.1.0
Oracle Multimedia Client Option 12.1.0.1.0
Oracle Java Client 12.1.0.1.0
Database Migration Assistant for Unicode 12.1.0.1.0
Perl Interpreter 5.14.1.0.0
PL/SQL Embedded Gateway 12.1.0.1.0
OLAP SQL Scripts 12.1.0.1.0
Database SQL Scripts 12.1.0.1.0
Oracle Globalization Support 12.1.0.1.0
Oracle Extended Windowing Toolkit 11.1.1.6.0
SQL*Plus Files for Instant Client 12.1.0.1.0
Required Support Files 12.1.0.1.0
Oracle ODBC Driver 12.1.0.1.0
Precompilers 12.1.0.1.0
Precompiler Common Files 12.1.0.1.0
Deinstallation Tool 12.1.0.1.0
Oracle Recovery Manager 12.1.0.1.0
Oracle Net Java Required Support Files 12.1.0.1.0
XML Parser for Java 12.1.0.1.0
Oracle Security Developer Tools 12.1.0.1.0
Oracle Wallet Manager 12.1.0.1.0
Cluster Verification Utility Common Files 12.1.0.1.0
Oracle Clusterware RDBMS Files 12.1.0.1.0
Oracle Ice Browser 11.1.1.6.0
Installation Plugin Files 12.1.0.1.0
Installation Common Files 12.1.0.1.0
Oracle LDAP administration 12.1.0.1.0
Buildtools Common Files 12.1.0.1.0
Oracle Java Layout Engine 11.0.0.0.0
Oracle Database User Interface 11.0.0.0.0
Oracle Help Share Library 11.1.1.6.0
Oracle Help for Java 11.1.1.6.0
HAS Common Files 12.1.0.1.0
PL/SQL 12.1.0.1.0
Assistant Common Files 12.1.0.1.0
Oracle Notification Service

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:
17034172 16694728 16448848 16863422 16634384 16465158 16320173 1
6313881 16910734 16816103 16911800 16715647 16825779 16707927 16392068 1
4197853 16712618 17273253 16902138 16524071 16856570 16465149 16705020 1
6689109 16372203 16864864 16849982 16946613 16837842 16964279 16459685 1
6978185 16845022 16195633 14536110 16964686 16787973 16850996 16674842 1
6838328 16178562 15996344 16503473 16842274 16935643 17000176 14355775 1
6362358 16994576 16485876 16919176 16928832 16864359 16617325 16921340 1
6679874 16788832 16483559 16733884 16784167 16286774 15986012 16660558 1
6674666 16191248 16697600 16993424 16946990 16589507 16173738 16784143 1
6772060 16991789 17346196 16495802 16859937 16590848 16910001 16603924 1
6427054 16730813 16227068 16663303 16784901 16836849 16186165 16457621 1
6007562 16170787 16663465 16524968 16543323 17027533 16675710 17005047 1
6795944 16668226 16070351 16212405 16523150 16698577 16621274 16930325 1
7330580 16443657

SQL> select xmltransform(DBMS_QOPATCH.GET_OPATCH_LIST, DBMS_QOPATCH.GET_OPATCH_XSLT) from dual;

XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_LIST,DBMS_QOPATCH.GET_OPATCH_XSLT)
--------------------------------------------------------------------------------

Patch Details:

Patch(sqlpatch) 17027533: applied on 2013-12-02T18:28:17+08:00
Unique Patch ID: 16677152
Patch Description: Database Patch Set Update : 12.1.0.1.1 (17027533)
Created on : 27 Sep 2013, 05:30:33 hrs PST8PDT
Bugs fixed:
17034172 16694728 16448848 16863422 16634384 16465158 16320173 1
6313881 16910734 16816103 16911800 16715647 16825779 16707927 16392068 1
4197853 16712618 17273253 16902138 16524071 16856570 16465149 16705020 1
6689109 16372203 16864864 16849982 16946613 16837842 16964279 16459685 1
6978185 16845022 16195633 14536110 16964686 16787973 16850996 16674842 1
6838328 16178562 15996344 16503473 16842274 16935643 17000176 14355775 1
6362358 16994576 16485876 16919176 16928832 16864359 16617325 16921340 1
6679874 16788832 16483559 16733884 16784167 16286774 15986012 16660558 1
6674666 16191248 16697600 16993424 16946990 16589507 16173738 16784143 1
6772060 16991789 17346196 16495802 16859937 16590848 16910001 16603924 1
6427054 16730813 16227068 16663303 16784901 16836849 16186165 16457621 1
6007562 16170787 16663465 16524968 16543323 17027533 16675710 17005047 1
6795944 16668226 16070351 16212405 16523150 16698577 16621274 16930325 1
7330580 16443657
Files Touched:

/kcb.o
/kcbo.o
/kcl.o
/kjzd.o
/kjzn.o
/kjb.o
/kjbl.o
/kjbm.o
/kjbr.o
/kjcts.o
... <omitted> ..
libzt12.a
libnnzst12.a
libnnz12.so

 

SQL> set lin 120
SQL> select xmltransform(dbms_qopatch.GET_OPATCH_LIST,
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="patch">
4 <xsl:text>Patch Id= </xsl:text><xsl:value-of select="patchID"/>
5 <xsl:text> Description: </xsl:text> <xsl:value-of select="patchDescription"/>
6 </xsl:template>
7 </xsl:stylesheet>'
)
8 from dual
9 /

XMLTRANSFORM(DBMS_QOPATCH.GET_OPATCH_LIST,'<XSL:STYLESHEETVERSION="1.0"XMLNS:XSL="HTTP://WWW.W3.ORG/1999/XSL/TRANSFORM"X
------------------------------------------------------------------------------------------------------------------------
Patch Id= 17027533 Description: Database Patch Set Update : 12.1.0.1.1 (17027533)



2. What is behind the sense



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


SQL> select directory_path from dba_directories where directory_name='OPATCH_SCRIPT_DIR';

DIRECTORY_PATH
--------------------------------------------------------------------------------
/u01/app/oracle/product/12.1.0/dbhome_1/QOpatch

SQL> ! ls -l /u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/qopiprep.bat
-r-xr-xr--. 1 oracle oinstall 1353 Jan 26 2013 /u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/qopiprep.bat

SQL> ! file /u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/qopiprep.bat
/u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/qopiprep.bat: POSIX shell script text executable

SQL> ! tail -n 6 /u01/app/oracle/product/12.1.0/dbhome_1/QOpatch/qopiprep.bat

$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


3. Possible error



SQL> select xmltransform(dbms_qopatch.GET_OPATCH_LIST,
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="patch">
4 <xsl:text>Patch Id= </xsl:text><xsl:value-of select="patchID"/>
5 <xsl:text> Description: </xsl:text> <xsl:value-of select="patchDescription"/>
6 </xsl:template>
7 </xsl:stylesheet>'
)
8 from dual
9 /
ERROR:
ORA-20001: Latest xml inventory is not loaded into table
ORA-06512: at "SYS.DBMS_QOPATCH", line 1448
ORA-06512: at "SYS.DBMS_QOPATCH", line 152



When this error happens, just retry the SQL statement, Oracle will delete these XML files inside “$ORACLE_HOME/QOpatch/” automatically.