Flashback Database Logs can exceed the Retention Target

The documentation on the Flashback Retention Target in 11.2 and 12.1 states that this parameter specifies an upper limit on how far the database may be flashed back.

However, if the FRA (db_recovery_file_dest_size) is actually large enough, Oracle may retain flashback logs for a much longer duration.

SQL> alter session set nls_date_format='DD-MON-RR HH24:MI:SS';

Session altered.

SQL> select sysdate, l.* from v$flashback_database_log l;

SYSDATE            OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_T RETENTION_TARGET
------------------ -------------------- ------------------ ----------------
FLASHBACK_SIZE ESTIMATED_FLASHBACK_SIZE
-------------- ------------------------
29-OCT-16 07:42:44              6968261 28-OCT-16 22:35:50              180
     157286400                 86467584


SQL> 
SQL> show parameter flashback

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_flashback_retention_target        integer                                180
SQL> 
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size           2260088 bytes
Variable Size      750781320 bytes
Database Buffers   310378496 bytes
Redo Buffers         5517312 bytes
Database mounted.
SQL> flashback database to timestamp trunc(sysdate);

Flashback complete.

SQL>

Thus, it is useful to check the V$FLASHBACK_DATABASE_LOG, V$FLASHBACK_DATABASE_LOGFILE and V$FLASHBACK_DATABASE_STAT and V$FLASH_RECOVERY_AREA_USAGE views from time to time.

See Oracle Support Doc# 1194013.1 for a discrepancy between the first two views.

Note : If you have Standby database configured, also see John Hallas's blog post.
.
.
.