ORA-16038 Log Sequence Cannot Be Archived - Data Guard Archive Management

ORA-16038: Log Sequence [sequence] Cannot Be Archived

Error Overview

Error Text: ORA-16038: log sequence [sequence] cannot be archived

This critical error occurs when Oracle cannot archive a redo log sequence, often in Data Guard environments or when archive destinations become unavailable. ORA-16038 can cause database instance crashes, system state dumps, and break disaster recovery capabilities if not resolved promptly.

Understanding Archive Log Management

Archive Process Architecture

Archive Log Flow
├── Primary Database
│   ├── Online Redo Logs
│   ├── Archive Process (ARC0-ARCn)
│   ├── Local Archive Destination
│   └── Remote Archive Destinations
├── Data Guard Standby
│   ├── Remote File Server (RFS)
│   ├── Managed Recovery Process (MRP)
│   └── Standby Archive Logs
└── Network Transport
    ├── SYNC/ASYNC Mode
    ├── LGWR/ARCH Process
    └── NET_TIMEOUT Settings

Critical Dependencies

• Archive destinations must be accessible and have space
• Network connectivity for remote destinations
• Storage availability for local archiving
• Data Guard configuration for standby databases

Common Causes

1. Data Guard Configuration Issues

• Standby database offline or inaccessible
• Network connectivity problems between primary and standby
• Standby database lag preventing log acceptance
• Service name resolution failures

2. Storage and Space Problems

• Archive destination filesystem full
• Insufficient disk space for archive logs
• Permission issues on archive directories
• NFS mount failures for remote storage

3. Network and Connectivity Issues

• Network partitions between sites
• TNS listener problems on standby
• Firewall blocking archive log shipping
• Network timeout configuration issues

4. Configuration Mismatches

• Archive destination parameters misconfigured
• LOG_ARCHIVE_DEST_n settings incorrect
• Data Guard broker configuration errors
• Service registration problems

Diagnostic Steps

1. Check Archive Destination Status

-- Check all archive destinations
SELECT
  dest_id,
  dest_name,
  status,
  binding,
  target,
  destination,
  error,
  fail_sequence,
  fail_date
FROM v$archive_dest
WHERE status != 'INACTIVE'
ORDER BY dest_id;

-- Check archive destination details
SELECT
  dest_id,
  applied_scn,
  applied_time,
  database_mode,
  recovery_mode,
  protection_mode,
  synchronized
FROM v$archive_dest_status
WHERE dest_id IN (
  SELECT dest_id FROM v$archive_dest WHERE status = 'ERROR'
);

-- Check current archiving status
SELECT
  sequence#,
  first_change#,
  next_change#,
  archived,
  applied,
  deleted,
  status,
  completion_time
FROM v$archived_log
WHERE sequence# > (SELECT MAX(sequence#) - 10 FROM v$archived_log)
ORDER BY sequence# DESC;

2. Analyze Data Guard Configuration

-- Check Data Guard configuration
SELECT
  database_role,
  protection_mode,
  protection_level,
  switchover_status,
  dataguard_broker
FROM v$database;

-- Check standby database status
SELECT
  dest_id,
  standby_logfile_count,
  standby_logfile_active,
  archived_seq#,
  applied_seq#,
  applied_scn,
  apply_lag,
  transport_lag
FROM v$archive_dest_status
WHERE dest_id > 1;

-- Check Data Guard broker status (if enabled)
SELECT
  severity,
  error_code,
  message,
  timestamp
FROM v$dataguard_status
WHERE timestamp > SYSDATE - 1
ORDER BY timestamp DESC;

-- Check log transport services
SELECT
  thread#,
  sequence#,
  status,
  archived,
  applied,
  deleted,
  standby_dest,
  completion_time
FROM v$archived_log
WHERE dest_id > 1
  AND sequence# > (SELECT MAX(sequence#) - 20 FROM v$archived_log)
ORDER BY sequence# DESC, dest_id;

3. Check Network and Connectivity

-- Test connectivity to standby database
SELECT
  dest_id,
  destination,
  status,
  error
FROM v$archive_dest
WHERE target = 'STANDBY';

