Moving Online Redo Log Files

We found a lot of sessions waiting for Log File Sync and in turn found that LGWR was waiting for Log File Parallel Write.

We have 2 dedicated disks for Online redo logs. They are multiplexed, with members of the same group on different disks. When a Log Switch occurs 1 member of the Redo Logs being Archived gets READS from the ARCH process while both members of the CURRENT group are receiving WRITE requests from LGWR. We decided to add 2 more disks and split the even numbered groups from the uneven numbered groups. So that we can separate the READS and WRITES.

Current layout:

SQL>select GROUP#, STATUS, TYPE, MEMBER from v$logfile;

G# STATUS TYPE MEMBER
-------------------------
1 ONLINE /dev/vx/rdsk/redodg/casprd_REDO1
1 ONLINE /dev/vx/rdsk/redodg/casprd_REDO1b
2 ONLINE /dev/vx/rdsk/redodg/casprd_REDO2
2 ONLINE /dev/vx/rdsk/redodg/casprd_REDO2b
3 ONLINE /dev/vx/rdsk/redodg/casprd_REDO3
3 ONLINE /dev/vx/rdsk/redodg/casprd_REDO3b
4 ONLINE /dev/vx/rdsk/redodg/casprd_REDO4
4 ONLINE /dev/vx/rdsk/redodg/casprd_REDO4b
5 ONLINE /dev/vx/rdsk/redodg/casprd_REDO5
5 ONLINE /dev/vx/rdsk/redodg/casprd_REDO5b
6 ONLINE /dev/vx/rdsk/redodg/casprd_REDO6
6 ONLINE /dev/vx/rdsk/redodg/casprd_REDO6b

SQL> select GROUP#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS,FIRST_TIME from v$log;

G# S# BYTES MEMBERS ARC STATUS FIRST_TIME
------------------------------------------
1 45181 2147483648 2 YES INACTIVE 03-SEP-2008 08:00:18
2 45182 2147483648 2 YES INACTIVE 03-SEP-2008 08:45:28
3 45183 2147483648 2 NO CURRENT 03-SEP-2008 09:00:47
4 45178 2147483648 2 YES INACTIVE 03-SEP-2008 06:25:02
5 45179 2147483648 2 YES INACTIVE 03-SEP-2008 07:02:21
6 45180 2147483648 2 YES INACTIVE 03-SEP-2008 07:48:50

We use RAW devices, so after the Unix Admins created the new RAW volumes on the new disks I simply added the members:

Creating Redo Log Members for the even groups on the 2 new disks:

ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO2c' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO2d' TO GROUP 2;
ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO4c' TO GROUP 4;
ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO4d' TO GROUP 4;
ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO6c' TO GROUP 6;
ALTER DATABASE ADD LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO6d' TO GROUP 6;

The status of the new log member is shown as INVALID. This is normal and it will change to active (blank) when it is first used.

SQL> select GROUP#,STATUS,TYPE,MEMBER from v$logfile order by group#, member;

G STATUS TYPE MEMBER
-----------------------------------------
1 ONLINE /dev/vx/rdsk/redodg/casprd_REDO1
1 ONLINE /dev/vx/rdsk/redodg/casprd_REDO1b
2 ONLINE /dev/vx/rdsk/redodg/casprd_REDO2
2 ONLINE /dev/vx/rdsk/redodg/casprd_REDO2b
2 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO2c
2 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO2d
3 ONLINE /dev/vx/rdsk/redodg/casprd_REDO3
3 ONLINE /dev/vx/rdsk/redodg/casprd_REDO3b
4 ONLINE /dev/vx/rdsk/redodg/casprd_REDO4
4 ONLINE /dev/vx/rdsk/redodg/casprd_REDO4b
4 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO4c
4 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO4d
5 ONLINE /dev/vx/rdsk/redodg/casprd_REDO5
5 ONLINE /dev/vx/rdsk/redodg/casprd_REDO5b
6 ONLINE /dev/vx/rdsk/redodg/casprd_REDO6
6 ONLINE /dev/vx/rdsk/redodg/casprd_REDO6b
6 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO6c
6 INVALID ONLINE /dev/vx/rdsk/redodg/casprd_REDO6d

SQL> select GROUP#,SEQUENCE#,BYTES,MEMBERS,ARC,STATUS,FIRST_TIME from v$log; 

G# S# BYTES MEMBERS ARC STATUS FIRST_TIME 
------------------------------------------------
1 45181 2147483648 2 YES ACTIVE 03-SEP-2008 09:24:18
2 45182 2147483648 4 YES ACTIVE 03-SEP-2008 09:27:28
3 45183 2147483648 2 NO CURRENT 03-SEP-2008 09:30:47
4 45178 2147483648 4 YES INACTIVE 03-SEP-2008 09:17:02
5 45179 2147483648 2 YES INACTIVE 03-SEP-2008 09:19:21
6 45180 2147483648 4 YES INACTIVE 03-SEP-2008 09:22:50

Before dropping Redo Log Members you should be aware of the following rules:
You can drop a redo log member only if it is not part of an active or current group.
Make sure the group to which a redo log member belongs is archived before dropping the member.
If the member you want to drop is the last valid member of the group, you cannot drop the member until the other members become valid.
So after a few log switches (ALTER SYSTEM SWITCH LOGFILE) I was ready to drop the old members.
Note that V$LOG.STATUS show ACTIVE for group 1 and 2. This means the Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived. This was the result of all the log switches I did. Once their STATUS changed to INACTIVE I continued dropping the old member.

ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO2';
ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO2b';
ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO4';
ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO4b';
ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO6';
ALTER DATABASE DROP LOGFILE MEMBER '/dev/vx/rdsk/redodg/casprd_REDO6b';