Using of Flashback Database

Database can be flashed back either through SQL*Plus or RMAN.

1. In SQL*Plus, you can use this feature by issuing of the SQL command FLASHBACK DATABASE statement. For the command syntax, see: Oracle Database SQL Reference - 10g Release 2 (10.2) - Chapter 18 SQL Statements: DROP SEQUENCE to ROLLBACK - FLASHBACK DATABASE
2. In RMAN, you can use this feature by issuing of the command FLASHBACK. For the command syntax, see: Oracle Database Backup and Recovery Reference - 10g Release 2 (10.2) - Chapter 2 RMAN Commands - FLASHBACK Command

If there is an event (human error) that insists the database to be flashed back to a point in time, you can do this in few steps:

1. Ensure that you are not dealing with some kind of media failure. The Flashback Database is useful only when your data is logically corrupted.

2. Find the desired point in time for the flashback operation. Usually it is he time just before the time when some human error caused a logical damage of the database data. You can calculate this as SCN or a Date (TIMESTAMP expression). Additionally, you can use a restore point or a time just before last RESETLOGS operation. A Log Sequence Number can be used in 10g R1 but it is not available as an option in 10g R2 (via SQL*Plus). It is still available as an option in RMAN.

3. Find the current oldest SCN and time to which the database can be backwarded:
SQL> SELECT oldest_flashback_scn,oldest_flashback_time FROM v$flashback_database_log;
If the desired time is after the oldest time currently recorded in the flashback log files then go to the next step

4. Ensure that there are not tablespaces with disabled flashback logging:
SQL> SELECT NAME tbs_name,file_name db_file,flashback_on fb_logging, online_status status FROM v$tablespace,dba_data_files WHERE NAME=tablespace_name;
If there are files with no important data that are excluded from flashback logging then you can follow steps from the section: Dealing with Excluded Tablespaces in case of Flashback Database Operations
Otherwise, go to the next step.

5. Ensure that all needed archived and redo logs (covering the whole time period between oldest_flashback_time column value and the time of failure) are available:
5.1 Check physical availability:
RMAN> CROSSCHECK ARCHIVELOG FROM SCN oldest_flashback_scn;
Where oldest_flashback_scn is the value of the column oldest_flashback_scn in V$FLASHBACK_DATABASE_LOG.
5.2 Check logical availability:
RMAN> REPORT UNRECOVERABLE;
REPORT UNRECOVERABLE command reports all datafiles that cannot be recovered from existing backups because redo may be missing.
You can check the logical availability of archived redo logs with the following SQL as well:
SQL> SELECT * FROM v$archived_log WHERE first_change# > = oldest_flashback_scn AND status = 'A';
Where oldest_flashback_scn is the value of the column oldest_flashback_scn in V$FLASHBACK_DATABASE_LOG.

6. Find and write down the current SCN (or create a normal restore point if you are using 10g R2). Don’t forget that the Flashback Database is a change on the database and it must be recorded if some failure occurs during this operation.
SQL> SELECT current_scn FROM v$database;
or
SQL> CREATE RESTORE POINT before_flashback_operation;
To map a time value and SCN, you can use the built-in SQL functions (available since 10g):
SCN_TO_TIMESTAMP – to find associated timestamp with this SCN
TIMESTAMP_TO_SCN – to find associated SCN with this timestamp
SQL> SELECT current_scn, scn_to_timestamp(current_scn) FROM v$database;
This timestamp mapping information is recorded in the SMON_SCN_TIME table.
Oracle keeps the information in SMON_SCN_TIME table for a period of 5 days.

7. Restart the database in MOUNT stage
7.1 SQL> SHUTDOWN IMMEDIATE
7.2 SQL> STARTUP MOUNT
For RAC environments, use:
SQL> STARTUP MOUNT EXCLUSIVE
If you want to be able to return the database to its state just as it was closed, you can create a restore point at this moment (restore points can be created even in MOUNT stage):
SQL> CREATE RESTORE POINT just_after_shutdown;
Or to find the last checkpointed SCN:
SQL> SELECT checkpoint_change# FROM v$database;
In the next step, if you perform a few flashback operations and you need to return the database to its state just before to be closed, you will need the above SCN.

8. Flashback the Database
For this example the point of time before the logical corruption is:

