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.

Maxlogfiles, maxlogmembers etc.

As far as I know, this information is accurate up to and including Oracle 9. When you run the create database command, you can specify various options e.g.

 

Maxlogfiles (the maximum number of redo log groups which the database may have).

Maxlogmembers (the maximum number of members allowed in each redo log group).

Etc.

 

To alter these values subsequently, you need to recreate the control file. Once the database has been created, you can find out what they were set to as follows. One way is to backup the control file:

 

SQL> alter database backup controlfile to trace;

 

Database altered.

 

SQL>

 

This will produce a trace file in user_dump_dest. If you go there straight away and do an ls –t|more (assuming your database is running on UNIX) the trace file will be the first name on the list (unless your database happens to be generating lots of trace files for some reason):

 

TEST9 > ls -t|more

andrew_ora_459234.trc

etc.

 

The value(s) you want will be in this file, just after the create controlfile statement:

 

CREATE CONTROLFILE REUSE DATABASE "ANDREW" RESETLOGS  NOARCHIVELOG

--  SET STANDBY TO MAXIMIZE PERFORMANCE

    MAXLOGFILES 16

    MAXLOGMEMBERS 2

    MAXDATAFILES 300

    MAXINSTANCES 1

    MAXLOGHISTORY 1470

 

If that is too long-winded, you can see the maxlogfiles setting like this:

 

SQL> select records_total

  2  from v$controlfile_record_section

  3  where type = 'REDO LOG'

  4  /

 

RECORDS_TOTAL

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

           16

 

SQL>

 

This SQL gives you maxlogmembers:

 

SQL> conn / as sysdba

Connected.

SQL> l

  1* select dimlm from x$kccdi

SQL> /

 

     DIMLM

----------

         2

 

SQL>

 

The value of maxdatafiles is here:

 

SQL> select records_total

  2  from v$controlfile_record_section

  3  where type = 'DATAFILE'

  4  /

 

RECORDS_TOTAL

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

          300

 

SQL>

 

The following query shows maxinstances:

 

  1  select records_total

  2  from v$controlfile_record_section

  3* where type = 'REDO THREAD'

SQL> /

 

RECORDS_TOTAL

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

            1

 

SQL>

 

And finally, here is maxloghistory:

 

  1  select records_total

  2  from v$controlfile_record_section

  3* where type = 'LOG HISTORY'

SQL> /

 

RECORDS_TOTAL

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

         1470

 

SQL>