Oracle: Fast Recovery Area size, usage and occupants
Queries to measure the Oracle Fast Recovery Area (FRA), its reclaimable space and its occupants.
The Fast Recovery Area (FRA) stores the archived logs, the backups and the other recovery files of an Oracle database. When it is full, the database cannot archive its redo logs. The queries below show the size, the usage and the occupants of the FRA.
For a discussion of cleaning archived logs: Stack Exchange question.
Size and usage
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;
Configured location and size
show parameter db_recovery_file_dest
Used and reclaimable space
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 ;
Act on the FRA
Increase the FRA size:
ALTER SYSTEM SET db_recovery_file_dest_size=xxG;
Or change its location (new archived logs are written to the new location):
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='/u.../';