Flashback Database is similar to conventional point-in-time recovery in its effects. It enables you to return a database to its state at a time in the recent past. Flashback Database is much faster than point-in-time recovery because it does not require restoring data files from backup and requires applying fewer changes from the archived redo logs.

You can use Flashback Database to reverse most unwanted changes to a database if the data files are intact. You can return a database to its state in a previous incarnation, and undo the effects of an ALTER DATABASE OPEN RESETLOGS statement. "Rewinding a Database with Flashback Database" explains how to use the FLASHBACK DATABASE command to reverse database changes.

Flashback Database uses its own logging mechanism, creating flashback logs and storing them in the fast recovery area. You can only use Flashback Database if flashback logs are available. To take advantage of this feature, you must set up your database in advance to create flashback logs.

To enable Flashback Database, you configure a fast recovery area and set a flashback retention target. This retention target specifies how far back you can rewind a database with Flashback Database.

From that time onwards, at regular intervals, the database copies images of each altered block in every data file into the flashback logs. These block images can later be reused to reconstruct the data file contents for any moment at which logs were captured.

When you use Flashback Database to rewind a database to a past target time, the command determines which blocks changed after the target time and restores them from the flashback logs. The database restores the version of each block that is immediately before the target time. The database then uses redo logs to reapply changes that were made after these blocks were written to the flashback logs.

Redo logs on disk or tape must be available for the entire time period spanned by the flashback logs. For example, if the flashback retention target is 1 week, then you must ensure that online and archived redo logs that contain all changes for the past week are accessible. In practice, redo logs are typically needed much longer than the flashback retention target to support point-in-time recovery.

The range of SCNs for which there is currently enough flashback log data to support the FLASHBACK DATABASE command is called the flashback database window. The flashback database window cannot extend further back than the earliest SCN in the available flashback logs.

You cannot back up flashback logs to locations outside the fast recovery area. To increase the likelihood that enough flashback logs are retained to meet the flashback database window, you can increase the space in your fast recovery area (see "Table 5-3").

If the fast recovery area is not large enough to hold the flashback logs and files such as archived redo logs and other backups needed for the retention policy, then the database may delete flashback logs from the earliest SCNs forward to make room for other files. Consequently, the flashback database window can be shorter than the flashback retention target, depending on the size of the fast recovery area, other backups that must be retained, and how much flashback logging data is needed. The flashback retention target is a target, not a guarantee that Flashback Database is available.

If you cannot use FLASHBACK DATABASE because the flashback database window is not long enough, then usually you can use database point-in-time recovery (DBPITR) to achieve a similar result. Guaranteed restore points are the only way to ensure that you can use Flashback Database to return to a specific point in time or guarantee the size of the flashback window.

Because Flashback Database works by undoing changes to the data files that exist at the moment when you run the command, it has certain limitations.

Following are the limitations of Flashback Database:

• Flashback Database can only undo changes to a data file made by Oracle Database. It cannot be used to repair media failures, or to recover from accidental deletion of data files.

• You cannot use Flashback Database to undo a shrink data file operation. However, you can take the shrunken file offline, flash back the rest of the database, and then later restore and recover the shrunken data file.

• You cannot use Flashback Database alone to retrieve a dropped data file. If you flash back a database to a time when a dropped data file existed in the database, only the data file entry is added to the control file. You can only recover the dropped data file by using RMAN to fully restore and recover the data file.

• If the database control file is restored from backup or re-created, all accumulated flashback log information is discarded. You cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file.

• When using Flashback Database with a target time at which a NOLOGGING operation was in progress, block corruption is likely in the database objects and data files affected by the NOLOGGING operation. For example, if you perform a direct-path INSERT operation in NOLOGGING mode, and that operation runs from 9:00 to 9:15 on April 3, 2005, and you later use Flashback Database to return to the target time 09:07 on that date, the objects and data files updated by the direct-path INSERT may be left with block corruption after the Flashback Database operation 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 data files 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, consider performing the operation in LOGGING mode.

  See Also:

  Oracle Database SQL Language Reference for more information about operations that support NOLOGGING mode.

Creating a normal restore point assigns a restore point name to an SCN or specific point in time.

Thus, a restore point functions as a bookmark or alias for this SCN. Before performing any operation that you may have to reverse, you can create a normal restore point. The control file stores the name of the restore point and the SCN.

If you use flashback features or point-in-time recovery, then you can use the name of the restore point instead of a time or SCN. The following commands support this use of restore points:

• The RECOVER DATABASE and FLASHBACK DATABASE commands in RMAN

• The FLASHBACK TABLE statement in SQL

Creating a normal restore point eliminates manually recording an SCN in advance or determine the correct SCN after the fact by using features such as Flashback Query.

Normal restore points are lightweight. The control file can maintain a record of thousands of normal restore points with no significant effect on database performance. Normal restore points eventually age out of the control file if not manually deleted, so they require no ongoing maintenance.

Like a normal restore point, a guaranteed restore point serves as an alias for an SCN in recovery operations. A principal difference is that guaranteed restore points never age out of the control file and must be explicitly dropped.

