ORA-16014: Log Not Archived, No Available Destinations - Fix Archive Failures

ORA-16014: Log Sequence Not Archived, No Available Destinations

Error Overview

Error Text: ORA-16014: log string sequence# string not archived, no available destinations

The ORA-16014 error occurs when Oracle’s archiver process (ARCn) attempts to archive a filled online redo log but cannot write the archived log to any configured destination. Because archiving is mandatory in ARCHIVELOG mode, the database will stall redo log switches and eventually suspend all DML when all online redo log groups are filled, making this a critical availability issue that demands immediate attention.

Common Causes

1. Archive Destination Full

• The filesystem or ASM diskgroup hosting LOG_ARCHIVE_DEST_n has run out of space
• Flash Recovery Area (FRA) has reached its DB_RECOVERY_FILE_DEST_SIZE limit
• Archived logs not being purged after successful backup, causing rapid space exhaustion
• Multiple databases sharing the same archive destination directory

2. All LOG_ARCHIVE_DEST_n Destinations in Error State

• A primary archive destination is down and no mandatory fallback exists
• Network connectivity loss to a remote standby causing the destination to enter DEFERRED state
• Destination directory permissions changed, preventing ARCn from writing
• All destinations marked DEFER or set with VALID_FOR clauses that exclude the current log type

3. Data Guard Standby Transport Failures

• Standby database unreachable and destination configured as MANDATORY
• Standby redo logs not created on standby, preventing apply
• DB_UNIQUE_NAME mismatch between primary and standby LOG_ARCHIVE_DEST_n configuration
• Network latency or packet loss causing repeated transport timeouts

4. VALID_FOR Misconfiguration

• All destinations configured with VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE) leaving no destination valid for the primary role
• Log type filters leaving online redo logs with no eligible destination
• Role transition after switchover leaving destination configuration inconsistent

5. ARCn Process Failure

• Archiver processes terminated due to OS-level signal or resource exhaustion
• Insufficient LOG_ARCHIVE_MAX_PROCESSES for the redo generation rate
• ARCn unable to start due to shared memory or file descriptor limits

Diagnostic Queries

Check Archive Destination Status

-- View all archive destinations and their current status
SELECT
  dest_id,
  dest_name,
  status,
  target,
  archiver,
  schedule,
  destination,
  error,
  db_unique_name,
  valid_role,
  valid_type
FROM v$archive_dest
WHERE dest_id <= 31
ORDER BY dest_id;

-- Detailed error information for failed destinations
SELECT
  dest_id,
  dest_name,
  status,
  error,
  fail_count,
  fail_sequence,
  fail_block
FROM v$archive_dest
WHERE status != 'INACTIVE'
  AND status != 'VALID'
ORDER BY dest_id;

Identify Unarchived Redo Logs

-- Find online redo logs that have not been archived
SELECT
  group#,
  sequence#,
  bytes / 1024 / 1024   AS size_mb,
  members,
  archived,
  status,
  first_change#,
  TO_CHAR(first_time, 'DD-MON-YYYY HH24:MI:SS') AS first_time
FROM v$log
ORDER BY sequence# DESC;

-- Check archiving history for the current day
SELECT
  sequence#,
  name,
  dest_id,
  archived,
  applied,
  TO_CHAR(first_time, 'DD-MON-YYYY HH24:MI:SS')  AS first_time,
  TO_CHAR(completion_time, 'DD-MON-YYYY HH24:MI:SS') AS completion_time,
  blocks * block_size / 1024 / 1024                AS size_mb
FROM v$archived_log
WHERE first_time > SYSDATE - 1
ORDER BY sequence# DESC
FETCH FIRST 50 ROWS ONLY;

Check Flash Recovery Area Space

-- FRA space 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
FROM v$flash_recovery_area_usage
ORDER BY space_used DESC;

-- Overall FRA configuration and current usage
SELECT
  name,
  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(100 * space_used / NULLIF(space_limit, 0), 1) AS pct_used
FROM v$recovery_file_dest;

Check ARCn Processes

-- Active archiver processes
SELECT
  process,
  status,
  log_sequence,
  state
FROM v$managed_standby
WHERE process LIKE 'ARC%'
ORDER BY process;

-- Also check v$bgprocess
SELECT
  name,
  description,
  error
FROM v$bgprocess
WHERE name LIKE 'ARC%'
  AND paddr != '00'
ORDER BY name;

Data Guard Transport Status

-- Standby destination transport lag (primary side)
SELECT
  dest_id,
  dest_name,
  status,
  target,
  archiver,
  transmit_mode,
  async_blocks,
  net_timeout,
  error,
  db_unique_name
FROM v$archive_dest
WHERE target = 'STANDBY'
ORDER BY dest_id;

-- Check Data Guard configuration (if broker is in use)
-- DGMGRL> SHOW CONFIGURATION VERBOSE;
-- DGMGRL> SHOW DATABASE VERBOSE '<standby_name>';

Step-by-Step Resolution

1. Free Space in the Archive Destination

If the FRA or a filesystem destination is full, reclaim space immediately:

-- Delete archived logs already backed up (RMAN)
-- Run in RMAN:
-- RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1' BACKED UP 2 TIMES TO DISK;
-- RMAN> DELETE EXPIRED ARCHIVELOG ALL;

-- Increase FRA size as an emergency measure
ALTER SYSTEM SET db_recovery_file_dest_size = 200G SCOPE=BOTH;

-- Alternatively, add a new FRA location
ALTER SYSTEM SET db_recovery_file_dest = '+RECO' SCOPE=BOTH;

At the OS level, if using a filesystem destination:

# Check filesystem usage
df -h /arch

# Remove archived logs that have been backed up
# Only do this if RMAN catalog confirms they are backed up:
# rman target / catalog rman/password@catdb
# RMAN> LIST ARCHIVELOG ALL;
# RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE - 2';

2. Re-enable a Failed Archive Destination

-- Re-enable a destination that Oracle deferred after repeated failures
ALTER SYSTEM SET log_archive_dest_state_2 = ENABLE SCOPE=BOTH;

-- Reset a destination entirely if the configuration is wrong
ALTER SYSTEM SET log_archive_dest_2 =
  'SERVICE=standby1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby1'
  SCOPE=BOTH;

ALTER SYSTEM SET log_archive_dest_state_2 = ENABLE SCOPE=BOTH;

-- Confirm the destination is now valid
SELECT dest_id, status, error FROM v$archive_dest WHERE dest_id = 2;

3. Fix VALID_FOR Configuration

-- Ensure at least one mandatory local destination covers online redo logs in primary role
ALTER SYSTEM SET log_archive_dest_1 =
  'LOCATION=USE_DB_RECOVERY_FILE_DEST
   VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
   DB_UNIQUE_NAME=primary1'
  SCOPE=BOTH;

ALTER SYSTEM SET log_archive_dest_state_1 = ENABLE SCOPE=BOTH;

-- Verify: at least one destination must be VALID and mandatory
SELECT
  dest_id,
  status,
  target,
  valid_role,
  valid_type,
  destination
FROM v$archive_dest
WHERE status = 'VALID'
ORDER BY dest_id;

4. Increase ARCn Processes

-- Increase maximum archiver processes (default is 4, max is 30)
ALTER SYSTEM SET log_archive_max_processes = 8 SCOPE=BOTH;

-- Verify processes started
SELECT process, status, log_sequence
FROM v$managed_standby
WHERE process LIKE 'ARC%'
ORDER BY process;

5. Manually Force Archiving After Resolving Root Cause

-- After fixing the destination, manually archive all unarchived logs
ALTER SYSTEM ARCHIVE LOG ALL;

-- Or archive a specific sequence
ALTER SYSTEM ARCHIVE LOG SEQUENCE 12345;

-- Verify all logs are now archived
SELECT sequence#, archived, status
FROM v$log
ORDER BY sequence#;

