Downgrade Database from Oracle 11g to 10g Using Flash Back SCN

Before Upgrade:

To use this document, below are prerequisite for downgrade.

I convert database in archive log mode and turn flashback on in Oracle 10.2.0.2. using below commands:

sql> Alter database archivelog;
sql> Alter database flashback on;

I note down SCN number of database before upgrade using command:


SQL> select current_scn from v$database;

   CURRENT_SCN
--------------
      14209646

Perform Upgrade activity using step-by-step-manual-database-upgrade-to Oracle 11.2.0.2. Unfortunately upgrade fails or due to any reason downgrade database is required. 

Following are Steps for Downgrade using Flashback Database using SCN:


[oracle@11g_upgrade]$ sqlplus sys as sysdba


SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
cdr02

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> shutdown immediate

[oracle@11g_upgrade]$ rman target sys/sys


connected to target database (not started)

RMAN> startup mount

database is already started
database mounted
released channel: ORA_DISK_1

RMAN>  FLASHBACK DATABASE TO SCN 14209646;

Starting flashback at 06-APR-12
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK


starting media recovery

archived log for thread 1 with sequence 74 is already on disk as file /data1/FRA/cdr02/CDR02/archivelog/2012_04_05/o1_mf_1_74_7qv7lbbj_.arc
media recovery complete, elapsed time: 00:00:03
Finished flashback at 06-APR-12

RMAN> shutdown immediate

using target database control file instead of recovery catalog
database dismounted
Oracle instance shut down

------------------------Go to previous Oracle Home (Oracle 10g Home)----------------------------------


[oracle@11g_upgrade]$ export ORACLE_SID=cdr02
[oracle@11g_upgrade]$ sqlplus sys as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Fri Apr 6 14:23:34 2012

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

Enter password:
Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  629145600 bytes
Fixed Size                  1269064 bytes
Variable Size             171967160 bytes
Database Buffers          452984832 bytes
Redo Buffers                2924544 bytes
Database mounted.
SQL> alter database open resetlogs;

Database altered.

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
cdr02

Now, Database is downgraded to Database Management Software Oracle 10.2.0.4.

No comments:

Post a Comment