In general, you can use a guaranteed restore point as an alias for an SCN with any command that works with a normal restore point. Except as noted, the information about where and how to use normal restore points applies to guaranteed restore points as well.

A guaranteed restore point ensures that you can use Flashback Database to rewind a database to its state at the restore point SCN, even if the generation of flashback logs is disabled. If flashback logging is enabled, then a guaranteed restore point enforces the retention of flashback logs required for Flashback Database to any SCN after the earliest guaranteed restore point. Thus, if flashback logging is enabled, you can rewind the database to any SCN in the continuum rather than to a single SCN only.

If the recovery area has enough disk space to store the needed logs, then you can use a guaranteed restore point to rewind a whole database to a known good state days or weeks ago. As with Flashback Database, even the effects of NOLOGGING operations like direct load inserts can be reversed with guaranteed restore points.

Certain rules govern the usage of space in the fast recovery area.

When you create a guaranteed restore point, with or without enabling full flashback database logging, you must monitor the space available in your fast recovery area. "Managing Space for Flashback Logs in the Fast Recovery Area" explains how to monitor fast recovery area disk space usage.

The following rules govern creating, retaining, overwriting and deleting of flashback logs in the fast recovery area:

• If the fast recovery area has enough space, then a flashback log is created whenever necessary to satisfy the flashback retention target.

• If a flashback log is old enough that it is no longer needed to satisfy the flashback retention target, then the flashback log may be reused or deleted.

• If the database must create a flashback log and the fast recovery area is full or there is no disk space, then the oldest flashback log is reused instead.

  Note:

  Reusing the oldest flashback log shortens the flashback database window. If enough flashback logs are reused due to a lack of disk space, then the flashback retention target may not be satisfied.

• If the fast recovery area is full, then an archived redo log that is reclaimable according to the fast recovery area rules may be automatically deleted by the fast recovery area to make space for other files. In this case, any flashback logs that require the use of that redo log file for the use of FLASHBACK DATABASE are also deleted.

  Note:

  According to fast recovery area rules, a file is reclaimable when one of the following criteria is true:

  • The file is reported as obsolete and not needed by the flashback database. For example, the file is outside the DB_FLASHBACK_RETENTION_TARGET parameters.

  • The file is backed up to tape.

• Files in the fast recovery area are not eligible for deletion or reuse if they are required to satisfy a guaranteed restore point. However, archived redo logs required to satisfy a guaranteed restore point may be deleted after they are backed up to disk or tape. When you use the RMAN FLASHBACK DATABASE command, if the archived redo logs required to satisfy a specified guaranteed restore point are not available in the fast recovery area, they are restored from the backups.

  Retention of flashback logs and other files required to satisfy the guaranteed restore point, in addition to files required to satisfy the backup retention policy, can cause the fast recovery area to fill completely. Consult "Responding to a Full Fast Recovery Area" if your fast recovery area becomes full.

Assume that you create a guaranteed restore point when logging for Flashback Database is disabled. In this case, the first time a data file block is modified after the time of the guaranteed restore point, the database stores an image of the block before the modification in the flashback logs. Thus, the flashback logs preserve the contents of every changed data block when the guaranteed restore point was created. Later modifications to the same block do not cause the contents to be logged again unless another guaranteed restore point was created after the block was last modified or a subsequent flashback database operation has restored the original contents of the block. When you use Flashback Database to restore a database multiple times to the same restore point, it is common practise to drop and recreate the guaranteed restore point each time. This deletes the old flashback logs and also ensures that the space quota for the fast recovery area is not exceeded.

This method of logging has the following important consequences:

• FLASHBACK DATABASE can re-create the data file contents at the time of a guaranteed restore point by using the block images.

• For workloads that repeatedly modify the same data, disk space usage can be less than normal flashback logging. Less space is needed because each changed block is only logged once. Applications with low volume inserts may benefit from this disk space saving. This advantage is less likely for applications with high volume inserts or large batch inserts. The performance overhead of logging for a guaranteed restore point without flashback database logging enabled can also be lower.

Assume that your primary goal is the ability to return your database to the time at which the guaranteed restore point was created. In this case, it is usually more efficient to turn off flashback logging and use only guaranteed restore points. For example, suppose that you are performing an application upgrade on a database host over a weekend. You could create a guaranteed restore point at the start of the upgrade. If the upgrade fails, then reverse the changes with the FLASHBACK DATABASE command.

If you enable Flashback Database and define one or more guaranteed restore points, then the database performs normal flashback logging.

In this case, the recovery area retains the flashback logs required to flash back to any arbitrary time between the present and the earliest currently defined guaranteed restore point. Flashback logs are not deleted in response to space pressure if they are required to satisfy the guarantee.

Flashback logging causes some performance overhead. Depending upon the pattern of activity on your database, it can also cause significant space pressure in the fast recovery area. Thus, you should monitor space used in the fast recovery area.

To create normal or guaranteed restore points, use the CREATE RESTORE POINT SQL statement, providing a name for the restore point and specifying whether it is to be a guaranteed restore point or a normal one (the default).

