Fast Recovery Area is used for various components such as duplicates of control files and redo logs, backups, archive logs and flashback logs. If a database is in ARCHIVELOG mode and there is no space left in FRA, the database aborts any connections. Only SYS (SYSDBA) user can connect.
The space used can be checked with the following statement:
select
name,
floor(space_limit / 1024 / 1024) "Size MB",
ceil(space_used / 1024 / 1024) "Used MB"
from v$recovery_file_dest;
OR
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 ;
Space used can be further analyzed with the following query:
SELECT * FROM V$RECOVERY_AREA_USAGE;
Mind that there is reclaimable space and this will be overwritten automatically when needed, They are mostly used from flashback logs.
If the FRA is full then perhaps some archive logs can be deleted to free some space. Another quick option to resolve the connection errors is to resize FRA to a bigger size, if there is space available on the disk hosting it.
ALTER SYSTEM SET db_recovery_file_dest_size= xxxx scope=memory;
If there is no disk available then the quickest way may be to change the location of the FRA temporarily:
ALTER SYSTEM SET db_recovery_file_dest= '/u01/xxxx' scope=memory;
You can use RMAN to delete archive logs from FRA. For example the following RMAN command deletes all archives that are older than 24 hours:
delete archivelog from time 'SYSDATE-1';