FLASHBACK

Syntax

flashback::=

Description of flashback.gif follows
Description of the illustration flashback.gif

Purpose

Performs a Flashback Database operation, returning the database to (or to just before) target time, as specified by time, SCN or log sequence number.

The result of using flashback database is generally similar to a database point-in-time recovery performed with RECOVER, except for the following principal differences:

  • You do not need to restore a backup.

  • RMAN uses flashback logs to undo changes to a point before the target time or SCN, and then uses archived redo logs to recover the database forward to make it consistent. RMAN automatically restores from backup any archived logs that are needed. (Flashback logs are stored as Oracle-managed files in the flash recovery area, and cannot be created if no flash recovery area is configured.)

  • Some NOLOGGING changes may be reflected in the flashback which would not be reflected in the results of a point-in-time recovery, because flashback database uses backed-up block images as the basis for undoing changes to your current datafiles instead of relying on full and incremental backups and redo logs.

Because FLASHBACK DATABASE does not require you to restore a backup, it is usually much faster than incomplete recovery.

Flashback Database also has a number of applications in the context of standby databases, including providing functionality comparable to storage snapshots for reporting and testing databases, more flexibility in testing disaster recovery, and recovery from unwanted changes at a standby such as application of corrupted or erroneous updates at the standby database. These uses are discussed in more detail in Oracle Data Guard Concepts and Administration.

Restrictions and Usage Notes

  • You can run this command from the RMAN prompt or from within a RUN command.

  • The target database must be in ARCHIVELOG mode.

  • The target database must be a Release 10g database.

  • You must be connected to the target database.

  • You must have enabled the flashback logging functionality before the target time for flashback, using the SQL statement ALTER DATABASE ... FLASHBACK ON. You can check V$DATABASE.FLASHBACK_ON to determine whether flashback logging has been enabled.

  • The target database must be mounted with a current control file, that is, the control file cannot be a backup or have been re-created.

  • The flash recovery area must be enabled to enable logging for Flashback Database (that is, DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE must be set). Flashback logs can only be stored in the flash recovery area.

  • The FLASHBACK 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.

  • RMAN performs restore failover when unable to restore an archvied redo log file for use in a Flashback Database operation. See Oracle Database Backup and Recovery Advanced User's Guide for details on restore failover.

  • 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 upon the actual retention of flashback logs permitted by available disk space

    If the FLASHBACK command does not have enough flashback data to return the database to the requested SCN or time, then RMAN issues an error and does not modify the database.

  • Flashback logs may be deleted if disk space in the flash recovery area is low. If there is not enough space in the flash recovery area for the retention of other files, then the database may delete an archived redo log file that is eligible for deletion because it is backed up to another location, or because it is obsolete. In such a case, flashback logs that cannot be used without that redo log file are also deleted. To ensure that this does not occur, make sure the flash recovery area is large enough to accomodate your flashback logs and other backups.

  • FLASHBACK DATABASE can be used to return your database to a point prior to the most recent OPEN RESETLOGS operation, if your database has been upgraded to 10g Relase 2.

  • If the database has not retained enough flashback logs to allow a Flashback Database operation on all of your datafiles, then you can identify the datafiles for which there are not enough flashback logs, and take these datafiles offline. You can then run FLASHBACK DATABASE on the database and only the datafiles that are online will be affected. Then, you can perform point-in-time recovery on those files where there was not sufficient flashback log data.

  • RMAN issues an error if you attempt to perform Flashback Database on online tablespaces on which flashback was disabled using the SQL statement ALTER TABLESPACE ... FLASHBACK OFF.

  • RMAN never flashes back data for temporary tablespaces.

  • If a datafile has changed status between the current SCN and the SCN to which you are flashing back, then the FLASHBACK command behaves differently depending on the nature of the status change. Refer to Table 2-3 for details.

  • If the FLASHBACK DATABASE command fails or is interrupted, then the database is left mounted. At that point you can issue another FLASHBACK DATABASE command ouse the RECOVER to perform complete recovery, returning the database to its original state.

  • When performing a FLASHBACK DATABASE operation, your database may not be left at the SCN most immediately before the target time you specify. There are events other than transactions which cause the SCN for your database to be updated. If you use the FLASHBACK DATABASE TO form of the command and there is a transaction associated with your specified SCN, the database after the flashback operation will include all changes up to and including that transaction. Otherwise, all changes up to but not including that transaction will be included in your datafiles, whether you use the FLASHBACK DATBASE TO or FLASHBACK DATABASE TO BEFORE form of the command. Changes after the specified target time or SCN are never applied as a result of a FLASHBACK DATBASEoperation.

  • After the FLASHBACK DATABASE operation is complete, you may wish to open the database read-only and run some queries to see if you have achieved the desired result. If you are not satisfied with your flashback operation, you can perform RECOVER DATABASE to re-apply all changes and bring the database back to its state when you started the flashback operation. You can then attempt flashb ack again. If you are satisfied, you can either perform an OPEN RESETLOGS to abandon all changes after the target time for the flashback, or you can export lost data, use RECOVER DATABASE to return your database to its state before the flashback database operation, and then re-import the lost data.

  • If you perform a FLASHBACK DATABASE operation, and some datafiles are not flashed back because they are offline, then you may encounter an error when you attempt to open the database with an OPEN RESETLOGS. In such a case you must do one of the following:

    • Use FLASHBACK DATABASE to bring the affected datafiles to the same time or SCN as the other datafiles.

    • Run RESTORE and then RECOVER to bring the affected datafiles to the same time or SCN as the rest of the database.

    • Take the affected datafiles offline and then drop them.