Prevention Strategies

1. Monitor Archive Destination Space

-- Create a monitoring query for FRA utilisation
SELECT
  ROUND(100 * space_used / NULLIF(space_limit, 0), 1) AS fra_pct_used,
  ROUND(space_reclaimable / 1024 / 1024 / 1024, 2)    AS reclaimable_gb,
  ROUND((space_limit - space_used + space_reclaimable) / 1024 / 1024 / 1024, 2) AS free_gb
FROM v$recovery_file_dest;

-- Alert when FRA exceeds 80% utilisation
-- Incorporate into a DBMS_SCHEDULER job that emails the DBA team

2. Configure Archive Destination Redundancy

-- Always configure at least one local and one remote destination
-- Primary local (mandatory)
ALTER SYSTEM SET log_archive_dest_1 =
  'LOCATION=USE_DB_RECOVERY_FILE_DEST
   VALID_FOR=(ALL_LOGFILES,ALL_ROLES)
   DB_UNIQUE_NAME=primary1'
  SCOPE=SPFILE;

-- Remote standby (async, optional for availability)
ALTER SYSTEM SET log_archive_dest_2 =
  'SERVICE=standby1
   ASYNC
   REOPEN=60
   MAX_FAILURE=5
   VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
   DB_UNIQUE_NAME=standby1'
  SCOPE=SPFILE;

3. RMAN Archive Log Deletion Policy

-- Set RMAN to automatically delete archived logs after backup
-- RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
-- RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON ALL STANDBY;

-- Schedule regular RMAN archive log maintenance
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'RMAN_ARCHLOG_CLEANUP',
    job_type        => 'EXECUTABLE',
    job_action      => '/u01/scripts/rman_archlog_cleanup.sh',
    repeat_interval => 'FREQ=HOURLY;INTERVAL=4',
    enabled         => TRUE,
    comments        => 'Delete archived logs backed up to tape and applied on standby'
  );
END;
/

4. Size the FRA Appropriately

• FRA should hold at least 3 days of archived logs plus a full backup
• As a starting point: DB_RECOVERY_FILE_DEST_SIZE = daily redo volume × 3 + full backup size
• Review v$flash_recovery_area_usage weekly and adjust as the database grows

Diagnostic Scripts

These Oracle Day by Day scripts can help with redo and archive log management:

• health.sql — Overall database health including archive log status
• db.sql — Database mode, log mode, and configuration overview

Related Errors

• ORA-00257 - Archiver error, connect internal only until freed
• ORA-16038 - Log sequence cannot be archived
• ORA-19815 - Flash Recovery Area full warning

Emergency Response

Quick Fixes

1. Immediately free FRA space using RMAN

   rman target /
   # RMAN> DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-1' BACKED UP 1 TIMES TO DISK;

2. Defer a failing remote destination so local archiving can proceed

   ALTER SYSTEM SET log_archive_dest_state_2 = DEFER SCOPE=MEMORY;
   ALTER SYSTEM ARCHIVE LOG ALL;

3. Increase FRA size in memory immediately

   ALTER SYSTEM SET db_recovery_file_dest_size = 500G SCOPE=MEMORY;

Post-Resolution Cleanup

-- Re-enable deferred destination after fixing the root cause
ALTER SYSTEM SET log_archive_dest_state_2 = ENABLE SCOPE=BOTH;

-- Synchronise the standby by shipping any gap sequences
ALTER SYSTEM ARCHIVE LOG ALL;

-- Confirm no log gaps remain
SELECT
  thread#,
  low_sequence#,
  high_sequence#
FROM v$archive_gap;

-- Review AWR for redo generation rate to re-size FRA
SELECT
  snap_id,
  ROUND(SUM(value) / 1024 / 1024, 2) AS redo_mb
FROM dba_hist_sysstat
WHERE stat_name = 'redo size'
GROUP BY snap_id
ORDER BY snap_id DESC
FETCH FIRST 24 ROWS ONLY;