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:


<?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/</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/
Inventory         : /oracle/oraInventory
--------------------------------------------------------------------------------                                                                                                                     Installed Top-level Products (1):
Oracle Database 12c                          
Installed Products ( 131)

Oracle Database 12c                               
Sun JDK                                           
Installer SDK Component                           
Oracle One-Off Patch Installer                    
Oracle Universal Installer                        
Oracle USM Deconfiguration                        
Oracle Configuration Manager Deconfiguration      
Oracle RAC Deconfiguration                        
Oracle DBCA Deconfiguration                       
Oracle Database Plugin for Oracle Virtual Assembly Builder
Oracle Configuration Manager Client               
Oracle Configuration Manager                      
Oracle ODBC Driverfor Instant Client              
LDAP Required Support Files                       
Expat libraries                                   
Oracle Bali Share                                 
SSL Required Support Files for InstantClient      
Oracle Net Required Support Files                 
Oracle R Enterprise Server Files                  
Oracle Real Application Testing                   
Oracle Label Security                             
Oracle Data Mining RDBMS Files                    
Oracle OLAP RDBMS Files                           
Oracle OLAP API                                   
SQL*Plus Required Support Files                   
Oracle Database Vault option                      
XDK Required Support Files                        
Oracle Notification Service for Instant Client    
Platform Required Support Files                   
Oracle RAC Required Support Files-HAS             
Oracle JDBC Server Support Package                
Oracle SQL Developer                              
Oracle Application Express                        
RDBMS Required Support Files Runtime              
RDBMS Required Support Files for Instant Client   
SQLJ Runtime                                      
Database Workspace Manager                        
Precompiler Common Files for Core                 
Parser Generator Required Support Files           
Oracle Globalization Support                      
Oracle Notification Service (eONS)                
Oracle Text ATG Language Support Files            
Oracle Text for Core                              
Oracle Text Required Support Files                
Precompiler Required Support Files                
Oracle Database 12c Multimedia Files              
Oracle Multimedia Java Advanced Imaging           
Oracle JDBC/OCI Instant Client                    
Oracle Multimedia Locator Java Required Support Files
Oracle Multimedia Locator RDBMS Files             
Oracle Core Required Support Files for Core DB    
Oracle Core Required Support Files                
Sample Schema Data                                
Oracle Starter Database                           
Oracle Message Gateway Common Files               
Oracle XML Query                                  
Oracle Core XML Development Kit                   
XML Parser for Oracle JVM                         
Oracle JFC Extended Windowing Toolkit             
Oracle Globalization Support For Core             
Oracle Database Deconfiguration                   
Oracle Quality of Service Management (Client)     
Oracle Locale Builder                             
JAccelerator (COMPANION)                          
Oracle Containers for Java                        
Oracle JVM For Core                               
Perl Modules                                      
RDBMS Required Support Files                      
Secure Socket Layer                               
Oracle Universal Connection Pool                  
Oracle JDBC/THIN Interfaces                       
Oracle Multimedia Client Option                   
Oracle Java Client                                
Database Migration Assistant for Unicode          
Perl Interpreter                                  
PL/SQL Embedded Gateway                           
OLAP SQL Scripts                                  
Database SQL Scripts                              
Oracle Globalization Support                      
Oracle Extended Windowing Toolkit                 
SQL*Plus Files for Instant Client                 
Required Support Files                            
Oracle ODBC Driver                                
Precompiler Common Files                          
Deinstallation Tool                               
Oracle Recovery Manager                           
Oracle Net Java Required Support Files            
XML Parser for Java                               
Oracle Security Developer Tools                   
Oracle Wallet Manager                             
Cluster Verification Utility Common Files         
Oracle Clusterware RDBMS Files                    
Oracle Ice Browser                                
Installation Plugin Files                         
Installation Common Files                         
Oracle LDAP administration                        
Buildtools Common Files                           
Oracle Java Layout Engine                         
Oracle Database User Interface                    
Oracle Help Share Library                         
Oracle Help for Java                              
HAS Common Files                                  
Assistant Common Files                            
Oracle Notification Service                       
Oracle Net                                        
Oracle Database Utilities                         
Generic Connectivity Common Files                 
Oracle Database Gateway for ODBC                  
Oracle Programmer                                 
Oracle Netca Client                               
Oracle Multimedia Locator                         
Oracle Multimedia                                 
Oracle Call Interface (OCI)                       
Oracle XML Development Kit                        
Database Configuration and Upgrade Assistants     
Oracle JVM                                        
Oracle Advanced Security                          
Oracle Internet Directory Client                  
Oracle Net Listener                               
HAS Files for DB                                  
Oracle Text                                       
Oracle Net Services                               
Oracle Database 12c                               
Oracle OLAP                                       
Oracle Spatial and Graph                          
Oracle Partitioning                               
Enterprise Edition Options                        

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:
  Files Touched:



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


Post a Comment