flashback database 的限制

• Flashback Database :

The Flashback Database allows you to flash the entire database back to a specific point-intime.
It is best used as a replacement for incomplete recovery of a complete database. The main benefit of the Oracle Flashback Database over incomplete database recovery is that the Flashback Database is much quicker and more efficient. The Flashback Database is not based on undo data but on flashback logs.

If flashback database is enabled, its flashback logs are stored in the Flash Recovery area.

Flashback logs are written sequentially During normal database operation, and they are not archived. Oracle automatically creates, deletes, and resizes Flashback logs in the flash recovery area. You only need to be aware of Flashback logs for monitoring performance and deciding how much disk space to allocate to the flash recovery area for Flashback logs.

The amount of time it takes to Flashback a database is proportional to how far back you need to revert the database, rather than the time it would take to restore and recover the whole database, which could be much longer. The before images in the Flashback logs are only used to restore the database to a point in the past, and forward recovery is used to bring the database to a consistent state at some time in the past. Oracle returns datafiles to the previous point-in-time, but not auxiliary files, such as initialization parameter files.

DB_FLASHBACK_RETENTION_TARGET A parameter value that determines how far back in time you can recover the flashback database, This value is in minutes.

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.

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;

Prerequisites :

• You must have the SYSDBA system privilege.

• A flash recovery area must have been prepared for the database.

• The database must have been put in FLASHBACK mode with an ALTER DATABASE FLASHBACK ON statement unless you are flashing the database back to a guaranteed restore point.

• The database must be mounted but not open.
• 归档模式不需要吗？

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 accidental deletion of datafiles.

• You cannot use Flashback Database to undo a shrink datafile operation.---shrink不生效 但是不影响flashback吧

• 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.   看这个说法， 只有回复到nolonging期间的才有问题，9:00  之前或者9:15之后都没问题

 shrinking a datafile or dropping a tablespace can prevent flashing back the Database  19C没有限制了吧？

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.

1. flashback log 最少要保留一小时

Symptoms

 With flashback database turned on, the flashback logs used most of the space in the fast recovery area, and were not deleted when space was needed for archive logs. Subsequently, the database hung.

ORA-19815: WARNING: db_recovery_file_dest_size of 524288000 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from flash recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
system command was used to delete files, then use RMAN CROSSCHECK and
DELETE EXPIRED commands.
************************************************************************
Errors in file /<PATH>/orcl_arc2_15324.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 5242880 bytes disk space from 524288000 limit
ARC2: Error 19809 Creating archive log file to '+<FRA DISK GROUP>' 

SQL> alter session set NLS_DATE_FORMAT='DD-MON-YYYY HH24:MI:SS';
set linesize 132 serveroutput on pagesize 50000
select * from v$FLASHBACK_DATABASE_LOGFILE;
select * from V$FLASHBACK_DATABASE_STAT;
select * from V$FLASHBACK_DATABASE_LOG;

NAME LOG# THREAD# SEQUENCE# BYTES FIRST_CHANGE# FIRST_TIME
---------------------------------------------------- ---------- ---------- ---------- ---------- ------------- --------------------
+<FRA DISK GROUP>/log_1.261.782829073 1 2 6 15941632 1048169 10-MAY-2012 10:00:04 lowest log sequence# listed
...
+<FRA DISK GROUP>/log_60.339.782904711 60 1 103 15941632 1045574 10-MAY-2012 09:31:51 highest log sequence# listed.

 
The first_time only accounts for 30 minutes of flashback logging so the 1 hour hard limit has not been reached in this database yet.

Cause

Some flashback logs that will not be deleted when the fast recovery area sees space pressure and archiver will fail. There is a coded hard limit on how much flashback logs can be deleted to make space for new archivelogs.

When archiver wants disk space to archive online redo log and there is no files to purged from FRA, then instead of making the database hang, flashback logs (that is not required for GRP) will be purged to hard limit. Once that hard limit is reached, no more flashback logs can be purged and the database will hang.

