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.

oracle@oel.acme.com:/home/oracle $. oraenv
ORACLE_SID = [ora12c] ? ora12c
The Oracle base remains unchanged with value /oracle/app
oracle@oel.acme.com:/home/oracle $cd $ORACLE_HOME
oracle@oel.acme.com:/oracle/app/product/12.1.0/db12c $cd bin
oracle@oel.acme.com:/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&#8221; 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!

 

 

 

 

 

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Martins Blog

Trying to explain complex things in simple terms

Oracle Data Warrior

Changing the world, one data model at a time. How can I help you?

Maaz Anjum's Blog

A life yet to be lived...

PeteWhoDidNotTweet

Stuff that interests me, if not you!

The Oracle Instructor

Explain, Exemplify, Empower

Frits Hoogland Weblog

IT Technology; Oracle, linux, TCP/IP and other stuff I find interesting

Oracle Spin - Flimatech Blog

Sharing Our Database Experience

Oracle Scratchpad

Just another Oracle weblog

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Follow

Get every new post delivered to your Inbox.

Join 1,457 other followers

%d bloggers like this: