Oracle and Flashback Retention

Interestingly, I had a database that was running a large load of data.  I was running across issues with space.


On further investigation the flashback area was filling up.  After several iterations of setting db_flashback_retention_target lower and lower.   I tried to set db_flashback_retention_target to 0 but found setting the parameter this low had no affect. After some digging around I have found the hidden parameter - '_minimum_db_flashback_retention' which was is set to 60 (which is minutes).


All explained in -FRA: Flashback logs are not being deleted when space is needed for archive logs [ID 1481739.1]

Applies to:

Oracle Database - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.

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 /u01/home/oracle/diag/rdbms/dcdi01p/orcl/trace/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 '+DG_FLASH'

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
---------------------------------------------------- ---------- ---------- ---------- ---------- ------------- --------------------
+DG_FLASH/test/flashback/log_1.261.782829073  1          2          6          15941632   1048169       10-MAY-2012 10:00:04  lowest log sequence# listed
...
+DG_FLASH/test/flashback/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.

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:
  FLASHBACK_RETENTION_PERIOD = 1440 (1 DAY) since we stop purging when there is only 1 hour of flashback logs left