5.1 Restore Points and Flashback Database: Concepts

Flashback Database and Restore Points are two related data protection features of Oracle that provide more efficient alternatives to point-in-time recovery for reversing unwanted database changes. This section provides a conceptual overview of Flashback Database first, and then of Restore Points.

Flashback Database enables you to wind your entire database backward in time, reversing the effects of unwanted database changes within a given time window. The effects are similar to database point-in-time recovery.

Restore points provide capabilities related to Flashback Database as well as other recovery operations. Guaranteed restore points, in particular, provide a complementary capability to Flashback Database, allowing you to select an SCN and enforce the requirement that Flashback Database be usable to that SCN, though not necessarily to SCNs between the guaranteed restore point and the present SCN.

Restore points and Flashback Database can be used independently of each other or together. In both cases, the RMAN FLASHBACK DATABASE command or the SQL*Plus FLASHBACK DATABASE statement is used to actually return the database to a specified SCN, as in the following examples:


It is easier, however, to explain the functionality of guaranteed restore points, the interactions of the two features and the tradeoffs in choosing to use either or both of them, after an overview of the more general Flashback Database functionality.

This section contains the following topics:

5.1.1 About Flashback Database

Oracle Flashback Database, accessible from both RMAN (by means of the FLASHBACK DATABASE command) and SQL*Plus (by means of the FLASHBACK DATABASE statement), lets you quickly recover the entire database from logical data corruptions or user errors.

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

Flashback Database can be used to reverse most unwanted changes to a database, as long as the datafiles are intact. This includes returning a database to its state in previous incarnations, that is, undoing the effects of an OPEN RESETLOGS operation.


Detailed information on the use of the FLASHBACK DATABASE command in reversing unwanted database changes can be found in "Reversing Database Changes with Flashback Database".

Flashback Database uses its own logging mechanism, creating flashback logs which are stored in the flash recovery area. You can only use Flashback Database if flashback logs are available. Therefore, you must set up your database in advance to create flashback logs if you want to take advantage of this feature.

To enable Flashback Database, you set up a flash recovery area, and set a flashback retention target, to specify how far back into the past you want to be able to restore your database with Flashback Database.

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

When a database is restored to its state at some past target time using Flashback Database, each block changed since that time is restored from the copy of the block in the flashback logs most immediately prior to the desired target time. The redo log is then used to re-apply changes since the time that block was copied to the flashback logs.


Redo logs must be available for the entire time period spanned by the flashback logs, whether on tape or on disk. (In practice, however, redo logs are generally needed much longer than the flashback retention target to support point-in-time recovery.) About the Flashback Database Window

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. If space in the flash recovery area is low, then flashback logs may be deleted to free space for files required by the configured retention policy. The result is that the flashback database window can be shorter than the flashback retention target, depending upon the size of the flash recovery area, other backups that must be retained and how much flashback logging data is needed.


The flashback retention target is a target, not an absolute guarantee that Flashback Database will be available.

If your flash recovery area is not large enough to hold both the flashback logs and files that must be retained to meet the retention policy, such as archived redo logs and other backups, then the flashback logs from the earliest SCNs may be deleted to make room in the flash recovery area for other files.

The flashback database window cannot extend further back than the earliest SCN in the available flashback logs. Flashback logs cannot be backed up outside the flash recovery area, so to increase the likelihood that enough logs are retained to meet the flashback database window, maximize the free space available in your flash recovery area. See "Sizing the Flash Recovery Area to Include Flashback Logs" for details.

There are also a number of operations you can perform on your database, such as dropping a tablespace or shrinking a datafile, which cannot be reversed with Flashback Database. After such an operation, the flashback database window begins at the time immediately following that operation.

If FLASHBACK DATABASE fails because the flashback database window is not long enough, database point-in-time recovery (DBPITR) can be used in most cases to achieve a similar result. See "Performing Database Point-In-Time Recovery" for details on DBPITR.

Using guaranteed restore points is 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. See "About Guaranteed Restore Points" for more information on guaranteed restore points and Flashback Database.

5.1.2 About Normal Restore Points

Creating a normal restore point assigns the restore point name to a specific point in time or SCN, as a kind of bookmark or alias you can use with commands that recognize a RESTORE POINT clause as a shorthand for specifying an SCN.

Before performing any operation that you may have to reverse, you can create a normal restore point. The name of the restore point and the SCN are recorded in the control file. Then, if you later need to use Flashback Database, Flashback Table, or point-in-time recovery, you can refer to the target time using the name of the restore point instead of a time expression or SCN. Defining a normal restore point before an operation to be reversed later eliminates the need to manually record an SCN in advance, or investigate the correct SCN after the fact using features such as Flashback Query.

Normal restore points are very lightweight. The control file can maintain a record of thousands of normal restore points with no significant impact upon database performance. Normal restore points eventually age out of the control file if not manually deleted, so they require no ongoing maintenance. Commands Supporting the Use of Restore Points

Restore points can be used to specify the target SCN in the following contexts:


  • The FLASHBACK TABLE statement in SQL*Plus


In general, a guaranteed restore point can be used 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.

5.1.3 About Guaranteed Restore Points

