PFB steps to Rollback a Database PSU applied on Oracle Database :-
In my case Database PSU is 10.2.0.5.12 and Database is 10.2.0.5
1. Check details of patch applied :-
[oracle@localhost admin]$ opatch lsinventory
Invoking OPatch 10.2.0.5.1
Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation. All rights reserved.
Oracle Home : /oracle_home/app
Central Inventory : /oracle11gr2/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.5.1
OUI version : 10.2.0.5.0
OUI location : /oracle_home/app/oui
Log file location : /oracle_home/app/cfgtoollogs/opatch/opatch2013-09-30_09-59-14AM.log
Patch history file: /oracle_home/app/cfgtoollogs/opatch/opatch_history.txt
Lsinventory Output file location : /oracle_home/app/cfgtoollogs/opatch/lsinv/lsinventory2013-09-30_09-59-14AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 4 10.2.0.5.0
There are 2 products installed in this Oracle Home.
Interim patches (1) :
Patch 16619894 : applied on Mon Sep 30 09:57:21 GMT+05:30 2013
Unique Patch ID: 16519126
Created on 28 Jun 2013, 01:27:47 hrs PST8PDT
Bugs fixed:
13596521, 8865718, 11790175, 13489660, 9020537, 9772888, 8650138
8664189, 10091698, 14275629, 14469008, 10092858, 12551710, 7519406
9821321, 13349665, 8771916, 7509714, 16619894, 8822531, 10139235
10159846, 13257247, 8350262, 11792865, 7119382, 13632738, 11724962
16309604, 16309605, 16309606, 8966823, 9320130, 16961614, 16961615
13775862, 16961616, 11674645, 16961617, 16961618, 15877957, 7026523
16961619, 15877958, 15877959, 9399589, 14841459, 9672816, 13503598
9499302, 9150282, 9448311, 9659614, 13632743, 14220725, 9949948, 8882576
10327179, 7612454, 7111619, 9711859, 9714832, 9735237, 9952230, 15877960
12780098, 13561951, 15877961, 15877962, 14665116, 15877963, 8660422
11066597, 16703112, 16279401, 14546673, 14105702, 14459552, 9713537
14105703, 14105704, 13483152, 13737773, 13737775, 14269955, 12925532
12748240, 9694101, 14390396, 12862186, 12862187, 10249537, 14727319
9586877, 8211733, 6694396, 9548269, 7115910, 7710224, 9337325, 8354642
7602341, 14076510, 10157402, 11856395, 12565867, 6402302, 10327190
10269717, 13015379, 11693109, 14023636, 10017048, 8546356, 8394351
9024850, 13561750, 8224558, 9770451, 9360157, 8488233, 9109487, 10132870
14841558, 9171933, 16817117, 10173237, 9532911, 10068982, 7361418
10306945, 8666117, 11725006, 6157713, 10214450, 9184754, 14205448
8544696, 9767674, 16306019, 9323583, 8277300, 13343467, 16279211
9726739, 16382448, 13791364, 8412426, 10326338, 10165083, 10208905
12419392, 6651220, 9145204, 13554409, 11076894, 7450366, 11893577
8970313, 14492313, 6690853, 6011045, 14492314, 11814891, 10162036
14492315, 10248542, 14492316, 16742123, 9469117, 13359623, 9952270
9842573, 13343471, 12710774, 10324526, 14546638, 12419258, 9322219
8636407, 16056270, 10010310, 12828105, 9689310, 9390484, 13736501
13736502, 9824435, 13736503, 13736504, 13736505, 13736506, 9963497
9032322, 13736507, 12551700, 12551701, 14035825, 12551702, 11858315
12551703, 12551704, 10076669, 16270946, 12551705, 12551706, 14040433
12551707, 6076890, 14258925, 12551708, 9308296, 13916709, 12827745
12880299, 14038805, 13923855, 9072105, 8528171, 11737047
--------------------------------------------------------------------------------
OPatch succeeded.
2. Shutdown Database and listener :-
[oracle@localhost admin]$ sqlplus
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Sep 30 10:02:42 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Enter user-name: /as sysdba
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost admin]$ lsnrctl stop
LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 30-SEP-2013 10:06:03
Copyright (c) 1991, 2010, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
The command completed successfully
[oracle@localhost admin]$
3. Before Rollback check that "catbundle_PSU__ROLLBACK.sql" file exist in ORACLE_HOME/rdbms/admin. if exists start the Rollback process :-
[oracle@localhost DBSOFT]$ opatch rollback -id 16619894
Invoking OPatch 10.2.0.5.1
Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation. All rights reserved.
Oracle Home : /oracle_home/app
Central Inventory : /oracle11gr2/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.5.1
OUI version : 10.2.0.5.0
OUI location : /oracle_home/app/oui
Log file location : /oracle_home/app/cfgtoollogs/opatch/opatch2013-09-30_10-08-49AM.log
Patch history file: /oracle_home/app/cfgtoollogs/opatch/opatch_history.txt
RollbackSession rolling back interim patch '16619894' from OH '/oracle_home/app'
Running prerequisite checks...
OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/oracle_home/app')
Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files affected by the patch '16619894' for restore. This might take a while...
Execution of 'sh /oracle_home/app/.patch_storage/16619894_Jun_28_2013_01_27_47/original_patch/custom/scripts/pre -rollback 16619894 ':
Return Code = 0
Patching component oracle.rdbms, 10.2.0.5.0...
Deleting "kstst.o" from archive "/oracle_home/app/lib/libserver10.a"
Deleting "kststqad.o" from archive "/oracle_home/app/lib/libserver10.a"
Updating archive file "/oracle_home/app/lib/libserver10.a" with "lib/libserver10.a/kcbl.o"
Updating archive file "/oracle_home/app/lib/libserver10.a" with "lib/libserver10.a/qecsel.o"
Updating archive file "/oracle_home/app/lib/libserver10.a" with "lib/libserver10.a/ksfd.o"
Updating archive file "/oracle_home/app/lib/libserver10.a" with "lib/libserver10.a/qkexr.o"
Updating archive file "/oracle_home/app/lib/libserver10.a" with "lib/libserver10.a/xty.o"
Updating archive file "/oracle_home/app/lib/libserver10.a" with "lib/libserver10.a/qergh.o"
Updating archive file "/oracle_home/app/lib/libserver10.a" with "lib/libserver10.a/qergs.o"
Updating archive file "/oracle_home/app/lib/libserver10.a" with "lib/libserver10.a/ktsx.o"
Updating archive file "/oracle_home/app/lib/libserver10.a" with "lib/libserver10.a/kdt.o"
Updating archive file "/oracle_home/app/lib/libserver10.a" with "lib/libserver10.a/kkpod.o"
Updating archive file "/oracle_home/app/lib/libserver10.a" with "lib/libserver10.a/kdiss.o"
Updating archive file "/oracle_home/app/lib/libserver10.a" with "lib/libserver10.a/qerix.o"
Updating archive file "/oracle_home/app/lib/libserver10.a" with "lib/libserver10.a/knld.o"
Updating archive file "/oracle_home/app/lib/libserver10.a" with "lib/libserver10.a/ktein.o"
Updating archive file "/oracle_home/app/lib/libserver10.a" with "lib/libserver10.a/kkzu.o"
Updating archive file "/oracle_home/app/lib/libserver10.a" with "lib/libserver10.a/tbsdrv.o"
Updating archive file "/oracle_home/app/lib/libserver10.a" with "lib/libserver10.a/ktec.o"
Updating archive file "/oracle_home/app/lib/libserver10.a" with "lib/libserver10.a/kteop.o"
Updating archive file "/oracle_home/app/lib/libserver10.a" with "lib/libserver10.a/ktsp.o"
.
.
.
.
Updating jar file "/oracle_home/app/sysman/jlib/emjsp.jar" with "/oracle_home/app/.patch_storage/16619894_Jun_28_2013_01_27_47/files//sysman/jlib/emjsp.jar/_database/_dbclone/_dbClone__Warning$__jsp_StaticText.class"
Updating jar file "/oracle_home/app/sysman/jlib/emjsp.jar" with "/oracle_home/app/.patch_storage/16619894_Jun_28_2013_01_27_47/files//sysman/jlib/emjsp.jar/_database/_dist/_streams/_queue/_editQueue.class"
Updating jar file "/oracle_home/app/sysman/jlib/emjsp.jar" with "/oracle_home/app/.patch_storage/16619894_Jun_28_2013_01_27_47/files//sysman/jlib/emjsp.jar/_database/_dist/_streams/_queue/_editQueue$__jsp_StaticText.class"
Copying file to "/oracle_home/app/oc4j/j2ee/oc4j_applications/applications/em/em/admin/rep/editUserSummary.uix"
Copying file to "/oracle_home/app/oc4j/j2ee/oc4j_applications/applications/em/em/WEB-INF/web.xml"
Patching component oracle.xdk.rsf, 10.2.0.5.0...
Updating archive file "/oracle_home/app/lib/libxml10.a" with "lib/libxml10.a/lpxpar.o"
Updating archive file "/oracle_home/app/lib32/libxml10.a" with "lib32/libxml10.a/lpxpar.o"
Patching component oracle.precomp.common, 10.2.0.5.0...
Patching component oracle.rdbms.rman, 10.2.0.5.0...
Copying file to "/oracle_home/app/rdbms/admin/recover.bsq"
Patching component oracle.sdo.locator, 10.2.0.5.0...
Updating archive file "/oracle_home/app/lib/libordsdo10.a" with "lib/libordsdo10.a/mdidx.o"
Updating archive file "/oracle_home/app/lib/libordsdo10.a" with "lib/libordsdo10.a/mdrcr.o"
Updating archive file "/oracle_home/app/lib/libordsdo10.a" with "lib/libordsdo10.a/mdrt.o"
Updating archive file "/oracle_home/app/lib/libordsdo10.a" with "lib/libordsdo10.a/mdopp.o"
Updating archive file "/oracle_home/app/lib/libordsdo10.a" with "lib/libordsdo10.a/mdgr.o"
Copying file to "/oracle_home/app/md/admin/mdprivs.sql"
Patching component oracle.network.listener, 10.2.0.5.0...
Patching component oracle.network.client, 10.2.0.5.0...
Copying file to "/oracle_home/app/bin/adapters"
Patching component oracle.ovm, 10.2.0.5.0...
Copying file to "/oracle_home/app/rdbms/admin/owmr1116.plb"
Copying file to "/oracle_home/app/rdbms/admin/owmv1116.plb"
Patching component oracle.oem.oemlt, 10.2.0.5.0...
Copying file to "/oracle_home/app/rdbms/admin/execocm.sql"
Patching component oracle.javavm.server, 10.2.0.5.0...
Copying file to "/oracle_home/app/lib/libjox10.so"
Copying file to "/oracle_home/app/lib32/libjox10.so"
Running make for target client_sharedlib
Running make for target ioracle
Running make for target iwrap
Running make for target client_sharedlib
Running make for target proc
Running make for target irman
Running make for target itnslsnr
RollbackSession removing interim patch '16619894' from inventory
The local system has been patched and can be restarted.
OPatch succeeded.
4. Start database and run "catbundle_PSU_ORCL_ROLLBACK.sql" script :-
[oracle@localhost admin]$ sqlplus
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Sep 30 10:10:21 2013
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Enter user-name: /as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.
Total System Global Area 599785472 bytes
Fixed Size 2098112 bytes
Variable Size 230689856 bytes
Database Buffers 360710144 bytes
Redo Buffers 6287360 bytes
Database mounted.
Database opened.
SQL> @catbundle_PSU_ORCL_ROLLBACK.sql
SQL> COLUMN spool_file NEW_VALUE spool_file NOPRINT
SQL> SELECT '/oracle_home/app/cfgtoollogs/catbundle/' || 'catbundle_PSU_' || name || '_ROLLBACK_' || TO_CHAR(SYSDATE, 'YYYYMonDD_hh24_mi_ss', 'NLS_DATE_LANGUAGE=''AMERICAN''') || '.log' AS spool_file FROM v$database;
SQL> SPOOL &spool_file
SQL> exec dbms_registry.set_session_namespace('SERVER')
PL/SQL procedure successfully completed.
SQL> PROMPT Processing Oracle Enterprise Manager...
Processing Oracle Enterprise Manager...
SQL> ALTER SESSION SET current_schema = SYSMAN;
Session altered.
SQL> @?/sysman/admin/emdrep/sql/core/latest/ecm/ecm_util_pkgdef.sql
SQL> Rem
SQL> Rem $Header: ecm_util_pkgdef.sql 31-oct-2003.14:19:54 kchiasso Exp $
SQL> Rem
SQL> Rem ecm_util_pkgdef.sql
SQL> Rem
SQL> Rem Copyright (c) 2002, 2003, Oracle Corporation. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem ecm_util_pkgdef.sql -
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem
SQL> Rem
SQL> Rem NOTES
SQL> Rem
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem vkhizder 10/31/03 - adding functions for getting
SQL> Rem patchsets/patches/bugs for a home
SQL> Rem groyal 08/28/03 - Enhance concat to support a limit
SQL> Rem jmansur 08/21/03 - update get_clone_source to match latest design
SQL> Rem shuberma 04/16/03 - Removing procedure that is not longer used
SQL> Rem shuberma 02/12/03 - Document new column in returned cursor
SQL> Rem shuberma 01/03/03 - Adding procedure for clone home source
SQL> Rem shuberma 12/23/02 - Adding an optional argument to the concat_col call
SQL> Rem rmenon 10/18/02 - added CONCAT_COMPONENT_VERSION definition
SQL> Rem rpinnama 05/15/02 - rpinnama_reorg_rep_scripts
SQL> Rem rpinnama 05/15/02 - Created
SQL> Rem
SQL>
SQL> rem********************************************************************
SQL> rem
SQL> rem PURPOSE
SQL> rem
SQL> rem The ECM_UTIL package contains procedures and functions for various
SQL> rem purposes including returning a list of target names for jobs, or admins
SQL> rem for rules.
SQL> rem
SQL> rem PROCEDURES and FUNCTIONS
SQL> rem
SQL> rem JOB_TARGET_LIST
SQL> rem ARGUMENTS: All are IN parameters unless otherwise noted.
SQL> rem job_id -- The internal job id for which to return the target list.
SQL> rem RETURNS: a VARCHAR2 which is the space separated target list.
SQL> rem
SQL> rem HOST_HOME_TARGET_LIST
SQL> rem ARGUMENTS: All are IN parameters unless otherwise noted.
SQL> rem host_name -- The name of the host for which to compare for ORACLE_HOME.
SQL> rem oracle_home -- The path of the ORACLE_HOME for which to compare the host.
SQL> rem target_type -- The type of target for which to compare for ORACLE_HOME.
SQL> rem RETURNS: a VARCHAR2 which is the comma separated target list.
SQL> rem
SQL> rem NOTES
SQL> rem
SQL> rem The methods in this package do not make any assumptions about
SQL> rem transacations. Essentially, it's up to the call to commit or rollback,
SQL> rem unless otherwise noted.
SQL> rem
.
.
.
.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Session altered.
Updating registry...
1 row created.
Commit complete.
Check the following log file for errors:
SQL> @?/cpu/scripts/bug11057369.sql
SQL> Rem
SQL> Rem $Header: rdbms/admin/bug11057369.sql st_server_jheng_rfi_bug-11057369/1 2011/02/25 18:31:55 jheng Exp $
SQL> Rem
SQL> Rem bug11057369.sql
SQL> Rem
SQL> Rem Copyright (c) 2011, Oracle and/or its affiliates. All rights reserved.
SQL> Rem
.
.
.
.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Grant succeeded.
Session altered.
Updating registry...
1 row created.
Commit complete.
Check the following log file for errors:
/oracle_home/app/cfgtoollogs/catbundle/catbundle_PSU_ORCL_ROLLBACK_2013Sep30_10_11_02.log
Check Log file /oracle_home/app/cfgtoollogs/catbundle/catbundle_PSU_ORCL_ROLLBACK_2013Sep30_10_11_02.log for any errors occured.
5. Check for Invalid objects :-
SQL> @utlrp.sql
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2013-09-30 10:11:28
DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid
DOC> objects in the database. Recompilation time is proportional to the
DOC> number of invalid objects in the database, so this command may take
DOC> a long time to execute on a database with a large number of invalid
DOC> objects.
DOC>
DOC> Use the following queries to track recompilation progress:
DOC>
DOC> 1. Query returning the number of invalid objects remaining. This
DOC> number should decrease with time.
DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6);
DOC>
DOC> 2. Query returning the number of objects compiled so far. This number
DOC> should increase with time.
DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;
DOC>
DOC> This script automatically chooses serial or parallel recompilation
DOC> based on the number of CPUs available (parameter cpu_count) multiplied
DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu).
DOC> On RAC, this number is added across all RAC nodes.
DOC>
DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel
DOC> recompilation. Jobs are created without instance affinity so that they
DOC> can migrate across RAC nodes. Use the following queries to verify
DOC> whether UTL_RECOMP jobs are being created and run correctly:
DOC>
DOC> 1. Query showing jobs created by UTL_RECOMP
DOC> SELECT job_name FROM dba_scheduler_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>
DOC> 2. Query showing UTL_RECOMP jobs that are running
DOC> SELECT job_name FROM dba_scheduler_running_jobs
DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';
DOC>#
PL/SQL procedure successfully completed.
TIMESTAMP
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2013-09-30 10:11:29
PL/SQL procedure successfully completed.
DOC> The following query reports the number of objects that have compiled
DOC> with errors (objects that compile with errors have status set to 3 in
DOC> obj$). If the number is higher than expected, please examine the error
DOC> messages reported with each object (using SHOW ERRORS) to see if they
DOC> point to system misconfiguration or resource constraints that must be
DOC> fixed before attempting to recompile these objects.
DOC>#
OBJECTS WITH ERRORS
-------------------
0
DOC> The following query reports the number of errors caught during
DOC> recompilation. If this number is non-zero, please query the error
DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors
DOC> are due to misconfiguration or resource constraints that must be
DOC> fixed before objects can compile successfully.
DOC>#
ERRORS DURING RECOMPILATION
---------------------------
0
PL/SQL procedure successfully completed.
6. Check that Patch has been successfully rollbacked :-
[oracle@localhost admin]$ opatch lsinventory
Invoking OPatch 10.2.0.5.1
Oracle Interim Patch Installer version 10.2.0.5.1
Copyright (c) 2010, Oracle Corporation. All rights reserved.
Oracle Home : /oracle_home/app
Central Inventory : /oracle11gr2/app/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.5.1
OUI version : 10.2.0.5.0
OUI location : /oracle_home/app/oui
Log file location : /oracle_home/app/cfgtoollogs/opatch/opatch2013-09-30_10-11-56AM.log
Patch history file: /oracle_home/app/cfgtoollogs/opatch/opatch_history.txt
Lsinventory Output file location : /oracle_home/app/cfgtoollogs/opatch/lsinv/lsinventory2013-09-30_10-11-56AM.txt
--------------------------------------------------------------------------------
Installed Top-level Products (2):
Oracle Database 10g 10.2.0.1.0
Oracle Database 10g Release 2 Patch Set 4 10.2.0.5.0
There are 2 products installed in this Oracle Home.
There are no Interim patches installed in this Oracle Home.
--------------------------------------------------------------------------------
OPatch succeeded.
I hope this article helped you.
Regards,
Amit Rath