-- Check TNS connectivity from SQL*Plus
-- CONNECT sys/password@standby_tns_alias AS SYSDBA

-- Check archive lag on standby
SELECT
  name,
  value,
  datum_time
FROM v$dataguard_stats
WHERE name IN ('apply lag', 'transport lag')
ORDER BY name;

# Test network connectivity from OS level
# Replace with actual standby hostname/IP
STANDBY_HOST="standby-server.domain.com"
STANDBY_PORT="1521"

echo "Testing network connectivity to standby..."
telnet $STANDBY_HOST $STANDBY_PORT

# Test TNS resolution
tnsping STANDBY_SERVICE_NAME

# Check listener status on standby
# (run on standby server)
lsnrctl status
lsnrctl services

4. Examine Alert Logs and Trace Files

# Check primary database alert log
tail -n 100 $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log | grep -A5 -B5 "ORA-16038"

# Look for related archive errors
grep -E "(ORA-16038|ORA-00257|ORA-19815|archive)" $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log | tail -20

# Check for network-related errors
grep -E "(TNS|network|connection)" $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/alert_$ORACLE_SID.log | tail -10

# Check archive process trace files
ls -la $ORACLE_BASE/diag/rdbms/$ORACLE_SID/$ORACLE_SID/trace/ | grep -E "arc[0-9]" | tail -10

# Check standby database alert log (if accessible)
# tail -n 100 $ORACLE_BASE/diag/rdbms/$STANDBY_SID/$STANDBY_SID/trace/alert_$STANDBY_SID.log

Immediate Solutions

Solution 1: Fix Archive Destination Issues

Clear Archive Destination Errors

-- Check which destinations are in error
SELECT dest_id, status, error, destination
FROM v$archive_dest
WHERE status = 'ERROR';

-- Clear error state and retry
ALTER SYSTEM SET log_archive_dest_state_2 = DEFER SCOPE = BOTH;
ALTER SYSTEM SET log_archive_dest_state_2 = ENABLE SCOPE = BOTH;

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

-- Monitor archive destination status
SELECT dest_id, status, error FROM v$archive_dest WHERE dest_id = 2;

Temporarily Disable Problematic Destinations

-- If standby is unreachable, temporarily disable to prevent hanging
SELECT dest_id, destination, status, error
FROM v$archive_dest
WHERE status IN ('ERROR', 'DEFERRED');

-- Disable problematic destination temporarily
ALTER SYSTEM SET log_archive_dest_state_2 = DEFER SCOPE = BOTH;

-- Verify primary database continues archiving locally
ALTER SYSTEM SWITCH LOGFILE;
SELECT sequence#, archived, applied FROM v$archived_log ORDER BY sequence# DESC;

-- Re-enable destination once issue is resolved
-- ALTER SYSTEM SET log_archive_dest_state_2 = ENABLE SCOPE = BOTH;

Solution 2: Resolve Storage and Space Issues

Check and Resolve Space Issues

# Check archive destination disk space
ARCHIVE_DEST=$(sqlplus -s / as sysdba <<< "SELECT value FROM v\$parameter WHERE name='db_recovery_file_dest';" | grep -v '^$' | tail -1)

echo "Checking space for archive destination: $ARCHIVE_DEST"
df -h "$ARCHIVE_DEST"

# Check for space issues
if [ $(df "$ARCHIVE_DEST" | awk 'NR==2 {print $5}' | sed 's/%//') -gt 90 ]; then
    echo "WARNING: Archive destination is over 90% full"

    # Emergency cleanup of old archive logs
    find "$ARCHIVE_DEST" -name "*.arc" -mtime +2 -ls
    # find "$ARCHIVE_DEST" -name "*.arc" -mtime +2 -delete  # Uncomment after verification
fi

# Check permissions
ls -la "$ARCHIVE_DEST"

-- Clean up old archive logs if space is critical
RMAN TARGET /
DELETE NOPROMPT ARCHIVELOG UNTIL TIME 'SYSDATE-1';
EXIT;

-- Check Flash Recovery Area usage
SELECT
  ROUND(space_limit/1024/1024/1024, 2) as size_gb,
  ROUND(space_used/1024/1024/1024, 2) as used_gb,
  ROUND((space_used/space_limit)*100, 2) as pct_used
