Flash/Fast Recovery Area (FRA) in Oracle Posted by MOHAMMED ABDUL HAMEED on 10:08 PM

Flash Recovery Area can be defined as a single, centralized, unified storage area that keep all the database backup & recovery related files and performs those activities in Oracle databases.

Related views
V$RECOVERY_FILE_DEST
V$FLASH_RECOVERY_AREA_USAGE
V$DBA_OUTSTANDING_ALERTS
V$FLASHBACK_DATABASE_LOGFILE

# Instance Name

select instance_name from v$instance;

# Used GB in FRA

select name

, floor(space_limit / 1024 / 1024/1024) "Size GB"

, ceil(space_used  / 1024 / 1024/1024) "Used GB"

from v$recovery_file_dest

order by name;

# FRA Occupants

SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;

SELECT NAME,

TO_CHAR(SPACE_LIMIT, '999,999,999,999') AS SPACE_LIMIT,

TO_CHAR(SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE, '999,999,999,999') AS SPACE_AVAILABLE,

ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1) AS PERCENT_FULL

FROM V$RECOVERY_FILE_DEST;

select * from v$Recovery_file_dest;

SELECT

ROUND((SPACE_USED - SPACE_RECLAIMABLE)/SPACE_LIMIT * 100, 1)

AS PERCENT_FULL

FROM V$RECOVERY_FILE_DEST;

# Location and size of the FRA

show parameter db_recovery_file_dest;

# Size, used, Reclaimable

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 ;