Oracle:flashback database to earlier time from SQL prompt

In the last post we have seen how to flashback a database using RMAN utility. And i have discussed about the pros and cons of flashback database also. To review please check:

HOW TO FLASHBACK DATABASE USING RMAN UTILITY

Now , we will see what are the options available to flashback database from SQL*plus utility.

being in the sql prompt also you can do several flashback of database .Available options are:
1) flashback to a specific date and time
2) flashback to a specific SCN number
3) flashback to a named restore point

In Detail::

1) flashback to a specific date and time:

sql>connect sys/e@lion as sysdba
sql>shutdown immediate
sql>startup mount
sql>flashback database to timestamp to_date('14-FEB-2007 11:30:00','DD-MON-YYYY HH24:MI:SS');

Please read USING RESETLOGS WITH FLASHBACK DATABASE FEATURE before performing the following step.

sql>alter database open resetlogs;

2) flashback to a specific SCN number:

sql>connect sys/e@lion as sysdba

Check the current SCN number first:
sql> select current_scn from v$database;

CURRENT_SCN
-----------
916022

Then,
sql>shutdown immediate
sql>startup mount
sql> flashback database to scn 915000;
Please read USING RESETLOGS WITH FLASHBACK DATABASE FEATURE before performing the following step.

sql>alter database open resetlogs;

3) flashback to a named restore point:

sql>connect sys/e@lion as sysdba
sql>shutdown immediate
sql>startup mount
then issue the following with the restore point name you want to flashback. In my case, it is "ershad"

sql>flashback database to restore point ershad;

Please read USING RESETLOGS WITH FLASHBACK DATABASE FEATURE before performing the following step.

sql>alter database open resetlogs;

Topic explained:
WHAT IS RESTORE POINT
HOW TO CREATE RESTORE POINT