12c - Queryable Patch Inventory Alex Zaballa - DBA Oracle - ex-Oracle ACE Director, OCM, OCA, OCP, OCE, OCS

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
--------------------------- FlexibleDataType-e35d74f4-a8bd-4518-852f-7f877ddb7a76ENHANCEMENT TO DBMS_XDBT.CREATEINDEX

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

Abraço

Alex Zaballa