Oracle: How to increase redo log size

Below is list of sql's to increase the size of redo log files. In order to increase the size, you need to create new redo log files, check point the old one and drop the old one. Replace the path to match your redo location.

In this example, I am creating 3 more redolog files with group number 4,5 and 6 and dropping the old ones.

Log in as sys

Select * from v$log --- Get max group no

Max log files you can have in your database can be got from

Select * from v$controlfile_record_section
where type ='REDO LOG'

Alter database add logfile group 4 'c:\oracle\oradata\orcldb\redo04.log'
size 250M reuse;
Alter database add logfile group 5 'c:\oracle\oradata\orcldb\redo05.log'
size 250M reuse;
Alter database add logfile group 6 'g:\oracle\oradata\orcldb\redo06.log'
size 250M reuse;

Now switch log file 3 times to make these new files online.

Alter system switch logfile;
Alter system switch logfile;
Alter system switch logfile;

force checkpoint to write all contents to data files.

Alter system checkpoint;

Drop the old log files.
Alter database drop logfile group 1;
Alter database drop logfile group 2;
Alter database drop logfile group 3;