DB_RECOVERY_FILE_DEST_SIZE - Size Oracle Flash Recovery Area (FRA)

DB_RECOVERY_FILE_DEST_SIZE

Overview

DB_RECOVERY_FILE_DEST_SIZE sets the maximum disk space (in bytes) that Oracle is permitted to use for the Flash Recovery Area (FRA). The FRA is a unified storage location for all recovery-related files: RMAN backups, archived redo logs, flashback logs, control file autobackups, and online redo log copies. When usage approaches the configured limit, Oracle raises ORA-19815 warnings and — if the limit is reached — can halt archiving, which in turn causes the database to hang.

This parameter must be set whenever DB_RECOVERY_FILE_DEST is configured. Without a size limit, Oracle has no boundary to enforce and the parameter is meaningless. Together, these two parameters define the FRA: one specifies the location, the other the allowed size.

Parameter Type: Dynamic (ALTER SYSTEM) Default Value: None (no default; must be set explicitly when using the FRA) Valid Range: Minimum 1 MB; no defined upper limit beyond available storage Available Since: Oracle 10g Modifiable: Yes — ALTER SYSTEM (effective immediately, no restart required) PDB Modifiable: No — CDB-level parameter only; applies to the entire CDB

Configuration

Viewing Current Value

-- Current FRA location and size limit
SELECT name, value
FROM   v$parameter
WHERE  name IN ('db_recovery_file_dest', 'db_recovery_file_dest_size')
ORDER BY name;

-- SPFILE values
SELECT name, value
FROM   v$spparameter
WHERE  name IN ('db_recovery_file_dest', 'db_recovery_file_dest_size')
ORDER BY name;

-- Current FRA utilisation summary
SELECT space_limit                              AS limit_bytes,
       space_used                               AS used_bytes,
       space_reclaimable                        AS reclaimable_bytes,
       number_of_files                          AS file_count,
       ROUND(space_used / space_limit * 100, 2) AS pct_used,
       ROUND(space_limit / 1024/1024/1024, 2)   AS limit_gb,
       ROUND(space_used / 1024/1024/1024, 2)    AS used_gb
FROM   v$recovery_file_dest;

Setting the Parameter

-- Set FRA location and size (run together if FRA is not yet configured)
ALTER SYSTEM SET db_recovery_file_dest      = '/u02/fra'    SCOPE = BOTH;
ALTER SYSTEM SET db_recovery_file_dest_size = 200G          SCOPE = BOTH;

-- Increase the FRA size limit (common after storage expansion)
ALTER SYSTEM SET db_recovery_file_dest_size = 500G SCOPE = BOTH;

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

Values can be specified in bytes, kilobytes (K), megabytes (M), or gigabytes (G).

Tuning Guidance

Recommended Sizing

The FRA must accommodate all files Oracle places there within the retention window. The primary consumers are:

File Type	Typical Size Driver
Archived redo logs	Redo generation rate × archive retention window
RMAN backup sets / image copies	Database size × number of copies × compression ratio
Flashback logs	Flashback retention target × redo change rate
Control file autobackups	Small; negligible
Online redo log copies	2–3× redo log group size

A practical rule of thumb:

FRA size ≥ 2× database size (for two backup copies)
          + (daily redo volume × archive retention days)
          + (redo change rate × flashback retention hours / 3600)

How to Size Using V$RECOVERY_FILE_DEST and V$FLASH_RECOVERY_AREA_USAGE

-- Space breakdown by file type within the FRA
SELECT file_type,
       percent_space_used                                   AS pct_used,
       percent_space_reclaimable                           AS pct_reclaimable,
       number_of_files
FROM   v$flash_recovery_area_usage
ORDER BY percent_space_used DESC;

-- Estimate daily archived log volume (bytes generated per day)
SELECT trunc(completion_time)                               AS day,
       COUNT(*)                                             AS log_count,
       ROUND(SUM(blocks * block_size)/1024/1024/1024, 2)   AS gb_generated
FROM   v$archived_log
WHERE  completion_time >= sysdate - 7
  AND  standby_dest = 'NO'
GROUP BY trunc(completion_time)
ORDER BY day DESC;

-- Project total FRA requirement given RMAN retention and archive retention
-- (manual calculation helper)
SELECT ROUND(d.db_size_gb * 2, 0)                AS rman_backup_gb,
       ROUND(a.daily_archive_gb * a.arch_days, 0) AS archive_log_gb,
       ROUND(d.db_size_gb * 2
             + a.daily_archive_gb * a.arch_days, 0) AS recommended_fra_gb
FROM  (SELECT SUM(bytes)/1024/1024/1024 AS db_size_gb
         FROM dba_data_files)          d,
      (SELECT ROUND(AVG(gb), 2)        AS daily_archive_gb,
              7                        AS arch_days
         FROM (SELECT trunc(completion_time) AS dy,
                      SUM(blocks*block_size)/1024/1024/1024 AS gb
                 FROM v$archived_log
                WHERE completion_time >= sysdate - 7
                  AND standby_dest = 'NO'
                GROUP BY trunc(completion_time))) a;

Monitoring

Monitor FRA usage proactively to avoid ORA-19815 before it halts archiving:

