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.
In a RAC infrastructure, the FRA is shared shared among all of the instances of an Oracle RAC database. It’s located in a shared storage area and on an Oracle ASM disk group.
A best practice is to be generous on FRA size!
Compatibility: Oracle 18c, 12c, 11g, 10g
Author: Vincent FENOLL – Oracle DBA Montreal