In practice, redo logs are typically needed much longer than the flashback retention target to support point-in-time recovery. You can use either command to quickly recover the database from logical data corruption or user errors.
The number of write operations issued for writing data blocks, The number of read operations issued for reading data blocks, The number of write operations issued for writing to the redo log, The number of write operations issued for writing to flashback logs, Total size in bytes of flashback database data written from this instance, Oracle Database Reference for more details on columns in the V$SYSSTAT view, Oracle Database Performance Tuning Guide to learn about AWR, Oracle Database 2 Day + Performance Tuning Guide for more information about AWR reports. 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. Thus, the flashback logs preserve the contents of every changed data block at the time that the guaranteed restore point was created.
This chapter discusses configuring, monitoring, and maintaining these features as part of an overall data protection strategy.
As with Flashback Database, even the effects of NOLOGGING operations like direct load inserts can be reversed with guaranteed restore points. The variations of the command are as follows: RMAN indicates the SCN and time of the restore point, the type of restore point, and the name of the restore point. The effects are similar to database point-in-time recovery (DBPITR). If the instance is mounted, then the database must be shut down cleanly unless it is a physical standby database. 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.
All existing transactions and queries proceed unaffected. 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. Operating system file caching is typically not effective for these files, and may actually add CPU overhead for reading from and writing to these files. Flashback Database is similar to conventional point-in-time recovery in its effects. 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. 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. 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 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. Thus, a restore point functions as a bookmark or alias for this SCN. Your recoverability goals partially determine whether to enable logging for flashback database, or use guaranteed restore points, or both. This advantage is less likely for applications with high volume inserts or large batch inserts. 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. The columns FLASHBACK_DATA and REDO_DATA correspond to sequential writes, whereas DB_DATA column corresponds to random reads and writes. 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 "Initialization Parameters for the Fast Recovery Area"). Creating a normal restore point assigns a restore point name to an SCN or specific point in time.
Thus, you should monitor space used in the fast recovery area. 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.
If the database control file is restored from backup or re-created, all accumulated flashback log information is discarded. Consult "Responding to a Full Fast Recovery Area" if your fast recovery area becomes full. For Oracle Real Application Clusters (Oracle RAC) databases, the fast recovery area must be in a clustered file system or in ASM. Follow these are the steps to enable Flashback Database. To take advantage of this feature, you must set up your database in advance to create flashback logs. You can return a database to its state in a previous incarnation, and undo the effects of an ALTER DATABASE OPEN RESETLOGS statement.
You must have a fast recovery area enabled, because flashback logs can only be stored in the fast recovery area. Oracle Database Reference for information about V$RESTORE_POINT, "Rewinding a Database with Flashback Database".
If you enable Flashback Database and define one or more guaranteed restore points, then the database performs normal flashback logging. Configure enough disk spindles for the file system that holds the fast recovery area.
In practice, guaranteed restore points provide a useful alternative to storage snapshots.
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. A principal difference is that guaranteed restore points never age out of the control file and must be explicitly dropped.
Thus, 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. You could create a guaranteed restore point at the start of the upgrade. (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.)
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. The flashback database window cannot extend further back than the earliest SCN in the available flashback logs. Redo logs on disk or tape must be available for the entire time period spanned by the flashback logs.
Like a normal restore point, a guaranteed restore point serves as an alias for an SCN in recovery operations. If the storage system used to hold the fast recovery area does not have nonvolatile RAM, then try to configure the file system on striped storage volumes.
For example, the file is outside the DB_FLASHBACK_RETENTION_TARGET parameters. Before performing any operation that you may have to reverse, you can create a normal restore point. 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.
Depending upon the pattern of activity on your database, it can also cause significant space pressure in the fast recovery area. This behavior is expected for both single-instance and Oracle RAC databases.
"Managing Space for Flashback Logs in the Fast Recovery Area" explains how to monitor fast recovery area disk space usage.
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.
To enable Flashback Database, you configure a fast recovery area and set a flashback retention target. You can only recover the dropped data file by using RMAN to fully restore and recover the data file. Flashback Database and restore points are not only effective in traditional database recovery situations but can also be useful during database upgrades, application deployments and testing scenarios when test databases must be quickly created and re-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.
There are several data analysis methods for monitoring the Flashback Database workload on your system. This section describes the various commands and monitoring capabilities you use with normal and guaranteed restore points. The file is reported as obsolete and not needed by the flashback database. The performance overhead of logging for a guaranteed restore point without flashback database logging enabled can also be lower. 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". The following examples return the database to a specified SCN or restore point: Flashback Database is accessible through the RMAN command and SQL statement FLASHBACK DATABASE. Note the following cases: On a primary database, Oracle Database automatically disables Flashback Database while the database is open. Thus, if flashback logging is enabled, you can rewind the database to any SCN in the continuum rather than to a single SCN only. By default, flashback logs are generated for all permanent tablespaces.
For example: The same statement is used to drop both normal and guaranteed restore points.
Guaranteed restore points never age out of the control file.
It cannot be used to repair media failures, or to recover from accidental deletion of data files. No file in the fast recovery area is eligible for deletion if it is required to satisfy a guaranteed restore point. It enables you to return a database to its state at a time in the recent past. To ensure successful operation of Flashback Database and guaranteed restore points, you must first set several key database options.
Each row in the view shows the statistics accumulated (typically over the course of an hour). 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, 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. You can use the LIST command to list either a specific restore point or all restore points known to the RMAN repository.
The following example shows sample output: To see a list of all currently defined restore points (normal and guaranteed), use the V$RESTORE_POINT control file view with the following query: Name of each restore point (guaranteed and normal), Time and database incarnation number when the restore points were created, Type of restore point (normal or guaranteed), Amount of space in the fast recovery area being used to support information needed for Flashback Database operations for that restore point. 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.
Oracle Flashback Database and restore points are related data protection features that enable you to rewind data back in time to correct any problems caused by logical data corruption or user errors within a designated time window.
Except as noted, the information about where and how to use normal restore points applies to guaranteed restore points as well.
For workloads that repeatedly modify the same data, disk space usage can be less than normal flashback logging. Normal restore points eventually age out of the control file if not manually deleted, so they require no ongoing maintenance. 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. Enable the Flashback Database feature for the whole database: Optionally, disable flashback logging for specific tablespaces. Other Oracle RAC instances can be in any mode. Changed blocks are written from memory to the flashback logs at relatively infrequent, regular intervals, to limit processing and I/O overhead. For guaranteed restore points, STORAGE_SIZE indicates the amount of disk space in the fast recovery area used to retain logs required to guarantee FLASHBACK DATABASE to that restore point. Less space is needed because each changed block is only logged once.
Thus, it is recommended to use a file system that avoids operating system file caching, such as ASM. 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. For large databases, set the initialization parameter LOG_BUFFER to at least 8 MB. Storage snapshots are often used to protect a database before risky operations such as large-scale database updates or application patches or upgrades.
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 FLASHBACK DATABASE command can use these images to return the data files to their previous state. The overhead of logging for Flashback Database depends on the mixture of reads and writes in the database workload. Ensure the database instance is open or mounted. In particular, a guaranteed restore point created at an system change number (SCN) ensures that you can use Flashback Database to rewind the database to this SCN. You can then perform the risky operation with the certainty that the required flashback logs are retained. If the upgrade fails, then reverse the changes with the FLASHBACK DATABASE command. 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 fast recovery area.
These features provide a more efficient alternative to point-in-time recovery and does not require a backup of the database to be restored first. 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). These differences affect space usage for logs and database performance. The flashback retention target is a target, not a guarantee that Flashback Database is available. Use a relatively small stripe size such as 128 KB. Optionally, set the DB_FLASHBACK_RETENTION_TARGET to the length of the desired flashback window in minutes: By default DB_FLASHBACK_RETENTION_TARGET is set to 1 day (1440 minutes). 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.
For example, you can compare AWR reports from before and after the Flashback Database was turned on. Files that the database creates in the fast recovery area, including flashback logs, are typically large. Ensure that the database is open or mounted.
You cannot use Flashback Database alone to retrieve a dropped data file.
The Automatic Workload Repository (AWR) automates database statistics gathering by collecting, processing, and maintaining performance statistics for database problem detection and self-tuning. To use guaranteed restore points, the database must satisfy the following additional prerequisite: the COMPATIBLE initialization parameter must be set to 10.2.0 or greater. You cannot use Flashback Database to undo a shrink data file operation. You cannot use FLASHBACK DATABASE to return to a point in time before the restore or re-creation of a control file. The control file stores the name of the restore point and the SCN. This chapter contains the following topics: Understanding Flashback Database, Restore Points and Guaranteed Restore Points, Logging for Flashback Database and Guaranteed Restore Points, Prerequisites for Flashback Database and Guaranteed Restore Points, Using Normal and Guaranteed Restore Points. From that time onwards, at regular intervals, the database copies images of each altered block in every data file into the flashback logs. Flashback logging causes some performance overhead. Normal restore points that do not meet either of these conditions may age out of the control file. The file is backed up to tape.
Scripting on this page enhances content navigation, but does not change the content in any way. Applications with low volume inserts may benefit from this disk space saving. 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. It also lists guidelines to ensure optimal performance of Flashback Database.
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. You can use Flashback Database to reverse most unwanted changes to a database if the data files are intact. You cannot back up flashback logs to locations outside the fast recovery area.
The control file can maintain a record of thousands of normal restore points with no significant effect on database performance. Flashback Database also provides an efficient alternative to rebuilding a failed primary database after a Data Guard failover. For large production databases, multiple disk spindles may be needed to support the required disk throughput for the database to write the flashback logs effectively. 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. 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. Configure the following database settings before enabling Flashback Database: Your database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation.