How To Rollback 11.2.0.4.1 to 10.2.0.4.0

i’d been doing a lot of upgrade for the last month, and one it decided to rollback
due to some application incompatibility.

1. Perform cold backup of the database.
2. Remove PSU applied, for me i need to remove Patch 17478514

Shut down all instances and listeners associated with the Oracle home that you are updating.
    opatch rollback -id 17478514

    Start all database instances running from the Oracle home.

    cd $ORACLE_HOME/rdbms/admin
    SQL> CONNECT / AS SYSDBA
    SQL> STARTUP
    SQL> @catbundle_PSU_PTMS5_ROLLBACK.sql

dbu05lnx:PTMS5>$ cd $ORACLE_HOME/rdbms/admin
dbu05lnx:PTMS5>$ ls -l catbundle_PSU_*
-rw-r----- 1 oracle dba 1007 Apr  8 01:43 catbundle_PSU_PTMS5_APPLY.sql
-rw-r----- 1 oracle dba 1056 Apr  8 01:43 catbundle_PSU_PTMS5_ROLLBACK.sql
dbu05lnx:PTMS5>$
SQL> QUIT

3. since i updgraded my DST to 14 during post upgrade i need to apply the patch on my old oracle home.


dbu05lnx:PTMS5>$ cd 7695070
dbu05lnx:PTMS5>$ opatch apply
Invoking OPatch 10.2.0.4.2

Oracle Interim Patch Installer version 10.2.0.4.2
Copyright (c) 2007, Oracle Corporation.  All rights reserved.


Oracle Home       : /opt/app/oracle/product/10.2.0
Central Inventory : /opt/app/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.2
OUI version       : 10.2.0.4.0
OUI location      : /opt/app/oracle/product/10.2.0/oui
Log file location : /opt/app/oracle/product/10.2.0/cfgtoollogs/opatch/opatch2014-05-08_22-42-59PM.log

ApplySession applying interim patch '7695070' to OH '/opt/app/oracle/product/10.2.0'

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 = '/opt/app/oracle/product/10.2.0')


Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '7695070' for restore. This might take a while...
Backing up files affected by the patch '7695070' for rollback. This might take a while...

Patching component oracle.oracore.rsf, 10.2.0.4.0...
Updating archive file "/opt/app/oracle/product/10.2.0/lib/libcore10.a"  with "lib/libcore10.a/ltz.o"
Updating archive file "/opt/app/oracle/product/10.2.0/lib/libcore10.a"  with "lib/libcore10.a/ltz_v2.o"
Updating archive file "/opt/app/oracle/product/10.2.0/lib/libcore10.a"  with "lib/libcore10.a/lditza.o"
Updating archive file "/opt/app/oracle/product/10.2.0/lib/libcore10.a"  with "lib/libcore10.a/sldigpts.o"
Updating archive file "/opt/app/oracle/product/10.2.0/lib32/libcore10.a"  with "lib32/libcore10.a/ltz.o"
Updating archive file "/opt/app/oracle/product/10.2.0/lib32/libcore10.a"  with "lib32/libcore10.a/ltz_v2.o"
Updating archive file "/opt/app/oracle/product/10.2.0/lib32/libcore10.a"  with "lib32/libcore10.a/lditza.o"
Updating archive file "/opt/app/oracle/product/10.2.0/lib32/libcore10.a"  with "lib32/libcore10.a/sldigpts.o"

Patching component oracle.rdbms, 10.2.0.4.0...
Updating archive file "/opt/app/oracle/product/10.2.0/lib/libserver10.a"  with "lib/libserver10.a/prm.o"
Running make for target client_sharedlib
Running make for target client_sharedlib
Running make for target ioracle
ApplySession adding interim patch '7695070' to inventory

Verifying the update...
Inventory check OK: Patch ID 7695070 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 7695070 are present in Oracle Home.

The local system has been patched and can be restarted.


OPatch succeeded.
dbu05lnx:PTMS5>$ pwd

dbu05lnx:PTMS5>$ cd 9742710

dbu05lnx:PTMS5>$ opatch apply
Invoking OPatch 10.2.0.4.2

Oracle Interim Patch Installer version 10.2.0.4.2
Copyright (c) 2007, Oracle Corporation.  All rights reserved.


