About Me

My photo
Bangalore, India
I am an Oracle Certified Professional working in SAP Labs. Everything written on my blog has been tested on my local environment, Please test before implementing or running in production. You can contact me at amit.rath0708@gmail.com.

Monday, September 30, 2013

How to RollBack a Database PSU

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_<DATABASE_NAME>_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 - <one-line expansion of the name>
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         <short description of component this file declares/defines>
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         <other useful comments, qualifications, etc.>
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

3 comments:

  1. Hi thanks for the article and If possible please tell me how to rollback in Oracle 11gR2 because I am not able to rollback. Its always saying to use "opatch rollback -id -ph "... here I dont where the Patch_Home locates.. Please help me with this...

    Thanks You..

    Sudheer (sudheer.sri96@gmail.com)

    ReplyDelete
    Replies
    1. Hi Sudheer ,

      Thanks for writing in, Your patch home is the same either ORACLE_HOME or GRID_HOME, where ever you applied the patch.

      if you applied it in ORACLE_HOME and your ORACLE_HOME is suppose /opt/oracle/product/11.2.0.4 and patch to be rollback is 1123 then use below command :-

      opatch rollback -id 1123 -ph /opt/oracle/product/11.2.0.4

      Hope this helps

      Thanks
      Amit Rath

      Delete
  2. It is nice post.I have prepared a comphrehensive post which details rollback steps for all type of Oracle patch
    http://techgoeasy.com/how-to-rollback-the-patches-in-oracle/

    Do take a look

    ReplyDelete