How to Modifying the Size of Redo Logs

How to Modifying the Size of Redo Logs

To change the size of your redo logs, you must drop them and then re-create them. You can do this online without shutting down the database.
Steps:
1. Make sure the logfile you wish to change is not the current or Active logfile.
2. Drop the logfile group you wish to change.
3. Re-create the logfile group, resizing it as required.

SQL> select group#, status from v$log;
    GROUP# STATUS
---------- ----------------
         1 INACTIVE
         2 INACTIVE
 3 CURRENT
Suppose as per the above case, we want to replace log group 3, which is CURRENT so we need to make it INACTIVE first. To do this we need to force log switch wait for the archive generated.

SQL> Alter system switch logfile;
A CURRENT status indicates that the resulting dirty block that are associated with the redo in the logfile have not yet
been check pointed which is required before we drop the logfile.

SQL> Alter system switch logfile;
Checkpoint the system. This will remove the log file we want to drop from a possible active status.

Alter system checkpoint;
Now check the status of logfile again the status of group 3 is 'INACTIVE' now

SQL> select group#, status from v$log;
    GROUP# STATUS
---------- ----------------
         1 CURRENT
         2 INACTIVE
         3 INACTIVE
Once the checkpoint is complete, we drop the online redo log group, and re-create it defining a new size for the redo log files.
Note that we use the REUSE keyword also to indicate that we are reusing the files that already exist there and Oracle will resize them accordingly.

SQL> Alter database drop logfile group 3;
Now, add the logfile group with the new sizing information.

SQL> alter database add logfile group 3
  ('D:\ORACLE9I\ORADATA\ORAC\redo03a.log',
   'D:\ORACLE9I\ORADATA\ORAC\redo03b.log') size 100m REUSE;