<\!DOCTYPE html>
📦 Archive Log Emergency

# Oracle Archive Log Destination Full Runbook

⏱ 5–20 min recovery ARCHIVELOG mode databases Updated 2026-05-15
🔴 Database is hanging right now?
If the database has stopped accepting new transactions (sessions hanging, apps unresponsive), jump to Step 3: Emergency RMAN Cleanup immediately. The database will resume within seconds of freeing space.

## 1. Error Signatures and Symptoms

Two errors typically surface when the archive log destination is full:

ORA-19809 / ORA-19804
`ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 52428800 bytes disk space from 107374182400 limit`
The Fast Recovery Area (FRA) has hit its size limit. These errors appear when Oracle cannot write new archive logs into the FRA.
ORA-00257
`ORA-00257: archiver error. Connect internal only, until freed.`
The archiver (ARCn) process cannot write archive logs. Only SYSDBA connections are allowed. All other sessions will hang waiting for archive log space.

Other signs:

- Alert log: `ARC0: Archival stopped, error occurred. Will continue retrying`
- Alert log: `ARCH: Archival stopped, error occurred`
- All application sessions hanging indefinitely
- OS-level: `df -h` shows archive destination filesystem at 100%
- TuneVault health check flag: archive destination usage > 85%

## 2. Diagnose the Archive Destination

### 2a. Check FRA usage

    -- FRA space usage breakdown
    SELECT
      space_limit/1073741824                    AS fra_size_gb,
      space_used/1073741824                     AS used_gb,
      space_reclaimable/1073741824              AS reclaimable_gb,
      ROUND((space_used - space_reclaimable) / space_limit * 100, 1) AS pct_truly_used
    FROM v$recovery_file_dest;

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

### 2b. Check archive log status

    -- Archive logs in FRA and their status
    SELECT
      name,
      ROUND(blocks * block_size / 1048576, 1) AS size_mb,
      archived,
      status,
      next_time
    FROM v$archived_log
    WHERE standby_dest = 'NO'
      AND name IS NOT NULL
    ORDER BY next_time DESC
    FETCH FIRST 20 ROWS ONLY;

    -- How many archive logs exist and their total size
    SELECT
      COUNT(*) AS num_arclogs,
      ROUND(SUM(blocks * block_size)/1073741824, 2) AS total_gb,
      MIN(next_time) AS oldest,
      MAX(next_time) AS newest
    FROM v$archived_log
    WHERE standby_dest = 'NO'
      AND name IS NOT NULL
      AND status \!= 'D';

### 2c. Check archive destination if NOT using FRA

    -- Check archive log destination parameters
    SHOW PARAMETER log_archive_dest;
    SHOW PARAMETER log_archive_dest_1;

    -- Check OS-level filesystem space (run as oracle OS user)
    -- df -h /u01/app/oracle/archivelog

## 3. Emergency RMAN Cleanup

⚠️ Before deleting archive logs
Confirm all archive logs have been applied to any standby databases or Data Guard standbys. Deleting unshipped arclogs will break replication. Check `v$archived_log` where `applied = 'NO'` before proceeding.

### 3a. Crosscheck and delete obsolete backups (safest first step)

    -- Connect to RMAN as target
    -- rman target /

    CROSSCHECK ARCHIVELOG ALL;
    DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;

    -- Delete archive logs already backed up (safe)
    DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 1 TIMES TO DISK;

### 3b. Delete archive logs older than N hours/days

    -- Delete archive logs older than 24 hours (adjust to your RPO)
    DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1';

    -- Delete archive logs older than 7 days
    DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';

ℹ️ "NOPROMPT" flag
The `NOPROMPT` flag skips the "are you sure?" confirmation. Remove it on your first run if you want to preview what will be deleted before committing.

### 3c. If FRA is completely full — force delete to unblock DB

    -- Last resort: delete ALL archive logs (DB is down, no standby)
    -- This will prevent point-in-time recovery to times covered by deleted logs
    DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE SYSDATE;

    -- After deletion, release the FRA space Oracle tracks
    CROSSCHECK BACKUP;
    DELETE NOPROMPT EXPIRED BACKUP;

