Change the size of the RedoLog group (Statement collection)

[Former Member]

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;

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

select group#, member from V$logfile;

select group#, status from V$log;

To add a 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 a RedoLog group or if you want to delete a whole 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 as shown 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\BWQ\ORIGLOGA\LOG_G1M1.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 dropping a RedoLog group or a member from the database configuration, you have to delete it from the filesystem manually. If there is some storage available, you can chose a different 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.