Changing redo log Size

Redo logs cannot be resized on the fly we must drop and recreate them.This is the only method known to resize at the time this post is written.

A database requires at least two groups of redo log files,regardless the number of the members. We cannot drop the redo log file if it's status is current or active. Initially the status need to be changed "inactive" before dropping the redo log member. Soon after a redo log member is drooped the file doesn't remove from the file system, instead it need to be separately removed from the file system.

Step 1 : Check the Status of Redo Logfile 

 SQL>  select group#,sequence#,bytes,archived,status from v$log;   

 GROUP#     SEQUENCE#      BYTES    ARC STATUS

----------  ----------   ----------    -----  -------------

         1          5    52428800      YES    INACTIVE

         2          6    52428800      YES    ACTIVE

         3          7    52428800      NO      CURRENT

         4          4    52428800      YES    INACTIVE

According to above cannot drop Current and Active log groups which is Group 3 and 4 cannot be dropped.

Step  2 :  Forcing a Checkpoint  :

The SQL statement "alter system checkpoint" explicitly forces Oracle to perform a checkpoint for either the current instance or all instances. 

Forcing a checkpoint ensures that all changes to the database buffers are written to the datafiles on disk.

A global checkpoint is not finished until all instances that require recovery have been recovered.

SQL> alter system checkpoint global ;
system altered.

SQL> select group#,sequence#,bytes,archived,status from v$log;

GROUP#        SEQUENCE#        BYTES    ARC       STATUS
----------    ----------    ----------  -----     ----------------
         1          5       52428800     YES      INACTIVE
         2          6       52428800     YES      INACTIVE
         3          7       52428800     NO       CURRENT
         4          4       52428800     YES      INACTIVE

Since the status of group 1,2,4 are inactive .so we will drop the group 1 and group 2 redo log file.

Step  3  :  Drop Redo Log File :

SQL> alter database drop logfile group 1;
Database altered.

SQL> alter database drop logfile group 2;
Database altered.

SQL>  select group#,sequence#,bytes,archived,status from v$log;
    GROUP#  SEQUENCE#      BYTES        ARC    STATUS
----------  ----------   ----------     ---    ----------------
         3          7      52428800     NO     CURRENT
         4          4      52428800     YES    INACTIVE

Step  4  : Create new redo log file 

If we don't delete the old redo logfile by OS command when creating the log file with same name then face the below error . Therefore to solve it delete the file by using OS command .

SQL> alter database add logfile group 1 'C:\app\neerajs\oradata\orcl\redo01.log' size 100m;
alter database add logfile group 1 'C:\app\neerajs\oradata\orcl\redo01.log' size 100m
*
ERROR at line 1:
ORA-00301: error in adding log file 'C:\app\neerajs\oradata\orcl\redo01.log' - file cannot be created
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists

SQL> alter database add logfile group 1 'C:\app\neerajs\oradata\orcl\redo01.log' size 100m;
Database altered.

SQL> alter database add logfile group 2 'C:\app\neerajs\oradata\orcl\redo02.log' size 100m;
Database altered.

SQL>  select group#,sequence#,bytes,archived,status from v$log;
    GROUP#    SEQUENCE#      BYTES         ARC      STATUS
----------    ----------     ----------    ---      ----------------
         1          0        104857600     YES      UNUSED
         2          0        104857600     YES      UNUSED
         3          7        52428800      NO       CURRENT
         4          4        52428800      YES      INACTIVE

Step 5 :  Now drop the remaining two old redo log file 
SQL> alter system switch logfile ;
System altered.

SQL> alter system switch logfile ;
System altered.

SQL>  select group#,sequence#,bytes,archived,status from v$log;
    GROUP#  SEQUENCE#      BYTES ARC       STATUS
---------- ---------- ---------- --- ----------------
         1          8  104857600 YES       ACTIVE
         2          9  104857600  NO      CURRENT
         3          7   52428800 YES       ACTIVE
         4          4   52428800 YES     INACTIVE

SQL> alter system checkpoint global;
System altered.

SQL>  select group#,sequence#,bytes,archived,status from v$log;
    GROUP#  SEQUENCE#      BYTES         ARC STATUS
---------- ---------- ----------       --- ----------------
         1          8    104857600     YES INACTIVE
         2          9    104857600     NO  CURRENT
         3          7     52428800     YES INACTIVE
         4          4     52428800     YES INACTIVE

SQL> alter database drop logfile group 3;
Database altered.

SQL> alter database drop logfile group 4;
Database altered.

SQL>  select group#,sequence#,bytes,archived,status from v$log;
    GROUP#  SEQUENCE#      BYTES ARC STATUS
---------- ---------- ---------- --- ----------------
         1          8  104857600 YES INACTIVE
         2          9  104857600 NO  CURRENT

Step 6 : Create the redo log file 
SQL> alter database add logfile group 3 'C:\app\neerajs\oradata\orcl\redo03.log' size 100m;
Database altered.

SQL> alter database add logfile group 4 'C:\app\neerajs\oradata\orcl\redo04.log' size 100m;
Database altered.

SQL>  select group#,sequence#,bytes,archived,status from v$log;
    GROUP#  SEQUENCE#      BYTES        ARC STATUS
---------- ---------- ----------        --- ----------------
         1          8   104857600       YES INACTIVE
         2          9   104857600       NO  CURRENT
         3          0   104857600       YES UNUSED
         4          0   104857600       YES UNUSED


Portions of above contents derived from "http://neeraj-dba.blogspot.com/" because the post contained in  an informative fashion.

Thank you Neeraj.