1. Enabling Logging for Flashback Database

1
2
3
4
5
6
7
8
9

SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
 
# Optionally, set the length of desired flashback window in minutes:
BY DEFAULT DB_FLASHBACK_RETENTION_TARGET IS SET TO one day (1440 minutes).
SQL> ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET=4320; # 3 days
 
# Enable Flashback Database on whole database:
SQL> ALTER DATABASE FLASHBACK ON;

By default, flashback logs are generated for all permanent tablespaces.

1
2
3
4

#Disabling flashback logging for specific tablespaces:
SQL> ALTER TABLESPACE tbs_3 FLASHBACK OFF;
#re-enable flashback logging for a tablespace later:
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.

1
2

# Disable flashback logging for entire database:
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.

2. Estimating and Managing Disk Requirements 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.

• You cannot manage the flashback logs in the flash recovery area directly
• BACKUP RECOVERY AREA does not include the flashback logs when backing up flash recovery area contents to tape.
• 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.

3. 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.

1
2
3
4
5
6

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.

1
2
3
4

#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;

4. Performance Tuning for Flashback Database

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. It is recommended to use a file system that avoids operating system file caching, such as ASM.
• 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.

5. Monitoring Flashback Database Performance Impact

• To monitor system usage due to flashback logging is to take performance statistics using the Oracle Statspack.
• The V$FLASHBACK_DATABASE_STAT view shows the bytes of flashback data logged by the database
• 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

6. Flashback Writer (RVWR)

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:
  
  1. 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.
  2. 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.

Related Articles

1. FlashBack Database Technology [Part-1] [Introduction]
2. FlashBack Database Technology [Part-2] [Set up and Maintain flashback database]
3. FlashBack Database Technology [Part-3] [Set up and Maintain Restore points]
4. FlashBack Database Technology [Part-4] [Flashback Database]
5. FlashBack Database Technology [Part-5] [Flashback and Database Point-in-Time Recovery (Coming Soon)]