The hard limit is _MINIMUM_DB_FLASHBACK_RETENTION value. The default is 1 hour. So, we start purging flashback logs until we reach 1 hour of flashback_retention_time after which we give up purging flashback logs.

NOTE:  Flashback logs will also be retained for restore points.  

Solution

The hard limit is  _MINIMUM_DB_FLASHBACK_RETENTION value. The default is 1 hour.  So, we start purging flashback logs until we reach 1 hour of flashback_retention_time after which we give up purging flashback logs. 

The real solution is to add more space to the FRA to allow for the flashback_retention_period.  Tweaking _MINIMUM_DB_FLASHBACK_RETENTION is NOT recommended

Add: SQL> alter system set "_flashback_verbose_info"=true;

 This will provide more detailed information about the space pressure and how to manage it but in this case the solution is to add more space to allow for:

  db_flashback_retention_target = 1440 (1 DAY) since we stop purging when there is only 1 hour of flashback logs left.

 NOTE: Flashback logs will also be retained for restore points.

2.

Purpose

Some application profiles may require special tuning or additional considerations when enabling flashback database or using guaranteed restore points.

This note describes

• flashback database benefits,
• performance observations,
• configuration best practices
• operational best practices,
• performance tuning for specific application workloads

Scope

This note is intended for database administrators who are using or considering using flashback database or guaranteed restore points.

Flashback Database Feature and its Benefits

Flashback database can be used to quickly flashback a primary or standby database to a point in time as opposed to the traditional point-in-time recovery. Flashback database feature can provide the following benefits

1. Point-in-time recovery – to quickly rewind the primary or standby database to point-in-time by using restore points or normal flashback database functionality. This functionality can be used for 1) repair from failed batch updates that has modified a significant portion of the database,  2) repair from a crashed database where the current online redo log is corrupted or irreparable  这个功能非常好啊，主库坏了没关系，回退吧。 上次创建standby把主库搞坏了。  flashback 不光可以正常需求 ，也可以搞定主库打不开的错误。
   
   or 3) repair from a planned maintenance event (e.g. application change that changes the database) that goes awry.
2. Data Guard Fast-start failover - to quickly reinstate the primary database as a standby database.  这个也要flashback 技术
3. Manual primary database reinstate- if Data Guard is being used without the fast-start failover feature and a Data Guard failover is necessary, then flashback database can be used to manually reinstate the failed primary database.  Reinstatement of the failed primary database via flashback can complete in minutes regardless of database size compared to much more extensive work and time required for database reinstantiation performed across the network.  Using flashback database to reinstate a failed primary database after a failover is documented in the Data Guard Administration and Concepts guide.
4. Data Guard Snapshot Standby - A snapshot standby database is a fully updatable standby database.  It receives and archives redo data from a primary database but does not apply it.  An implicit guaranteed restore point is created when a physical standby database is converted into a snapshot standby database and this restore point is used to flashback a snapshot standby to its original state when it is converted back into a physical standby database.
5. To flashback a failed database upgrade - Additional to having a backup of the database, it is recommended to create a Flashback Guaranteed Restore Point (GRP).  As long as the database COMPATIBLE parameter is not changed after creating the GRP, the database can easily be flashed back after a (failed) upgrade.  The AutoUpgrade Tool will also offer an option to create a GRP before proceeding with the upgrade.  Flashing back to a GRP will back out all changes in the database made after the creation  of the GRP.  
6. Rolling Database Upgrades with Physical Standby Databases using Transient Logical Standby or DBMS_ROLLING - As part of the database rolling upgrade when a physical standby is temporarily converted to a logical standby, a guaranteed restore point is taken on the primary database prior to the upgrade process.  This is so that the database can be flashed back and converted back to a physical standby after the upgrade.  For details on this see the MAA paper, Database Rolling Upgrade using Data Guard and Automated Database Upgrades using Oracle Active Data Guard and DBMS_ROLLING at our website www.oracle.com/goto/maa
7. RMAN - flashback database is integrated with Oracle Recovery Manager and used implicitly in recovery and explicitly if desired.  One example of RMAN implicit use of flashback database is when block media recovery is performed.
8. Restoring Test and Training Databases - Another common use of flashback database is for flashing back a test or training database to the same starting point after each test run or training session.
9. Flashback PDB - Starting in Oracle 12.2 and higher, flashback pluggable database (PDB) can rewind a PDB without affecting other PDBs. You can also create PDB restore points.