FROM v$recovery_file_dest;

-- Increase FRA size if needed
ALTER SYSTEM SET db_recovery_file_dest_size = 100G SCOPE = BOTH;

Solution 3: Restart Standby Database and Services

Restart Standby Database (Run on Standby Server)

-- Connect to standby database
sqlplus / as sysdba

-- Check standby status
SELECT database_role, open_mode FROM v$database;

-- If standby is in mount mode but not receiving logs
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

-- Start managed recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

-- Check recovery status
SELECT process, status, thread#, sequence#, block#, blocks
FROM v$managed_standby
ORDER BY process;

Restart Data Guard Broker (if used)

-- On primary database
ALTER SYSTEM SET dg_broker_start = FALSE SCOPE = BOTH;
ALTER SYSTEM SET dg_broker_start = TRUE SCOPE = BOTH;

-- Check broker status
SELECT database_role, enabled FROM v$database;

-- Connect to DGMGRL and check configuration

# DGMGRL commands
dgmgrl sys/password@primary_service

DGMGRL> SHOW CONFIGURATION;
DGMGRL> SHOW DATABASE primary_db;
DGMGRL> SHOW DATABASE standby_db;
DGMGRL> ENABLE DATABASE standby_db;
DGMGRL> EXIT;

Long-Term Solutions

1. Optimize Data Guard Configuration

Configure Optimal Archive Transport

-- Configure efficient archive transport settings
ALTER SYSTEM SET log_archive_dest_2 =
'SERVICE=standby_service LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=standby_unique_name
NET_TIMEOUT=30
REOPEN=300' SCOPE = BOTH;

-- Alternative: Synchronous transport for maximum protection
ALTER SYSTEM SET log_archive_dest_2 =
'SERVICE=standby_service LGWR SYNC AFFIRM
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=standby_unique_name
NET_TIMEOUT=30' SCOPE = BOTH;

-- Configure archive destination states
ALTER SYSTEM SET log_archive_dest_state_1 = ENABLE SCOPE = BOTH;
ALTER SYSTEM SET log_archive_dest_state_2 = ENABLE SCOPE = BOTH;

-- Set minimum successful destinations
ALTER SYSTEM SET log_archive_min_succeed_dest = 1 SCOPE = BOTH;

-- Configure Data Guard parameters
ALTER SYSTEM SET log_archive_config =
'DG_CONFIG=(primary_unique_name,standby_unique_name)' SCOPE = BOTH;

ALTER SYSTEM SET standby_file_management = AUTO SCOPE = BOTH;
ALTER SYSTEM SET db_file_name_convert =
'/primary/path/','/standby/path/' SCOPE = SPFILE;
ALTER SYSTEM SET log_file_name_convert =
'/primary/path/','/standby/path/' SCOPE = SPFILE;

Configure Standby Redo Logs

-- Check current standby redo log configuration
SELECT group#, thread#, sequence#, bytes/1024/1024 as mb, status
FROM v$standby_log
ORDER BY group#;

-- Add standby redo logs (one more group than online redo logs per thread)
-- Check online redo log groups first
SELECT group#, thread#, bytes/1024/1024 as mb, status FROM v$log;

-- Add standby redo logs (adjust paths and sizes as needed)
ALTER DATABASE ADD STANDBY LOGFILE GROUP 10
('/standby/redo/standby_redo01_10.log') SIZE 1G;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 11
('/standby/redo/standby_redo01_11.log') SIZE 1G;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 12
('/standby/redo/standby_redo01_12.log') SIZE 1G;

ALTER DATABASE ADD STANDBY LOGFILE GROUP 13
('/standby/redo/standby_redo01_13.log') SIZE 1G;

2. Implement Archive Log Monitoring

Create Archive Log Monitoring System

-- Create archive monitoring table
CREATE TABLE archive_dest_monitor (
  monitor_time TIMESTAMP DEFAULT SYSTIMESTAMP,
  dest_id NUMBER,
  dest_name VARCHAR2(128),
  status VARCHAR2(16),
  error VARCHAR2(4000),
  fail_sequence NUMBER,
  applied_sequence NUMBER,
  gap_count NUMBER,
  lag_minutes NUMBER
);

