Monitoring the Oracle Fast Recovery Area (FRA)

1. Objective

The objective of monitoring the Fast Recovery Area (FRA) in Oracle is to:

• Prevent database outage caused by FRA saturation
• Avoid archive log generation failure (ORA-19809, ORA-19804)
• Ensure backups and flashback logs function properly
• Maintain database recoverability and high availability
• Optimize storage usage and retention policies

A full FRA can completely stop redo log archiving, which may lead to database freeze in ARCHIVELOG mode.

2. What is the Fast Recovery Area?

The Fast Recovery Area (FRA) is a centralized storage location defined by:

• DB_RECOVERY_FILE_DEST
• DB_RECOVERY_FILE_DEST_SIZE

It stores recovery-related files such as:

• Archived redo logs
• Flashback logs
• RMAN backups
• Control file autobackups
• Foreign archived logs

Oracle automatically manages file deletion inside FRA based on the retention policy and reclaimable space.

3. Key Parameters to Monitor

Check FRA location and configured size

SHOW PARAMETER db_recovery_file_dest;
SHOW PARAMETER db_recovery_file_dest_size;

Or:

SELECT name, value
FROM v$parameter
WHERE name IN ('db_recovery_file_dest', 'db_recovery_file_dest_size');

4. FRA Space Usage Monitoring Queries

4.1. Global FRA Usage

SELECT 
    name,
    ROUND(space_limit/1024/1024/1024,2) AS total_size_gb,
    ROUND(space_used/1024/1024/1024,2)  AS used_size_gb,
    ROUND(space_reclaimable/1024/1024/1024,2) AS reclaimable_gb,
    ROUND((space_limit - space_used)/1024/1024/1024,2) AS free_size_gb
FROM v$recovery_file_dest;

4.2. FRA Usage Percentage

SELECT 
    ROUND((space_used/space_limit)*100,2) AS used_percent,
    ROUND((space_reclaimable/space_limit)*100,2) AS reclaimable_percent
FROM v$recovery_file_dest;

4.3. FRA Usage by File Type

SELECT 
    file_type,
    percent_space_used,
    percent_space_reclaimable,
    number_of_files
FROM v$recovery_area_usage
ORDER BY percent_space_used DESC;

This helps identify whether archive logs, flashback logs, or backups are consuming space.

5. Recommended Alert Threshold

Usage %	Status	Action
< 70%	Normal	No action
70% – 85%	Warning	Check retention policy
85% – 95%	Critical	Backup & delete obsolete files
> 95%	Emergency	Immediate action required

6. Proactive Monitoring Query (Ready for Monitoring Tool)

SELECT 
    CASE 
        WHEN (space_used/space_limit)*100 > 90 THEN 'CRITICAL'
        WHEN (space_used/space_limit)*100 > 80 THEN 'WARNING'
        ELSE 'NORMAL'
    END AS status,
    ROUND((space_used/space_limit)*100,2) AS used_percent
FROM v$recovery_file_dest;

This query can be integrated into:

• OEM
• Zabbix
• Custom monitoring scripts
• Shell + SQL*Plus health checks

7. Best Practices

• Configure proper RMAN retention policy
• Schedule regular DELETE OBSOLETE
• Monitor archive log generation rate
• Size FRA based on backup strategy and flashback retention
• Enable alerts when usage exceeds 80%

8. Common Errors When FRA is Full

• ORA-19809: limit exceeded for recovery files
• ORA-19804: cannot reclaim space
• Archiver process stuck (database may hang)

9. Conclusion

Monitoring the Oracle Fast Recovery Area is critical for database stability and recoverability.
Proactive monitoring prevents production incidents and ensures continuous archiving and backup operations.