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.
Flashback Database also has a number of uses in a Data Guard environment.
Oracle Database SQL Language Reference for a complete list of command prerequisites and usage notes for
Oracle Data Guard Concepts and Administration to learn about uses of Flashback Database in a Data Guard environment
You can run this command from the RMAN prompt or from within a
RMAN must be connected as
TARGET to a database, which must be Oracle Database 10g or later. The target database must be mounted with a current control file, that is, the control file cannot be a backup or re-created. The database must run in
You cannot use
FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file. If the database control file is restored from backup or re-created, then all existing flashback log information is discarded.
The flash recovery area must be configured to enable flashback logging. Flashback logs are stored as Oracle-managed files in the flash recovery area and cannot be created if no flash recovery area is configured. You must have enabled the flashback logging before the target time for flashback by means of the SQL statement
ALTER DATABASE ... FLASHBACK ON. Query
V$DATABASE.FLASHBACK_ON to see whether flashback logging has been enabled.
The database must contain no online tablespaces for which flashback functionality was disabled with the SQL statement
ALTER TABLESPACE ... FLASHBACK OFF.
A Flashback Database operation applies to the whole database. You cannot flash back individual tablespaces. A Flashback Database operation is similar to a database point-in-time recovery (DBPITR) performed with
RECOVER, but RMAN uses flashback logs to undo changes to a point before the target time or SCN. RMAN automatically restores from backup any archived redo logs that are needed and recovers the database to make it consistent. Note that RMAN never flashes back data for temporary tablespaces.
The earliest SCN that can be used for a Flashback Database operation depends on the setting of the
DB_FLASHBACK_RETENTION_TARGET initialization parameter, and on the actual retention of flashback logs permitted by available disk. View the current database SCN in
FLASHBACK DATABASE with a target time at which a
NOLOGGING operation was in progress, block corruption is likely in the database objects and datafiles affected by the
NOLOGGING operation. For example, assume that you do a direct-path
INSERT operation in
NOLOGGING mode and that the operation runs from 9:00 to 9:15 on April 3. If you later use Flashback Database to return to 09:07 on this date, then the objects and datafiles updated by the direct-path
INSERT may be left with block corruption after Flashback Database completes.
If possible, avoid using
FLASHBACK DATABASE with a target time or SCN that coincides with a
NOLOGGING operation. Also, perform a full or incremental backup of the affected datafiles immediately after any
NOLOGGING operation to ensure recoverability to points in time after the operation. If you expect to use
FLASHBACK DATABASE to return to a point in time during an operation such as a direct-path
INSERT, then consider performing the operation in
See Also:The discussion of
logging_clausein Oracle Database SQL Language Reference for more information about operations that support
FLASHBACK DATABASE command does not start modifying the database until it has made sure that it has all the files and resources that it needs. A Flashback Database operation should never fail due to missing datafiles, redo log files, or flashback logs.
If a datafile has changed status between the current SCN and the target SCN of the flashback, then the
FLASHBACK DATABASE command behaves differently depending on the nature of the status change. Refer to Table 2-7 for details.
|If this datafile operation occurred during the flashback window ...||Then the FLASHBACK DATABASE command ...|
Removes the datafile record from the control file.
Adds the datafile to the control file, but marks it as offline and does not flash it back. You can then restore and recover the datafile to the same time or SCN.
Ignores the renaming. The datafile retains its current name.
May fail. You can take the datafile offline and then rerun the
Ignores the operation. The datafile retains its current online status.
Ignores the operation. The datafile retains its current offline status.
Made read-only or read/write
Changes the status of the datafile in the control file.
It is possible for the
ALTER TABLESPACE ... FLASHBACK OFF statement to have been executed for some tablespaces. If
FLASHBACK DATABASE has insufficient flashback data to rewind a tablespace to the target SCN, then RMAN issues an error and does not modify the database. Whenever
FLASHBACK DATABASE fails or is interrupted, the database is left mounted.
Drop the affected datafiles with the
ALTER DATABASE DATAFILE ... OFFLINE FOR DROP statement. You can then open the database with the
RESETLOGS option. After the database is open, execute
DROP TABLESPACE statements for the tablespaces that contain the dropped datafiles.
FLASHBACK DATABASE, the database may not be left at the SCN most immediately before the target time. Events other than transactions can cause the database SCN to be updated. If you use the
FLASHBACK DATABASE TO form of the command, and if a transaction is associated with the target SCN, then after the flashback the database will include all changes up to and including this transaction. Otherwise, all changes up to but not including this transaction will be included in the datafiles, whether you use the
FLASHBACK DATABASE TO or
FLASHBACK DATABASE TO BEFORE form of the command. Changes after the specified target SCN are never applied as a result of a
FLASHBACK DATABASE completes, you may want to open the database read-only and run queries to ensure that you achieved the intended result. If you are not satisfied, then you can use
DATABASE to recover the database to its state when you started the flashback. You can then rerun
If you are satisfied with the results of the flashback, then you can
OPEN RESETLOGS to abandon all changes after the target time. Alternatively, you can use Data Pump to export lost data, use
RECOVER DATABASE to return the database to its state before the flashback operation, and then use Data Pump to reimport the lost data.
||Allocates automatic channels for the specified device type only. For example, if you configure automatic disk and tape channels, and issue
||Returns the database to its state just before the specified SCN. Any changes at an SCN lower than that specified are applied, but if there is a change associated with the specified SCN it is not applied. By default, the provided SCN resolves to the current or ancestor incarnation. You can override the default by using the
||Specifies a redo log sequence number and thread as an upper limit. RMAN applies changes up to (but not including) the last change in the log with the specified sequence and thread number.|
||Returns the database to its state including all changes up to the SCN of the most recent
||Returns the database to its state including all changes up to but not including changes at the specified time.
||Returns the database to the point up to (and including) the specified SCN. By default, the provided SCN resolves to the current or ancestor incarnation. You can override the default by using the RMAN
||Specifies a redo log sequence number and thread as an upper limit. RMAN applies changes up to (and including) the last change in the log with the specified sequence and thread number.|
||Returns the database to the SCN associated with the specified restore point. This can be an ordinary restore point or a guaranteed restore point.|
||Returns the database to its state at the specified time. You can use any SQL
Assume that you inserted corrupted rows in many tables at 5:00 p.m. on February 14. You connect SQL*Plus to the database and query the earliest SCN in the flashback window:
SQL> SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME 2 FROM V$FLASHBACK_DATABASE_LOG; OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK -------------------- ---------------- 411010 2007/02/14 16:49
You then open a new terminal, start the RMAN client, and connect to the target database and recovery catalog. You enter RMAN commands as follows (sample output for the
FLASHBACK DATABASE is included):
RMAN> SHUTDOWN IMMEDIATE RMAN> STARTUP MOUNT RMAN> FLASHBACK DATABASE TO SCN 411010; Starting flashback at 15-FEB-07 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=104 device type=DISK starting media recovery media recovery complete, elapsed time: 00:00:07 Finished flashback at 15-FEB-07 RMAN> ALTER DATABASE OPEN RESETLOGS;
Assume that you are preparing to load a massive number of updates to the database. You create a guaranteed restore point before the performing the updates:
SQL> CREATE RESTORE POINT before_update GUARANTEE FLASHBACK DATABASE;
The bulk update fails, leaving the database with extensive corrupted data. You start an RMAN session, connect to the target database and recovery catalog, and list the guaranteed restore points:
RMAN> LIST RESTORE POINT ALL; SCN RSP Time Type Time Name ---------------- --------- ---------- --------- ---- 412742 GUARANTEED 15-FEB-07 BEFORE_UPDATE
You mount the database, flash back the database to the restore point (sample output included), and then open the database with the
RMAN> SHUTDOWN IMMEDIATE RMAN> STARTUP MOUNT RMAN> FLASHBACK DATABASE TO RESTORE POINT 'BEFORE_UPDATE'; Starting flashback at 15-FEB-07 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=104 device type=DISK starting media recovery archived log for thread 1 with sequence 34 is already on disk as file /disk2/oracle/oradata/prod/arch/archive1_34_614598462.dbf media recovery complete, elapsed time: 00:00:01 Finished flashback at 15-FEB-07 RMAN> ALTER DATABASE OPEN RESETLOGS;