Oracle DBA Tips and Techniques

By Arjun Raja

Archive for July, 2010

USING FLASHBACK DATABASE TO ROLLBACK FROM 11g to 10g.

Posted by Arjun Raja on July 2, 2010

Using flashback database to rollback a failed upgrade to 11g works.

This method can probably be used as a first line of defence to avoid a time consuming restore incase a 11g upgrade fails.

However it goes without saying that a proper backup should also be taken prior to attempting any upgrade.

In 10g database –

Flashback should be turned on prior to upgrade –

Create a guaranteed restore point .

sql > create restore point arjun guarantee flashback database ;

Make sure atleast one more flashback log is created in the 10g environment after the restore point is created –

If not and the existing flashback log is appended after the database is upgraded to 11g , the following error will appear later on while trying to flashback the database.

“ORA-38792: encountered unknown flashback record from release 11.0.0.0.0″

Now Upgrade database to 11.2.0.1 –

Once complete –

Still in 11g environment –

SQL> select * from v$version;

BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
PL/SQL Release 11.2.0.1.0 – Production
CORE 11.2.0.1.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.2.0.1.0 – Production
NLSRTL Version 11.2.0.1.0 – Production

SQL> shutdown immediate;

Log back into the 10g environment –

cd $ORACLE_HOME/dbs

/u01/oracle/product/10.2/ofsad2/dbs> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 – Production on Fri Jul 2 07:48:00 2010

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

Connected to an idle instance.

SQL> startup mount;

Database mounted.

SQL> flashback database to restore point arjun;

ALERT LOG –

flashback database to restore point arjun
Flashback Restore Start
Fri Jul 2 07:49:37 2010
Flashback Restore Complete
Flashback Media Recovery Start
parallel recovery started with 3 processes
Flashback Media Recovery Log /u03/oradata/flash_recovery_area/DB10G/archivelog/2010_07_01/o1_mf_1_120_62r31dt6_.arc
Fri Jul 2 07:49:37 2010
Incomplete Recovery applied until change 5868883
Flashback Media Recovery Complete
Completed: flashback database to restore point arjun

sql > alter database open resetlogs;

SQL> select * from v$version;

BANNER
—————————————————————-
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bi
PL/SQL Release 10.2.0.4.0 – Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 – Productio
NLSRTL Version 10.2.0.4.0 – Production

Get rid of the restore point created.

SQL> select * from v$restore_point order by scn;

SCN DATABASE_INCARNATION# GUA STORAGE_SIZE
———- ——————— — ————
TIME
—————————————————————————
NAME
——————————————————————————–
5868880 2 YES 646053888
01/JUL/10 11:31:27.000000000 AM
ARJUN

SQL> drop restore point arjun;

Restore point dropped.

SQL> select * from v$restore_point order by scn;

no rows selected

SQL>

Posted in Oracle 11g | Tagged: , | 2 Comments »