Oracle Home       : /opt/app/oracle/product/10.2.0
Central Inventory : /opt/app/oracle/oraInventory
   from           : /etc/oraInst.loc
OPatch version    : 10.2.0.4.2
OUI version       : 10.2.0.4.0
OUI location      : /opt/app/oracle/product/10.2.0/oui
Log file location : /opt/app/oracle/product/10.2.0/cfgtoollogs/opatch/opatch2014-05-08_20-43-00PM.log

ApplySession applying interim patch '9742718' to OH '/opt/app/oracle/product/10.2.0'

Running prerequisite checks...

OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only.

Backing up files and inventory (not for auto-rollback) for the Oracle Home
Backing up files affected by the patch '9742718' for restore. This might take a while...
Backing up files affected by the patch '9742718' for rollback. This might take a while...

Patching component oracle.oracore.rsf, 10.2.0.4.0...
Copying file to "/opt/app/oracle/product/10.2.0/oracore/zoneinfo/timezone.dat"
Copying file to "/opt/app/oracle/product/10.2.0/oracore/zoneinfo/timezlrg.dat"
Copying file to "/opt/app/oracle/product/10.2.0/oracore/zoneinfo/readme.txt"
ApplySession adding interim patch '9742718' to inventory

Verifying the update...
Inventory check OK: Patch ID 9742718 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 9742718 are present in Oracle Home.

OPatch succeeded.
dbu05lnx:PTMS5>$

Perform the actual downgrade.

4.check if all component are valid.

COMP_NAME                                          STATUS                            VERSION
-------------------------------------------------- --------------------------------- ------------------------------
JServer JAVA Virtual Machine                       VALID                             11.2.0.4.0
OLAP Analytic Workspace                            VALID                             11.2.0.4.0
OLAP Catalog                                       VALID                             11.2.0.4.0
Oracle Data Mining                                 VALID                             11.2.0.4.0
Oracle Database Catalog Views                      VALID                             11.2.0.4.0
Oracle Database Java Packages                      VALID                             11.2.0.4.0
Oracle Database Packages and Types                 VALID                             11.2.0.4.0
Oracle Enterprise Manager                          VALID                             11.2.0.4.0
Oracle Expression Filter                           VALID                             11.2.0.4.0
Oracle Multimedia                                  VALID                             11.2.0.4.0
Oracle OLAP API                                    VALID                             11.2.0.4.0
Oracle Rules Manager                               VALID                             11.2.0.4.0
Oracle Text                                        VALID                             11.2.0.4.0
Oracle Workspace Manager                           VALID                             11.2.0.4.0
Oracle XDK                                         VALID                             11.2.0.4.0
Oracle XML Database                                VALID                             11.2.0.4.0
Spatial                                            VALID                             11.2.0.4.0

17 rows selected.

SQL>

5.Shutdown database

SQL> select name from v$database;

NAME
---------------------------
PTMS5

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

6.Start database in downgrade mode.

SQL> startup downgrade;
ORACLE instance started.

Total System Global Area 6413680640 bytes
Fixed Size                  2265224 bytes
Variable Size            1224740728 bytes
Database Buffers         5167382528 bytes
Redo Buffers               19292160 bytes
Database mounted.
Database opened.
SQL>

7. Drop sysman user.

SQL> DROP USER sysman CASCADE;

User dropped.

SQL>

8. Execute the downgrade script

SQL> !pwd
/opt/app/oracle/product/11.2.0.4/db_1/rdbms/admin

SQL> SPOOL downgrade.log
SQL> @catdwgrd.sql

PL/SQL procedure successfully completed.

Function created.

Function dropped.

DOC>#############################################################################
DOC>#############################################################################
DOC>
DOC>    If the below PL/SQL block raises an ORA-20001 error, then
DOC>    de-initialize the DBMS_AUDIT_MGMT package using
DOC>    declare
DOC>      RetVal BOOLEAN;
.
.
.
.


SQL> SELECT 'COMP_TIMESTAMP DWGRD_END ' ||
  2          TO_CHAR(SYSTIMESTAMP,'YYYY-MM-DD HH24:MI:SS ')  ||
  3          TO_CHAR(SYSTIMESTAMP,'J SSSSS ')
  4          AS timestamp FROM DUAL;

TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP DWGRD_END 2014-05-08 21:28:16 2456786 77296

1 row selected.

