Recreate an Inactive Online Redo Log Group in Oracle 26ai

An inaccessible online redo log group can prevent normal database operation even when its contents are no longer needed for instance recovery. If the original files cannot be used, an eligible group can be dropped and recreated with members on usable storage. The replacement restores online redo capacity; it does not reconstruct the old redo records.

This lesson follows a single-instance primary container database (CDB) running in ARCHIVELOG mode. Group 3 in thread 1 is inaccessible, but investigation establishes that it is inactive and archived. The current control files, normal startup configuration, datafiles, and redo needed for instance recovery remain usable. At least two valid groups will remain when group 3 is removed.

The illustrated database is mounted after an unsuccessful open attempt. An authorized DBA connects to CDB$ROOT, verifies the evidence, replaces group 3, and opens the database normally. The same maintenance concepts also help with planned resizing, but a planned change on a healthy open database has different preparation steps.

Do not transfer this example unchanged to a lost current group, an active group, or a database with additional file damage. RAC and Data Guard configurations also require their applicable thread and standby coordination.

The previous lessons introduced ALTER DATABASE CLEAR LOGFILE. Clearing reinitializes an existing configured group. Explicit replacement uses DROP LOGFILE to remove a group from the database structure and ADD LOGFILE to define its replacement. These operations have different restrictions and should not be treated as interchangeable command sequences.

Start by deciding what actually failed. A temporary storage interruption may require restoring access rather than recreating files. A surviving usable member calls for consideration of member repair. Whole-group replacement is appropriate only after confirming that its old contents and the remaining configuration permit the operation.

Select the response that matches the evidence

Finding	Maintenance direction
Original files are usable after a temporary access fault is repaired	Restore access and reassess operation before discarding files.
One or more usable members survive	Preserve the surviving redo and assess member-only repair.
An eligible group needs reinitialization at its configured destinations	Consider the documented clearing procedure.
An eligible group needs new members, locations, or size	Plan explicit group replacement and verify the remaining groups.
Required redo is lost or the database has additional damage	Develop a recovery plan before changing the redo structure.

A failed clear is not automatic permission to drop a group. Oracle may already have recorded a clearing operation in the control file before an I/O failure interrupted recreation. Inspect the resulting state and use the documented failure-handling procedure. Do not substitute a DROP command merely because CLEAR returned an error.

V$LOG describes online redo groups. V$LOGFILE describes their members. Both expose a column named STATUS, but those columns answer different questions. A member marked STALE does not establish that its group is inactive.

Use this joined query when you want to examine the two levels together:

SELECT l.group#, l.thread#, l.status AS group_status,
       l.archived, f.status AS member_status, f.member
FROM v$log l
JOIN v$logfile f ON f.group# = l.group#
WHERE f.type = 'ONLINE'
ORDER BY l.group#, f.member;

A current group is the group being written by LGWR. An active group remains necessary for instance recovery. An inactive group is no longer required for that purpose, but its redo may still be needed to recover an older backup or an offline datafile. Archiving and instance-recovery requirements must be assessed separately.

When LGWR can write to a usable member, a failed member need not mean the loss of the whole group. Preserve that surviving copy. Adding a replacement member does not establish that it already contains the group's earlier redo, so do not immediately discard the last usable member on that assumption.

Member maintenance has its own restrictions. This lesson does not provide an unconditional member ADD/DROP script against current or active groups. After diagnosing a member-only failure, follow the appropriate procedure and verify that redundancy has actually been restored. Review the related lesson on multiplexing and protecting redo log files.

1. Connect and Confirm the Database State

Select the affected instance's Oracle environment and connect using an authorized account:

sqlplus / as sysdba

If the instance is shut down, mount it for this investigation:

STARTUP MOUNT

Use this startup command only when the instance is down. If an earlier open failure left it mounted, continue there. Do not restart an already running instance just to follow the example. Once mounted, verify the context:

SHOW CON_NAME

SELECT instance_name, status
FROM v$instance;

SELECT name, database_role, open_mode, log_mode
FROM v$database;

The expected context is the root of the intended primary CDB, mounted and in ARCHIVELOG mode. Redo groups belong to the CDB's redo thread; switching to an application PDB is not the way to perform this maintenance.