Table 2-3 How FLASHBACK Responds to Datafile Operations

If this datafile operation occurred during the flashback window ... Then the FLASHBACK command ...

Added

Removes the datafile record from the control file.

Dropped

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.

Renamed

Ignores the renaming. The datafile retains its current name.

Resized

May fail. You can take the datafile offline and then rerun the FLASHBACK command. The datafile will not be flashed back. You can then restore and recover the datafile to the same time or SCN.

Taken offline

Ignores the operation. The datafile retains its current online status.

Brought online

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.

Keywords and Parameters

Syntax Element Description
DEVICE TYPE deviceSpecifier Allocates automatic channels for the specified device type only. For example, if you configure automatic disk and tape channels, and issue FLASHBACK...DEVICE TYPE DISK, then RMAN allocates only disk channels. RMAN may need to restore redo logs from backup during the flashback database process. Changes between the last flashback log and the target time must be re-created based on the archived redo log. If no automatic channels are allocated for tape and a needed redo log is on tape, the FLASHBACK operation will fail.

See Also: "deviceSpecifier"
DATABASE Returns the database to the specified point. Query OLDEST_FLASHBACK_SCN and OLDEST_FLASHBACK_TIME in V$FLASHBACK_DATABASE_LOG to display the approximate lowest SCN and time to which you can flash back. View the current database SCN in V$DATABASE.CURRENT_SCN.
TO RESTORE POINT = ' restore_point_name Returns the database to the SCN associated with the specified restore point. This can be an ordinary restore point or a guaranteed restore point.
TO SCN = integer 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. The DBA can override the default by using the RMAN RESET DATABASE INCARNATION command to set the recovery target incarnation.
TO BEFORE SCN = integer 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. The DBA can override the default by using the RMAN RESET DATABASE INCARNATIONcommand to set the recovery target incarnation.
TO SEQUENCE = integer THREAD = integer 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.
TO BEFORE SEQUENCE = integer [THREAD = integer] 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.
TO TIME = 'date_string' Returns the database to its state at the specified time. You can use any SQL DATE expressions to convert the time to the current format, for example, FLASHBACK DATABASE UNTIL TIME 'SYSDATE-7'.
TO BEFORE TIME = 'date_string' Similar to the TO TIME clause, but returns the database to its state including all changes up to but not including changes at the specified time.
TO BEFORE RESETLOGS Returns the database to its state including all changes up to the SCN of the lastOPEN RESETLOGS.

Examples

FLASHBACK DATABASE to a Specific SCN: Example The following command uses Flashback Database to return the database to the specified SCN:

RMAN> FLASHBACK DATABASE TO SCN 46963;

FLASHBACK DATABASE to One Hour Ago: Example The following command uses Flashback Database to return the database to 1/24 of a day (one hour) in the past:

RMAN> FLASHBACK DATABASE TO TIMESTAMP (SYSDATE-1/24);

FLASHBACK DATABASE to a Specific Time: Example The following command uses SQL date conversion functions to specify the target time:

RMAN> FLASHBACK DATABASE TO TIMESTAMP
   TO_TIMESTAMP('2002-03-11 16:00:00', 'YYYY-MM-DD HH24:MI:SS');

FLASHBACK DATABASE to a Restore Point: Example The following command uses a restore point to identify the target time for a Flashback Database operation:

RMAN> FLASHBACK DATABASE TO RESTORE POINT "before_batch";

FLASHBACK DATABASE to Before the Last Resetlogs: Example The following command returns the database to immediately before the most recent OPEN RESETLOGS:

RMAN> FLASHBACK DATABASE TO BEFORE RESETLOGS;