DB_FLASHBACK_RETENTION_TARGET

 Configuration Best Practices

1. Set DB_FLASHBACK_RETENTION_TARGET correctly.  Set DB_FLASHBACK_RETENTION_TARGET initialization parameter to the largest value prescribed by any of the following conditions that apply:
• To leverage flashback database to reinstate your failed primary database after Data Guard failover, for most cases set DB_FLASHBACK_RETENTION_TARGET to a minimum of 60 (minutes) to enable reinstatement of a failed primary. 
• Consider cases where there are multiple outages (e.g. first a network outage, followed later by a primary database outage) that may result in a transport lag between primary and standby database at failover time.  For such cases set DB_FLASHBACK_RETENTION_TARGET to a value equal to the sum of 60 (mins) plus the maximum transport lag that you wish to accommodate.  This will insure that the failed primary database can be flashed back to an SCN that precedes the SCN at which the standby became primary - a requirement for primary reinstatement.
• If using Flashback Database for fast point in time recovery from user error or logical corruptions, set DB_FLASHBACK_RETENTION_TARGET to a value equal to the farthest time in the past that you wish to be able to recover to.
• Set Primary and Standby  DB_FLASHBACK_RETENTION_TARGET to be the same.
2. Size Fast Recovery Area (FRA).  Ensure the fast recovery area has allocated sufficient space to accommodate flashback database flashback logs for the target retention size and for peak batch rates.  Sizing the fast recovery area is described in detail in the 10g Database Backup and Recovery Basics guide  and the 11g Database Backup and Recovery User's Guide /  12c Database Backup and Recovery User's Guide  but the general rule of thumb is the volume of flashback log generation is approximately the same order of magnitude as redo log generation.  Use the following conservative formula and approach

Target FRA = Current FRA + DB_FLASHBACK_RETENTION_TARGET x 60 x Peak Redo Rate (MB/sec)

      Example:

• Current FRA or DB_RECOVERY_FILE_DEST_SIZE=1000G
• Target DB_FLASHBACK_RETENTION_TARGET=360 (360 minutes)
• From AWR:  1) Peak redo rate for OLTP workload is 3 MB/sec for database.  2)  Peak redo rate for batch workload is 30 MB/sec for database and longest duration is 4 hours.   3) worst-case redo generation size for 6 hour window is       ( 240 minutes x 30 MB/sec x 60 secs/min) +  (120 minutes x 3 MB/sec x 60 secs/min ) = 453,600 MB or approx 443 GB
• Proposed FRA or DB_RECOVERY_FILE_DEST_SIZE= 443 GB +1000 GB = 1443 GB.

An additional method to determine fast recovery area sizing is to enable flashback database and allow the database applications to run for a short period (2-3 hours) and query V$FLASHBACK_DATABASE_STAT.ESTIMATED_FLASHBACK_SIZE.

Note that the DB_FLASHBACK_RETENTION_TARGET is a target and there is no guarantee that you can flashback the database that far.  In some cases if there is space pressure in the flash recovery area where the flashback logs are stored then the oldest flashback logs may be deleted.  For a detailed explanation of the flash recovery area deletion rules see the Database Backup and Recovery User's Guide, Maintaining the Fast Recovery Area section.  To guarantee a flashback point-in-time you must use guaranteed restore points (GRP).  With GRP, the required flashback logs will never be recycled or purged until GRP is dropped.     You can hang the database if you have a GRP and there’s insufficient space; so you need allocate more space in the FRA depending on the intended duration of the GRP.

