LLI Wiki — My Digital Knowledge Base

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.../';
Mastodon LLIMastodon SocialFosstodon