5.3 Setup and Maintenance for Oracle Flashback Database

This section describes planning for Oracle Flashback Database, configuring your database to use it and useful maintenance, monitoring and tuning procedures.

This discussion contains the following topics:

Limitations of Flashback Database
Requirements for Enabling Flashback Database
Enabling Logging for Flashback Database
Sizing the Flash Recovery Area to Include Flashback Logs
Managing Space For Flashback Logs in the Flash Recovery Area
Determining the Current Window for Flashback Database
Performance Tuning for Flashback Database
Monitoring Flashback Database Performance Impact

5.3.1 Limitations of Flashback Database

Because Flashback Database works by undoing changes to the datafiles that exist at the moment that you run the command, it has the following limitations:

Flashback Database can only undo changes to a datafile made by an Oracle database. It cannot be used to repair media failures, or to recover from accidential deletion of datafiles.
You cannot use Flashback Database to undo a shrink datafile operation.
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 datafiles 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 need to use Flashback Database to return to the target time 09:07 on that date, the objects and datafiles 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 datafiles 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 the discussion of logging_clause in Oracle Database SQL Reference for more information about operations that support NOLOGGING mode.

5.3.2 Requirements for Enabling Flashback Database

The requirements for enabling Flashback Database are:

Your database must be running in ARCHIVELOG mode, because archived logs are used in the Flashback Database operation.
You must have a flash recovery area enabled, because flashback logs can only be stored in the flash recovery area.
For Real Application Clusters databases, the flash recovery area must be stored in a clustered file system or in ASM.

5.3.3 Enabling Logging for Flashback Database

To enable logging for Flashback Database, set the DB_FLASHBACK_RETENTION_TARGET initialization parameter and issue the ALTER DATABASE FLASHBACK ON statement. Follow the process outlined here:

Start SQL*Plus and ensure that the database is mounted, but not open. For example:
```
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
```
Optionally, set theDB_FLASHBACK_RETENTION_TARGET to the length of the desired flashback window in minutes:
```
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days
```
By default DB_FLASHBACK_RETENTION_TARGET is set to one day (1440 minutes).
Enable the Flashback Database feature for the whole database:
```
SQL> ALTER DATABASE FLASHBACK ON;
```

By default, flashback logs are generated for all permanent tablespaces. If you wish, you can reduce overhead by disabling flashback logging for specific tablespaces:

SQL> ALTER TABLESPACE tbs_3 FLASHBACK OFF;

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

SQL> ALTER TABLESPACE tbs_3 FLASHBACK ON;

Note that if you disable Flashback Database for a tablespace, then you must take its datafiles offline before running FLASHBACK DATABASE.

You can disable flashback logging for the entire database with this command:

SQL> ALTER DATABASE FLASHBACK OFF;

You can also enable flashback logging on a standby database. Enabling Flashback Database on a standby database enables you to perform Flashback Database on the standby database. Flashback Database of standby databases has a number of applications in the Data Guard environment. See Oracle Data Guard Concepts and Administration for details.

5.3.4 Sizing the Flash Recovery Area to Include Flashback Logs

When using Flashback Database, you must add extra space to the flash recovery area to hold the flashback logs, along with the other files stored in the flash recovery area.

The setting of the DB_FLASHBACK_RETENTION_TARGET initialization parameter determines, indirectly, how much flashback log data the database retains. The size of flashback logs generated by the database for a given time period can vary considerably, however, depending on the specific database workload. If more blocks are affected by database updates during a given interval, then more disk space is used by the flashback log data generated for that interval.

5.3.4.1 Estimating Disk Space Requirements for Flashback Database Logs

The V$FLASHBACK_DATABASE_LOG view can help you estimate how much space to add to your flash recovery area for flashback logs. After you have enabled logging for Flashback Database and set a flashback retention target, allow the database to run under a normal workload for a while, to generate a representative sample of flashback logs. Then run the following query:

SQL> SELECT ESTIMATED_FLASHBACK_SIZE FROM V$FLASHBACK_DATABASE_LOG;

The result is an estimate of the disk space needed to meet the current flashback retention target, based on the database workload since Flashback Database was enabled. Add the amount of disk space specified in $FLASHBACK_DATABASE_LOG.ESTIMATED_FLASHBACK_SIZE to your flash recovery area size, to hold the expected database flashback logs.

Space usage in the flash recovery area is always balanced among backups and archived logs which must be kept according to the retention policy, and other files which may be subject to deletion, such as flashback logs and backups already backed up to tape but still cached on disk. If you have not allocated enough space in your flash recovery area to store your flashback logs and still meet your other backup retention requirements, flashback logs may be deleted from the recovery area to make room.

5.3.5 Managing Space For Flashback Logs in the Flash Recovery Area

You cannot manage the flashback logs in the flash recovery area directly, other than by setting the flashback retention target or using guaranteed restore points. You can, however, manage flash recovery area space as a whole, in order to maximize space available for retention of flashback database logs. In this way you increase the likelihood of achieving the flashback retention target.