-- Create monitoring procedure
CREATE OR REPLACE PROCEDURE monitor_archive_destinations AS
  v_gap_count NUMBER;
  v_lag_minutes NUMBER;
BEGIN
  FOR dest IN (
    SELECT dest_id, dest_name, status, error, fail_sequence
    FROM v$archive_dest
    WHERE status != 'INACTIVE'
  ) LOOP

    -- Calculate gap count for this destination
    SELECT COUNT(*) INTO v_gap_count
    FROM v$archive_gap
    WHERE dest_id = dest.dest_id;

    -- Calculate lag in minutes
    SELECT NVL(ROUND((SYSDATE - MAX(completion_time)) * 24 * 60), 0)
    INTO v_lag_minutes
    FROM v$archived_log
    WHERE dest_id = dest.dest_id
      AND applied = 'YES';

    -- Insert monitoring record
    INSERT INTO archive_dest_monitor (
      dest_id, dest_name, status, error, fail_sequence,
      gap_count, lag_minutes
    ) VALUES (
      dest.dest_id, dest.dest_name, dest.status, dest.error, dest.fail_sequence,
      v_gap_count, v_lag_minutes
    );

    -- Alert on issues
    IF dest.status = 'ERROR' OR v_gap_count > 0 OR v_lag_minutes > 60 THEN
      DBMS_OUTPUT.PUT_LINE('ALERT: Destination ' || dest.dest_name ||
                          ' - Status: ' || dest.status ||
                          ', Gaps: ' || v_gap_count ||
                          ', Lag: ' || v_lag_minutes || ' min');
    END IF;
  END LOOP;

  COMMIT;
END;
/

-- Schedule archive monitoring
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'MONITOR_ARCHIVE_DESTINATIONS',
    job_type => 'STORED_PROCEDURE',
    job_action => 'monitor_archive_destinations',
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=5',
    enabled => TRUE
  );
END;
/

Automated Gap Resolution

-- Create gap resolution procedure
CREATE OR REPLACE PROCEDURE resolve_archive_gaps AS
  v_gap_count NUMBER;
  v_sql_stmt VARCHAR2(4000);
