What is Oracle OPatch, How to use OPatch & List patches

Patch is an Oracle supplied utility to assist you with the process of applying interim patches to Oracle’s software. OPatch is a Java-based utility which requires the Oracle Universal Installer to be installed. It is platform independent and runs on all supported operating systems.

OPatch supports the following:
* Applying an interim patch.
* Rolling back the application of an interim patch.
* Detecting conflict when applying an interim patch after previous interim patches have been applied. It also suggests the best options to resolve a conflict.
* Reporting on installed products and interim patch.

Prior to release 10.2 (OPatch for 10.2 is only compatible with 10.2 and nothing earlier), OPatch was available from MetaLink as a patch in and of itself (p2617419_10102_GENERIC.zip for release 10.1.0.2). With 10.2, OPatch is installed as part of the RDBMS software.

Opatch has several options to execute it:
* “lsinventory” = Adding the lsinventory option to opatch produces a report saying the patches that were applied
* “-report” = Will report to screen and will NOT apply the real patch. Using “-report” is a good way of performing nondestructive testing of the patch installation process.
* “rollback”= Will undo the patch that was applied. Example: opatch rollback –id 4667809

Applying a patch is simple as:
* opatch lsinventory    -> To see he list of patches already installed
* opatch apply <patchid>     –> To REALLY aplply the patch

Example Applying Patch 4751921

After the Patch is Installed:
1.Log in as sys as sysdba.
2. cd $ORACLE_HOME/rdbms/admin
3. spool catpatch_database_name
4. shutdown immediate
5. startup migrate
6. @catpatch.sql   ( this takes at least 1 hour ). After catpatch completed,
7. select object_name,owner from dba_objects where ststus=’INVALID’;
( YOU WILL GET BETWEEN 230-3300 INVALID OBJECTS , DON’T PANIC )
8. @utlrp.sql
9.select object_name,owner from dba_objects where ststus=’INVALID’;   ( YOU WILL GET near 0 invalid objects )
10. shutdown immediate;
11. startup

Listing Patches

All patches that are installed with Oracle’s OPatch Utility (Oracle’s Interim Patch Installer) can be listed by invoking the opatch command with the lsinventory option. Here is an example:

$ cd $ORACLE_HOME/OPatch
$ opatch lsinventory
Invoking OPatch 10.2.0.1.0
Oracle interim Patch Installer version 10.2.0.1.0
Copyright (c) 2005, Oracle Corporation.  All rights reserved..
…
Installed Top-level Products (1):
Oracle Database 10g                                           10.2.0.1.0
There are 1 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
OPatch succeeded.

Another Method using SYS.REGISTRY$HISTORY Table

Since January 2006, contains 1 row for most recent CPU patch applied. A method for determining if CPU patch is applied

SELECT comments, action_time, id “PATCH_NUMBER”, version FROM sys.registry$history WHERE action = ‘CPU’;

COMMENTS	ACTION_TIME	PATCH_NUMBER	VERSION
view recompilation	42:39.2	6452863
view recompilation	59:20.3	6452863
view recompilation	23:58.7	6452863

SELECT comments, action_time, id “PATCH_NUMBER”, version FROM sys.registry$history

COMMENTS	ACTION_TIME	PATCH_NUMBER	VERSION
Upgraded from 10.2.0.1.0	40:28.8		10.2.0.4.0
CPUApr2009	46:06.0	4	10.2.0.4
view recompilation	42:39.2	6452863
CPUOct2009	56:35.7	6	10.2.0.4
view recompilation	59:20.3	6452863
CPUJan2010	01:47.4	6	10.2.0.4
view recompilation	23:58.7	6452863

One other useful Opatch feature

Along with the log and inventory files, Opatch output includes a history file, which contains date and action performed information. The history file is named opatch_history.txt and is located in the $OH\cfgtools\opatch directory. As an example of its contents, the “rollback –help” action performed earlier was recorded as:

[oracle@ opatch]$ pwd
/u01/app/oracle/product/10.2.0/cfgtoollogs/opatch
[oracle@opatch]$ ls -lrt
-rw-r–r– 1 oracle oracle  98608 May 29  2009 opatch2009-05-29_11-37-50AM.log
-rw-r–r– 1 oracle oracle 103814 Dec 14  2009 opatch2009-12-14_20-49-31PM.log
-rw-r–r– 1 oracle oracle   5838 Mar 11  2010 opatch2010-03-11_16-01-00PM.log
-rw-r–r– 1 oracle oracle  33878 Mar 29  2010 opatch2010-03-29_19-53-07PM.log

vi opatch2010-03-29_19-53-07PM.log

Applying patch 9173244…
INFO:Starting Apply Session at Mon Mar 29 19:53:42 BST 2010
INFO:ApplySession applying interim patch ’9173244′ to OH ‘/u01/app/oracle/product/10.2.0′
INFO:Starting to apply patch to local system at Mon Mar 29 19:53:42 BST 2010
INFO:Start the Apply initScript at Mon Mar 29 19:53:42 BST 2010
INFO:Finish the Apply initScript at Mon Mar 29 19:53:42 BST 2010
INFO:OPatch detected ARU_ID/Platform_ID as 226
INFO:Start saving patch at Mon Mar 29 19:53:44 BST 2010