How to check the FRA usage and troubleshoot the issue in oracle DB?
step1)Check the FRA usage.
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 ;
step2)Check the FRA occupants.
SELECT * FROM V$FLASH_RECOVERY_AREA_USAGE;
step3) Increase the FRA size or change the location of FRA path
ALTER SYSTEM SET db_recovery_file_dest_size=100G SCOPE=BOTH;
or
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST=’/u….’;
step4)If you want to clear the old archive logs you can run the archvie bkp job.
$./archive_bkp.sh