Flashback Area Full

SQL> select * from V$FLASH_RECOVERY_AREA_USAGE;
FILE_TYPE        PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
----------------        ------------------------------------ ------------------------------------------------  ------------------------------
CONTROLFILE                   0                                                      0                                                    0
ONLINELOG                       0                                                      0                                                    0
ARCHIVELOG                     0                                                      0                                                    0
BACKUPPIECE                   0                                                      0                                                    0
IMAGECOPY                       0                                                      0                                                    0
FLASHBACKLOG             99.79                                                 0                                                 151
NOTE: The above example shows that the flashback area ONLY contains flashback logs and is currently 99.79% full.


SQL> select * from v$restore_point;


NAME                                 guarantee_flashback_database
=============================================================
BEFORE                                           YES

NOTE: If there are NO restore points created the above query will return NO rows.  The column guarantee_flashback_database shows whether it is a guaranteed restore point or just a normal restore point.  Both should be highlighted to the regional DBA team on transfer of the issue.

SQL> col FLASHBACK_SIZE format 9999999999999
SQL> col ESTIMATE_FLASHBACK_SIZE format 99999999999
SQL> select FLASHBACK_SIZE,RETENTION_TARGET, ESTIMATED_FLASHBACK_SIZE from v$flashback_database_log;

FLASHBACK_SIZE RETENTION_TARGET ESTIMATED_FLASHBACK_SIZE
-------------- ---------------- ------------------------
   85717303296             1440             346175422464


NOTE: Current DB standards are for a 24 hour flashback retention policy. The above shows that the flashback size is 85Gb but the required flashback area size is actually 85Gb+346Gb, so the flashback area / allocation is too small and flashback retention is not being met.  This requires further investigation.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
alter system set db_flashback_retention_target = 6;
alter system set db_recovery_file_dest_size =20g;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

For example if the db_flashback_retention_target is set to 60 and db_recovery_file_dest_size is set to 20 Gb and the Flashback area is almost full, reduce the db_flashback_retention_target to 5 minutes and db_recovery_file_dest_size to 16G to relieve the database. Use the syntax as below: