Download Brave : Secure, Fast & Private Browser with Adblocker

Sometimes the Flash Recovery Area (FRA) is full and the Oracle DBA wants to know what is it’s usage, size and the list of occupants (archives, RMAN backups pieces or image copies, flashback logs).

To Enable  or Disable the FRA, it’s in another post here.

-- Use (MB) of FRA
set lines 100
col name format a60

select 
   name,
  floor(space_limit / 1024 / 1024) "Size MB",
  ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest;

-- FRA Occupants
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

-- Location and size of the FRA
show parameter db_recovery_file_dest

-- Size, usage, Reclaimable space used 
SELECT 
  ROUND((A.SPACE_LIMIT / 1024 / 1024 / 1024), 2) AS FLASH_IN_GB, 
  ROUND((A.SPACE_USED / 1024 / 1024 / 1024), 2) AS FLASH_USED_IN_GB, 
  ROUND((A.SPACE_RECLAIMABLE / 1024 / 1024 / 1024), 2) AS FLASH_RECLAIMABLE_GB,
  SUM(B.PERCENT_SPACE_USED)  AS PERCENT_OF_SPACE_USED
FROM 
  V$RECOVERY_FILE_DEST A,
  V$FLASH_RECOVERY_AREA_USAGE B
GROUP BY
  SPACE_LIMIT, 
  SPACE_USED , 
  SPACE_RECLAIMABLE ;

-- After that you can resize the FRA with:
-- ALTER SYSTEM SET db_recovery_file_dest_size=xxG;

-- Or change the FRA to a new location (new archives will be created to this new location):
-- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u....';

It is important to monitor regularly space usage in the fast recovery area. The Oracle DBA must make sure that the FRA is large enough to contain backups and other recovery-related files.

If the PERCENT_FULL value (V$RECOVERY_FILE_DEST) is frequently close to 100% after several recent backups; consider allocating more space for your flash recovery area or decrease the retention time of your backups.

Oracle Database provides two views to monitor fast recovery area space usage, V$RECOVERY_FILE_DEST and V$RECOVERY_AREA_USAGE.

A best practice is to be generous on FRA size!

Compatibility: Oracle 18c, 12c, 11g, 10g
Author: Vincent FENOLL – Oracle DBA Montreal