SQL>
SQL> Rem ***********************************************************************
SQL> Rem END catdwgrd.sql
SQL> Rem ***********************************************************************
SQL>
SQL>

9. After an hour downgrade scripts completes. stop and start database in old oracle home.

dbu05lnx:PTMS5>$ sqlplus '/ as sysdba'

SQL*Plus: Release 10.2.0.4.0 - Production on Thu May 8 21:53:51 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup upgrade;
ORACLE instance started.

Total System Global Area 6442450944 bytes
Fixed Size                  2093584 bytes
Variable Size            1090522608 bytes
Database Buffers         5335154688 bytes
Redo Buffers               14680064 bytes
Database mounted.
Database opened.

10. Now Execute the reload script it will take another 45-60mins to complete.

SQL> SPOOL reload.log
SQL> @catrelod.sql

DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC>   The above query lists the SERVER components now loaded in the
DOC>   database, along with their current version and status.
DOC>
DOC>   Please review the status and version columns and look for
DOC>   any errors in the spool log file.  If there are errors in the spool
DOC>   file, or any components are not VALID or not the correct 10.1.0
DOC>   patch version, consult the downgrade chapter of the current release
DOC>   Database Upgrade book.
DOC>
DOC>   Next shutdown immediate, restart for normal operation, and then
DOC>   run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL>
SQL> Rem *******************************************************************
SQL> Rem END catrelod.sql
SQL> Rem *******************************************************************
SQL> 

11. Validate all components are valid. Some components will be invalid.Restart Database in normal mode and execute ultrp.

COMP_NAME                           STATUS                            VERSION
----------------------------------- --------------------------------- ------------------------------
JServer JAVA Virtual Machine        VALID                             10.2.0.4.0
OLAP Analytic Workspace             VALID                             10.2.0.4.0
OLAP Catalog                        VALID                             10.2.0.4.0
Oracle Data Mining                  VALID                             10.2.0.4.0
Oracle Database Catalog Views       VALID                             10.2.0.4.0
Oracle Database Java Packages       VALID                             10.2.0.4.0
Oracle Database Packages and Types  VALID                             10.2.0.4.0
Oracle Expression Filter            INVALID                           10.2.0.4.0
Oracle OLAP API                     VALID                             10.2.0.4.0
Oracle Rule Manager                 INVALID                           10.2.0.4.0
Oracle Text                         VALID                             10.2.0.4.0
Oracle Workspace Manager            VALID                             10.2.0.4.3
Oracle XDK                          VALID                             10.2.0.4.0
Oracle XML Database                 VALID                             10.2.0.4.0
Oracle interMedia                   VALID                             10.2.0.4.0
Spatial                             INVALID                           10.2.0.4.0

16 rows selected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup ;
ORACLE instance started.

Total System Global Area 6413680640 bytes
Fixed Size                  2265224 bytes
Variable Size            1224740728 bytes
Database Buffers         5167382528 bytes
Redo Buffers               19292160 bytes
Database mounted.
Database opened.
SQL>

SQL>@?rdbms/admin/utlrp.sql

12. After executing utlrp all invalid objects will be valid again.

COMP_NAME                                          STATUS                            VERSION
-------------------------------------------------- --------------------------------- ------------------------------
JServer JAVA Virtual Machine                       VALID                             10.2.0.4.0
OLAP Analytic Workspace                            VALID                             10.2.0.4.0
OLAP Catalog                                       VALID                             10.2.0.4.0
Oracle Data Mining                                 VALID                             10.2.0.4.0
Oracle Database Catalog Views                      VALID                             10.2.0.4.0
Oracle Database Java Packages                      VALID                             10.2.0.4.0
Oracle Database Packages and Types                 VALID                             10.2.0.4.0
Oracle Expression Filter                           VALID                             10.2.0.4.0
Oracle OLAP API                                    VALID                             10.2.0.4.0
Oracle Rule Manager                                VALID                             10.2.0.4.0
Oracle Text                                        VALID                             10.2.0.4.0
Oracle Workspace Manager                           VALID                             10.2.0.4.3
Oracle XDK                                         VALID                             10.2.0.4.0
Oracle XML Database                                VALID                             10.2.0.4.0
Oracle interMedia                                  VALID                             10.2.0.4.0
Spatial                                            VALID                             10.2.0.4.0

16 rows selected.

SQL>

DONE.
Thanks!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s