An open attempt may have reported ORA-00313 and ORA-00312. These identify an opening problem and an associated redo filename. Inspect accompanying operating-system errors, alert-log entries, and trace information. An open failure alone does not prove physical corruption or that every member has been lost.

2. Inspect All Groups and Members

Inspect the entire configuration so that the decision accounts for both the failed group and the groups that must remain:

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

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

Record the original member paths and group sizes before changing metadata. Cross-check every member against storage and diagnostic evidence. A blank member status does not replace physical accessibility checks, and the number of registered members does not prove all are usable.

For example, group 3 might be inactive and archived while groups 1, 2, and 4 remain valid. That gives three remaining groups after its removal. This is an illustrative configuration, not output from your database. Use your actual group inventory and investigate any additional failed group before continuing.

If the target is current, active, unarchived, or already in a clearing state, stop this sequence and reassess. Do not make the evidence fit the example by repeatedly switching logs or appending an option that discards unarchived redo.

3. Prepare the New Member Locations

The replacement uses two new filenames on storage intended to provide separate failure domains:

D:\ORADATA\ORCL\REDO03A_NEW.LOG
E:\ORADATA\ORCL\REDO03B_NEW.LOG

These Windows paths are examples. Verify the actual storage behind the drive letters; two letters can still refer to a shared point of failure. Confirm directory access for the operating-system account running Oracle, not merely for your interactive login.

For ASM or Oracle Managed Files, select the corresponding managed-storage creation method. Do not copy this filesystem example literally or assume that an ASM filename has the same lifecycle as an ordinary file. Resolve the storage fault before attempting recreation.

The example uses 200 MB members. Choose the real size using workload and operational requirements. A larger group changes switch frequency and archive volume per file; it does not repair a failed device. New filenames also avoid ambiguity about which previous file would be overwritten.

4. Drop Only the Verified Eligible Group

Recheck the prerequisites immediately before execution. In this example, group 3 is inactive and archived, recovery dependencies have been assessed, and at least two valid groups will remain. Then issue:

ALTER DATABASE DROP LOGFILE GROUP 3;

Read the statement's result before proceeding. If it fails, retain the diagnostic output and determine why the operation was rejected. Do not delete the member files at the operating-system level to bypass the restriction.

Once DROP succeeds, the old group is no longer registered in the database structure. Treat the following ADD as a separate operation. A later ROLLBACK does not restore a successfully dropped redo group and its old contents.

5. Create the Replacement Group

After the drop succeeds, create group 3 with its two new members:

ALTER DATABASE ADD LOGFILE GROUP 3
  ('D:\ORADATA\ORCL\REDO03A_NEW.LOG',
   'E:\ORADATA\ORCL\REDO03B_NEW.LOG')
  SIZE 200M;

The statement deliberately omits REUSE. Do not add it just because an existing-file error appears. Establish why the file exists and whether it belongs to another configuration before choosing any overwrite operation.

If ADD fails after DROP succeeded, stop and diagnose the creation error. Correct the destination, permissions, capacity, or naming issue while preserving the other groups. Do not continue through an unattended script as though a replacement had been created successfully.

Reusing group number 3 keeps this example easy to follow. Planned replacements can use a different unused group number. Neither choice reconstructs the original group's redo.

6. Verify Creation, Then Open Normally

Inspect the replacement before opening the mounted database:

SELECT group#, thread#, status, members, bytes
FROM v$log
WHERE group# = 3;

SELECT group#, status, member
FROM v$logfile
WHERE group# = 3
ORDER BY member;

Confirm the intended size, member count, and exact paths. A newly created group can be UNUSED until it is first used. That state is not a reason to repeat the creation operation.

Only after successful replacement and resolution of any other opening problems, open the database:

ALTER DATABASE OPEN;

This is a normal open, without RESETLOGS. The example does not call for restoring datafiles or using a backup control file. Automatic instance recovery may still occur after an abnormal shutdown. If opening produces another error, investigate that error rather than assuming more group replacements are needed.

SELECT name, open_mode, database_role
FROM v$database;

SELECT name, open_mode, restricted
FROM v$pdbs;

RMAN> BACKUP CURRENT CONTROLFILE;