As SCN: 4125962274
As Timestamp: 24.05.2006 15:10

In SQL it can be done by FLASHBACK DATABASE command. In RMAN it can be done by FLASHBACK command.
Different types of usage:
8.1. Flashes back the database to a point in time just as in the specified SCN:
SQL> FLASHBACK DATABASE TO SCN 4125962274; /* returns database to its state as in the point in time marked with SCN 4125962274 */
8.2. Flashes back the database to a point in time just as in the specified TIMESTAMP (by using of custom TIMESTAMP value):
SQL> FLASHBACK DATABASE TO TIMESTAMP to_timestamp('24.05.2006 15:10','dd.mm.yyyy hh24:mi'); /* returns database to its state as in the point in time 24.05.2006 15:10 */
8.3. Flashes back the database to a point in time just as in the specified TIMESTAMP (by using of SYSTIMESTAMP)
SQL> FLASHBACK DATABASE TO TIMESTAMP (SYSTIMESTAMP -1/24); /* returns database to its state as in the point in time one hour ago */
8.4. Flashes back the database to a point in time just as in the specified TIMESTAMP (by using of SYSTIMESTAMP and INTERVAL):
SQL> FLASHBACK DATABASE TO TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE); /* returns database to its state as in the point in time one minute ago */
8.5. Flashes back the database to a point in time just as in the specified restore point
SQL> FLASHBACK DATABASE TO RESTORE POINT before_flashback_operation; /* returns database to its state as in the point in time marked with the restore point with name before_flashback_operation */
8.6. Flashbacks the database to the point in time just before the last RESETLOGS operation
SQL> FLASHBACK DATABASE TO BEFORE RESETLOGS;
8.7. Flashes back the database to a SCN just before the specified SCN
SQL> FLASHBACK DATABASE TO BEFORE SCN 4125962274;
8.8. Flashes back the database to a point in time one second before the specified TIMESTAMP
SQL> FLASHBACK DATABASE TO BEFORE TIMESTAMP to_timestamp('24.05.2006 15:10','dd.mm.yyyy hh24:mi');
SQL> FLASHBACK DATABASE TO BEFORE TIMESTAMP (SYSDATE -1/24); SQL> FLASHBACK DATABASE TO BEFORE TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' MINUTE);
8.9. Flashes back the database to a point in time just before the specified restore point
SQL> FLASHBACK DATABASE TO BEFORE RESTORE POINT before_flashback_operation;
8.10. Flashing back of the database via RMAN
RMAN> FLASHBACK DATABASE TO SCN 4125962274; RMAN> FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24); RMAN> FLASHBACK DATABASE TO TIMESTAMP to_timestamp('24.05.2006 15:10','dd.mm.yyyy hh24:mi');
RMAN> FLASHBACK DATABASE TO RESTORE POINT before_flashback_operation; RMAN> FLASHBACK DATABASE TO BEFORE RESETLOGS;

Let’s demonstrate the ‘Time Machine’ capabilities of this feature:


As it can be seen, the Flashback feature allows you to shift within the time as it doesn’t exist. In some articles it is called a “rewind” button but its abilities as a “forward” button make this feature to behave as a “Time Machine” device for your data.
I want to note here again that you can move forward in time only with applying of redo logs. You can backward in time by applying flashback logs along with smaller amount of redo data.
Be aware of that you will be able to travel through the time only if you are well prepared for it. You must have all flashback and archived redo logs for the whole time period. With the Flashback Database you can return database to its state as in every point in time in an extremely fast way. The speed of this operation depends only of the number of the applied flashback logs and this mainly depends of the number of the data block changes within the database for this time period. It doesn’t depend on the database size because no restore operation is performed. Usually this operation can complete even within several seconds or minutes and can be between 25 to 105 times faster than usual incomplete media recovery.
Flashback logs are still populated during every performed flashback operation. RVWR doesn’t care if the database is in either MOUNT or OPEN stage. Flashback logs are generated because the data blocks within datafiles are changing and the before-images of data must be recorded as well even that the database is not opened. You can see this behavior in the following example:


9. Open the Database with OPEN RESETLOGS clause:
SQL> ALTER DATABASE OPEN RESETLOGS;


Thanks.