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
SQL> CREATE RESTORE POINT restorepoint1 GUARANTEE FLASHBACK DATABASE;
List Restore Points
RMAN> LIST RESTORE POINT ALL;
Restore Database to SCN With Flashback Database
RMAN> SHUTDOWN IMMEDIATE
RMAN> STARTUP MOUNT
RMAN> FLASHBACK DATABASE TO SCN 12546872;
RMAN> ALTER DATABASE OPEN RESETLOGS;
Restore Database to Restore Point With Flashback Database
RMAN> SHUTDOWN IMMEDIATE
RMAN> STARTUP MOUNT
RMAN> FLASHBACK DATABASE TO RESTORE POINT ‘restorepoint1’;
RMAN> ALTER DATABASE OPEN RESETLOGS;
Restore Database to Time With Flashback Database
SHUTDOWN DATABASE
STARTUP MOUNT
FLASHBACK DATABASE TO TIMESTAMP SYSDATE-1;
RMAN> ALTER DATABASE OPEN RESETLOGS;
Drop Restore Point
DROP RESTORE POINT restorepoint1;