3. Configure sufficient I/O bandwidth for Fast Recovery Area.  Insufficient I/O bandwidth with flashback database on is usually indicated by a high occurrence of the "FLASHBACK BUF FREE BY RVWR" wait event in an Automatic Workload Repository (AWR) report for OLTP workloads and “FLASHBACK LOG FILE WRITE” latency > 30 ms for large insert operations.   In general, flashback IOs are 1 MB in size and the overall write throughput will be similar to the redo generation rate if database force logging was enabled or similar to your load rate for direct load operations.   For simplicity, configure one large shared storage GRID and configure DATA on the outer portion of the disks or LUNS and RECO (fast recovery area) on the inner portion of the disks or LUNS.
4. Recommended LOG_BUFFER settings to give flashback database more buffer space in memory.
   
   Recommend to set the LOG_BUFFER to maximum value for the specific database release and platform.   
   The previous 8 MB recommendation does not work well in high throughput applications with flashback database enabled.   
   
   We now recommend  64 MB for 32-bit systems
                and up to  256 MB for 64-bit systems
   
   
5. Set _DB_FLASHBACK_LOG_MIN_SIZE = <redo log size> for any 11.2.0.2 release.   In previous releases, the initially flashback log allocations are hindered because the initial file sizes are too small which may impact primary load performance.  As of 11.2.0.3, the default min size is the redo log group size which is the prescribed best practice.    This enhancement is not available in Oracle 10g so customers may experience some additional performance overhead until DB_FLASHBACK_RETENTION_TARGET is met 

     Example:

     SQL> alter system set "_db_flashback_log_min_size"=4g;

6. Set _DB_FLASHBACK_LOG_MIN_TOTAL_SPACE =< projected flashback size> temporarily if you want to pre-allocate flashback logs in the FRA but you must add more space to FRA beforehand.  This is normally unnecessary since Oracle will allocate flashback logs as you generate changes.   We do recommend enabling flashback database at a non-peak period especially avoiding periods immediately prior to or during your direct load operations.   You can then monitor by querying V$FLASHBACK_DATABASE_LOG.FLASHBACK_SIZE.   Setting this undocumented parameter may be useful if you want to create a guaranteed restore point or enable flashback database prior to a big load and you want to quickly pre-allocate the necessary flashback logs.    

        Example:

            SQL> alter system set "_db_flashback_log_min_size"=4g;

            SQL> alter system set "_db_flashback_log_min_total_space"=50g;

Wait 5 minutes and query “select flashback_size from V$flashback_database_log;”   Repeat until flashback target minimum size is met.  When completed unset _DB_FLASHBACK_LOG_MIN_TOTAL_SPACE.

Operational Best Practices

1. Gather database statistics using Automatic Workload Repository (AWR), Enterprise Manager before and after enabling flashback database so you can measure the impact of enabling flashback database.
2. Set the Enterprise Manager monitoring metric, "Recovery Area Free Space (%)" for proactive alerts of space issues with the fast recovery area.
3. From 11.2 onward, you can enable flashback database while the database is open.  However this operation may fail and signal an error if it fails to get enough contiguous memory.  To guarantee success, you can enable flashback in mount mode.
4. To monitor the progress of a flashback database operation you can query the V$SESSION_LONGOPS view.  An example query to monitor progress is:

     select * from v$session_longops where opname like 'Flashback%';

If more detail is required on the flashback database operation then set _FLASHBACK_VERBOSE_INFO=TRUE database parameter which will generate a detailed trace of the flashback database operation in the DIAGNOSTIC_DEST trace directory for the database

5. When using flashback database to perform repeated tests on a test database, it is recommended to use Guaranteed Restore Points (GRP) only without explicitly turning on flashback database.  To minimize space usage and flashback performance overhead, follow this recommended approach:

Create Guaranteed Restore Point (GRP)
Execute test
loop

     Flashback database to GRP
     Open resetlogs
     Create new GRP
     Drop old GRP
     Execute test

End loop

6. Follow the Data Guard redo apply best practices described in Best Practices for Data Guard and Active Data Guard Redo Apply Performance.
7. Also review the 10g Database Backup and Recovery Basics guide or the 11g Release 2 Backup and Recovery User's Guide.

Performance tuning for specific application use cases