Check FRA location utilization

Sometimes FRA runs our of space and a DBA must be able to gather FRA space utilization. It is very important to monitor space usage in the fast recovery area to ensure that it is large enough to contain backups and other recovery-related files.

Find Oracle FRA Size

Below script gives you Fast Recovery Area utilization details

set linesize 500
col NAME for a50
select name, ROUND(SPACE_LIMIT/1024/1024/1024,2) "Allocated Space(GB)", 
round(SPACE_USED/1024/1024/1024,2) "Used Space(GB)",
round(SPACE_RECLAIMABLE/1024/1024/1024,2) "SPACE_RECLAIMABLE (GB)" ,
(select round(ESTIMATED_FLASHBACK_SIZE/1024/1024/1024,2) 
from V$FLASHBACK_DATABASE_LOG) "Estimated Space (GB)"
from V$RECOVERY_FILE_DEST;

Estimate Flashback Destination Size

Sometimes application team will ask DBA to enable flashback for x number of days. In such case, a DBA needs to estimate the flashback space required for x number of days in order to store the flashback logs. The flashback log size is same as archive log size generated in a database.

Check archive generation size via below query

select to_char(COMPLETION_TIME,'DD-MON-YYYY') Arch_Date,count(*) No#_Logs,
sum((BLOCKS*512)/1024/1024/1024) Arch_LogSize_GB 
from v$archived_log 
where to_char(COMPLETION_TIME,'DD-MON-YYYY')>=trunc(sysdate-7) and DEST_ID=1
group by to_char(COMPLETION_TIME,'DD-MON-YYYY')
order by to_char(COMPLETION_TIME,'DD-MON-YYYY');

Let us assume the average archive log files size generated per day is 5 GB. Your application team wants you to enable FLASHBACK for 4 days. In this case you will need Approx 20 GB (5GB X 4Days) of space for flashback logs