Oracle DBA Tips and Techniques

By Arjun Raja

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>

Advertisements

2 Responses to “USING FLASHBACK DATABASE TO ROLLBACK FROM 11g to 10g.”

  1. oracleman said

    how do we
    “Make sure at least one more flashback log is created in the 10g environment after the restore point is created”

    and if we were to do this
    upgrading 11gr1 to 11gr2
    do we need there is one more FBL ?

    thanks

  2. Chandra said

    Thanks for this great article. You menntioned that “Make sure atleast one more flashback log is created in the 10g environment after the restore point is created”.

    However, my question is how do you generate a new flashback log in case it doesn’t create one automatically?
    Thanks.

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

 
%d bloggers like this: