DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00361 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00361: Cannot Remove Last Log Member for Group

ORA-00361 is thrown by Oracle when a DBA attempts to drop the last remaining member of a redo log group using the ALTER DATABASE DROP LOGFILE MEMBER command. Oracle enforces a strict rule that every redo log group must retain at least one member at all times to ensure database recoverability. Understanding this constraint is essential for safe redo log management in any Oracle environment.

Top 3 Causes

1. Only One Member Exists in the Redo Log Group

The most common cause is a non-multiplexed redo log configuration where each group has only a single member. When an administrator tries to remove that sole member, Oracle immediately raises ORA-00361.

-- Check how many members exist per group
SELECT GROUP#, COUNT(*) AS MEMBER_COUNT
FROM V$LOGFILE
GROUP BY GROUP#
ORDER BY GROUP#;

-- Example output showing a single-member group (dangerous):
-- GROUP# | MEMBER_COUNT
-- 1      | 1   <-- ORA-00361 will occur if you try to drop this member
Enter fullscreen mode Exit fullscreen mode

2. Dropping Members Sequentially Until the Last One

In a multiplexed setup, DBAs sometimes remove members one by one — for example, to relocate redo logs to a new disk. If they drop all but the last member and then attempt to drop that final one, ORA-00361 is raised.

-- This sequence will cause ORA-00361 on the last DROP:
-- Step 1: Drop first member (succeeds if 2+ members exist)
ALTER DATABASE DROP LOGFILE MEMBER '/old_disk/redo01a.log';

-- Step 2: Try to drop the last member (FAILS with ORA-00361)
ALTER DATABASE DROP LOGFILE MEMBER '/old_disk/redo01b.log';
-- ERROR: ORA-00361: cannot remove last log member for group 1
Enter fullscreen mode Exit fullscreen mode

3. Confusing Member Drop with Group Drop

Some DBAs intend to remove an entire redo log group but mistakenly use DROP LOGFILE MEMBER repeatedly instead of DROP LOGFILE GROUP. Once the last member is reached, ORA-00361 stops the operation.

-- WRONG approach: trying to remove a group by dropping all members
ALTER DATABASE DROP LOGFILE MEMBER '/oradata/redo02.log'; -- fails on last member

-- CORRECT approach: drop the entire group at once
-- First confirm the group is INACTIVE
SELECT GROUP#, STATUS FROM V$LOG;

-- Then drop the group (only works on INACTIVE groups)
ALTER DATABASE DROP LOGFILE GROUP 2;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Fix 1 — Add a new member first, then drop the old one:

-- Add a replacement member to the group
ALTER DATABASE ADD LOGFILE MEMBER '/new_path/redo01b.log' TO GROUP 1;

-- Verify the new member was added
SELECT GROUP#, MEMBER, STATUS FROM V$LOGFILE WHERE GROUP# = 1;

-- Now safely drop the old member
ALTER DATABASE DROP LOGFILE MEMBER '/old_path/redo01a.log';
Enter fullscreen mode Exit fullscreen mode

Fix 2 — Drop the whole group if it is no longer needed:

-- Force a log switch and checkpoint if the group is ACTIVE
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;

-- Confirm the group is now INACTIVE
SELECT GROUP#, STATUS FROM V$LOG WHERE GROUP# = 3;

-- Drop the entire group
ALTER DATABASE DROP LOGFILE GROUP 3;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  1. Always multiplex redo log groups — configure at least 2 members per group on separate disks from day one. This not only prevents ORA-00361 but also protects against redo log corruption. 
-- Add a second member to all groups (best practice setup)
ALTER DATABASE ADD LOGFILE MEMBER '/disk2/redo01b.log' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER '/disk2/redo02b.log' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/disk2/redo03b.log' TO GROUP 3;
Enter fullscreen mode Exit fullscreen mode
  1. Run a pre-check query before any DROP LOGFILE MEMBER operation — make it part of your standard runbook to verify member counts before executing any redo log maintenance. 
-- Pre-drop safety check
SELECT L.GROUP#, L.STATUS, L.MEMBERS, LF.MEMBER
FROM V$LOG L
JOIN V$LOGFILE LF ON L.GROUP# = LF.GROUP#
ORDER BY L.GROUP#;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-00350 – Log member needs to be archived before it can be dropped.
  • ORA-00362 – Member is required to form a valid online log.
  • ORA-01624 – Log required for crash recovery; cannot be dropped.

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