Manually Switching Redo Log Files in Oracle Database 23ai

Oracle Database records every change in the online redo log before the related modified blocks are written permanently to data files. The log writer process, LGWR, writes redo entries to every member of the current redo log group. When that group fills, Oracle stops writing to it, advances to the next available group, assigns a new log sequence number, and continues. This transition is a log switch. It normally occurs automatically, but a database administrator can request one when a defined operational task requires a clean redo boundary.

The command is simple, yet its consequences extend beyond changing a group number. A switch can start archiving, influence checkpoint activity, send another sequence toward standby databases, and expose problems in archive destinations or storage. A responsible administrator therefore inspects the configuration before the command and verifies the outcome afterward. This lesson develops that complete procedure rather than treating repeated switches as a harmless shortcut.

Understand the Online Redo Cycle

An Oracle database has at least two online redo log groups for each enabled thread. A group can contain one member, although multiplexing members on independent storage is the safer configuration. LGWR writes identical redo to all valid members of the current group. When Oracle switches, the next reusable group becomes current and receives a higher sequence number. In ARCHIVELOG mode, ARCn processes copy filled groups to configured archive destinations before Oracle reuses them.

The status of a group explains its role in the cycle. CURRENT identifies the group receiving redo. ACTIVE identifies a previous group still needed for instance recovery. INACTIVE means the group is no longer needed for instance recovery, although it may still require archiving or media recovery. UNUSED commonly identifies a newly added group that has not received redo. A switch moves the current designation, but it does not guarantee that the former group immediately becomes inactive.

A sequence number identifies the redo stream within a thread. Each successful switch closes one sequence and opens another. Sequence numbers help administrators correlate online groups, archived logs, backup records, and Data Guard transport. In Oracle Real Application Clusters, each enabled thread has its own sequence progression, so always interpret a sequence together with its thread number.

Prepare Before Forcing a Switch

Use a privileged administrative session authorized to issue ALTER SYSTEM. Confirm that the database is open and that no unresolved storage, archive, or standby incident would be worsened by producing another completed sequence. Check free space and destination status when the database runs in ARCHIVELOG mode. If Data Guard is configured, review transport and apply health rather than assuming that a locally successful switch proves end-to-end protection.

Start with a joined view of groups and members:

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

Record the current thread, group, and sequence. Verify that another group is available and that every group has the intended members. A null V$LOGFILE.STATUS value normally means the member is in use; values such as INVALID or STALE require investigation. Note whether the current group has filled only partially. A forced switch closes it at its present position, so frequent unnecessary switches can create many small archived logs.

Review recent switch history to establish a baseline:

SELECT thread#,
       sequence#,
       first_time,
       next_time
FROM   v$log_history
ORDER  BY first_time DESC
FETCH FIRST 12 ROWS ONLY;

If switches already occur every few minutes during ordinary workload, investigate redo sizing, application behavior, and checkpoint pressure before adding manual switches. The goal is not to maximize the sequence number. The goal is to create one intentional boundary for a specific operational reason.

Force the Log Switch

After the prechecks pass, issue the following statement from SQL Plus, SQLcl, or another administrative client:

ALTER SYSTEM SWITCH LOGFILE;

Oracle closes the current group for the connected instance's thread and selects the next available group. LGWR begins writing to that new current group, and Oracle increments the sequence. In ARCHIVELOG mode, the closed group becomes eligible for archiving. The exact wait experienced by the session depends on the database state and whether Oracle can acquire and initialize the next group safely.

Do not repeatedly submit the command merely because a target group still shows ACTIVE. The database may need a checkpoint to advance the recovery boundary before that group becomes INACTIVE. Workload volume, dirty buffers, I/O capacity, group count, and checkpoint settings all affect that transition. Re-query, interpret the state, and allow Oracle to complete the necessary work.

Verify the New Current Group

Immediately repeat a focused query and compare it with the recorded values:

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

For the relevant thread, another group should now be CURRENT with a higher sequence number. The former current group will commonly be ACTIVE before becoming INACTIVE. Its ARCHIVED value can change independently of its recovery status. An archived group may remain active, and an inactive group is not automatically proof that every configured archive destination received it.

Confirm the historical record:

SELECT thread#,
       sequence#,
       first_time,
       next_time
FROM   v$log_history
WHERE  first_time > SYSDATE - (1/24)
ORDER  BY thread#, sequence#;

In ARCHIVELOG mode, query archived-log records for the completed sequence:

SELECT thread#,
       sequence#,
       dest_id,
       archived,
       applied,
       status,
       completion_time
FROM   v$archived_log
WHERE  sequence# >= :completed_sequence
ORDER  BY thread#, sequence#, dest_id;

Multiple rows for one sequence can represent different destinations or archived copies. Interpret APPLIED in the context of standby records; it is not meaningful as a general local archive-success flag. Also inspect the alert log and the archive destination status used by your operating runbook. A command that returns successfully confirms the database switched locally, not that every downstream copy or apply operation has finished.

Switch, Checkpoint, and Archive Current Are Different

Three administrative statements are often confused because all can participate in redo operations. They solve different problems.

ALTER SYSTEM SWITCH LOGFILE closes the current sequence for the connected instance and moves LGWR to another group in that thread. Use it when the objective is specifically to force a log switch, such as moving away from a group before planned maintenance.

