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

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:

I’m Bobby Curtis and I’m just your normal average guy who has been working in the technology field for awhile (started when I was 18 with the US Army). The goal of this blog has changed a bit over the years. Initially, it was a general blog where I wrote thoughts down. Then it changed to focus on the Oracle Database, Oracle Enterprise Manager, and eventually Oracle GoldenGate.

If you want to follow me on a more timely manner, I can be followed on twitter at @dbasolved or on LinkedIn under “Bobby Curtis MBA”.