For more information about flashback database and restore points, refer to the Backup and Recovery User's Guide Using Flashback Database and Restore Points section.

Details

Performance Observations

After adopting the configuration and operational best practices below and recommended patches, Oracle has observed the following performance observations when enabling flashback database on the primary or standby database.

• Flashing back of a database or PDB to previous hour usually take seconds and minutes even with very high workload. It finishes in a fraction of the time it takes to apply a given amount redo. Here are some observations:
  • Flashing back a large batch workload consisting of 400 GB of changes completed in less than 5 minutes.
  • Flashing back of a heavy OLTP of 8GB of changes completed in less than 2 minutes.
  • Due to a myriad of variables, there is no rule-of-thumb or calculation to estimate time to complete a flashback. The above tests were done on Exadata to remove system bottlenecks such as storage IO bandwidth.
• Impact on OLTP workload on the primary is usually less than 5 percent.
• Impact of large insert (e.g. batch inserts) or direct load operations is usually less than 5 percent if flashback block new optimization is in effect; otherwise the impact can vary dramatically (2-40% impact) so testing is required.   Refer to block new optimization descriptions and exceptions.
• Enabling Flashback database can reduce peak redo apply performance rates on a physical standby database, however it is normally not noticeable because the achievable redo apply rates with flashback enabled is still very high and can outperform application’s redo generation rates.

 Here are the key flashback milestones and key performance improvements across different database software releases:

•  Oracle 11g Release 2
  • FEATURE:  Flashback database can be enabled while the database is open
  • PERFORMANCE FEATURE:  Block new optimization feature available for direct load and insert operations for RAC. Currently flashback block new optimizations does not work for these cases: 1) Insert/load operation after truncate operation; 2) Insert/load operation after a drop table where the table exists in Oracle recycle bin; 3) Redo apply or recovery of insert/load operation and 4) when loading into tables with SecureFile LOB or traditional LOB data types.
  • PERFORMANCE FEATURE:  Initial overhead when allocating flashback logs before reaching DB_FLASHBACK_RETENTION_TARGET has been reduced and in some cases eliminated by attempting to pre-allocate before blocking any application foreground processes.   For pre-11.2.0.2 releases, the overhead of enabling flashback is usually higher when flashback is first enabled.
  • PERFORMANCE FEATURE:  Significant improvements in reading flashback logs during flashback database operation. 
    
    Flashback database performs two basic steps:  1) reading the flashback logs to determine which buffers need to be restored from flashback logs and 2) DBWR writes the buffers from buffer cache to disk.
• Oracle 12c Release 2 (12.2)
  • FEATURE: Flashback Pluggable Database enables flashback of individual PDBs without effecting other PDBs.
  • FEATURE: PDB Restore Points enables ease of use method to set an alias to and SCN. This alias can then be used for flashback PDB or Point-In-Time Recovery.
• Oracle 19c  19C才有的。。。
  • FEATURE: Creating a Restore Point on a primary database will automatically propagate to a standby database and create a corresponding Restore Point on the standby. FEATURE: When Flashback Database is enabled on both primary and standby databases in a Data Guard configuration, flashing back the primary will cause the standby database to automatically flashback as well.

Configuration Best Practices

• 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:
  1. 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.  When enabling flashback database, you need a couple of hours to generate sufficient flashback data into the flashback logs before reinstatement is possible.  Query V$FLASHBACK_DATABASE_LOG for the oldest flashback time.
  2. 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.
  3. 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.
  4. Typically, set Primary and Standby  DB_FLASHBACK_RETENTION_TARGET to the same value.
