DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00308 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00308: Cannot Open Archived Log — Causes, Fixes & Prevention

ORA-00308 is thrown when Oracle attempts to open an archived redo log file but cannot access it — typically because the file is missing, corrupted, or located at a different path than what Oracle expects. This error commonly surfaces during database recovery operations, LogMiner sessions, or Data Guard Redo Apply processes. Left unresolved, it can halt recovery completely and cause extended downtime.

Top 3 Causes

1. Archived Log File Is Missing or Physically Deleted

The most frequent cause is that archived logs were deleted directly at the OS level (e.g., using rm) instead of through RMAN, leaving Oracle's control file or catalog pointing to files that no longer exist.

-- Check for archived logs Oracle expects but cannot find
SELECT NAME, SEQUENCE#, STATUS, DELETED
FROM V$ARCHIVED_LOG
WHERE STATUS = 'A'
  AND DELETED = 'NO'
ORDER BY SEQUENCE# DESC;

-- In RMAN: crosscheck and clean up expired entries
CROSSCHECK ARCHIVELOG ALL;
DELETE EXPIRED ARCHIVELOG ALL;

-- If the log exists in backup, restore it
RESTORE ARCHIVELOG SEQUENCE 12345 THREAD 1;
Enter fullscreen mode Exit fullscreen mode

2. Archive Log Destination Path Has Changed

After storage migrations, NFS remounts, or parameter changes, the path stored in Oracle's control file no longer matches the actual file location. Oracle looks in the old path and fails immediately.

-- Verify current archive log destination
SHOW PARAMETER LOG_ARCHIVE_DEST;

SELECT DEST_ID, STATUS, TARGET, DESTINATION, ERROR
FROM V$ARCHIVE_DEST
WHERE STATUS != 'INACTIVE';

-- Update the destination parameter
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1 = 'LOCATION=/new/archive/path' SCOPE=BOTH;

-- Re-catalog archived logs from the new location
CATALOG START WITH '/new/archive/path/' NOPROMPT;

-- Clean up stale references
CROSSCHECK ARCHIVELOG ALL;
DELETE NOPROMPT EXPIRED ARCHIVELOG ALL;
Enter fullscreen mode Exit fullscreen mode

3. Data Guard Archive Log Gap

In a Data Guard environment, if archived logs fail to transfer from the Primary to the Standby (due to network issues, disk full, or permission errors), the Managed Recovery Process (MRP) encounters ORA-00308 when it tries to apply a sequence it never received.

-- Check for archive log gap on Primary
SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE#
FROM V$ARCHIVE_GAP;

-- Verify received logs on Standby
SELECT SEQUENCE#, APPLIED, NEXT_TIME
FROM V$ARCHIVED_LOG
WHERE STANDBY_DEST = 'YES'
ORDER BY SEQUENCE# DESC;

-- Reconfigure FAL to auto-fetch missing logs (run on Standby)
ALTER SYSTEM SET FAL_SERVER = 'PRIMARY_DB' SCOPE=BOTH;
ALTER SYSTEM SET FAL_CLIENT = 'STANDBY_DB' SCOPE=BOTH;

-- Restart Managed Recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

-- Manually catalog logs copied from Primary
CATALOG START WITH '/standby/archive/' NOPROMPT;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Summary

Scenario Action
File deleted via OS CROSSCHECK → DELETE EXPIRED → restore from backup
Path changed Update LOG_ARCHIVE_DEST, re-catalog with CATALOG START WITH
Data Guard gap Fix FAL settings, copy missing logs, restart MRP
No backup exists Perform incomplete recovery up to available sequence 
-- Incomplete recovery when a log is unrecoverable
RECOVER DATABASE UNTIL SEQUENCE 12346 THREAD 1;
ALTER DATABASE OPEN RESETLOGS;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

Always delete archived logs through RMAN, never via OS commands.

-- Safe archive log backup and cleanup via RMAN
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
BACKUP ARCHIVELOG ALL DELETE INPUT;
DELETE NOPROMPT OBSOLETE;
Enter fullscreen mode Exit fullscreen mode

Monitor archive log space and Data Guard gaps proactively.

-- Monitor Fast Recovery Area usage
SELECT SPACE_LIMIT / 1024 / 1024 / 1024  AS LIMIT_GB,
       SPACE_USED  / 1024 / 1024 / 1024  AS USED_GB,
       ROUND((SPACE_USED / NULLIF(SPACE_LIMIT,0)) * 100, 1) AS USE_PCT
FROM V$RECOVERY_FILE_DEST;

-- Schedule this to alert when USE_PCT exceeds 80%
SELECT * FROM V$ARCHIVE_GAP;
Enter fullscreen mode Exit fullscreen mode

Set up automated alerts when FRA usage exceeds 80% and when any archive gap is detected on Standby — catching these conditions early will prevent ORA-00308 before it disrupts operations.

Related Errors

  • ORA-00279 — Requests a specific archived log needed for recovery; often precedes ORA-00308.
  • ORA-00280 — Displays sequence/thread info alongside ORA-00308 during recovery.
  • ORA-01547 — Recovery failed; can be a downstream result of ORA-00308 blocking apply.
  • ORA-16055 / ORA-16014 — Data Guard archivelog transmission failures that lead to ORA-00308 on the Standby.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)