OPatch – 12c edition

Patching an Oracle database requires being able to check the inventory of installed patches before applying the patch.  In order to do this, we use the OPatch utility to review what patches are installed.  In order to use the OPatch, first we need to set the ORACLE_HOME for the target (database) that we will apply the patch to.  After setting the ORACLE_HOME, then we need to go to the OPatch directory and run the opatch lsinventory command (example below):

[oracle@oel]$ export ORACLE_HOME=/oracle/app/product/12.1.0/db12c
[oracle@oel]$ cd $ORACLE_HOME/OPatch
[oracle@oel OPatch]$ pwd
/oracle/app/product/12.1.0/db12c/OPatch
[oracle@oel OPatch]$ ./opatch lsinventory
Oracle Interim Patch Installer version 12.1.0.1.0
Copyright (c) 2012, Oracle Corporation. All rights reserved.


Oracle Home : /oracle/app/product/12.1.0/db12c
Central Inventory : /oracle/oraInventory
from : /oracle/app/product/12.1.0/db12c/oraInst.loc
OPatch version : 12.1.0.1.0
OUI version : 12.1.0.1.0
Log file location : /oracle/app/product/12.1.0/db12c/cfgtoollogs/opatch/opatch2013-06-09_16-45-07PM_1.log

Lsinventory Output file location : /oracle/app/product/12.1.0/db12c/cfgtoollogs/opatch/lsinv/lsinventory2013-06-09_16-45-07PM.txt

——————————————————————————–
Installed Top-level Products (1):

Oracle Database 12c 12.1.0.1.0
There are 1 products installed in this Oracle Home.


There are no Interim patches installed in this Oracle Home.


——————————————————————————–

OPatch succeeded.

As  you can see my Oracle Database 12c environment doesn’t have any interim patches installed.  Now that we understand how to pull the OPatch information from the command line, the point of this post is to show that we can now pull the OPatch information from the database itself.  With the release of Oracle Database 12c,  Oracle has created a DBMS function to allow us access to the OPatch information from within the database.  This function is named: DBMS_QOPATCH.  In reviewing the documentation on this feature, the function has a few subprograms that cover the basic commands used to query the OPatch information.  These subprograms are listed here for quick reference:

  • GET_OPATCH_BUGS
  • GET_OPATCH_COUNT
  • GET_OPATCH_DATA
  • GET_OPATCH_FILES
  • GET_OPATCH_INSTALL_INFO
  • GET_OPATCH_LIST
  • GET_OPATCH_LSINVENTORY
  • GET_OPATCH_OLAYS
  • GET_OPATCH_PREQS
  • GET_OPATCH_XSLT
  • GET_PENDING_ACTIVITY
  • GET_SQLPATCH_STATUS
  • IS_PATCH_INSTALLED
  • PATCH_CONFLICT_DETECTION
  • SET_CURRENT_OPINST
Lets take a moment and now focus into how this new function works.  The basics of this functions are the same as any function in Oracle.  The function needs to be called from a SQL statement.  The simplest form of this, is to use a query against the dual table. Lets take a look at an example:
 

Note: Sample only gives a particial listing of lsinventory.  Make adjustments as needed.

[email protected]:/home/oracle $. oraenv
ORACLE_SID = [ora12c] ? ora12c
The Oracle base remains unchanged with value /oracle/app
[email protected]:/home/oracle $cd $ORACLE_HOME
[email protected]:/oracle/app/product/12.1.0/db12c $cd bin
[email protected]:/oracle/app/product/12.1.0/db12c/bin $./sqlplus / as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Sun Jun 9 17:10:56 2013

Copyright (c) 1982, 2013, Oracle. All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> set long 1000
SQL> select dbms_qopatch.get_opatch_lsinventory() from dual;

DBMS_QOPATCH.GET_OPATCH_LSINVENTORY()
——————————————————————————–
<?xml version=”1.0″ encoding=”US-ASCII” standalone=’yes’?>
<InventoryInstance>
<oracleHome>
<UId>OracleHome-954a152e-2eaf-4558-9e73-5f11c4f9c681</UId>
<targetTypeId>oracle_home</targetTypeId>
<patchingModel>oneoff</patchingModel>
<path>/oracle/app/product/12.1.0/db12c</path>
<targetTypeId>oracle_home</targetTypeId>
<inventoryLocation>/oracle/oraInventory</inventoryLocation>
<isShared>false</isShared>
</oracleHome>
<patches/>
<components>
<component xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xsi:type=”OUIComponent” name=”Oracle Database 12c” id=”oracle.server”>
<UId>OUIComponent-6272d683-af31-4786-bb68-3e540ced19e0</UId>
<description>Installs an optional preconfigured starter database, productoptions, management tools, networking services, utilities, and basic client software for an Oracle Database server. This option also supports Automatic StorageManagement database configuration.</description>
<version>12.1.0.1.0<

As you can see, you get the same information as running the ./opatch lsinventory command from the $ORACLE_HOME/OPatch directory.  Formatting may be an issue, but I’m sure some ingenious DBA/Developer will find a way to workaround this.  At least, we can now query the OPatch information from the database now making it easier to identify what patches have been applied to our environments.

Enjoy!

 

 

 

 

 

Please follow and like:
Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

Enquire now

Give us a call or fill in the form below and we will contact you. We endeavor to answer all inquiries within 24 hours on business days.