To make space for flashback logs, back up the other contents of your flash recovery area to tape, using commands such as BACKUP RECOVERY AREA, BACKUP BACKUPSET and so on. Then use the DELETE command to explicitly remove files from the flash recovery area once they have been backed up on tape. If this still does not create enough space to satisfy your backup retention policy and flashback retention target, allocate more space for your flash recovery area.

Note:

BACKUP RECOVERY AREA does not include the flashback logs when backing up flash recovery area contents to tape.

5.3.5.1 Rules for Retention and Deletion of Flashback Logs

The following rules govern the flash recovery area's creation, retention, overwriting and deletion of flashback logs:

A flashback log is created whenever necessary to satisfy the flashback retention target, as long as there is enough space in the flash recovery area.
A flashback log can be reused, once it is old enough that it is no longer needed to satisfy the flashback retention target.
If the database needs to create a new flashback log and the flash recovery area is full or there is no disk space, then the oldest flashback log is reused instead.

Note:
Re-using the oldest flashback log shortens the flashback database window. If enough flashback logs are reused due to a lack of disk space, the flashback retention target may not be satisfied.
If the flash recovery area is full, then an archived redo log may be automatically deleted by the flash recovery area to make space for other files. In such a case, any flashback logs that would require the use of that redo log file for the use of FLASHBACK DATABASE are also deleted.

5.3.6 Determining the Current Window for Flashback Database

When flashback logging is enabled, the earliest SCN in the flashback database window can be determined by querying V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_SCN and V$FLASHBACK_DATABASE_LOG.OLDEST_FLASHBACK_TIME as shown in this example:

SELECT OLDEST_FLASHBACK_SCN, OLDEST_FLASHBACK_TIME 
      FROM V$FLASHBACK_DATABASE_LOG;

If the results of this query regularly indicate that the flashback database window does not satisfy the flashback retention target, you may need to increase your flash recovery area to accomodate more flashback logs.

Caution:

This view indicates how much flashback log data is available for your database. However, there are operations, such as shrinking or dropping a datafile, which can prevent Flashback Database from succeeding against your entire database, even if flashback log data is available. The results of this query do not reflect such limitations.

The most recent SCN that can be reached with Flashback Database is the current SCN of the database. This query returns the current SCN:

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

5.3.7 Performance Tuning for Flashback Database

Maintaining flashback logs imposes comparatively limited overhead on an Oracle 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 the following:

Use a fast file system for your flash recovery area, preferably without operating system file caching. Files the database creates in the flash recovery area, including flashback logs, are typically large. 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. Thus, it is recommended to use a file system that avoids operating system file caching, such as ASM.
Configure enough disk spindles for the file system that will hold the flash recovery area. 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.
If the storage system used to hold the flash recovery area does not have non-volatile RAM, try to configure the file system on top of striped storage volumes, with a relatively small stripe size such as 128K. This will allow each write to the flashback logs to be spread across multiple spindles, improving performance
For large, production databases, set the init.ora parameter LOG_BUFFER to be at least 8MB. This makes sure the database allocates maximum memory (typically 16MB) for writing flashback database logs.

The overhead of turning on logging for Flashback Database depends on the mixture of reads and writes in the database workload. The more write-intensive the workload, the higher the overhead caused by turning on logging for Flashback Database. (Queries do not change data and thus do not contribute to logging activity for Flashback Database.)

5.3.8 Monitoring Flashback Database Performance Impact

The best way to monitor system usage due to flashback logging is to take performance statistics using the Oracle Statspack. For example, if you see "flashback buf free by RVWR" as the top wait event, it indicates that Oracle cannot write flashback logs very quickly. In such a case, you may want to tune the file system and storage used by the flash recovery area, possibly using one of the methods described in "Performance Tuning for Flashback Database".

The V$FLASHBACK_DATABASE_STAT view (described in Oracle Database Reference) 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 describe bytes of data blocks read and written. Note that FLASHBACK_DATA and REDO_DATA correspond to sequential writes, while DB_DATA corresponds to random reads and writes.

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 following statistics in V$SYSSTAT can tell you the number of I/O operations your instance has issued for various purposes:

Column Name	Column Meaning
Physical write I/O request	The number of write operations issued for writing data blocks
physical read I/O request	The number of read operations issued for reading data blocks
redo writes	The number of write operations issued for writing to the redo log.
flashback log writes	The number of write operations issued for writing to flashback logs.

See Oracle Database Reference for more details on columns in the V$SYSSTAT view.

5.3.9 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 flash recovery area. If RVWR encounters an I/O error, the following behavior is expected:

If there are any guaranteed restore points defined, the instance will crash when RVWR encounters I/O errors.
If no guaranteed restore points are defined, the instance will not crash when RVWR encounters I/O errors. There are 2 cases:
- On a primary database, Oracle automatically disables flashback database while the database is open. All existing transactions and queries will proceed unaffected. This behavior is expected for both single instance and RAC.
- On a physical or logical standby, RVWR will appear to be hung, retrying the I/O periodically. This may eventually hang the logical standby or the managed recovery of physical standby. (Oracle does not crash the standby instance because it does not want to crash the primary database in turn in max protection mode.) To resolve the hang, a DBA can either perform a SHUTDOWN ABORT or issue ALTER DATABASE FLASHBACK OFF.