Thursday, 8 January 2015

Oracle flashback logs not being deleted

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;

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 +ASM_FRA01/flashback/log_3924.10318.866631799
Deleted Oracle managed file +ASM_FRA01/flashback/log_3925.7536.866631803
Deleted Oracle managed file +ASM_FRA01/flashback/log_3926.751.866631807
Deleted Oracle managed file +ASM_FRA01/flashback/log_3927.1666.866631809
Deleted Oracle managed file +ASM_FRA01/flashback/log_3928.14699.866631813


++ 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.

No comments: