Lesson 6	Moving redo log files
Objective	Move a redo log file.

Moving Redo Log Files in Oracle Database 23ai

Moving an online redo log file changes where Oracle writes the redo needed for instance recovery. You might relocate redo to replace storage, separate competing I/O workloads, standardize file locations, or correct a configuration problem. Because the control file records every redo log member, moving an operating-system file alone is not enough.

Oracle Database 23ai supports two practical strategies. An offline relocation shuts down and mounts the database while you move conventional filesystem members and update their names with ALTER DATABASE RENAME FILE. An online replacement adds new groups or members at the destination, waits until the old redo is safe to remove, and then drops the old configuration. The online method is a replacement rather than a literal rename.

Inspect the Redo Configuration First

Connect as an administrator with the ALTER DATABASE system privilege. You also need permission to create, copy, and remove files at the operating-system level when conventional filesystem storage is involved. Before making a structural change, confirm that a current database backup is available.

Query V$LOG and V$LOGFILE together so that group state and member paths are visible in one result:

SELECT l.thread#,
       l.group#,
       l.sequence#,
       l.bytes / 1024 / 1024 AS size_mb,
       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;

The V$LOG.STATUS value describes the group. CURRENT is receiving redo, ACTIVE may still be required for instance recovery, and INACTIVE is no longer needed for instance recovery. The V$LOGFILE.STATUS value describes an individual member; a null member status normally means that the member is in use. Do not confuse that null with an error.

Record every member in the affected group and determine whether the database uses Oracle Managed Files (OMF), Oracle RAC redo threads, or Data Guard. These configurations require storage- and topology-specific planning. The examples below show conventional filesystem files in a single-instance database.

Method 1: Relocate Redo Log Members During Downtime

Use this method when a maintenance window is available and you want to retain the existing group numbers. Plan to relocate all intended members consistently; do not leave the control file pointing at paths that no longer exist.

Review the query results and make a complete list of old and new absolute paths.
Shut down the database normally.
Copy or move the physical redo log members to their new locations with the appropriate operating-system tools.
Start the instance and mount the database without opening it.
Rename the members in the control file.
Open the database and verify every new path.

SHUTDOWN IMMEDIATE;
STARTUP MOUNT;

ALTER DATABASE
  RENAME FILE '/u01/oradata/ORCL/redo01a.log',
              '/u02/oradata/ORCL/redo01b.log'
           TO '/redo1/oradata/ORCL/redo01a.log',
              '/redo2/oradata/ORCL/redo01b.log';

ALTER DATABASE OPEN;

ALTER DATABASE RENAME FILE does not move or rename an operating-system file. It only replaces the recorded member names in the control file. The new files must already exist at the destinations before the database is opened.

After opening the database, query V$LOGFILE again, check the alert log for file errors, and confirm that log switches complete normally. Back up the control file after the relocation so that recovery metadata reflects the new paths.

Method 2: Replace a Redo Log Group Online

The add/switch/drop method keeps the database open. It is often preferable for an available production system because the new storage is introduced before the old storage is removed. Match the new group size to the redo sizing plan and preserve multiplexing across independent failure domains.

Add the Replacement Group

Create a new group with the required members in the destination. Choose a group number that is not already in use:

ALTER DATABASE ADD LOGFILE GROUP 4
  ('/redo1/oradata/ORCL/redo04a.log',
   '/redo2/oradata/ORCL/redo04b.log')
  SIZE 1G;

Re-query V$LOG and V$LOGFILE and confirm that the new group and both members appear without errors before changing the old configuration.

Wait Until the Old Group Is Safe to Drop

An old group cannot be dropped while it is CURRENT or ACTIVE. In ARCHIVELOG mode, it must also be archived. You can request a controlled switch when necessary:

ALTER SYSTEM SWITCH LOGFILE;

A single switch does not guarantee that the target group immediately becomes INACTIVE. Database activity, checkpoints, and the number of groups affect the transition. Re-query the views and wait until the target group is INACTIVE and its ARCHIVED value is YES when archiving is enabled.

Drop the Old Group

After the safety conditions are satisfied, remove the old group from the Oracle configuration:

ALTER DATABASE DROP LOGFILE GROUP 3;

Dropping a conventional filesystem group removes its members from the control file; it does not delete the old operating-system files. Verify the database configuration and alert log first, and only then remove the obsolete files with operating-system tools. Ensure that at least two valid redo log groups remain for each redo thread.

Replace One Member of a Multiplexed Group

When only one disk or member path is changing, add a replacement member before dropping the old one. This preserves another valid member throughout the operation.

ALTER DATABASE ADD LOGFILE MEMBER
  '/redo3/oradata/ORCL/redo01c.log'
  TO GROUP 1;

Confirm that the new member appears in V$LOGFILE and is usable. Observe Oracle's group-state and archive restrictions, and never drop the last valid member of a group. Then remove the old member from the control file:

ALTER DATABASE DROP LOGFILE MEMBER
  '/u01/oradata/ORCL/redo01a.log';

After verification, remove the obsolete conventional filesystem file. If Oracle reports that the member is needed, current, active, unarchived, or the last valid member, stop and resolve that condition rather than forcing the drop.

Oracle Managed Files, RAC, and Data Guard

Oracle Managed Files: Let Oracle create and clean up managed redo files. Do not manually move an OMF member with operating-system commands. Use add-and-drop operations with the configured OMF destination.
Oracle RAC: Check the THREAD# column and plan the change for the correct redo thread. Confirm shared-storage visibility from every applicable instance.
Data Guard: Review online and standby redo log placement on both primary and standby systems. Coordinate path changes with the standby file-management and name-conversion configuration.

Post-Change Checklist

Query V$LOG and V$LOGFILE and confirm the intended groups, members, sizes, and paths.
Verify that each group has the planned number of valid members on independent storage.
Perform and monitor log switches to confirm that Oracle can cycle through every group.
Review the alert log for redo, archive, or file-access errors.
Back up the control file and update operational documentation.
Delete obsolete conventional filesystem files only after all database checks pass.

In the next lesson, you will learn how to force a log switch manually and monitor redo log activity.

Moving Redo Log Files - Quiz

Take this quiz to see how well you understand the processes involved in moving and replacing redo log files.
Moving Redo Log Files - Quiz