Flash recovery area was set quite big (1500GB) comparing to the database size (200GB) and we missed the fact that the flash area had a significant space used over the years.
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 .03 0 6 IMAGECOPY 13.13 0 17 FLASHBACKLOG 44.04 0 7019
44.04% used by FLASHBACKLOG - - -
PERCENT_SPACE_RECLAIMABLE - - - says nothing to delete from flash.
db_flashback_retention_target is one day. database size is 200GB. flashback log size 660GB - seems quite wasteful.
database control:
Current size of the flashback logs(GB) 660.563 Lowest SCN in the flashback data 1318377092383 Flashback Time Sep 10, 2010 5:18:35 PM
here we've got the key - flashback time - which indicates that there should be a restore point keeping all flashback before 2010.
select guarantee_flashback_database gua, storage_size, time, name from v$restore_point; GUA STORAGE_SIZE TIME NAME ------ ------------ ------------------------------- ----------------- YES 7.0927E+11 10-SEP-10 05.18.35.000000000 PM BEOFRE_DELTA_POST
So, it is the guaranteed restore point to 10-SEP-2010. We just drop it since no need for any purpose (at least in a new project);
SQL> DROP RESTORE POINT BEOFRE_DELTA_POST; Restore point dropped.
as a result old logs removed from flash area.
FILE_TYPE PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES ------------ ------------------ ------------------------- --------------- CONTROLFILE 0 0 0 ONLINELOG 0 0 0 ARCHIVELOG 0 0 0 BACKUPPIECE .03 0 6 IMAGECOPY 13.13 0 17 FLASHBACKLOG 0 0 1
Never forget guaranteed restore points. They are not being deleted automatically.
Thanks,