1. Connect SQL*Plus to a target database.

   See Also:

   Oracle Database Administrator’s Guide for using SQL*Plus to connect to a database

2. Ensure that the database is open or mounted. If the database is mounted, then it must have been shut down cleanly (unless it is a physical standby database).
3. Run the CREATE RESTORE POINT statement.

   The following example shows how to create a normal restore point in SQL*Plus:

   SQL> CREATE RESTORE POINT before_upgrade;
   

   This example shows how to create a guaranteed restore point:

   SQL> CREATE RESTORE POINT before_upgrade GUARANTEE FLASHBACK DATABASE;

When you are satisfied that you do not need an existing restore point, or when you want to create a restore point with the name of an existing restore point, you can drop the restore point, using the DROP RESTORE POINT SQL*Plus statement.

SQL> DROP RESTORE POINT before_app_upgrade;
Restore point dropped.

Use the ALTER DATABASE command to enable Flashback Database

1. Configure the recovery area as described in "Enabling the Fast Recovery Area".
2. Ensure the database instance is open or mounted. If the instance is mounted, then the database must be shut down cleanly unless it is a physical standby database. Other Oracle Real Application Clusters (Oracle RAC) instances can be in any mode.
3. Optionally, set the DB_FLASHBACK_RETENTION_TARGET to the length of the desired flashback window in minutes:

   ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320 SCOPE=BOTH; # 3 days
   

   By default DB_FLASHBACK_RETENTION_TARGET is set to 1 day (1440 minutes).

   Note:

   This setting must be persistent across database startup and shutdown.

4. Enable the Flashback Database feature for the whole database:

   ALTER DATABASE FLASHBACK ON;
   

5. Optionally, disable flashback logging for specific tablespaces.

   By default, flashback logs are generated for all permanent tablespaces. You can reduce overhead by disabling flashback logging for specific tablespaces as in the following example:

   ALTER TABLESPACE tbs_3 FLASHBACK OFF;
   

   You can re-enable flashback logging for a tablespace later with this command:

   ALTER TABLESPACE tbs_3 FLASHBACK ON;

   If you disable Flashback Database for a tablespace, then you must take its data files offline before running FLASHBACK DATABASE.

Use the ALTER DATABASE command to disable Flashback Database.

ALTER DATABASE FLASHBACK OFF;

Maintaining flashback logs imposes comparatively limited overhead on an database instance. Changed blocks are written from memory to the flashback logs at relatively infrequent, regular intervals, to limit processing and I/O overhead.

Several data analysis methods are available to monitor the Flashback Database workload on your system.

• AWR reports

  The Automatic Workload Repository (AWR) automates database statistics gathering by collecting, processing, and maintaining performance statistics for database problem detection and self-tuning. You can compare AWR reports from before and after the Flashback Database was turned on to monitor performance effects.

• AWR snapshots

  You can review AWR snapshots to pinpoint system usage caused by flashback logging. For example, if flashback buf free by RVWR is the top wait event, then you know that Oracle Database cannot write flashback logs very quickly. Therefore, you might want to tune the file system and storage used by the fast recovery area, possibly using a technique described in "Configuring the Environment for Optimal Flashback Database Performance"

• V$FLASHBACK_DATABASE_STAT view

  The V$FLASHBACK_DATABASE_STAT view shows the bytes of flashback data logged by the database. Each row in the view shows the statistics accumulated (typically over the course of an hour). The FLASHBACK_DATA and REDO_DATA columns describe bytes of flashback data and redo data written respectively during the time interval, while the DB_DATA column describes bytes of data blocks read and written. The columns FLASHBACK_DATA and REDO_DATA correspond to sequential writes, whereas DB_DATA column corresponds to random reads and writes.

• V$SYSSTAT view

  Because of the difference between sequential I/O and random I/O, a better indication of I/O overhead is the number of I/O operations issued for flashback logs. The V$SYSSTAT statistics shown in Table 7-1 can tell you the number of I/O operations that your instance has issued for various purposes.

When flashback is enabled or when there are guaranteed restore points, the background process RVWR writes flashback data to flashback database logs in the fast recovery area.

If RVWR encounters an I/O error, then the following behavior is expected:

• If there are any guaranteed restore points defined, then the instance fails when RVWR encounters I/O errors.

• If no guaranteed restore points are defined, then the instance remains unaffected when RVWR encounters I/O errors. Note the following cases:

  • On a primary database, Oracle Database automatically disables Flashback Database while the database is open. All existing transactions and queries proceed unaffected. This behavior is expected for both single-instance and Oracle RAC databases.

  • On a physical or logical standby, RVWR appears to have stopped responding, retrying the I/O periodically. This may eventually cause the logical standby or the managed recovery of the physical standby to suspend. (Oracle Database does not cause the standby instance to fail because it does not want to cause the primary database to fail in maximum protection mode.) To resolve the issue, you can issue either a SHUTDOWN ABORT or an ALTER DATABASE FLASHBACK OFF command.