1. Error Signatures and Symptoms

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

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

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';

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