Multiplexing the Redo Log Files

To protect against failure of the redo logs, Oracle allows redo logs to be multiplexed. With multiplexed redo logs two or more identical copies can be placed in separate locations. The log writer process (LGWR) writes the same redo information to each multiplexed log.

Multiplexing uses groups of redo log files. A group contains a redo log and all of its multiplexed copies. Each copy or member is identical. It is required that there are at least two groups and in the case of multiple members per group that all members be the same size. It is recommend but not required that each group has the same number of members.

First we take a look at our current redo log groups and see how many members are in each group.

SQL> select group#, members, bytes from v$log;

    GROUP#    MEMBERS      BYTES
---------- ---------- ----------
         1          1   52428800
         2          1   52428800
         3          1   52428800

SQL>

Here we see that there are three redo log groups with one member each and that the log files are 50MB in size.

In this example we will add one member to each group using ALTER DATABASE ADD LOGFILE MEMBER TO GROUP.

SQL> alter database
  2  add logfile member '/u03/app/oracle/oradata/orcl/redo/redo01_b.log'
  3  to group 1;

Database altered.

SQL> alter database
  2  add logfile member '/u03/app/oracle/oradata/orcl/redo/redo02_b.log'
  3  to group 2;

Database altered.

SQL> alter database
  2  add logfile member '/u03/app/oracle/oradata/orcl/redo/redo03_b.log'
  3  to group 3;

Database altered.

SQL> host

Now when we execute the first query we see that each group has two members.

SQL> select group#, members, bytes from v$log;

    GROUP#    MEMBERS      BYTES
---------- ---------- ----------
         1          2   52428800
         2          2   52428800
         3          2   52428800

SQL>

If you happen to be need another redo log group you can multiplex the group at creation by specifying two file names. Below is an example of creating a multiplexed redo log group with two members with ALTER DATABASE ADD LOGFILE GROUP.

SQL> alter database
  2  add logfile group 4
  3  ('/u02/app/oracle/oradata/orcl/redo/redo04.log','/u03/app/oracle/oradata/orcl/redo/redo04_b.log')
  4  size 50M;

Database altered.

SQL>

Now looking at V$LOG we see four groups with two members each.

SQL> select group#, members, bytes from v$log;

    GROUP#    MEMBERS      BYTES
---------- ---------- ----------
         1          2   52428800
         2          2   52428800
         3          2   52428800
         4          2   52428800

SQL>