BEGIN
  -- Check for archive gaps
  SELECT COUNT(*) INTO v_gap_count FROM v$archive_gap;

  IF v_gap_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('Found ' || v_gap_count || ' archive gaps');

    -- Log gaps for troubleshooting
    FOR gap IN (
      SELECT dest_id, thread#, low_sequence#, high_sequence#
      FROM v$archive_gap
    ) LOOP
      DBMS_OUTPUT.PUT_LINE('Gap at dest ' || gap.dest_id ||
                          ': sequences ' || gap.low_sequence# ||
                          ' to ' || gap.high_sequence#);

      -- Attempt to resolve gap by re-archiving
      -- (This is a simplified example - real gap resolution is more complex)
      v_sql_stmt := 'ALTER SYSTEM ARCHIVE LOG THREAD ' || gap.thread# ||
                   ' SEQUENCE ' || gap.low_sequence#;

      BEGIN
        EXECUTE IMMEDIATE v_sql_stmt;
        DBMS_OUTPUT.PUT_LINE('Resolved gap for sequence ' || gap.low_sequence#);
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('Failed to resolve gap: ' || SQLERRM);
      END;
    END LOOP;
  END IF;
END;
/

3. Network and Performance Optimization

Configure Network Optimization

-- Optimize archive transport performance
ALTER SYSTEM SET log_archive_dest_2 =
'SERVICE=standby_service LGWR ASYNC
VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE)
DB_UNIQUE_NAME=standby_unique_name
NET_TIMEOUT=30
REOPEN=300
COMPRESSION=ENABLE' SCOPE = BOTH;

-- Configure archive process parameters
ALTER SYSTEM SET log_archive_max_processes = 10 SCOPE = BOTH;
ALTER SYSTEM SET archive_lag_target = 900 SCOPE = BOTH;  -- 15 minutes

-- Set network timeout parameters
ALTER SYSTEM SET sqlnet.expire_time = 10 SCOPE = BOTH;
ALTER SYSTEM SET tcp.connect_timeout = 60 SCOPE = BOTH;

Configure Data Guard Broker for Automation

-- Enable Data Guard Broker
ALTER SYSTEM SET dg_broker_start = TRUE SCOPE = BOTH;
ALTER SYSTEM SET dg_broker_config_file1 =
'/oracle/product/dg_broker/dr1_unique_name.dat' SCOPE = BOTH;
ALTER SYSTEM SET dg_broker_config_file2 =
'/oracle/product/dg_broker/dr2_unique_name.dat' SCOPE = BOTH;

# Configure Data Guard Broker via DGMGRL
dgmgrl sys/password@primary_service

DGMGRL> CREATE CONFIGURATION 'DG_CONFIG' AS
        PRIMARY DATABASE IS 'PRIMARY_DB' CONNECT IDENTIFIER IS primary_service
        MAINTAINED AS PHYSICAL;

DGMGRL> ADD DATABASE 'STANDBY_DB' AS
        CONNECT IDENTIFIER IS standby_service
        MAINTAINED AS PHYSICAL;

DGMGRL> ENABLE CONFIGURATION;
DGMGRL> SHOW CONFIGURATION;

# Configure automatic failover
DGMGRL> EDIT DATABASE standby_db SET PROPERTY 'FastStartFailoverTarget'='primary_db';
DGMGRL> EDIT CONFIGURATION SET PROPERTY 'FastStartFailoverThreshold'=30;
DGMGRL> ENABLE FAST_START FAILOVER;

Prevention Strategies

1. Proactive Monitoring

#!/bin/bash
# archive_health_check.sh - Comprehensive archive monitoring

ORACLE_SID=${1:-ORCL}
export ORACLE_SID

LOG_FILE="/var/log/oracle/archive_health_${ORACLE_SID}.log"
ALERT_EMAIL="[email protected]"

exec > >(tee -a "$LOG_FILE")
exec 2>&1

echo "========================================"
echo "Archive Health Check: $(date)"
echo "Oracle SID: $ORACLE_SID"
echo "========================================"

# Check archive destination status
echo "Archive Destination Status:"
sqlplus -s / as sysdba << EOF
SET PAGESIZE 100
COLUMN dest_name FORMAT A20
COLUMN status FORMAT A10
COLUMN error FORMAT A50
SELECT dest_id, dest_name, status, error
FROM v\$archive_dest
WHERE status != 'INACTIVE'
ORDER BY dest_id;
EXIT;
EOF

# Check for archive gaps
echo ""
echo "Archive Gaps:"
GAP_COUNT=$(sqlplus -s / as sysdba <<< "SELECT COUNT(*) FROM v\$archive_gap;" | grep -v '^$' | tail -1)

if [ "$GAP_COUNT" -gt 0 ]; then
    echo "WARNING: $GAP_COUNT archive gaps found"
    sqlplus -s / as sysdba << EOF
SELECT dest_id, thread#, low_sequence#, high_sequence#
FROM v\$archive_gap;
EXIT;
EOF

    # Send alert
    echo "Archive gaps detected on $ORACLE_SID" | \
    mail -s "Oracle Archive Gap Alert - $ORACLE_SID" "$ALERT_EMAIL"
else
    echo "No archive gaps found"
fi

# Check standby lag
echo ""
echo "Standby Database Lag:"
sqlplus -s / as sysdba << EOF
COLUMN name FORMAT A15
COLUMN value FORMAT A15
SELECT name, value, datum_time
FROM v\$dataguard_stats
WHERE name IN ('apply lag', 'transport lag');
EXIT;
EOF

echo "Archive Health Check Completed: $(date)"
echo "========================================"

# Schedule in crontab:
# */15 * * * * /path/to/archive_health_check.sh ORCL

2. Automated Recovery Procedures

-- Create automatic archive recovery procedure
CREATE OR REPLACE PROCEDURE automatic_archive_recovery AS
  v_error_count NUMBER;
  v_gap_count NUMBER;
  v_dest_id NUMBER;
BEGIN
  -- Count destinations in error
  SELECT COUNT(*) INTO v_error_count
  FROM v$archive_dest
  WHERE status = 'ERROR';

  -- Count archive gaps
  SELECT COUNT(*) INTO v_gap_count FROM v$archive_gap;

  IF v_error_count > 0 OR v_gap_count > 0 THEN
    DBMS_OUTPUT.PUT_LINE('Archive issues detected - attempting recovery');

    -- Try to restart archive destinations
    FOR dest IN (
      SELECT dest_id FROM v$archive_dest WHERE status = 'ERROR'
    ) LOOP
      BEGIN
        EXECUTE IMMEDIATE 'ALTER SYSTEM SET log_archive_dest_state_' ||
                         dest.dest_id || ' = DEFER SCOPE = MEMORY';

        -- Wait a moment
        DBMS_LOCK.SLEEP(5);

        EXECUTE IMMEDIATE 'ALTER SYSTEM SET log_archive_dest_state_' ||
                         dest.dest_id || ' = ENABLE SCOPE = MEMORY';

        DBMS_OUTPUT.PUT_LINE('Restarted destination ' || dest.dest_id);
      EXCEPTION
        WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('Failed to restart dest ' || dest.dest_id ||
                              ': ' || SQLERRM);
      END;
    END LOOP;

    -- Force log switch to test recovery
    EXECUTE IMMEDIATE 'ALTER SYSTEM SWITCH LOGFILE';

  END IF;
END;
/

-- Schedule automatic recovery
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'AUTO_ARCHIVE_RECOVERY',
    job_type => 'STORED_PROCEDURE',
    job_action => 'automatic_archive_recovery',
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=10',
    enabled => TRUE
  );
