DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00312 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00312: Online Log Thread – Causes, Fixes & Prevention

ORA-00312 is raised when Oracle cannot access an online redo log file belonging to a specific redo thread. This typically surfaces during database startup, a log switch, or media recovery when the physical log file is missing, corrupted, or located at an unexpected path. Left unresolved, the database may be unable to open, making prompt diagnosis critical.

Top 3 Causes

1. Physical Deletion or Corruption of the Redo Log File

The most common cause is the accidental deletion of an online redo log file at the OS level or physical corruption due to a storage failure. Oracle's control file still holds the registered path, but the file no longer exists or cannot be read.

-- Check redo log group and member status
SELECT l.group#, l.status, l.thread#,
       lf.member, lf.status AS file_status
FROM v$log l
JOIN v$logfile lf ON l.group# = lf.group#
ORDER BY l.thread#, l.group#;
Enter fullscreen mode Exit fullscreen mode

2. File Path Mismatch After Storage or Server Migration

When a server is migrated or mount points are reconfigured, the path registered in the control file no longer matches the actual file location. This causes Oracle to fail when trying to open the log file at startup or during a log switch.

-- Verify registered paths versus actual OS paths
SELECT group#, member, status
FROM v$logfile
ORDER BY group#;

-- After confirming new path, rename the log file member
ALTER DATABASE RENAME FILE
  '/old/path/redo02.log'
TO '/new/path/redo02.log';
Enter fullscreen mode Exit fullscreen mode

3. Redo Thread Configuration Issues in RAC

In an Oracle RAC environment, each instance owns a dedicated redo thread. If a thread is disabled or improperly configured after adding or removing a RAC node, Oracle cannot access the associated log files and throws ORA-00312.

-- Check thread status in RAC
SELECT thread#, status, enabled, groups
FROM v$thread
ORDER BY thread#;

-- Enable a disabled thread
ALTER DATABASE ENABLE PUBLIC THREAD 2;

-- Add a log group to a specific thread
ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 5
  ('/oracle/oradata/ORCL/redo_t2_05.log')
SIZE 200M;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

For INACTIVE log groups (safest scenario):

-- Drop the corrupted member and re-add it
ALTER DATABASE DROP LOGFILE MEMBER '/oracle/oradata/ORCL/redo02.log';

ALTER DATABASE ADD LOGFILE MEMBER '/oracle/oradata/ORCL/redo02.log'
TO GROUP 2;
Enter fullscreen mode Exit fullscreen mode

For CURRENT/ACTIVE log groups (force a log switch first):

-- Force a log switch to move away from the damaged group
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;

-- Once the group becomes INACTIVE, drop and recreate
ALTER DATABASE DROP LOGFILE GROUP 2;
ALTER DATABASE ADD LOGFILE GROUP 2
  ('/oracle/oradata/ORCL/redo02a.log',
   '/oracle/oradata/ORCL/redo02b.log')
SIZE 200M;
Enter fullscreen mode Exit fullscreen mode

Last resort – RESETLOGS (use only when the database cannot open):

⚠️ This may result in data loss. Ensure a valid backup exists before proceeding.

STARTUP MOUNT;
RECOVER DATABASE UNTIL CANCEL;
ALTER DATABASE OPEN RESETLOGS;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Always multiplex redo logs across separate disks.
Configure at least two members per log group on different storage paths. A single member failure will not bring down the database if a valid mirror exists.

-- Add a second member to all existing log groups
ALTER DATABASE ADD LOGFILE MEMBER '/disk2/oradata/ORCL/redo01b.log' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '/disk2/oradata/ORCL/redo02b.log' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/disk2/oradata/ORCL/redo03b.log' TO GROUP 3;
Enter fullscreen mode Exit fullscreen mode

2. Enable ARCHIVELOG mode and run regular RMAN backups.
Without ARCHIVELOG mode, a corrupted current redo log can result in unrecoverable data loss. Regular RMAN backups ensure you always have a recovery path.

-- Verify archive log mode
SELECT log_mode FROM v$database;

-- Schedule a regular RMAN backup (run in RMAN client)
-- BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
Enter fullscreen mode Exit fullscreen mode

Related Errors

Error Code Description
ORA-00313 Cannot open member of log group
ORA-00314 Log file sequence number mismatch
ORA-00316 Log file header mismatch
ORA-00333 Redo log read error during recovery
ORA-01157 Cannot identify/lock data file

📖 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)