Oracle:flashback a database to earlier time using RMAN

we can flashback an Oracle database with following features:

1) flashback to a specific date and time
2) flashback to a specific SCN number
3) flashback to a named restore point
4) flashback to last resetlogs option

first of all, we need to enable flashback and properly size the flash recovery area so that flashback logs are created and we can flashback our database to a previous stage.
to do that please check the following:

HOW TO ENABLE FLASHBACK
HOW TO CONFIGURE FLASH RECOVERY AREA

now, it is configure properly and we can flashback our database as required.

One more information you may need, that is how far back you can flashback the database.
follow the link for this,MAXIMUM FLASHBACK POSSIBLE FOR ORACLE DATABASE

1)Flashback to a specific date and time:(steps)
RMAN> connect target /@lion
RMAN> shutdown immediate
RMAN> startup mount
RMAN> flashback database to time 'sysdate-50/60/24'; (doing a flashback to 50 minutes from sysdate)

OUTPUT:
Starting flashback at 14-FEB-09
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:07

Finished flashback at 14-FEB-09

at this point, you may need to check whether database has been flashbackED to the point you want.
to do that, open the database in read only mode and check the data.

RMAN> sql 'alter database open read only';

and the answer is yes,you got what you want, shutdown the database and open it with resetlogs option.
RMAN>shutdown immediate;
RMAN>startup mount;
RMAN> alter database open resetlogs;

If answer is no, you can redo the procedure to flashback to a diiferent TIME. that is,shutdown database,startup mount,flashback to the desired TIME,open in read only mode and check. if now it is fine , then shutdown the database once again and open the database with resetlog options.

RMAN> alter database open resetlogs;
we need to do this , as we are doing an incomplete recovery.........

if you want to mention a specific time then:

RMAN> flashback database to time "to_date('13-FEB-2009 11:00:00','DD-MON-YYYY hh24:mi:ss')";

2) flashback to a specific SCN number
first check the current scn of the database by issuing following statement:
connect as sysdba
sql> select current_scn from v$database;

CURRENT_SCN
-----------
909158

that means we can flashback to a SCN prior to this SCN only.

RMAN> connect target /@lion
RMAN> shutdown immediate
RMAN> startup mount

now, think i want to flashback till SCN number--

RMAN> flashback database to scn 909100;

Starting flashback at 22-FEB-09
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK


starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished flashback at 22-FEB-09

at this point, you may need to check whether database has been flashback to the point you want.
to do that, open the database in read only mode and check the data.
RMAN> sql 'alter database open read only';

Now , check this,
sys@LION> select current_scn from v$database;

CURRENT_SCN
-----------
909100

and the answer is yes,you got what you want, shutdown the database and open it with resetlogs option.
RMAN>shutdown immediate;
RMAN>startup mount;
RMAN> alter database open resetlogs;

If answer is no, you can redo the procedure to flashback to a diiferent SCN. that is,shutdown database,startup mount,flashback to the desired SCN ,open in read only mode and check. if now it is fine , then shutdown the database once again and open the database with resetlog options.

RMAN> alter database open resetlogs;
we need to do this , as we are doing an incomplete recovery.........

3.Flashback to a Restore Point:
we can flashback a database to a restore point also.
to do that,
RMAN> connect target /@lion
RMAN> shutdown immediate
RMAN> startup mount
then issue the following with the restore point name you want to flashback. In my case, it is "ershad"

RMAN> flashback database to restore point ershad;

at this point, you may need to check whether database has been flashback to the point you want.
to do that, open the database in read only mode and check the data.
RMAN> alter database open read only;

and the answer is yes,you got what you want, shutdown the database and open it with resetlogs option.
RMAN>shutdown immediate;
RMAN>startup mount;
RMAN> alter database open resetlogs;

If answer is no, you can redo the procedure to flashback to a diiferent restore point. that is,shutdown database,startup mount,flashback to the desired Restore point ,open in read only mode and check. if now it is fine , then shutdown the database once again and open the database with resetlog options.

RMAN> alter database open resetlogs;
we need to do this , as we are doing an incomplete recovery.........

WHAT IS RESTORE POINT
HOW TO CREATE RESTORE POINT

4)flashback to last resetlogs option

At last , During the flashback operation , we may end up with a mistake like:
You may have flashback to a wrong point. As we know it is still recoverable, BY opening the database in read only mode,you can check whether the operation is desired or not . if yes , open the database with resetlog options, if no, you can redo the procedure to your desired point once again.

Problem is , if you have flashback to a point and open the database with resetlogs options. And now feel that it was wrong.

Dont worry. You can still go back to the previous incarnation and recover your old state of database before resetlogs.Because, Oracle knows you expect a lot from him.
Let us check, HOW TO FLASHBACK DATABASE TO PRIOR TO LAST RESETLOGS OPTIONS:


RMAN> connect target /@lion
RMAN> shutdown immediate;
RMAN> startup mount;
RMAN> flashback database to before resetlogs;
RMAN> alter database open resetlogs;

Note: When we do a resetlogs, oracle will record the SCN . So the previous operation is nothing but flashing back to a previous SCN. We can check the last resetlogs SCN from the following query:
SQL> select resetlogs_change# from v$database;

RESETLOGS_CHANGE#
-----------------
911255

Note: Assume the current SCN is 25444. And I have flashback logs Till 22999. So, we can flashback to any point in between this.and more important , backward and forward both are possible in this range till you reach your expected point. I mean to say , once you flashback to 23888 and found it is not right and you need a bit forward. So, you can then flashback to 24000(which is actually going forward as of your last operation).You can flashback as many times in your range till the flashback logs are available and you have not opened it with resetlogs option.

More:
FLASHBACK A DATABASE FROM SQL PROMPT

MAXIMUM FLASHBACK POSSIBLE


Thanks a lot.....

Have a nice day