Like normal restore points, guaranteed restore points can be used as aliases for SCNs in recovery operations. However, they also provide specific functionality related to the use of the Flashback Database feature.

Creating a guaranteed restore point at a particular SCN enforces the requirement that you can perform a Flashback Database operation to return your database to its state at that SCN, even if flashback logging is not enabled for your database. If flashback logging is enabled, creating a guaranteed restore point enforces the retention of flashback logs required for Flashback Database back to any point in time after the creation of the earliest guaranteed restore point.

A guaranteed restore point can be used to revert a whole database to a known good state days or weeks ago, as long as there is enough disk space in flash recovery area to store the needed logs. As with Flashback Database, even the effects of NOLOGGING operations like direct load inserts can be reversed using guaranteed restore points.


Limitations that apply to Flashback Database also apply to guaranteed restore points. For example, shrinking a datafile or dropping a tablespace can prevent flashing back the affected datafiles to the guaranteed restore point. Using Guaranteed Restore Points Instead of Storage Snapshots

In practice, guaranteed restore points provide a useful alternative to storage snapshots, which are often used to protect a database before risky operations like large-scale database updates, or application patching or upgrading. Rather than creating a snapshot or duplicate database against which to test the operation, you can create a guaranteed restore point on a primary or standby database, and then perform the risky operation, with the certainty that the required flashback logs are retained.

5.1.4 About Logging for Flashback Database and Guaranteed Restore Points

The logging for Flashback Database and guaranteed restore points is based upon capturing images of datafile blocks before changes are applied, so that these images can be used to return the datafiles to their previous state when a FLASHBACK DATABASE command is executed.

The chief differences between normal flashback logging and logging for guaranteed restore points are related to when blocks are logged and whether the logs can be deleted in response to space pressure in the flash recovery area. These differences affect space usage for logs and database performance.

Whether to enable logging for flashback database, use guaranteed restore points, or both depends upon your recoverability goals, and the implications in performance and in space usage for these features, separately and when used together. Guaranteed Restore Points and Flash Recovery Area Space Usage

When you create a guaranteed restore point, with or without enabling full flashback database logging, you must monitor the space available in your flash recovery area. No file in the flash recovery area is eligible for deletion if it is required to satisfy the guarantee. Thus, retention of flashback logs and other files required to satisfy the guarantee, as well as files required to satisfy your backup retention policy, can cause the flash recovery area to fill completely.


If no files are eligible for deletion from the flash recovery area because of the requirements imposed by your retention policy and the guaranteed restore point, then the database behaves as if it has encountered a disk full condition. In many circumstances, this causes your database to halt. See "When Space is Not Available in the Flash Recovery Area" for more information on the effects of a full flash recovery area.

See also:

See "Managing Space For Flashback Logs in the Flash Recovery Area" for instructions on how to monitor flash recovery area disk space usage. Logging for Guaranteed Restore Points With Flashback Logging Disabled

If a guaranteed restore point is created when logging for Flashback Database is disabled, then, the first time a datafile block is modified after the time of the guaranteed restore point, an image of the block before the modification is stored in the flashback logs. The flashback logs thus preserve the contents of every changed data block at the time that the guaranteed restore point was created. However, subsequent modifications to the same block do not cause the block contents to be logged again, unless another guaranteed restore point has been created since the block was last modified.

This method of logging has the following important consequences:

  • The available block images can be used to re-create the datafile contents at the time of a guaranteed restore point using FLASHBACK DATABASE, but you cannot use FLASHBACK DATABASE to reach points in time between the guaranteed restore points and the current time, as is possible when logging for Flashback Database is enabled. If you need to return the database to an intermediate point in time, your only option is database point-in-time recovery.

  • Because each block that changes is only logged once, disk space usage for logging for guaranteed restore points when flashback logging is disabled is generally considerably less than normal flashback logging. You could maintain a guaranteed restore point for days or even weeks without concern over the ongoing growth of flashback logs that occurs if logging for Flashback Database is enabled. The performance overhead of logging for a guaranteed restore point without flashback database logging is generally lower as well.

If your primary need is to be able to return your database to the specific point in time at which the guaranteed restore point was created, then it is generally more efficient to turn off logging for Flashback Database and use only guaranteed restore points. For example, in a scenario in which you are performing an application upgrade on a production database server over a weekend, you could create a guaranteed restore point at the start of the upgrade process and, if the process failed at the end, reverse the changes using FLASHBACK DATABASE instead of restoring the database from backups. Logging for Flashback Database With Guaranteed Restore Points Defined

If Flashback Database is enabled and one or more guaranteed restore points is defined, then the database performs normal flashback logging, which causes some performance overhead and, depending upon the pattern of activity on your database, can cause signifcant space pressure in the flash recovery area. However, unlike normal logging for Flashback Database, the flash recovery area always retains the flashback logs required to allow FLASHBACK DATABASE to any time as far back as 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.

In this scenario you have the full flexibility of FLASHBACK DATABASE to any point in the flashback window, and the certainty of the guaranteed restore point, but you must monitor space used in the flash recovery area to support the guarantee, as described in "Monitoring Space Usage For Guaranteed Restore Points" and "Managing Space For Flashback Logs in the Flash Recovery Area".