ORA-19815, Flashback Logs-Space management,

ORA-19815, Flashback Logs-Space management,

ORA-19815, Flashback Logs-Space management (Doc ID 369755.1)

Applicable:
Oracle Database-Enterprise Edition-Version 10.1.0.2 to 11.2.0.3 [Release 10.1 to 11.2]
Information in this document applies to any platform.
* ** Checked for relevance on 08-Jan-2014 ***

Objectives:
The recovery file dest was cracked by the flashback log, and a ora-19815 error was reported in the alert log. remove or reuse the flashback log (FBL)

Solution:
Flashback logs is internally processed by oracle, and DBAs cannot control flashback logs. This means that DBAs cannot manually delete flashback logs. the only method is to disable flashback.

The FlashBack Logs can ONLY be deleted by disabling flashback on database level
SQL> startup mount
Alter database flashback off;
Alter database open;

When space pressure occurs, the flashback log is deleted based on the following two conditions:

1) When an archived log is deleted, oracle will delete the flashback logs on which the archived log is dependent.

2) When quota is reduced, the flashback log is deleted to recycle disk space.
Reduce the value of DB_RECOVERY_FILE_DEST_SIZE to a specific value, so that the flashback log encounters space pressure. The old flashback log will be deleted.

We may encounter the following bug: the flashback log will not be deleted even if it encounters space pressure in fra.

Bug 5106952 FLASHBACK LOG SPACE NOT BEING RECLAIMED
Fixed In Ver: 11.0
Also the bug is fixed in patch set 10.1.0.6 and 10.2.0.3.

Although Bug 5106952 is fixed in 11g, this process can still be used to delete the flashback log

1.- First of all we need to check if there is any guaranteed restore point defined:     SQL> select name,scn,time,database_incarnation#,guarantee_flashback_database,storage_size from v$restore_point; 2.- Remove all the restore points     SQL> Drop restore point <name>;  3.- Check how much space is used before turning off Flashback     SQL> select * from v$flash_recovery_area_usage; 4.- SQL> ALTER DATABASE FLASHBACK OFF; 5.-  Now you can delete the Flashback logs manually 6.- SQL> ALTER DATABASE FLASHBACK ON; 7.- Check that there are no flashback logs before turning back on       SQL> select * from v$flash_recovery_area_usage;