Oracle Flashback Technology (Part V)

Part V: Flashback Database Restore Points

Introducing Flashback Database, Restore Points, and Guaranteed Restore Points

Oracle Flashback requires a combination of factors and options, such as control of undo retention, Oracle Flashback retention, and relevant undo tablespace utilization, block change tracking, and supplemental log management in many scenarios. Besides, Oracle Flashback Database and restore points are indeed related data protection features that enable DBAs to reverse data back in time to fix any problems caused by logical data corruption or user errors within a designated time window, providing a better approach than conventional database point-in-time recovery (DBPITR).

The following examples return the database to a specified SCN or restore point:

FLASHBACK DATABASE TO RESTORE POINT 'before_OrclFullBkp';

FLASHBACK DATABASE TO SCN ;

Flashback Database

In general, Flashback Database is accessible through the RMAN command and SQL statement FLASHBACK DATABASE. The DBA can use either interface to promptly recover the database from logical data corruptions or user errors. In its outcome, Flashback Database is similar to conventional point-in-time recovery in its effects. Similarly, Flashback Database uses its own logging mechanism, creating flashback logs and storing them in the fast recovery area. The DBA can only use Flashback Database when flashback logs are available. To take advantage of this feature, the DBA must set up the database in advance to create flashback logs. Thus, to enable Flashback Database, he then configures a fast recovery area and set a flashback retention target. This retention target specifies how far back it is possible to reverse the 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 such that these block images can later be reused to reconstruct the data file contents for any moment at which logs were captured.

The database restores the version of each block that is immediately before the target time, once Oracle has determined which blocks changed after the target time and restores them from the flashback logs. Likewise, Redo logs on disk or tape must be available for the entire time period spanned by the flashback logs. In real world practice, redo logs are usually needed much longer than the flashback retention target to support point-in-time recovery.

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. The flashback database window cannot extend further back than the earliest SCN in the available flashback logs.

In particular, some database operations, such as dropping a tablespace or shrinking a data file, cannot be reversed with Flashback Database. 

Limitations of Flashback Database

Based on the fact that Flashback Database works by reversing changes to the data files that exist at the moment when the DBA runs the command, it has the following limitations:

ñ Flashback Database can only reverse 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.

ñ The DBA cannot use Flashback Database to undo a shrink data file operation. Nevertheless, the DBA can take the shrunk file offline, flash back the rest of the database, and later restore and recover the shrunk data file.

ñ The DBA cannot use Flashback Database alone to retrieve a dropped data file. When the DBA flashes 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. Thus, he must recover the dropped data file by using RMAN to fully restore and recover the data file.

ñ When the database control file is restored from backup or re-created, all accumulated flashback log information is discarded. Consequently it is not possible to 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, e.g., an direct-path load, block corruption is likely in the database objects and datafiles affected by the NOLOGGING operation. Then concurrency of Flashback and NOLOGGING operations should be avoided.

Types of Restore Points

Oracle Flashback technology can work with both normal and guaranteed restored points

Normal Restore Points

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

The following commands support the use of normal restore points:

ñ The RECOVER DATABASE and FLASHBACK DATABASE commands in RMAN

ñ The FLASHBACK TABLE statement in SQL.

Guaranteed Restore Points

Similarly, 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. Thus, a guaranteed restore point is an alias for an SCN with any command that works with a normal restore point. A guaranteed restore point ensures that it is possible to 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, the DBA is able to rewind the database to any SCN in the continuum rather than to a single SCN only.

Guaranteed Restore Points versus Storage Snapshots

Today, guaranteed restore points offer a useful alternative to storage snapshots. Storage snapshots are often used to protect a database before risky operations such as large-scale database updates or application patches or upgrades, and to test a variety of daily business operations, in some cases, associated with Data Guard technology. Rather than creating a snapshot or duplicate database to test the operation, the DBA can create a guaranteed restore point on a primary or physical standby database. Then the DBA can perform the risky operation with the certainty that the required flashback logs are kept.

Guaranteed Restore Points and Fast Recovery Area Space Usage

In general, these are the set of governing the creation, retention, overwriting and deletion 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 a flashback log is reused.

ñ 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 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 would require the use of that redo log file for the use of FLASHBACK DATABASE are also deleted.

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.

ñ No file in the fast recovery area is eligible for deletion if it is required to satisfy a guaranteed restore point. 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.

Logging for Guaranteed Restore Points Using Flashback Logging Disabled

Assume that a DBA can 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 at the time that 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.

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.

Flashback Database Logging Using Explicit Guaranteed Restore Points

If the DBA enables Flashback Database and defines one or more guaranteed restore points, then the database performs normal flashback logging. Flashback logs are not deleted in response to space pressure if they are required to satisfy the guarantee. Indeed, Flashback logging can cause some performance overhead.

Prerequisites for Flashback Database and Guaranteed Restore Points

To ensure successful operation of Flashback Database and guaranteed restore points, the DBA must first set several key database options.

Flashback Database

Configure the following database settings before enabling Flashback Database:

ñ The database must be running in ARCHIVELOG mode, since archived redo logs are used in the Flashback Database operation.

ñ The DBA must have a fast recovery area enabled, because flashback logs can only be stored in the fast recovery area.

ñ For Oracle Real Application Clusters (Oracle RAC) databases, the fast recovery area has to be in a clustered file system or in ASM.

Guaranteed Restore Points

To attain successful guaranteed restore points usage, the database must meet the following additional prerequisite: the COMPATIBLE initialization parameter must be set to 10.2.0 or greater

Using Normal and Guaranteed Restore Points

I present next an array of various commands and monitoring capabilities, which can be used with normal and guaranteed restore points.

Creating Normal and Guaranteed Restore Points

In order 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 guaranteed or normal.

Sample SQL Commands

SQL> REM Creating a normal restore point

SQL> CREATE RESTORE POINT before_OrclFullBkp;

SQL> REM Creating a guarantee restore point

SQL> CREATE RESTORE POINT before_OrclFullBkp GUARANTEE FLASHBACK DATABASE;

SQL> Rem listing restore points

SQL> SELECT name,
            scn,

            time,

            database_incarnation#,

            guarantee_flashback_database,

            storage_size

       FROM v$restore_point;

SQL> SELECT * FROM rman.rc_restore_point;

Equivalently, the DBA can use the following RMAN sample commands.

RMAN> LIST RESTORE POINT before_OrclFullBkp;

RMAN> LIST RESTORE POINT ALL;

SQL> rem dropping a restore point

SQL> DROP RESTORE POINT before_OrclFullBkp;

Important Remarks

Normal restore points eventually age out of the control file, even if not explicitly dropped. The rules governing retention of restore points in the control file are:

ñ The most recent 2048 restore points are always kept in the control file, regardless of their age.

ñ Any restore point more recent than the value of CONTROL_FILE_RECORD_KEEP_TIME is retained, regardless of how many restore points are defined.

Normal restore points that do not meet either of these conditions may age out of the control file.

Guaranteed restore points never age out of the control file. They remain until they are explicitly dropped.

Using Flashback Database

I hereby highlight the basic commands for Flashback Database. It also lists guidelines to ensure optimal performance of Flashback Database.

Enabling Flashback Database

In order to enable Oracle Flashback Database, the DBA must follow these steps, namely:

1.   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. I applicable, any other Oracle RAC instance can be in any mode.

2.   Optionally, set the DB_FLASHBACK_RETENTION_TARGET to the length of the desired flashback window in minutes, e.g., for five days, the command would be:

ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET= 7200;

By default DB_FLASHBACK_RETENTION_TARGET is set to 1 day, i.e, 1440 minutes.

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

ALTER DATABASE FLASHBACK ON;

4.   Optionally, disable flashback logging for specific tablespaces.

By default, flashback logs are generated for all permanent tablespaces. The DBA can reduce some of the overhead by disabling flashback logging for specific tablespaces as shown below:

ALTER TABLESPACE tbs_adnencr FLASHBACK OFF;

Later the DBA can re-enable flashback logging for this tablespace:

ALTER TABLESPACE tbs_adnencr FLASHBACK ON;

Off course, when disabling Flashback Database for a tablespace, the DBA has to take its datafiles offline before executing the FLASHBACK DATABASE command.

Disabling Flashback Database Logging

On a database instances that is either in mount or open state, issue the following command:

ALTER DATABASE FLASHBACK OFF;

 Configurating a Better Environment for Optimal Flashback Database Performance

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.

To achieve good performance for large production databases with Flashback Database enabled, Oracle recommends for the DBA to perform the following actions:

ñ Use a fast file system for the fast recovery area, preferably without operating system file caching. ASM is highly recommended.

ñ Configure enough disk spindles for the file system that holds the fast recovery area.

ñ If the storage system used to hold the fast recovery area does not have nonvolatile RAM or is not SSS-based, then try to configure the file system on striped storage volumes using Oracle’s Stripe And Mirror Everything (SAME) approach to RAID technology, utilizing a small stripe size, e.g.,128KB.

ñ For large databases, set the initialization parameter LOG_BUFFER to at least 8 MB.

Monitoring the Outcome of Flashback Database in Terms of Performance

The DBA can use the Automatic Workload Repository (AWR) automates database statistics gathering by collecting, processing, and maintaining performance statistics for database problem detection and self-tuning. AWR provides several data analysis methods for monitoring the Flashback Database workload. For instance, the DBA could compare AWR reports from before and after the Flashback Database was turned on.

Column Name	Column Description
Physical write I/O request	Number of write operations issued for writing data blocks
Physical read I/O request	Number of read operations issued for reading data blocks
Redo writes	Number of write operations issued for writing to the redo log
Flashback log writes	Number of write operations issued for writing to flashback logs
Flashback log write bytes	Total size in bytes of flashback database data written from this instance by the database.

In particular, the V$FLASHBACK_DATABASE_STAT view shows the bytes of flashback data logged

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 below can inform the number of I/O operations that the instance has issued for various purposes.

Flashback Writer (RVWR) Behavior with I/O Errors

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 once RVWR encounters I/O errors.

ñ If no guaranteed restore points are defined, then the instance remains unaffected when RVWR encounters I/O errors. The following cases are to be emphasized:

ñ 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, the DBA can issue either a SHUTDOWN ABORT or an ALTER DATABASE FLASHBACK OFF command.