Increase the redo log file size on a live system

While running a workload on my test system I noticed in the AWR report that in the Top 5 Timed Foreground Events "log file switch (checkpoint incomplete)" was on the top. It was taking around 48.43% DB Time which is rather high.

To resolve the issue it is recommended to increase the size of the redo logs and/or add more redo log groups.

It is not possible to increase the size of an existing red log file, so on a live system where the workload was running I did the following to increase the size of the redo logs:

I added 3 new log files each of size 250M

SQL> alter database add logfile group 4 size 250M; Database altered. SQL> alter database add logfile group 5 size 250M; Database altered. SQL> alter database add logfile group 6 size 250M; Database altered. SQL> SQL> select group#,status from v$log;     GROUP# STATUS ---------- ----------------          1 ACTIVE          2 INACTIVE          3 ACTIVE          4 CURRENT          5 UNUSED          6 UNUSED 6 rows selected. SQL> SQL> select l.bytes/1024/1024 "Meg",g.member from v$log l,v$logfile g where l.group# = g.group#;        Meg ---------- MEMBER --------------------------------------------------------------------------------         50 +DATA/testasm/onlinelog/group_3.263.764177393         50 +DATA/testasm/onlinelog/group_2.262.764177393         50 +DATA/testasm/onlinelog/group_1.261.764177393        Meg ---------- MEMBER --------------------------------------------------------------------------------        250 +DATA/testasm/onlinelog/group_4.276.764264429        250 +DATA/testasm/onlinelog/group_5.277.764264441        250 +DATA/testasm/onlinelog/group_6.278.764264449 6 rows selected.

The log files need to be inactive to drop then, so dropped group 1 and 3 first.

SQL> alter database drop logfile group 1; Database altered. SQL> alter database drop logfile group 3; Database altered. SQL> SQL> alter system checkpoint; System altered. SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;     GROUP# ARC STATUS ---------- --- ----------------          2 NO  INACTIVE          4 NO  INACTIVE          5 NO  ACTIVE          6 NO  CURRENT

In then dropped log file 2:

SQL> alter database drop logfile group 2; Database altered. SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;     GROUP# ARC STATUS ---------- --- ----------------          4 NO  INACTIVE          5 NO  ACTIVE          6 NO  CURRENT SQL> SQL> alter system switch logfile; System altered. SQL> SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;     GROUP# ARC STATUS ---------- --- ----------------          4 NO  ACTIVE          5 NO  CURRENT          6 NO  ACTIVE SQL>

There we go, I now have 3 redo log each of 250M

SQL> select l.bytes/1024/1024 "Meg",g.member from v$log l,v$logfile g where l.group# = g.group#;        Meg ---------- MEMBER --------------------------------------------------------------------------------        250 +DATA/testasm/onlinelog/group_4.276.764264429        250 +DATA/testasm/onlinelog/group_5.277.764264441        250 +DATA/testasm/onlinelog/group_6.278.764264449