Dropping Oracle redo log groups and members

Dropping an online redo log group and dropping one member of a group are different operations. A group is one position in Oracle's circular redo sequence. A member is one physical copy within that group. Removing either one changes the database control-file configuration and can reduce recoverability or fault tolerance if the wrong target is selected.

Use ALTER DATABASE DROP LOGFILE for a complete group and ALTER DATABASE DROP LOGFILE MEMBER for an individual member. Both operations require the ALTER DATABASE system privilege. Confirm the database, container, redo thread, group number, member path, and archive state before issuing either statement.

Inspect groups and members first

Query V$LOG for group-level state and V$LOGFILE for member paths and member-level state:

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

The group status controls whether maintenance is safe. CURRENT is the group to which Log Writer (LGWR) is writing. ACTIVE is still required for instance recovery. INACTIVE is no longer required for instance recovery. In ARCHIVELOG mode, also require ARCHIVED = YES before dropping the group or one of its members.

V$LOGFILE.STATUS describes an individual member. A null value normally means that the member is in use. INVALID means Oracle cannot access it, while STALE means Oracle suspects that its contents are incomplete or incorrect. Do not confuse these member states with the group states in V$LOG.STATUS.

Safety gate for dropping a group

Before dropping a complete online redo log group, verify all of the following:

• The target belongs to the intended redo thread.
• The target group is INACTIVE, not CURRENT or ACTIVE.
• The target is archived when the database runs in ARCHIVELOG mode.
• At least two valid groups will remain for the thread.
• The remaining configuration gives LGWR sufficient groups and preserves the intended redundancy.

If the target is current, a controlled log switch moves LGWR to the next available group:

ALTER SYSTEM SWITCH LOGFILE;

A switch does not necessarily make the former current group immediately INACTIVE. It can remain ACTIVE while needed for instance recovery. Requery V$LOG, allow checkpoint and archive processing to complete, and perform additional controlled switches only when operationally appropriate. Do not drop the group until its state satisfies every safety condition.

Drop the complete redo log group

After verifying that group 3 is the intended inactive and archived target, remove it from the database configuration:

ALTER DATABASE DROP LOGFILE GROUP 3;

Rerun the group-and-member query and confirm that the group no longer appears. When conventional filesystem files are used, Oracle updates the control file but does not delete the former member files from the operating system. Delete those files manually only after confirming that the database operation succeeded and that the paths do not belong to another database or configuration.

When Oracle Managed Files is being used, Oracle removes the managed operating-system files automatically. Do not follow an Oracle Managed Files operation with an improvised manual deletion procedure.

Safety gate for dropping one member

Dropping one member retains the group but reduces its number of physical copies. Verify that the group is neither current nor active, that it is archived when required, and that another valid member will remain. Oracle does not permit removal of the last valid member from a group that contains valid redo.

A temporarily asymmetric configuration is permitted—for example, one group can briefly have one member while the others have two—but it creates a single point of failure. Restore the intended multiplexing promptly by adding a replacement member on an independent failure domain.

Drop an individual member

Use the complete server-side member path reported by V$LOGFILE. For example:

ALTER DATABASE DROP LOGFILE MEMBER
  '/u03/oradata/ORCL/redo03b.log';

Verify that the member has disappeared from V$LOGFILE and that the group retains the expected valid members. As with a conventionally named group, Oracle removes the member from the control-file configuration but does not delete its operating-system file. Remove the file separately only after verification. Oracle Managed Files are cleaned up automatically.

Dropping is not the same as clearing

ALTER DATABASE CLEAR LOGFILE reinitializes a redo log group; it does not remove the group from the configuration. Clearing is a recovery-oriented response for situations such as a corrupt group that cannot be dropped because only two groups exist or because the corrupt group is current.

ALTER DATABASE CLEAR LOGFILE GROUP 3;

Clearing an unarchived group requires the UNARCHIVED clause and can make backups unusable when they need that redo for recovery. Treat that operation as an exceptional recovery decision, review its consequences first, and take a new backup when required. It is not a shortcut for routine redo log maintenance.

RAC and standby considerations

In Oracle Real Application Clusters, evaluate the correct redo thread and ensure that each enabled thread retains a valid configuration. In a Data Guard environment, adding or dropping an online redo log group on the primary can require a corresponding standby-side change. Review the topology before applying a single-instance example to clustered or replicated databases.

Post-change verification checklist

1. Rerun the joined V$LOG and V$LOGFILE query.
2. Confirm the intended group or member is absent and no unintended entry changed.
3. Confirm that every redo thread retains at least two valid groups.
4. Confirm that each remaining group has the intended number of valid members.
5. Review the database alert log for LGWR, archive, or file-access errors.
6. Perform conventional filesystem cleanup only after the database change is proven.
7. Restore multiplexing promptly if a group temporarily has reduced redundancy.
8. Record the change and update operational recovery documentation.

In the next lesson, you will learn how to move redo log files safely.