In Oracle 23c, multiplexing redo log files means maintaining multiple copies of each redo log file in different locations to ensure data availability and fault tolerance. If one copy is lost or corrupted, the other copies can continue to be used without interrupting database operations.
✅ Why Multiplex Redo Logs?
Reduces the risk of data loss in the event of disk failure.
Required for high availability configurations.
Part of Oracle’s best practices for production environments.
🔧 Steps to Multiplex Redo Log Files
🔹 Step 1: View Current Redo Log Configuration
SELECT group#, member FROM v$logfile;
🔹 Step 2: Add a New Member to an Existing Redo Log Group
ALTER DATABASE ADD LOGFILE MEMBER
'/u02/oracle/redo/redo1b.log' TO GROUP 1;
ALTER DATABASE ADD LOGFILE MEMBER
'/u02/oracle/redo/redo2b.log' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER
'/u02/oracle/redo/redo3b.log' TO GROUP 3;
📌 The new members should be on different disks or mount points than the existing ones.
🔹 Step 3: Verify Multiplexing
SELECT group#, status, member FROM v$logfile ORDER BY group#;
You should now see multiple members per group (i.e., same group# with different file paths).
⚠️ Notes and Best Practices
Do not manually delete log files, use ALTER DATABASE DROP LOGFILE MEMBER to remove them safely.
Redo log files in the same group are identical copies.
Use Oracle Managed Files (OMF) for automatic file naming if desired, especially in CDB/PDB environments.
For Oracle RAC, redo logs must be stored on shared storage accessible to all instances.
🔄 Optional: Drop an Existing Logfile Member
If you need to remove a faulty or unneeded member:
ALTER DATABASE DROP LOGFILE MEMBER '/u01/oracle/redo/redo1a.log';
Make sure at least one valid member remains in each group.
Visual Diagram of redo log multiplexing in Oracle 23c
Multiplexing Redo Log Files
Most Oracle installations do not maintain the default redo log file organization because it becomes a single point of failure. If one of your redo log files becomes corrupted, the system stops working. To address this problem, Oracle provides you with a way to multiplex redo log files.
Multiplexed redo log files
The following diagram explains multiplex redo log files.
The uploaded image visually represents multiplexed redo log files in Oracle using two disks and two redo log groups. Here's the breakdown:
🟠 Disks Used
DISK 1
DISK 2
These disks are used to store multiple copies of the redo log files for redundancy and fault tolerance.
🟦 Redo Log Groups
Redo Log Group 1
g1 loga.ora (stored on DISK 1)
g1 logb.ora (stored on DISK 2)
Redo Log Group 2
g2 loga.ora (stored on DISK 1)
g2 logb.ora (stored on DISK 2)
Each group has two members, providing multiplexed logging—identical copies stored on separate disks.
🔄 Log Writer (LGWR) Process
LGWR simultaneously writes to both members of each group.
If one file is lost or damaged, the instance can continue using the remaining copy.
This is a classic and recommended setup for high-availability Oracle databases.
g1_loga.ora:This is member 1 of group 1 stored on disk 1.
g1_logb.ora:This is member 2 of group 1 stored on disk 2.
g2_loga.ora:This is member 1 of group 2 stored on disk 1.
g2_loga.ora:This is member 2 of group 2 stored on disk 2.
LGWR:LGWR will write to members 1 & 2 of group 1. When a switch occurs, LGWR will write to members 1 & 2 of group 2.
disk 1:If disk 1 fails, LGWR will continue writing to disk 2 without database failure.
disk 2:If disk 2 fails, LGWR will continue writing to disk 1 without database failure.
redo group 1:There must be at least 2 members of the same size in each group. Each group should contain the same number of members.
redo group 2:There must be at least 2 members of the same size in each group. Each group should contain the same number of members.
✅ Oracle 23c – Add a New Redo Log Group
Updated for Oracle 23c, reflecting modern terminology and practices
(e.g., `SPFILE`/`PFILE`, use of SQL\*Plus or SQL Developer instead of Server Manager):
In Oracle 23c, redo log configuration is still governed by structural limits defined during database creation, such as:
MAXLOGFILES: The maximum number of redo log groups allowed.
MAXLOGMEMBERS: The maximum number of members (copies) per group.
If you're planning to add a new redo log group—for example, to support a new disk or improve I/O balancing—you should first confirm that these limits can accommodate the addition.
You can check the current limits by querying:
SELECT maxlogfiles, maxlogmembers FROM v$database;
Assuming the limits are sufficient, and you're working with a third disk drive, you can now add a third redo log group with two multiplexed members on separate disks:
ALTER DATABASE ADD LOGFILE GROUP 3 (
'/u03/oracle/oradata/ORCL/g3_loga.ora',
'/u04/oracle/oradata/ORCL/g3_logb.ora'
);
🔍 Note*: Replace `/u03` and `/u04` with the actual mount points or directories on your system.
🛠 Best Practices in Oracle 23c
Use Oracle Managed Files (OMF) where possible to simplify log file management.
Prefer spfile over init.ora (pfile) for dynamic configuration management.
Leverage Enterprise Manager, SQL Developer, or sqlplus for modern DBA tasks.
The results of this are shown by the following diagram:
The image represents an Oracle redo log configuration using multiplexed redo log files across two disks and three redo log groups.
Here is the detailed analysis:
🟠 Disks Involved
DISK 1
DISK 2
These disks are used to physically separate mirrored redo log members to protect against disk failure.
🟦 Redo Log Groups and Members
Redo Log Group 1
g1 loga.ora — stored on DISK 1
g1 logb.ora — stored on DISK 2
Redo Log Group 2
g2 loga.ora — stored on DISK 1
g2 logb.ora — stored on DISK 2
Redo Log Group 3
g3 loga.ora — stored on DISK 1
g3 logb.ora — stored on DISK 2
Each redo log group contains two members, located on separate disks to ensure redundancy. This is a multiplexed redo log configuration.
🔄 LGWR (Log Writer)
The LGWR process writes simultaneously to both members of the active redo log group.
It rotates through groups in cyclical order during log switches.
This configuration ensures high availability and data protection, even in the event of a disk failure affecting one member of any group.
g1_loga.ora: This is member 1 of group 1 stored on disk 1.
g1_logb.ora: This is member 2 of group 1 stored on disk 2.
g2_loga.ora: This is member 1 of group 2 stored on disk 1.
g2_loga.ora: This is member 2 of group 2 stored on disk 2.
g3_loga.ora: This is member 1 of group 3 stored on disk 1.
g3_loga.ora: This is member 2 of group 3 stored on disk 2.
LGWR: LGWR will write to members 1 & 2 of group 1. When a switch occurs, LGWR will write to members 1 & 2 of group 2. On the next switch LGWR will write to members 1 & 2 of group 3.
disk 1: If disk 1 fails, LGWR will continue writing to disk 2 without database failure.
disk 2: If disk 2 fails, LGWR will continue writing to disk 1 without database failure.
redo group 1: There must be at least 2 members of the same size in each group. Each group should contain the same number of
members.
redo group 2: There must be at least 2 members of the same size in each group. Each group should contain the same number of members.
redo group 3: There must be at least 2 members of the same size in each group. Each group should contain the same number of
members.
Dropping an existing redo log group
If we take the diagram with three redo log file groups and we want to delete the third group, the command in SQL * PLus would be:
alter database drop logfile group 3
This would return us to the first diagram in this lesson with two redo log file groups. The next lesson is about archive log files.
Multiplexed Redo Logs - Quiz
Before you move on to the next lesson, click the Quiz button to answer some questions about redo logs. Multiplexed Redo Logs - Quiz
