DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-00357 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-00357: Too Many Members Specified for Log File

ORA-00357 is an Oracle database error that occurs when you attempt to add more members to a redo log group than the database's MAXLOGMEMBERS limit allows. This limit is defined at database creation time and cannot be changed without recreating the control file. It is commonly encountered when configuring redo log multiplexing or during database cloning operations.

Top 3 Causes

1. MAXLOGMEMBERS Limit Set Too Low at Database Creation

When a database is created with a low MAXLOGMEMBERS value (default is typically 2–5), any attempt to add members beyond that limit will trigger ORA-00357. This is the most common root cause.

-- Check current MAXLOGMEMBERS value
SELECT MAXLOGMEMBERS FROM V$DATABASE;

-- Check current members per group
SELECT GROUP#, COUNT(*) AS MEMBER_COUNT
FROM V$LOGFILE
GROUP BY GROUP#
ORDER BY GROUP#;
Enter fullscreen mode Exit fullscreen mode

2. Adding Members Without Checking Current Limits

DBAs often run ALTER DATABASE ADD LOGFILE MEMBER without first verifying how many members already exist in the target group versus the MAXLOGMEMBERS ceiling.

-- Check group status and member details before adding
SELECT A.GROUP#, A.MEMBERS, A.STATUS, B.MEMBER
FROM V$LOG A
JOIN V$LOGFILE B ON A.GROUP# = B.GROUP#
ORDER BY A.GROUP#;

-- This will fail if MAXLOGMEMBERS is already reached
ALTER DATABASE ADD LOGFILE MEMBER
    '/u01/oradata/orcl/redo01b.log' TO GROUP 1;
Enter fullscreen mode Exit fullscreen mode

3. Cloned or Migrated Database Inheriting a Low MAXLOGMEMBERS

When a database is cloned via RMAN or Data Guard, the control file—and its embedded MAXLOGMEMBERS setting—is copied from the source. If the source had a low limit, the target database will inherit the same restriction.

-- After cloning, always verify the inherited limit
SELECT NAME, DB_UNIQUE_NAME, MAXLOGMEMBERS
FROM V$DATABASE;

-- Review all redo log members in the cloned environment
SELECT GROUP#, STATUS, MEMBER, TYPE
FROM V$LOGFILE
ORDER BY GROUP#;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Solutions

Fix 1: Recreate the Control File with a Higher MAXLOGMEMBERS

Since MAXLOGMEMBERS can only be changed by recreating the control file, follow these steps:

-- Step 1: Generate current control file script
ALTER DATABASE BACKUP CONTROLFILE TO TRACE
    AS '/tmp/ctrlfile_backup.sql' REUSE RESETLOGS;

-- Step 2: Edit /tmp/ctrlfile_backup.sql
-- Change MAXLOGMEMBERS 2 → MAXLOGMEMBERS 5

-- Step 3: Restart in NOMOUNT and apply new control file
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;

CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS
    MAXLOGFILES 16
    MAXLOGMEMBERS 5        -- Updated value
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
    GROUP 1 '/u01/oradata/orcl/redo01a.log' SIZE 200M,
    GROUP 2 '/u01/oradata/orcl/redo02a.log' SIZE 200M,
    GROUP 3 '/u01/oradata/orcl/redo03a.log' SIZE 200M
DATAFILE
    '/u01/oradata/orcl/system01.dbf',
    '/u01/oradata/orcl/sysaux01.dbf',
    '/u01/oradata/orcl/undotbs01.dbf'
CHARACTER SET AL32UTF8;

-- Step 4: Open the database
ALTER DATABASE OPEN RESETLOGS;
Enter fullscreen mode Exit fullscreen mode

Fix 2: Drop an Existing Log Member to Free Up Space

If recreating the control file is not immediately feasible, remove an unnecessary member from the group first.

-- Drop a specific log member (group must be INACTIVE)
ALTER DATABASE DROP LOGFILE MEMBER
    '/u01/oradata/orcl/redo01_old.log';

-- Then add the new member
ALTER DATABASE ADD LOGFILE MEMBER
    '/u01/oradata/orcl/redo01b.log' TO GROUP 1;
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

1. Set MAXLOGMEMBERS generously at database creation time.
Always set MAXLOGMEMBERS to at least 5 when creating a new database. This gives you room to add multiple members per group for proper multiplexing without hitting the limit prematurely.

CREATE DATABASE mydb
    MAXLOGFILES 32
    MAXLOGMEMBERS 5
    MAXDATAFILES 200
    MAXINSTANCES 1
    ...
Enter fullscreen mode Exit fullscreen mode

2. Include a pre-check in any redo log automation scripts.
Before executing any ADD LOGFILE MEMBER command in scripts or automation pipelines, always query V$DATABASE and V$LOGFILE to validate current usage against the MAXLOGMEMBERS ceiling.

-- Pre-check script before adding members
SELECT
    G.GROUP#,
    G.MEMBERS AS CURRENT,
    D.MAXLOGMEMBERS AS MAX_ALLOWED,
    CASE
        WHEN G.MEMBERS >= D.MAXLOGMEMBERS
        THEN 'LIMIT REACHED - ACTION REQUIRED'
        ELSE 'OK'
    END AS STATUS_CHECK
FROM V$LOG G
CROSS JOIN (SELECT MAXLOGMEMBERS FROM V$DATABASE) D
ORDER BY G.GROUP#;
Enter fullscreen mode Exit fullscreen mode

Related Errors

  • ORA-00350: Log group needs to be archived before certain operations.
  • ORA-00312: Online redo log file not found; often related to member path issues.
  • ORA-00359: Specified log file group does not exist.

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