### 3d. If OS filesystem is full (archive logs outside FRA)

    -- Run as OS oracle user — check filesystem
    -- df -h /u01/app/oracle/archivelog

    -- List archive logs by size, oldest first
    -- ls -lhrt /u01/app/oracle/archivelog/

    -- NEVER manually delete archive log files from the OS
    -- Always use RMAN crosscheck + delete so Oracle's catalog stays in sync
    CROSSCHECK ARCHIVELOG ALL;
    DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-2';

## 4. Set a Proper Retention Policy

Without a retention policy, RMAN keeps archive logs indefinitely. Set one now to prevent recurrence:

    -- Connect to RMAN

    -- Option A: Retain based on recovery window (recommended)
    -- Keep enough to recover to any point in the last 7 days
    CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

    -- Option B: Retain based on redundancy (number of backup copies)
    CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

    -- Option C: Explicitly delete archived logs after successful backup
    CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;

## 5. FRA Configuration Best Practices

### 5a. Increase FRA size

    -- Check current FRA location and size
    SHOW PARAMETER db_recovery_file_dest;
    SHOW PARAMETER db_recovery_file_dest_size;

    -- Increase FRA size (online, no restart needed)
    ALTER SYSTEM SET db_recovery_file_dest_size = 50G SCOPE=BOTH;

    -- Move FRA to a larger filesystem
    ALTER SYSTEM SET db_recovery_file_dest = '/u02/fast_recovery_area' SCOPE=BOTH;

### 5b. Size the FRA correctly

A correctly sized FRA should hold:

- One full database backup
- All archive logs since the last backup
- Incremental backups within the recovery window
- Flashback logs (if flashback database is enabled)

Rule of thumb: FRA size = 3× the size of your database if you run daily backups with a 7-day recovery window.

    -- Calculate minimum recommended FRA size
    SELECT
      ROUND(SUM(bytes)/1073741824, 1) AS db_size_gb,
      ROUND(SUM(bytes)/1073741824 * 3, 1) AS recommended_fra_gb
    FROM dba_data_files;

## 6. Verify Archiving Has Resumed

    -- Force a log switch to test archiving is working
    ALTER SYSTEM SWITCH LOGFILE;

    -- Wait 10 seconds, then verify the log was archived
    SELECT sequence#, archived, status
    FROM v$log
    ORDER BY sequence# DESC;

    -- Check archiver process status
    SELECT status, log_sequence, process
    FROM v$archive_processes
    WHERE status \!= 'IDLE'
    ORDER BY log_sequence DESC;

    -- Confirm no more ORA-00257 in alert log
    SELECT originating_timestamp, message_text
    FROM v$diag_alert_ext
    WHERE message_text LIKE '%ORA-00257%'
      OR message_text LIKE '%Archival stopped%'
    ORDER BY originating_timestamp DESC
    FETCH FIRST 10 ROWS ONLY;

## 7. Prevention: Monitoring and Automation

Archive log destination full is entirely preventable with the right monitoring. The typical failure sequence is:

1. FRA or archive destination grows past 85% — no alert fires
2. FRA hits 100% — archiver stops, database hangs
3. On-call DBA gets paged by angry users 15 minutes later

✅ Prevention checklist

- RMAN retention policy configured (recovery window or redundancy)
- FRA sized at 3× database size minimum
- Alert on FRA usage > 70% (warning) and > 85% (critical)
- Automated RMAN backup with `DELETE INPUT` option
- Weekly RMAN crosscheck to remove expired entries
- If using standby: monitor `v$archived_log where applied = 'NO'`

### Catch archive log pressure before it pages you

TuneVault monitors FRA utilisation and archive log destination space across all your Oracle instances, with threshold alerts at 70% and 85%.

Run a Free Health Check →