Oracle Database 23ai: Properly Placing and Multiplexing Redo Log Files

Best practices guide for Oracle AI Database (23ai)

1. Proper Placement of Redo Log Files

Key principles:

• Performance first: Place on the fastest available storage (lowest latency, high IOPS for sequential writes). In ASM, prefer the +DATA disk group.
• Separation from data files: Reduce I/O contention between LGWR and DBWR processes.
• ASM pattern (recommended): Primary members in +DATA, multiplexed members in +RECO (Fast Recovery Area) or different failure groups.
• Avoid single points of failure: Never put all members of a group on the same physical disk/controller/failure group.
• Oracle Managed Files (OMF): Highly recommended. Configure DB_CREATE_ONLINE_LOG_DEST_n parameters.

Example: Creating a New Multiplexed Group in ASM

ALTER DATABASE ADD LOGFILE 
  GROUP 4 ('+DATA', '+RECO') SIZE 1G;

Sizing Guidelines

Target log switches every 15–30 minutes during peak load.

Number of groups: Minimum 2 (required). Recommended 3–4+ per thread (especially RAC).

2. Multiplexing Redo Log Files

Oracle strongly recommends multiplexing. Each redo log group consists of one or more identical members. LGWR writes concurrently to all members of the current group.

Best Practices

• At least 2 members per group
• Keep the configuration symmetrical (all groups same number of members and size)
• Members of the same group on different physical disks / failure groups

SQL Examples

Check Current Configuration

-- Groups
SELECT group#, thread#, bytes/1024/1024 AS "Size MB", members, status 
FROM v$log ORDER BY group#;

-- Members
SELECT group#, member, status FROM v$logfile ORDER BY group#;

Add a New Multiplexed Group

ALTER DATABASE ADD LOGFILE 
  GROUP 10 ('+DATA/redo10a.log', '+RECO/redo10b.log') SIZE 1G;

Add a Member to an Existing Group

ALTER DATABASE ADD LOGFILE MEMBER '+RECO/redo01b.log' TO GROUP 1;

Force Log Switches & Cleanup (for resizing)

ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;
ALTER DATABASE DROP LOGFILE GROUP 1;  -- Only when INACTIVE

Overall Best Practices Summary

Monitoring

-- Recent switches
SELECT TRUNC(first_time, 'HH24') AS hour, COUNT(*) AS switches
FROM v$log_history
WHERE first_time > SYSDATE - 1
GROUP BY TRUNC(first_time, 'HH24')
ORDER BY hour;

Purpose of redo logs

Protecting redo logs

Mirroring: hardware versus software