Multiplexing Online Redo Log Files in Oracle 26ai

Multiplexing maintains more than one physical member of an online redo log group. Each member receives the same redo stream, so losing one member need not mean losing the group's redo. The protection depends on keeping usable copies in genuinely separate storage failure domains.

Two administrative operations are easy to confuse: adding a member supplies another copy within an existing group; adding a group supplies another destination in the sequence of log switches. This lesson first builds two groups with two members each, then adds a third group while continuing to use the same two storage locations.

The previous lesson followed redo from the memory buffer to the online logs. Here, the focus is the organization of those physical files: how to read the diagrams, inspect the actual configuration, and make changes that match the intended layout.

A group is the unit that the database selects at a log switch. A member is a physical file belonging to that group. In a multiplexed group, the members are copies, not independent logs holding different transactions. Adding a second member therefore increases physical storage without doubling the amount of distinct redo the group holds.

LGWR writes the current group for its redo thread. With two usable members, it writes both copies of that group's redo. It does not write group 1, group 2, and group 3 simultaneously for the same thread. At a switch, writing moves to the next group available in the cycle.

Each redo thread needs at least two groups. Multiple members per group are recommended for redundancy, but two members are not the database's minimum requirement for every group. Members within a group must have equal sizes. These distinctions are described in Oracle's guide to managing the redo log.

The first diagram contains two groups and four files. Read across a row to identify one group's members. Read down a disk column to identify files that share a storage failure domain.

The highlighted row pairs g1_loga.ora with g1_logb.ora. Both belong to group 1. The next row pairs g2_loga.ora with g2_logb.ora in group 2. The teal arrows identify the current write destination; the dashed arrow represents a later log switch.

After group 2, this two-group example returns to group 1 when that group can be reused. The labels describe a snapshot and an expected sequence, not permanently assigned roles. Group 2 will itself become current, and group 1 will eventually receive a new redo sequence.

The SQL examples assume a single-instance CDB named CDB1. Use SQL*Plus or another suitable administrative client connected to the intended CDB root, with catalog access and the required ALTER DATABASE privilege for changes. Online logfile clauses operate at CDB scope; a PDB does not receive a separate set simply because it has its own datafiles. See the ALTER DATABASE prerequisites.

Begin with read-only inspection. These examples are illustrative commands, not captured results from your database.

SELECT name, cdb, log_mode
FROM v$database;

SELECT group#, thread#, sequence#,
       ROUND(bytes / 1024 / 1024) AS size_mib,
       members, archived, status AS group_status
FROM v$log
ORDER BY thread#, group#;

SELECT group#, type,
       NVL(status, 'IN USE') AS member_status,
       member
FROM v$logfile
WHERE type = 'ONLINE'
ORDER BY group#, member;

V$DATABASE identifies the database and archive mode. V$LOG reports groups, their sizes, member counts, and recovery states. V$LOGFILE lists the physical members. Filtering for ONLINE keeps standby redo members out of this particular inspection.

Group status and member status answer different questions. In V$LOG, CURRENT identifies the current group; ACTIVE identifies a noncurrent group still needed for crash recovery. INACTIVE means it is no longer needed for instance recovery. UNUSED can identify a newly added group that has not been written.

In V$LOGFILE, a null status means the member is in use. The query displays that null as IN USE; this is a display label, not another Oracle status. INVALID means inaccessible, and STALE means incomplete contents. Neither is a substitute for a group's recovery state.

Read the two result sets together. A group can have two registered member paths without both currently being usable. Conversely, an INACTIVE group is not a failed group; it describes recovery eligibility, not a storage fault.

For example, a CURRENT group, an archived ACTIVE group, and an archived INACTIVE group are not contradictory observations. The first is receiving writes, the second is still required for crash recovery, and the third has progressed beyond that requirement. Read each row's archive flag and status together instead of deciding that every archived group can immediately be removed.

Now assume group 3 does not exist, the first two groups have their intended members, and the proposed group 3 paths are available. Create the new group with one member in each of the same two failure domains:

ALTER DATABASE ADD LOGFILE GROUP 3 (
  '/u01/oradata/CDB1/g3_loga.ora',
  '/u02/oradata/CDB1/g3_logb.ora'
) SIZE 512M;

This statement creates one group containing two members. It does not create a third disk, and a second ADD MEMBER statement for g3_logb.ora is unnecessary. The 512M size is illustrative, assuming compatible existing group sizes; select the actual size and check block-size compatibility for the target system.

Plan storage for both copies. In this example, the two 512 MiB member files consume about 1 GiB of file space together, while representing one group's 512 MiB redo capacity. Three such groups with two members each require about 3 GiB for their six member files. Archived logs and backups require additional storage outside this calculation.

Query V$LOG and V$LOGFILE again. Confirm group 3, its two paths, and its size. It can appear UNUSED before the redo thread first writes it. The diagram below explains how it participates in the cycle, not an assertion that the command immediately changed the current group.

Group numbering is an administrative identifier. Do not confuse it with SEQUENCE#, which identifies successive uses of redo within a thread. Configuration limits belong to database creation and platform planning; they are not obtained by selecting MAXLOGFILES and MAXLOGMEMBERS from V$DATABASE. Consult the CREATE DATABASE reference when reviewing those limits.

The second diagram has three groups and six physical files. The third row extends the switching cycle, while the disk columns retain the same meaning as in the first diagram.

Group 1 is current in this snapshot. Group 2 follows when reusable, then group 3, and finally group 1 again. The dashed arrows describe successive switches. They are not additional LGWR write arrows. The labels NEXT WHEN REUSABLE and THEN WHEN REUSABLE are explanations, not values returned by V$LOG.STATUS.

The first two rows describe the four-file layout; all three describe the six-file layout. A third member would add another copy to a row. A third group adds the new row shown here. That distinction is useful when translating a storage request into the correct SQL operation.

Adding group 3 does not require dropping it afterward. Group removal is a separate maintenance decision, for example when correcting a configuration after reviewing capacity and reuse behavior. A drawing that contains fewer groups is not, by itself, a reason to remove working redundancy or capacity.

For an already-used group 3, verify that it is INACTIVE, that required archiving has completed, and that at least two valid groups will remain in its thread. Check the intended database and group number before the optional operation:

SELECT group#, thread#, status, archived, members
FROM v$log
ORDER BY thread#, group#;

-- Optional maintenance after verifying the stated conditions:
ALTER DATABASE DROP LOGFILE GROUP 3;

A switch away from a CURRENT group is not enough to prove that recovery no longer needs it. Recheck the state. An UNUSED group has a different history from an already-used group; evaluate its actual state rather than forcing every case through the same example.

Removing one member is different from removing its group. Before member maintenance, confirm another usable member remains and that the group's state and archive requirements allow the operation. Do not drop a good copy just to test the diagram. Follow the documented group and member maintenance procedures, then verify the resulting configuration.

Check your understanding of groups, members, switching, and storage placement in the Multiplexed Redo Logs - Quiz.