ALTER SYSTEM CHECKPOINT requests a checkpoint. A checkpoint advances the point at which modified database blocks required for recovery have been written to data files. It does not mean “switch the log.” A log switch can trigger checkpoint-related work, but issuing a checkpoint and issuing a switch are not interchangeable actions.

ALTER SYSTEM CHECKPOINT;

ALTER SYSTEM ARCHIVE LOG CURRENT is archive-oriented. It forces a switch and requests archiving of the current redo. In a single-instance database, administrators often use it when they need the current redo archived before a backup or another coordinated operation. In RAC, it archives current redo for all enabled threads, whereas SWITCH LOGFILE affects only the current instance's thread.

ALTER SYSTEM ARCHIVE LOG CURRENT;

Select the statement that matches the operational outcome. Do not substitute one based solely on familiarity. For a RAC-wide archive boundary, follow the approved cluster and backup runbook; do not connect to one instance, issue SWITCH LOGFILE, and assume every thread advanced.

Legitimate Reasons to Switch Manually

Prepare Redo Maintenance

You cannot drop the current group or its current members as routine maintenance. A manual switch moves LGWR away from that group. However, maintenance still requires all applicable drop conditions. The old group may remain active, may need archiving, and must not be removed if doing so would leave too few valid groups or members. Switch once, monitor the state, and proceed only after the maintenance gate is satisfied.

Validate Archive Processing

A controlled switch produces a completed sequence that can test whether local archive destinations are writable and appropriately configured. Record the sequence first, perform one switch, and trace that exact sequence through the destination records. This is safer and more informative than generating many sequences without observing them. Stop if destination errors, space pressure, or an archiver backlog appears.

Validate Data Guard Transport and Apply

In a Data Guard environment, a planned switch can create a known sequence for transport verification. Confirm that the primary archived it, the standby received it, and Redo Apply processed it according to the protection and lag objectives. Use the site's Data Guard views and broker commands. Never reproduce the legacy exercise that disables a destination and deletes required logs merely to manufacture a gap. Gap recovery is a separate advanced procedure with different safeguards.

Create an Operational Boundary

Some backup, cloning, auditing, or diagnostic runbooks ask for an archive boundary around a controlled event. Follow the exact runbook because its intended command may be ARCHIVE LOG CURRENT, not merely SWITCH LOGFILE. Record timestamps, thread numbers, and sequences so another administrator can prove which redo contains the event.

Troubleshoot Unexpected Results

The Switch Waits or Fails

Oracle must find a reusable next group. If every candidate is active, unarchived, inaccessible, or otherwise unavailable, the switch can wait or report an error. Check the alert log, V$LOG, member status, checkpoint progress, archive processes, and destination capacity. Correct the blocking condition. Do not use destructive commands to clear or delete redo unless a documented recovery decision explicitly requires them.

The Previous Group Remains Active

This can be normal. ACTIVE means the group is still needed for instance recovery, even though it is no longer current. Allow checkpoint processing to advance and monitor the group. If it remains active unusually long, evaluate write I/O, checkpoint behavior, long-running workload, and alert-log messages. Repeated switches can make the backlog worse by creating more groups that need processing.

Archiving Does Not Complete

Inspect destination status, filesystem or recovery-area capacity, permissions, network services, and archiver errors. A full archive destination can eventually prevent Oracle from reusing groups and stall database activity. Restore a healthy destination according to the site's recovery procedure. Deleting unbacked archived logs or changing destination parameters casually can weaken recoverability and Data Guard protection.

Switches Occur Too Frequently

Frequent automatic switches often indicate undersized redo groups for the workload. They can increase checkpoint demand and create excessive archived-log files. Measure redo generation across representative busy periods, review switch history, and size groups according to recovery and operational objectives. Keep groups within a thread consistently sized unless a deliberate migration is underway.

The Standby Does Not Show the Sequence

Separate primary switch success from transport and apply status. Confirm that the primary archived the sequence, the destination is enabled and valid, transport services can reach the standby, and the standby registered the log. Then evaluate apply lag and Data Guard messages. In RAC, include the correct thread in every comparison. Escalate through the Data Guard runbook instead of forcing a loop of additional switches.

A Safe Manual-Switch Runbook

1. State the reason for the switch and decide whether the real requirement is a thread switch, a checkpoint, or an archive-current boundary.
2. Confirm the administrative privilege, open database state, healthy redo members, and an available next group.
3. Check archive destinations, capacity, and Data Guard health when applicable.
4. Record the current thread, group, sequence, status, archive state, and time.
5. Issue one ALTER SYSTEM SWITCH LOGFILE statement.
6. Verify the new current group and higher sequence in V$LOG.
7. Confirm the completed sequence in history and archive records.
8. For Data Guard, verify transport, registration, and apply using the intended thread and destination.
9. Review the alert log and investigate errors before requesting another switch.
10. Continue any planned redo maintenance only after its separate safety conditions are satisfied.

A manual log switch is a controlled database operation, not merely a one-line convenience. It advances LGWR to another group, while verification proves that redo remains recoverable and available. With this procedure, you can switch for maintenance or validation. The next lesson reviews the redo log administration skills covered throughout this module.

Manually Switching Log Files - Quiz