Maxlogmembers in Oracle 9 and Oracle 10

In an earlier example, I looked at maxlogmembers. In Oracle 9, this limits the number of members allowed in any one redo log group:

 

SQL> select group#, members from v$log

  2  /

 

    GROUP#    MEMBERS

---------- ----------

         1          2

         2          2

         3          2

         4          2

 

SQL> select dimlm from x$kccdi

  2  /

 

     DIMLM

----------

         2

 

SQL> l

  1  alter database add logfile member

  2  '/disk3/test9/redo/log1c.rdo'

  3* to group 1

SQL> /

alter database add logfile member

*

ERROR at line 1:

ORA-00357: too many members specified for log file,

the maximum is 2

 

SQL>

In Oracle 10, the limit is still there but it is not applied:

 

SQL> select group#, members from v$log

  2  /

 

    GROUP#    MEMBERS

---------- ----------

         1          2

         2          2

         3          2

 

SQL> select dimlm from x$kccdi

  2  /

 

     DIMLM

----------

         3

 

SQL>

 

  1  alter database add logfile member

  2  'disk3/test10/redo/log1c.rdo'

  3* to group 1

SQL> /

 

Database altered.

 

SQL> alter database add logfile member

  2  'disk4/test10/redo/log1d.rdo'

  3  to group 1

  4  /

 

Database altered.

 

SQL> select group#, members from v$log

  2  /

 

    GROUP#    MEMBERS

---------- ----------

         1          4

         2          2

         3          2

 

SQL>

 

I believe that this change occurred in Oracle 10 Release 2 but I have not confirmed this.

Adding Redo Log Group Members

You can use the alter database add logfile member command to add extra member(s) to a redo log group. You might do this:

 

(1)  To add an extra member on a different disk and remove a single point of failure.

(2)  In conjunction with the alter database drop logfile member, to move a redo log group from one disk to another.

 

You can tell Oracle where to add the new member by giving a group number:

 

SQL> select group#, member from v$logfile;

 

    GROUP# MEMBER

---------- ----------------------------------------

         2 /disk1/redo/log2a.rdo

         1 /disk1/redo/log1a.rdo

 

SQL> alter database add logfile member

  2  '/disk2/redo/log1b.rdo'

  3  to group 1;

 

Database altered.

 

SQL> select group#, member from v$logfile;

 

    GROUP# MEMBER

---------- ----------------------------------------

         2 /disk1/redo/log2a.rdo

         1 /disk1/redo/log1a.rdo

         1 /disk2/redo/log1b.rdo

 

SQL>

 

Or by providing the name(s) of the members already in the group:

 

SQL> select group#, member from v$logfile;

 

    GROUP# MEMBER

---------- ----------------------------------------

         2 /disk1/redo/log2a.rdo

         1 /disk1/redo/log1a.rdo

         1 /disk2/redo/log1b.rdo

 

SQL> alter database add logfile member

  2  '/disk2/redo/log2b.rdo' to

  3  '/disk1/redo/log2a.rdo';

 

Database altered.

 

SQL> select group#, member from v$logfile

  2  order by 1, 2;

 

    GROUP# MEMBER

---------- ----------------------------------------

         1 /disk1/redo/log1a.rdo

         1 /disk2/redo/log1b.rdo

         2 /disk1/redo/log2a.rdo

         2 /disk2/redo/log2b.rdo

 

SQL>