• Size Fast Recovery Area (FRA).  Flashback Database uses its own logging mechanism, creating flashback logs and storing them in the Fast Recovery Area (FRA). Ensure the FRA 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 Oracle Backup and Recovery documentation 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 -------flashback大小和REDO产生量差不多
• 
  
  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. 用多少不需要用户考虑，Oracle会自己维护，我认为GRP之后的任何一点也是可以flashback的    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.

• 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.  This is done automatically for Exadata systems.    DATA RECO分开
• Set LOG_BUFFER=256MB to give flashback database more buffer space in memory.

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

1. 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.  flashback on不是必须打开 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

1. Follow the Data Guard redo apply best practices described in Oracle Data Guard Best Practices

Performance tuning for specific application use cases

For OLTP workloads:

• The flashback buf free by RVWR wait event only occurs when Flashback Database is turned on. A session waits for recovery writer (RVWR) to write flashback data to the flashback logs on disk because the buffers are full. Until RVWR can free up the buffers, the session may need to wait.  If this event becomes one of the top wait events for the database, it is typically because the file system or storage system for the Fast Recovery Area has insufficient IO bandwidth to accommodate additional I/O from the flashback writes. Refer to the Flashback Database section in the Database Backup and Recovery User's Guide for tuning considerations and evaluate the corresponding IO and storage stats.

For example

Top 5 Timed Foreground Events

For direct path operations

• Look at your system statistics flashback log write bytes and physical write bytes found in v$sysstat, in your AWR reports or EM. 
• If (flashback log write bytes) / (physical write bytes) < 5%, then flashback is not impacting your performance.  Otherwise evaluate any operational changes or bug fixes that will allow you to use flashback block-new optimization feature (refer to performance observation section above).  Furthermore, ignore flashback log file sync wait event even if it’s one of the top waits.
• flashback log write bytes= Total size in bytes of flashback database data written by RVWR to flashback database logs
• physical write bytes=Total size in bytes of all disk writes from the database application activity (and not other kinds of instance activity).

Example when block-new optimization is in effect

    flashback log write bytes = 1,223,442,432

     physical write bytes = 184,412,282,880

     (flashback log write bytes) / (physical write bytes)  = 0.0066 < 5%  implies that there’s only a fraction of flashback data compared to the physical writes within this interval where there’s direct load operations.   Even in this case, flashback log file sync wait eventwas the 2nd highest wait event in the database.

Top 5 Timed Foreground Events

Example when block-new optimization did NOT come into effect

 flashback log write bytes= 184,438,194,176

 physical write bytes =184,405,925,888

 (flashback log write bytes) / (physical write bytes)  = 100% >  5%  implies that in this case all direct writes are resulting in flashback log writes too.   Here’s the top wait events for this case.

Top 5 Timed Foreground Events

For conventional load operations

The following examples illustrates two conventional loads, one that utilizes block new optimization and one that does not:

The example below does not utilize the block new optimization due to the use of a truncate just prior to loading the table truncate导致flashback log增多.  The wait events for a conventional load without block new optimization show a fairly large amount of total wait time spent in "flashback log file sync".  This is due to the time needed to read the before image of the block into the buffer cache and well as write the block out to the flashback log.

Top 5 Timed Foreground Events

Looking at the instance statistics we see very little increases in the stats that track block new optimizations:

If the "flashback cache read optimizations for block new" is much smaller than "flashback log writes" then the block new optimzation is not kicking in.

The best tuning advice for the above load operation would be to increase I/O bandwidth or, perhaps better, change the manner in which the load is performed so that it can take advantage of block new optimizations.  You can also wait until you are outside flashback retention target or remove the object from recycle bin if it was dropped.

The wait events for a conventional load with block new optimization show a relatively small amount of total time spent in "flashback log file sync" compared to other database waits:

Top 5 Timed Foreground Events

Looking at the instance statistics we see that the stats that track block new operations have significantly increased during the load.