Resize of an Oracle RedoLog Group

To collect information regarding the active RedoLog groups and members configuration on database level, you can choose the following SQL-Statements:

col MEMBER format a38;
SELECT v$logfile.group#, v$logfile.member, v$log.bytes/1024/1024 as FS_SIZE, v$log.status FROM v$logfile LEFT JOIN v$log ON v$logfile.group#=v$log.group# ORDER BY v$logfile.group#, v$logfile.member;

Example for an output of the statement:

The statement above is an combination of the following two simple statements:

select group#, member from V$logfile;
select group#, status from V$log;

For extending an member file to an existing RedoLog group, please chose the following statements:

alter database add logfile member '<path and filename of the new RedoLog member>' to
group <Group number from v$logfile.group#>;

If a group is flagged as “active” in the status column, it is important to switch the logfile before you resize the group/member or you remove a group/member. This can be done by the execution of the following statements. The second statement recycle the RedoLog groups by writing the content into the database. After recycling the RedoLog groups, they get the status “inactive”.

alter system switch logfile;
alter system checkpoint;

If you want to remove a member of the RedoLog group or want to delete a hole group, use the following statements:

alter database drop logfile member '<path and filename of the new RedoLog member>';
alter database drop logfile group <Group number from v$logfile.group#>;

If you have four RedoLog groups like in the screenshot above and the group two has the status “active” and group 4 has the status “current”, a sequence of statements can look like this:

alter database drop logfile group 1;
alter database add logfile group 1 (
'E:\ORACLE\BWE\ORIGLOGA\LOG_G11M1.DBF',
'H:\ORACLE\BWQ\MIRRLOGA\LOG_G1M2.DBF'
) SIZE 300M;
alter system switch logfile;
alter system checkpoint;
alter database drop logfile group 2;
alter database add logfile group 2 (
'E:\ORACLE\BWQ\ORIGLOGA\LOG_G2M1.DBF',
'H:\ORACLE\BWQ\MIRRLOGA\LOG_G2M2.DBF'
) SIZE 300M;
alter system switch logfile;
alter database drop logfile group 3;
alter database add logfile group 3 (
'E:\ORACLE\BWQ\ORIGLOGA\LOG_G3M1.DBF',
'H:\ORACLE\BWQ\MIRRLOGA\LOG_G3M2.DBF'
) SIZE 300M;
alter system switch logfile;
alter database drop logfile group 4;
alter database add logfile group 4 (
'E:\ORACLE\BWQ\ORIGLOGA\LOG_G4M1.DBF',
'H:\ORACLE\BWQ\MIRRLOGA\LOG_G4M2.DBF'
) SIZE 300M;
alter system switch logfile;
alter system checkpoint;

After you drop a RedoLog group or a member from the database configuration, you has to delete it from the filesystem manually. If there is some storage available, you can chose an other name for the files (for example “LOG_Group4Member1.DBF”) and delete the old member files later.

Please use the statements in this post with care.