Oracle flashback logs are not deleting

Oracle flashback logs are not deleting

SQL> select name,open_mode,flashback_on from v$database

We have issue on flash_recovery_area_usage usage is 100%,
So we thought to remove the logs and we can use below option to remove logs rather than OS level.

To see the current size of db_recovery_file_dest_size and location.

SQL> show parameter db_recovery

To see % used

SQL>select space_used/(1024*1024*1024),space_limit/(1024*1024*1024) from v$recovery_file_dest;

SQL> select * from v$restore_point;

no rows selected

SQL> select sum(FLASHBACK_SIZE)/1024/1024/1024 from V$FLASHBACK_DATABASE_LOG;

To who is using more % on the above

SQL> select * from v$flash_recovery_area_usage;

TO delete the flashback log

Reduce the db_recovery_file_dest_size,

alter system set db_recovery_file_dest_size=100G;

In other session when you tail the alert log you will see below message.

Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_1.3351.896398699
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_4.8287.896533879
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_2.9436.896398703
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_5.7796.897271709
Deleted Oracle managed file +FLASH/prod/FLASHBACK/log_6.7771.897273413

++ As well as if needed we can increase the db_recovery_file_dest_size to as per cycle we can keep.

SELECT scn, database_incarnation#, guarantee_flashback_database, storage_size, time, name FROM gv$restore_point;

Check if you have any guarantee flash back point,If so Please cross check and remove


( Reference from  http://oracletechdba.blogspot.com)