Flash Recover Area

FINDING ORACLE FLASH RECOVERY AREA USAGE

SELECT NAME,(SPACE_LIMIT / 1024 / 1024 / 1024) SPACE_LIMIT_GB,((SPACE_LIMIT - SPACE_USED + SPACE_RECLAIMABLE) / 1024 / 1024 / 1024) AS SPACE_AVAILABLE_GB,ROUND((SPACE_USED - SPACE_RECLAIMABLE) / SPACE_LIMIT * 100, 1) AS PERCENT_FULL 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, 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 ;

-- After that you can resize the FRA with:

-- ALTER SYSTEM SET db_recovery_file_dest_size=xxG scope=both;

-- 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....';