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 )
9.select object_name,owner from dba_objects where ststus=’INVALID’; ( YOU WILL GET near 0 invalid objects )
10. shutdown immediate;
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.
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
[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
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