-- Primary FRA usage monitor — run this in your DBA monitoring scripts
SELECT ROUND(space_limit/1024/1024/1024, 2)                AS limit_gb,
       ROUND(space_used/1024/1024/1024, 2)                 AS used_gb,
       ROUND(space_reclaimable/1024/1024/1024, 2)          AS reclaimable_gb,
       ROUND((space_used - space_reclaimable)/space_limit * 100, 1) AS net_pct_used,
       ROUND(space_used/space_limit * 100, 1)              AS gross_pct_used
FROM   v$recovery_file_dest;

-- Alert threshold: warn at 70%, critical at 85%
SELECT CASE
         WHEN space_used/space_limit >= 0.85 THEN 'CRITICAL: FRA > 85% full'
         WHEN space_used/space_limit >= 0.70 THEN 'WARNING: FRA > 70% full'
         ELSE 'OK'
       END AS fra_status,
       ROUND(space_used/space_limit * 100, 1) AS pct_used
FROM   v$recovery_file_dest;

-- Files currently stored in the FRA
SELECT name,
       file_type,
       ROUND(space_used/1024/1024, 1) AS size_mb,
       status,
       to_char(completion_time, 'DD-MON-YYYY HH24:MI') AS completed
FROM   v$recovery_area_usage
ORDER BY space_used DESC
FETCH FIRST 20 ROWS ONLY;

Common Issues

Issue 1: ORA-19815 — FRA 100% full, archiving suspended

Symptom: Alert log shows ORA-19815: WARNING: db_recovery_file_dest_size of N bytes is 100.00% used and foreground sessions hang waiting for archive log space.

Cause: Oracle cannot write new archived logs to the FRA because the size limit has been reached and no reclaimable space exists.

Immediate Resolution:

-- Step 1: Check what is consuming the space
SELECT file_type, percent_space_used, percent_space_reclaimable, number_of_files
FROM   v$flash_recovery_area_usage;

-- Step 2a: Temporarily increase the limit to unblock archiving immediately
ALTER SYSTEM SET db_recovery_file_dest_size = 300G SCOPE = BOTH;

-- Step 2b: OR delete obsolete RMAN backups via RMAN
-- RMAN> DELETE NOPROMPT OBSOLETE;
-- RMAN> DELETE NOPROMPT EXPIRED BACKUP;

-- Step 3: After immediate resolution, plan permanent sizing increase

Issue 2: Reclaimable space exists but Oracle does not reclaim it automatically

Symptom: V$FLASH_RECOVERY_AREA_USAGE.PERCENT_SPACE_RECLAIMABLE is non-zero, but the FRA is still full.

Cause: Oracle only reclaims FRA space automatically under specific conditions (RMAN backups that are obsolete per the retention policy). If backups were not registered with RMAN, or if the RMAN catalog is inconsistent, automatic reclaim may not trigger.

Resolution:

-- From RMAN, force a crosscheck and delete obsolete/expired files
-- RMAN> CROSSCHECK BACKUP;
-- RMAN> CROSSCHECK ARCHIVELOG ALL;
-- RMAN> DELETE NOPROMPT EXPIRED BACKUP;
-- RMAN> DELETE NOPROMPT OBSOLETE;

-- After RMAN cleanup, verify space was released
SELECT ROUND(space_used/1024/1024/1024, 2) AS used_gb,
       ROUND(space_reclaimable/1024/1024/1024, 2) AS reclaimable_gb
FROM   v$recovery_file_dest;

Issue 3: ORA-16038 — Archiver cannot archive log

Symptom: ORA-16038: log N sequence# M cannot be archived appears in the alert log.

Cause: The FRA is full and Oracle cannot create new archive log files. This cascades into a database stall because online redo logs cannot be reused until their contents are archived.

Resolution: This is the downstream consequence of Issue 1. Resolve the FRA full condition first (increase size limit or delete obsolete backups), then Oracle’s archiver (ARCn) will automatically retry and the stall will resolve.

DB_RECOVERY_FILE_DEST — Specifies the directory path for the FRA. Must be set alongside DB_RECOVERY_FILE_DEST_SIZE.
DB_FLASHBACK_RETENTION_TARGET — Sets how many minutes of Flashback Database logs to retain in the FRA; larger values increase FRA space consumption.
LOG_ARCHIVE_DEST_10 — When DB_RECOVERY_FILE_DEST is set, Oracle implicitly uses it as LOG_ARCHIVE_DEST_10; do not set this manually if using the FRA.

ORA-19815 — FRA full warning; the direct trigger when this parameter’s limit is reached.
ORA-16038 — Archiver cannot archive; cascades from a full FRA.

Version Notes

Version	Notes
Oracle 10g	FRA and this parameter introduced; replaced manual `LOG_ARCHIVE_DEST` management for many environments.
Oracle 11g	FRA space management improved; Oracle more aggressively reclaims reclaimable files when space pressure increases.
Oracle 12c	In a CDB, the FRA is shared across all PDBs; `DB_RECOVERY_FILE_DEST_SIZE` is a CDB-level parameter only.
Oracle 18c+	Fast Recovery Area (FRA) terminology is now preferred over “Flash Recovery Area” in documentation, though the parameter name is unchanged.
Oracle 19c+	No parameter changes; RMAN policy-based reclamation improvements reduce the risk of ORA-19815 in well-managed environments.