How to change the Redo Log File size in Oracle Database

We had 50 MB Redo Log Files, 1 Redo Thread, 2 Redo Log Groups and One member in each group.
We decided to increase the Redo Log size to 100MB.
The Redo Logs must be dropped and recreated for changing the redo log size. It can be done online without shutting down the database. However, you need to make sure that the Redo Group being dropped should be INACTIVE when you do this.
We have the following Log Groups and members:

SQL> select * from v$logfile;

 GROUP# STATUS  TYPE    MEMBER          IS_RECOVE
------ ------- -----      --------- ---------
 2   ONLINE   /app01/oratest/oradata/BOTI/BOTI/redo02.log NO
 1   ONLINE /app01/oratest/oradata/BOTI/BOTI/redo01.log NO

And the status of the Log Groups is:

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVE STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- --------------
 1     1   12   52428800   1   NO    CURRENT   1156736  27-JAN-11
 2     1   11   52428800   1   YES   ACTIVE    1156732  27-JAN-11

Here we see that the Group# 1 is being used Currently and the Group# 2 though not being used Currently, however is ACTIVE (means if the Database crashes now, you will need this Group for recovery.) We need to make this group Inactive before proceeding ahead:
For this, execute a checkpoint:

SQL> alter system checkpoint;

System altered.

Now again check the status of the Redo Groups:

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- --------
 1 1 12 52428800 1 NO CURRENT 1156736 27-JAN-11
 2 1 11 52428800 1 YES INACTIVE 1156732 27-JAN-11

The group 2 is now Inactive. Hence we can proceed with dropping this group:

SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-01567: dropping log 2 would leave less than 2 log files for instance BOTI (thread 1)
ORA-00312: online log 2 thread 1: '/app01/oratest/oradata/BOTI/BOTI/redo02.log'

What went wrong ???
It’s a basic requirement in Oracle Database that there should always be a minimum 2 Redo Log Groups available with the Database. Hence we can not drop any Redo Group if there are only 2 Groups.
To overcome this issue, we need to add one more Redo group to the database.
Execute the following step:

SQL> alter database add logfile group 3 '/app01/oratest/oradata/BOTI/BOTI/redo03.log' size 100M;

Database altered.

Now check the logfiles:

SQL> select * from v$logfile;

 GROUP# STATUS TYPE MEMBER IS_RECOVE
---------- --------------------- --------------------- -
 3 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo03.log NO
 2 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo02.log NO
 1 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo01.log NO

… and the status of the Groups:

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVE STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ---------
 1 1 12   52428800 1 NO CURRENT 1156736 27-JAN-11
 2 1 11 52428800 1 YES INACTIVE 1156732 27-JAN-11
 3 1 0 104857600 1 YES UNUSED 0

The status of the new Group is UNUSED because it has never been used.
Now, we have 3 Redo Groups in our database. We can now proceed with Dropping Redo Group# 2.

SQL> alter database drop logfile group 2;

Database altered.

Also, delete the file ‘/app01/oratest/oradata/BOTI/BOTI/redo02.log’ from File system also.
Now add the Redo Group 2 back to the database with changed Redo size:

SQL> alter database add logfile group 2 '/app01/oratest/oradata/BOTI/BOTI/redo02.log' size 100M;

Database altered.

SQL> select * from v$logfile;

 GROUP#       STATUS  TYPE   MEMBER  IS_RECOVE
---------- --------------------- --------------------- --------
 3 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo03.log NO
 2 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo02.log NO
 1 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo01.log NO

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- --------------
 1 1 12 52428800 1 NO CURRENT 1156736 27-JAN-11
 2 1 0 104857600 1 YES UNUSED 0
 3 1 0 104857600 1 YES UNUSED 0

Now we have to drop the 1st Redo Log Group. Before that, we need to change the status of this group:

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ---- -----
 1 1 12 52428800 1 YES ACTIVE 1156736 27-JAN-11
 2 1 13 10485760 1 NO CURRENT 1157376 27-JAN-11
 3 1 0 104857600 1 YES UNUSED 0

Still the Grpoup is in Active status. Issue a checkpoint:

SQL> alter system checkpoint;

System altered.

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- -------
 1 1 12 52428800 1 YES INACTIVE 1156736 27-JAN-11
 2 1 13 104857600 1 NO CURRENT 1157376 27-JAN-11
 3 1 0 104857600 1 YES UNUSED 0

Now we can drop the Redo Group# 1.

SQL> alter database drop logfile group 1;

Database altered.

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- -----
 2 1 13 104857600 1 NO CURRENT 1157376 27-JAN-11
 3 1 0 104857600 1 YES UNUSED 0

SQL> select * from v$logfile;

 GROUP# STATUS TYPE MEMBEs IS_RECOVE
---------- --------------------- --------------------- 
3 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo03.log NO 
2 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo02.log NO

Delete the file ‘/app01/oratest/oradata/BOTI/BOTI/redo01.log’ from OS.
Now we need to add this group back to the database with 100MB Redo Log file:

SQL> alter database add logfile group 1 '/app01/oratest/oradata/BOTI/BOTI/redo01.log' size 100M;

Database altered.

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ----------
 1   1   0   104857600   1    YES    UNUSED    0
 2   1   13  104857600   1     NO   CURRENT     1157376       27-JAN-11
 3   1   0   104857600   1    YES   UNUSED    0

Here we can see that all the Redo Groups are now showing 100MB size (column BYTES).

SQL> select * from v$logfile;

 GROUP# STATUS  TYPE   MEMBER    IS_RECOVE
---------- --------------------- -----------------
 3 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo03.log NO
 2 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo02.log NO
 1 ONLINE /app01/oratest/oradata/BOTI/BOTI/redo01.log NO

Though it’s suggested that you should keep 3 groups, if you want you can drop the Redo Group 3 which we added for this exercise.

SQL> alter database drop logfile group 3;

Database altered.

SQL> select * from v$logfile;

 GROUP# STATUS    TYPE                MEMBER                   IS_RECOVE
------- ------- -------------- ---------------------
 2 ONLINE  /app01/oratest/oradata/BOTI/BOTI/redo02.log      NO
 1 ONLINE  /app01/oratest/oradata/BOTI/BOTI/redo01.log      NO

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME
------ ---------- ---------- ---------- ---------- --------- ---------
 1      1       0          104857600     1 YES UNUSED 0
 2      1      13          104857600     1 NO CURRENT 1157376 27-JAN-11

SQL> alter system switch logfile;

System altered.

SQL> select * from v$log;

 GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARCHIVE STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --------- ---------- 
 1        1    14  104857600 1  NO   CURRENT   1157584       27-JAN-11
 2        1    13  104857600 1  YES  ACTIVE     1157376     27-JAN-11

Congratulations ! You have changed the Redo Log file size from 50M to 100M.