Oracle Flashback Database Technology in Oracle 11g

FLASHBACK DATABASE Technology to rewind the database to a target time, SCN or log sequence number. This command works by undoing changes made by Oracle Database to the datafiles that exist when you run the command. Flashback can fix logical failures, but not physical failures. Thus, you cannot use the command to recover from disk failures or the accidental deletion of datafiles.

FLASHBACK DATABASE is usually much faster than a RESTORE operation followed by point-in-time recovery, because the time needed to perform FLASHBACK DATABASE depends on the number of changes made to the database since the desired flashback time. On the other hand, the time needed to do a traditional point-in-time recovery from restored backups depends on the size of the database.

Requirements

Database must run in ARCHIVELOG mode.

Database must contain no online tablespaces for which flashback functionality was disabled with the SQL statement ALTER TABLESPACE … FLASHBACK OFF.

Open Flashback Database Mode

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT
SQL> ALTER DATABASE FLASHBACK ON;
SQL> ALTER DATABASE OPEN;

Show Current SCN Number

SQL> SELECT dbms_flashback.get_system_change_number FROM DUAL;
or
SQL> SELECT current_scn FROM V$DATABASE;

Show Oldest SCN Number

SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME FROM V$FLASHBACK_DATABASE_LOG;
Note: You can use OLDEST_FLASHBACK_SCN as SCN number in flashback database query

Create Restore Point