12c querying inventory from plsql



Oracle have released a new package with 12c called DBMS_QOPATCH which allows you to query the inventory of the server itself and see what opatch sees from inside the inventory on the server.

QOPATCH - 'query opatch' - you see what they did there...

Now i guess you could have done this yourself in earlier versions using some clever code but oracle have done the hard work for you and there are a variety of functions to be called from within this package.

I'm still not really sure what the use case is for this package but it is quite clever anyway so I've done a quick demo.

In its basic form you can just dump out the inventory in xml format -and the whole thing looks a bit messy:

SQL> select DBMS_QOPATCH.GET_OPATCH_LSINVENTORY from dual;

GET_OPATCH_LSINVENTORY
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <InventoryInstance> <ora

SQL> set pages 0 lines 1024 long 1000000
SQL> /
<?xml version="1.0" encoding="UTF-8" standalone="yes"?> <InventoryInstance> <oracleHome> <UId>OracleHome-946f1ea1-389e-4872-a1ef-924e04dc2e87</UId> <targetTy                                        peId>oracle_home</targetTypeId> <patchingModel>oneoff</patchingModel> <path>/oracle/12.0.0.1</path> <targetTypeId>oracle_home</targetTypeId> <inventoryLocati                                        on>/oracle/oraInventory</inventoryLocation> <isShared>false</isShared> </oracleHome> <patches> <patch xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" x                                        si:type="Patch"> <UId>Patch-a92de3bb-5d25-47fc-b46a-b9b49e5e4aad</UId> <patchID>17316776</patchID> <bugs> <bug id="17316776"> <UId>FlexibleDataType-80577627-                                        f065-43fd-90f9-58281df0b6f3</UId> <description>INCORRECT SYS PASSWORD IS SPECIFIED IN PSE OF BUG;17033183</description> </bug> </bugs> <prereqPatches/> <crea                                        tionDate>8 Oct 2013, 11:52:10 hrs PST8PDT</creationDate> <appliedDate>2014-01-20T21:21:26Z</appliedDate> <rollbackable>true</rollbackable> <uniquePatchID>167                                        63713</uniquePatchID> <patchDescription></patchDescription> <wlsPrereqPatches/> <m
inimumOPatchVersion></minimumOPatchVersion> <patchLanguage>en</patchLanguage> <osPlatforms> <osPlatform id="0"> <UId>FlexibleDataType-841594ae-11be-4e74-9cd6                                        -6e1f937fad06</UId> <ver

........ etc

Oracle have helpfully provided some xml stylesheets to extracts the bits you might be interested in and present them in a nice format as shown here


SQL> select xmltransform(DBMS_QOPATCH.GET_OPATCH_LSINVENTORY, DBMS_QOPATCH.GET_O                                                                                                                     PATCH_XSLT) from dual;

Oracle Querayable Patch Interface 1.0
--------------------------------------------------------------------------------
Oracle Home       : /oracle/12.0.0.1
Inventory         : /oracle/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    17316776:   applied on 2014-01-20T21:21:26Z
Unique Patch ID: 16763713
  Patch Description:
  Created on     : 8 Oct 2013, 11:52:10 hrs PST8PDT
  Bugs fixed:
         17316776
  Files Touched:

    catcdb.sql
    catcdb_int.sql



SQL>


All very clever but i still dont see the point - why would you need to do this? We do all this kind of inventory stuff from cloud control and have it in one central point.

Maybe there is some use for it that i'm missing for now......

Comments