DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00359 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00359: log group does not exist — Causes, Fixes & Prevention

ORA-00359 is thrown by Oracle Database when a command references a Redo Log Group number that does not exist in the control file. This typically occurs during ALTER DATABASE DROP LOGFILE GROUP, ALTER DATABASE ADD LOGFILE MEMBER, or similar DDL operations targeting an invalid group number. It is a straightforward but easily preventable error that usually results from human error, stale scripts, or post-recovery control file mismatches.

Top 3 Causes

1. Specifying a Non-Existent Group Number

The most common cause is simply typing the wrong group number in a command. This frequently happens when a DBA reuses an old script that references a group number that no longer exists, or when managing multiple databases and confusing their configurations.

-- WRONG: Attempting to drop group 5 which does not exist
ALTER DATABASE DROP LOGFILE GROUP 5;
-- Result: ORA-00359: logfile group 5 does not exist

-- CORRECT: First check what groups actually exist
SELECT GROUP#, STATUS, MEMBERS, BYTES/1024/1024 AS "SIZE_MB"
FROM V$LOG
ORDER BY GROUP#;

-- Then drop the correct group (must be INACTIVE or UNUSED)
ALTER DATABASE DROP LOGFILE GROUP 3;
Enter fullscreen mode Exit fullscreen mode

2. Operating on an Already-Deleted Log Group

When automation scripts or batch jobs execute without validating the current state of the database, they may attempt to operate on a group that was already removed in a previous step. This is especially common in multi-step migration or reconfiguration scripts.

-- Always validate before acting
-- Check if a specific group exists before trying to drop it
SELECT COUNT(*) AS GROUP_EXISTS
FROM V$LOG
WHERE GROUP# = 4;

-- Only proceed if the result is 1
-- If COUNT = 0, the group does not exist — skip the DROP command

-- Safely add a new log group only if it doesn't already exist
SELECT GROUP#, STATUS FROM V$LOG WHERE GROUP# = 4;
-- If no rows returned, it is safe to add:
ALTER DATABASE ADD LOGFILE GROUP 4
    ('/u01/oradata/orcl/redo04a.log') SIZE 200M;
Enter fullscreen mode Exit fullscreen mode

3. Control File Mismatch After Database Recovery

After incomplete recovery or a RESETLOGS operation, the control file may contain inconsistent information about Redo Log Groups. In this scenario, group numbers referenced in scripts may no longer correspond to actual groups registered in the new control file.

-- After RESETLOGS or recovery, always re-query the actual log configuration
SELECT 
    l.GROUP#,
    l.STATUS,
    l.ARCHIVED,
    lm.MEMBER
FROM V$LOG l
JOIN V$LOGFILE lm ON l.GROUP# = lm.GROUP#
ORDER BY l.GROUP#;

-- Dump current control file to trace for documentation
ALTER DATABASE BACKUP CONTROLFILE TO TRACE;

-- If groups need to be re-added after RESETLOGS:
ALTER DATABASE ADD LOGFILE GROUP 2
    ('/u01/oradata/orcl/redo02.log') SIZE 200M;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions 

-- Step 1: Verify all existing log groups
SELECT GROUP#, MEMBERS, STATUS, BYTES/1024/1024 AS SIZE_MB
FROM V$LOG
ORDER BY GROUP#;

-- Step 2: Force a log switch to make current group INACTIVE
ALTER SYSTEM SWITCH LOGFILE;

-- Step 3: Archive all logs if in ARCHIVELOG mode
ALTER SYSTEM ARCHIVE LOG ALL;

-- Step 4: Confirm INACTIVE status, then drop
SELECT GROUP#, STATUS FROM V$LOG;
ALTER DATABASE DROP LOGFILE GROUP 3; -- use actual group number

-- Step 5: Add a replacement log group if needed
ALTER DATABASE ADD LOGFILE GROUP 3
    ('/u01/oradata/orcl/redo03.log') SIZE 200M;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Always query V$LOG and V$LOGFILE before any Redo Log DDL. Make this a mandatory step in all runbooks and automation scripts. Add guard logic that verifies group existence before executing DROP or ADD MEMBER commands.

  • Maintain versioned scripts and up-to-date documentation. Every time the Redo Log configuration changes, update your scripts and store them in a version control system (e.g., Git). Stale scripts are the leading cause of ORA-00359 in production environments.

Related Errors

  • ORA-00350 — Log file needs to be archived before it can be dropped
  • ORA-00360 — Not a logfile member (similar context, wrong member path)
  • ORA-00362 — Cannot drop the last member of a log group

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