ORA-19809: Recovery File Limit Exceeded - Manage FRA Space

ORA-19809: Limit Exceeded for Recovery Files

Error Overview

Error Text: ORA-19809: limit exceeded for recovery files

The ORA-19809 error is raised when Oracle cannot write a new file to the Fast Recovery Area (FRA) because doing so would exceed the space limit defined by DB_RECOVERY_FILE_DEST_SIZE. The FRA is a unified disk location that Oracle uses to store archive logs, RMAN backups, flashback logs, control file autobackups, and online log copies.

When the FRA reaches its configured size limit, Oracle stops writing archive logs to it. This causes the archiver process (ARCn) to stall, which in turn prevents redo log switches, which ultimately halts all DML on the database within minutes. ORA-19809 is therefore a critical error that demands immediate attention.

This error is closely related to ORA-19815, which is a warning message that appears in the alert log when FRA space usage crosses 85% or 97% thresholds — before ORA-19809 occurs. ORA-19809 is the hard failure after ORA-19815 warnings have been ignored.

Common Causes

1. FRA Size Limit Too Small for Backup Retention Policy

DB_RECOVERY_FILE_DEST_SIZE was set without accounting for the volume of archive logs generated per day, the RMAN backup retention policy, and flashback log requirements.

2. Archive Log Accumulation

Archive logs are accumulating in the FRA faster than they are being backed up or deleted. High-transaction databases can generate hundreds of gigabytes of archive logs per day.

3. RMAN Backups Not Cleaning Up Obsolete Files

RMAN backups are completing but DELETE OBSOLETE is not being run, or the retention policy is too long, causing old backups to remain in the FRA indefinitely.

4. Flashback Database Enabled with Insufficient Space

Flashback Database requires flashback logs stored in the FRA. In high-activity databases, flashback logs can consume enormous amounts of space if the flashback retention target is long.

5. Expired or Crosschecked Backups Not Deleted

RMAN has records of backup pieces in the FRA, but the actual files may have been manually deleted or moved. Without an RMAN CROSSCHECK and DELETE EXPIRED, Oracle still counts their catalog-recorded size against the FRA limit.

6. No Backup Job Running

RMAN backup jobs have stopped running (scheduler failure, job disabled) and archive logs continue to accumulate with nothing removing them.

Diagnostic Queries

Check FRA Usage

-- Current FRA space usage and limit
SELECT space_limit/1024/1024/1024        AS limit_gb,
       space_used/1024/1024/1024         AS used_gb,
       space_reclaimable/1024/1024/1024  AS reclaimable_gb,
       ROUND(space_used / space_limit * 100, 1) AS pct_used,
       number_of_files
FROM v$recovery_file_dest;

-- FRA parameters
SHOW PARAMETER db_recovery_file_dest;

Check FRA Usage by File Type

-- Breakdown of FRA usage by file type
SELECT file_type,
       ROUND(space_used/1024/1024/1024, 2)        AS used_gb,
       ROUND(space_reclaimable/1024/1024/1024, 2) AS reclaimable_gb,
       number_of_files,
       percent_space_used,
       percent_space_reclaimable
FROM v$recovery_area_usage
ORDER BY space_used DESC;

Check Archive Log Accumulation

-- Archive logs in FRA by day (last 7 days)
SELECT TRUNC(first_time, 'DD')          AS log_date,
       COUNT(*)                          AS log_count,
       ROUND(SUM(blocks * block_size)/1024/1024/1024, 2) AS size_gb
FROM v$archived_log
WHERE dest_id = (SELECT dest_id FROM v$archive_dest
                 WHERE target = 'PRIMARY' AND dest_name = 'LOG_ARCHIVE_DEST_1'
                 AND status = 'VALID'
                 FETCH FIRST 1 ROWS ONLY)
  AND standby_dest = 'NO'
  AND deleted = 'NO'
  AND first_time > SYSDATE - 7
GROUP BY TRUNC(first_time, 'DD')
ORDER BY log_date DESC;

Check RMAN Backup Retention and Obsolete Status

-- RMAN retention policy
SELECT value FROM v$rman_configuration WHERE name = 'RETENTION POLICY';

-- Obsolete backups (must be run via RMAN, not SQL)
-- RMAN> REPORT OBSOLETE;
-- RMAN> LIST BACKUP SUMMARY;