END;
/

Related Errors

• ORA-00257 - Archiver error (archive destination full)
• ORA-19815 - FRA space warning (can cause archive failures)
• ORA-12154 - TNS could not resolve (standby connectivity)
• ORA-03113 - End-of-file on communication (network issues)

Emergency Response

Quick Resolution Steps

1. Check archive destination status

   SELECT dest_id, status, error FROM v$archive_dest WHERE status = 'ERROR';

2. Restart failed destinations

   ALTER SYSTEM SET log_archive_dest_state_2 = DEFER SCOPE = BOTH;
   ALTER SYSTEM SET log_archive_dest_state_2 = ENABLE SCOPE = BOTH;

3. Force log switch to test

   ALTER SYSTEM SWITCH LOGFILE;

4. Check for gaps

   SELECT * FROM v$archive_gap;

Quick Reference Commands

-- Check archive status
SELECT dest_id, status, error, destination FROM v$archive_dest;

-- Restart archive destination
ALTER SYSTEM SET log_archive_dest_state_2 = DEFER;
ALTER SYSTEM SET log_archive_dest_state_2 = ENABLE;

-- Check gaps
SELECT dest_id, thread#, low_sequence#, high_sequence# FROM v$archive_gap;

-- Monitor standby apply
SELECT process, status, sequence# FROM v$managed_standby;

-- Force log switch
ALTER SYSTEM SWITCH LOGFILE;

Emergency Shell Script

emergency_archive_fix.sh

#!/bin/bash
ORACLE_SID=${1:-ORCL}
export ORACLE_SID

echo "Emergency archive destination recovery for $ORACLE_SID..."

# Restart archive destinations
sqlplus / as sysdba << EOF
-- Defer and re-enable all destinations
ALTER SYSTEM SET log_archive_dest_state_2 = DEFER SCOPE = MEMORY;
ALTER SYSTEM SET log_archive_dest_state_3 = DEFER SCOPE = MEMORY;
ALTER SYSTEM SET log_archive_dest_state_4 = DEFER SCOPE = MEMORY;

-- Wait and re-enable
ALTER SYSTEM SET log_archive_dest_state_2 = ENABLE SCOPE = MEMORY;
ALTER SYSTEM SET log_archive_dest_state_3 = ENABLE SCOPE = MEMORY;
ALTER SYSTEM SET log_archive_dest_state_4 = ENABLE SCOPE = MEMORY;

-- Force log switch
ALTER SYSTEM SWITCH LOGFILE;

-- Check status
SELECT dest_id, status, error FROM v\$archive_dest WHERE dest_id > 1;
EXIT;
EOF

echo "Emergency archive recovery completed"