Queryable Patch Inventory – 12c

Por Alex Zaballa
Postado em janeiro 2014

No Oracle Database 12c, existe a possibilidade de acessar as informações do Oracle Inventory através de comandos SQL e PL/SQL, utilizando a package DBMS_QOPATCH.

Estas são as procedures e functions disponíveis para serem utilizadas com a package DBMS_QOPATCH.

GET_OPATCH_BUGS Function
GET_OPATCH_COUNT Function
GET_OPATCH_DATA Function
GET_OPATCH_FILES Function
GET_OPATCH_INSTALL_INFO Function
GET_OPATCH_LIST Function
GET_OPATCH_LSINVENTORY
GET_OPATCH_OLAYS Function
GET_OPATCH_PREQS Function
GET_OPATCH_XSLT
GET_PENDING_ACTIVITY Function
GET_SQLPATCH_STATUS Procedure
IS_PATCH_INSTALLED Function
PATCH_CONFLICT_DETECTION Function
SET_CURRENT_OPINST Procedure

Tabelas relacionadas:

OPATCH_XML_INV
OPATCH_XINV_TAB
OPATCH_INST_JOB
OPATCH_INST_PATCH

A tabela OPATCH_XML_INV é do tipo ORGANIZATION EXTERNAL e utiliza um arquivo chamado qopiprep.bat como preprocessor.

[oracle@oracle01 ~]$ cd $ORACLE_HOME
[oracle@oracle01 dbhome_1]$ pwd
/u01/app/oracle/product/12.1.0/dbhome_1
[oracle@oracle01 dbhome_1]$ find -name qopiprep.bat
./QOpatch/qopiprep.bat

[oracle@oracle01 dbhome_1]$ cat ./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


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/prod	            1
				uct/12.1.0/dbhome_1/
				QOpatch

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="16733810">FlexibleDataType-e35d74f4-a8bd-4518-852f-7f877ddb7a76
</UId><description>ENHANCEMENT TO DBMS_XDBT.CREATEINDEX</description>
</bug></bugs></bugInfo>




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                                 12.1.0.1.0
Oracle Net                                                  12.1.0.1.0
Oracle Database Utilities                                   12.1.0.1.0
Generic Connectivity Common Files                           12.1.0.1.0
Oracle Database Gateway for ODBC                            12.1.0.1.0
Oracle Programmer                                           12.1.0.1.0
Oracle Netca Client                                         12.1.0.1.0
SQL*Plus                                                    12.1.0.1.0
Oracle Multimedia Locator                                   12.1.0.1.0
Oracle Multimedia                                           12.1.0.1.0
Oracle Call Interface (OCI)                                 12.1.0.1.0
Oracle XML Development Kit                                  12.1.0.1.0
Database Configuration and Upgrade Assistants               12.1.0.1.0
Oracle JVM                                                  12.1.0.1.0
Oracle Advanced Security                                    12.1.0.1.0
Oracle Internet Directory Client                            12.1.0.1.0
Oracle Net Listener                                         12.1.0.1.0
HAS Files for DB                                            12.1.0.1.0
Oracle Text                                                 12.1.0.1.0
Oracle Net Services                                         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:
         
Patch    16733810:   applied on 2013-10-18T12:16:31+01:00
Unique Patch ID: 16849541
  Patch Description: 
  Created on     : 3 Oct 2013, 00:12:06 hrs PST8PDT
  Bugs fixed:
         16733810  
  Files Touched:
        
    dbmsxdbt.sql
 


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:
	 16733810

Referências:

http://docs.oracle.com/cd/E16655_01/appdev.121/e17602/d_qopatch.htm#CBHDBJBE

 


Alex Zaballa é DBA e trabalha com Oracle há 14 anos, especialista em Banco de Dados Oracle e formado em Análise de Sistemas. Com sólidos conhecimentos em Banco de Dados e Sistemas operacionais, é certificado OCM Database 11G, bem como outras 110 certificações em produtos da Oracle.
Trabalha desde 2007 para a Jupiter em Angola, alocado em um projeto no Ministério das Finanças.