Check Flashback Log Usage

-- Flashback log space usage in FRA
SELECT file_type, space_used/1024/1024/1024 AS used_gb
FROM v$recovery_area_usage
WHERE file_type = 'FLASHBACK LOG';

-- Flashback retention target
SHOW PARAMETER db_flashback_retention_target;

-- Is flashback database enabled?
SELECT flashback_on FROM v$database;

Check for ORA-19815 Warnings in Alert Log

-- Recent space-related messages in alert log
SELECT originating_timestamp, message_text
FROM v$diag_alert_ext
WHERE message_text LIKE '%ORA-19815%'
   OR message_text LIKE '%ORA-19809%'
   OR message_text LIKE '%recovery area%'
ORDER BY originating_timestamp DESC
FETCH FIRST 20 ROWS ONLY;

Step-by-Step Resolution

1. Immediate Action — Check and Reclaim Space via RMAN

Connect to RMAN and run the following sequence:

rman target /

-- Step 1: Crosscheck all backup pieces and archive logs
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;

-- Step 2: Delete expired backups (catalog entries with no physical files)
DELETE EXPIRED BACKUP;
DELETE EXPIRED ARCHIVELOG ALL;

-- Step 3: Delete obsolete backups per retention policy
DELETE OBSOLETE;

-- Step 4: Confirm remaining FRA usage
-- (Check v$recovery_file_dest after exiting RMAN)

2. Delete Backed-Up Archive Logs

If archive logs have already been backed up and the backup is outside the recovery window:

-- Delete archive logs backed up 2+ times and older than 2 days
DELETE ARCHIVELOG ALL BACKED UP 2 TIMES TO DISK
  COMPLETED BEFORE 'SYSDATE-2';

-- Or delete all archive logs older than the recovery window
DELETE ARCHIVELOG UNTIL TIME 'SYSDATE - 7';

3. Increase FRA Size (DB_RECOVERY_FILE_DEST_SIZE)

-- Increase FRA size to provide immediate relief
ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 200G SCOPE=BOTH;

-- Verify the change took effect
SHOW PARAMETER db_recovery_file_dest_size;

SELECT space_limit/1024/1024/1024 AS new_limit_gb,
       ROUND(space_used / space_limit * 100, 1) AS new_pct_used
FROM v$recovery_file_dest;

4. Move Archive Logs Out of the FRA

If the database has a second archive log destination outside the FRA:

-- Add a second archive log destination outside the FRA
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2 =
  'LOCATION=/u02/archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES)' SCOPE=BOTH;

ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2 = ENABLE SCOPE=BOTH;

-- Optional: stop archiving TO the FRA while the second dest catches up
-- (Leave LOG_ARCHIVE_DEST_1 pointing to FRA, but set it OPTIONAL)
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 =
  'LOCATION=USE_DB_RECOVERY_FILE_DEST OPTIONAL' SCOPE=BOTH;

5. Reduce Flashback Retention (If Flashback is Consuming Too Much Space)

-- Check current flashback retention (in minutes)
SHOW PARAMETER db_flashback_retention_target;

-- Reduce flashback retention
ALTER SYSTEM SET DB_FLASHBACK_RETENTION_TARGET = 60 SCOPE=BOTH;  -- 1 hour

-- If flashback database is not needed, disable it to free all flashback log space
-- Requires MOUNT state:
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE NOFLASHBACK;
ALTER DATABASE OPEN;

6. Identify and Remove Manually-Deleted Files from RMAN Catalog

-- If files were manually deleted (bypassing RMAN), sync the catalog
CROSSCHECK BACKUP;
CROSSCHECK COPY;
CROSSCHECK ARCHIVELOG ALL;

-- Remove all expired entries
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED COPY;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;

7. Verify Archiver Is No Longer Stuck

-- After freeing space, check that archiver processes are running
SELECT process, status, log#, sequence#
FROM v$managed_standby
WHERE process LIKE 'ARC%';

-- Or check ARCn processes
SELECT name, description, pname, state
FROM v$bgprocess
WHERE name LIKE 'ARC%' AND paddr != '00';

-- Verify the archive log gap (next sequence to be archived)
SELECT dest_name, status, target, archiver, schedule,
       destination, archived_seq#
FROM v$archive_dest
WHERE status = 'VALID' AND target = 'PRIMARY';

Prevention Strategies

