Size Online Redo Logs

Is this still required for Oracle 23ai?

Yes—if you manage the database (on-premises or co-managed cloud such as OCI Base Database Service / Exadata Database Service), online redo log sizing is still a DBA responsibility because log switch rate and redo I/O directly affect throughput and recovery behavior. In Oracle’s multitenant architecture, redo is managed at the CDB level (not per PDB), so sizing decisions apply to the whole container database workload.

What “right-sized” means

Right-sizing is not a single number. The goal is to choose a redo log size that:

• Prevents excessively frequent log switches (which can increase checkpoint pressure and overhead).
• Avoids extremely infrequent switches (which can increase recovery exposure windows and operational risk).
• Matches your storage performance and workload (especially commit-heavy OLTP vs. batch loads).

Step 1: Inspect current redo log group sizes

In modern environments you will typically see ASM/OMF locations (for example +DATA), rather than manual file system paths. Use the following query to list redo log groups, members, size, and status:

 SELECT
  l.group#,
  l.thread#,
  l.bytes/1024/1024 AS size_mb,
  l.members,
  l.status,
  lf.member
FROM v$log l
JOIN v$logfile lf
  ON lf.group# = l.group#
ORDER BY l.thread#, l.group#, lf.member;

Step 2: Measure log switch frequency

The most practical way to size logs is to observe how often switches occur, then adjust sizes to reach a stable operational cadence. The following query summarizes switches per hour (recent history):

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

If switches are occurring very frequently during peak periods, increase redo log size (or review workload/redo generation drivers). If switches are very rare, you may be able to reduce size—provided you do not introduce other risks for your recovery objectives.

Step 3: Resize correctly (add new, then drop old)

Oracle does not “resize in place” for online redo logs. The standard approach is:

1. Add a new redo log group (or new members) using the target size.
2. Force log switches until the old group becomes inactive.
3. Drop the old group.

-- Example: add a new group (adjust thread# for RAC)
ALTER DATABASE ADD LOGFILE GROUP 4 ('+DATA') SIZE 1024M;

-- Switch to advance log usage (repeat as needed)
ALTER SYSTEM SWITCH LOGFILE;

-- When the old group is INACTIVE, drop it
ALTER DATABASE DROP LOGFILE GROUP 1;

What to watch when “redo is slow”

Redo contention is usually diagnosed by wait events and I/O latency, not by legacy-era parameter tweaking. If commit performance is poor, focus on redo-related waits (for example log file sync) and the storage path for redo. Use AWR/ASH (or cloud metrics) to confirm whether the bottleneck is LGWR, I/O latency, or application commit behavior.

Sizing Redo Log Files - Exercise