Creating Oracle online redo log groups and members

An Oracle online redo log contains groups, and each group contains one or more members. Log Writer (LGWR) writes the same redo records to every available member of the current group. Adding a group gives Oracle another unit to use in the log-switch cycle; adding a member gives an existing group another physical copy.

Both operations use ALTER DATABASE and require the ALTER DATABASE system privilege. Perform them from the root container when administering a multitenant database. Before changing the configuration, confirm the existing group numbers, sizes, member paths, and storage policy.

Inspect the current configuration

The following query combines group-level information from V$LOG with member-level information from V$LOGFILE:

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;

V$LOG.STATUS describes the group, using values such as CURRENT, ACTIVE, INACTIVE, and UNUSED. V$LOGFILE.STATUS describes an individual member. A null member status normally means that the file is in use; values such as INVALID, STALE, or DELETED require investigation.

Add a redo log group

Use the ADD LOGFILE clause to add a group. This conventional-filesystem example explicitly supplies the group number, two members, and a size:

ALTER DATABASE ADD LOGFILE GROUP 4
  ('/u02/oradata/ORCL/redo04a.log',
   '/u03/oradata/ORCL/redo04b.log')
  SIZE 500M;

Use full server-side paths; the paths are interpreted by the database host, not by a client workstation. Place members of the same group on independent disks, storage systems, or failure domains whenever the platform allows it. Multiplexing members protects the instance when one member becomes unavailable, but it is not a substitute for archived redo logs or backups.

The GROUP number is optional, but assigning it deliberately makes administration easier. Choose an unused positive number within the database's control-file limit and avoid large gaps because skipped group numbers consume unnecessary control-file space. All members within a group have the same size. Keeping all groups the same size also produces a more predictable log-switch and checkpoint rhythm.

When Oracle Managed Files is configured through destinations such as DB_CREATE_ONLINE_LOG_DEST_1 and DB_CREATE_ONLINE_LOG_DEST_2, Oracle can generate the member names:

ALTER DATABASE ADD LOGFILE GROUP 4 SIZE 500M;

Use the filename-free form only after verifying the Oracle Managed Files destinations. Otherwise, specify every member path explicitly.

Understand the REUSE option

REUSE tells Oracle that a named operating-system file already exists and may be reused. It is not a general default and should not be added casually. Verify the exact path and required size before using it, because selecting the wrong existing file can destroy data that was expected to remain available.

ALTER DATABASE ADD LOGFILE GROUP 4
  ('/u02/oradata/ORCL/redo04a.log',
   '/u03/oradata/ORCL/redo04b.log')
  SIZE 500M REUSE;

For a new conventional-filesystem group, specify SIZE. If an existing file is named, it must match the requested size for reuse. The database creates new files when the paths do not already exist and the Oracle process has the necessary directory permissions.

Add a member to an existing group

Use ADD LOGFILE MEMBER when the group already exists and needs another copy. The simplest and least error-prone form identifies the group by number:

ALTER DATABASE ADD LOGFILE MEMBER
  '/u03/oradata/ORCL/redo02b.log'
  TO GROUP 2;

Do not specify SIZE when adding a member. Oracle creates the member with the size of the existing group. If the target file already exists, it must have that same size and the statement must include REUSE:

ALTER DATABASE ADD LOGFILE MEMBER
  '/u03/oradata/ORCL/redo02b.log' REUSE
  TO GROUP 2;

Oracle also supports identifying the target group by listing all its existing member filenames, but TO GROUP n is clearer for routine administration. A member cannot be added when every member of the target group has been lost through media failure; that condition requires a recovery-oriented response.

Verify the change

Rerun the joined V$LOG/V$LOGFILE query and confirm the expected group number, member count, size, paths, type, and member status. A newly added group can initially appear as UNUSED until Oracle writes to it. If operational policy permits, perform controlled log switches and observe the group transition:

ALTER SYSTEM SWITCH LOGFILE;

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

Review the database alert log after the operation. Unexpected INVALID or STALE member states, path errors, or LGWR messages should be resolved before the change is considered complete.

Operational checklist

1. Record the current V$LOG and V$LOGFILE configuration.
2. Confirm the ALTER DATABASE privilege and the correct database container.
3. Choose an unused group number and a size consistent with the existing design.
4. Verify free space, directory ownership, and independent failure-domain placement.
5. Use REUSE only after confirming the exact existing files and sizes.
6. Execute the appropriate ADD LOGFILE or ADD LOGFILE MEMBER statement.
7. Verify the new configuration in the dynamic performance views.
8. Review log switches and the alert log for errors.

Creating redo groups exercise

Practice selecting a safe group number, member locations, and size for the COIN database:

Creating Redo Groups - Exercise