1. Right-Size FRA Based on Retention Requirements

-- FRA sizing formula:
-- DB_RECOVERY_FILE_DEST_SIZE = (daily archive volume × retention days)
--                             + (backup size × backup copies retained)
--                             + (flashback log size per day × flashback days)
--                             + 20% overhead

-- Query to estimate daily archive log volume
SELECT ROUND(AVG(daily_gb), 2) AS avg_daily_archive_gb,
       ROUND(MAX(daily_gb), 2) AS max_daily_archive_gb
FROM (
  SELECT TRUNC(first_time, 'DD') AS log_date,
         SUM(blocks * block_size)/1024/1024/1024 AS daily_gb
  FROM v$archived_log
  WHERE first_time > SYSDATE - 30
    AND standby_dest = 'NO'
  GROUP BY TRUNC(first_time, 'DD')
);

2. Schedule Regular RMAN Maintenance

-- RMAN maintenance script (run daily after backup)
CROSSCHECK BACKUP;
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED BACKUP;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
DELETE NOPROMPT OBSOLETE;

3. Set Up OEM or Script-Based FRA Monitoring

-- Alert when FRA exceeds 70% capacity
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'MONITOR_FRA_USAGE',
    job_type        => 'PLSQL_BLOCK',
    job_action      => q'[
      DECLARE
        v_pct NUMBER;
      BEGIN
        SELECT ROUND(space_used / space_limit * 100, 1)
        INTO v_pct
        FROM v$recovery_file_dest;

        IF v_pct >= 70 THEN
          INSERT INTO dba_alerts(alert_time, alert_msg)
          VALUES (SYSDATE, 'FRA at ' || v_pct || '% — ORA-19809 risk. Run RMAN DELETE OBSOLETE.');
          COMMIT;
        END IF;
      END;
    ]',
    repeat_interval => 'FREQ=HOURLY',
    enabled         => TRUE
  );
END;
/

4. Configure Archive Log Deletion Policy in RMAN

-- Tell RMAN it is safe to delete archive logs backed up to tape
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO SBT;

-- For disk-only environments, delete after backed up twice to disk
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;

5. Best Practices

Set DB_RECOVERY_FILE_DEST_SIZE to at least 3× daily archive log volume
Always monitor ORA-19815 warnings proactively — treat them as pre-failure alerts
Run RMAN DELETE OBSOLETE as the last step in every backup job
Use a second archive log destination outside the FRA as an overflow safety net
Review and update DB_RECOVERY_FILE_DEST_SIZE quarterly as database growth trends change

ORA-19815 - Flash Recovery Area Warning (space usage threshold)
ORA-00257 - Archiver Error (archiver stuck when FRA is full)
ORA-01652 - Unable to Extend Temp Segment
ORA-01653 - Unable to Extend Table
ORA-16038 - Log Sequence Cannot Be Archived

Emergency Response

Quick Fixes

Free space immediately with RMAN

CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
DELETE NOPROMPT OBSOLETE;
DELETE ARCHIVELOG ALL BACKED UP 1 TIMES TO DISK COMPLETED BEFORE 'SYSDATE-1';

Increase FRA limit without restarting

ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 300G SCOPE=BOTH;

Check ARCn processes resumed archiving

SELECT process, status, sequence# FROM v$managed_standby
WHERE process LIKE 'ARC%';

Post-Resolution Cleanup

-- Confirm FRA is now below 70%
SELECT ROUND(space_used / space_limit * 100, 1) AS pct_used,
       space_reclaimable/1024/1024/1024 AS reclaimable_gb
FROM v$recovery_file_dest;

-- Verify archive logs are successfully being written
SELECT MAX(sequence#), MAX(first_time)
FROM v$archived_log
WHERE standby_dest = 'NO' AND deleted = 'NO';

-- Review daily archive log growth trend
SELECT TRUNC(first_time,'DD') AS log_date,
       ROUND(SUM(blocks*block_size)/1024/1024/1024,2) AS daily_gb
FROM v$archived_log
WHERE first_time > SYSDATE - 14 AND standby_dest = 'NO'
GROUP BY TRUNC(first_time,'DD')
ORDER BY log_date;

-- Update DB_RECOVERY_FILE_DEST_SIZE in SPFILE based on trend analysis
-- ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = <new_value>G SCOPE=SPFILE;