Altering on low space for FRA

The view V$FLASH_RECOVERY_AREA_USAGE indicates how much space is used in the FRA as a percentage, and also how much of that space is reclaimable as a percentage.

If we make a note of the difference in these values we will have the ‘real’ value for used space as a percentage in the FRA so:

sum(PERCENT_SPACE_USED) - sum(PERCENT_SPACE_RECLAIMABLE) = Total Used space as a percentage

So if we then minus this value from 100 we have the Free space as a result

so 100 - (sum(PERCENT_SPACE_USED) - sum(PERCENT_SPACE_RECLAIMABLE)) = Total Free space as a percentage

Option 1

We can use this value to report the ‘Used’ non reclaimable space percentage, then monitoring software can be setup when to alert on a percentage full in 90s so someone can take action such as take an archivelog backup to release space.

select sum(PERCENT_SPACE_USED) - sum(PERCENT_SPACE_RECLAIMABLE) FRA_USED_PERCENT
from V$FLASH_RECOVERY_AREA_USAGE

Option 2

We can then use this value as the basis of monitoring for to alert when free space is at [n]% as below: this will alert when free space is less than ‘3’ percent, replace the value 3 with desired percentage, but 3 seems reasonable not to cause to many alerts and allow some time to run a backup or increase FRA or increase Vol size and FRA.

select (CASE WHEN FREE_SPACE <3 THEN 0 ELSE 1 END) as ALERT
from
(select 100 - (sum(PERCENT_SPACE_USED) - sum(PERCENT_SPACE_RECLAIMABLE)) FREE_SPACE from V$FLASH_RECOVERY_AREA_USAGE)

This SQL will report 0 for an alert and 1 for all ok, this can also be change if needed.