Change size of Redo log file on Oracle database

Redo log file size is increased online without shutdown the oracle database. you will take care while adding data files for location. check redo files is added at same location.

Standalone: Steps to Increase the redo log file size on oracle database

1. Check the size and location in redo log files.

select l.bytes/1024/1024 "Meg",g.member from v$log l,v$logfile g where l.group# = g.group#;

2. Add the new log file for increased the size to 250 MB.

alter database add logfile group 4 'F:\oracle\Redo\Redo04.log' size 250M;
alter database add logfile group 5 'F:\oracle\Redo\Redo05.log' size 250M;
alter database add logfile group 6 'F:\oracle\Redo\Redo06.log' size 250M;

ALTER DATABASE ADD LOGFILE ('F:\oracle\redo\redo04A.log','F:\oracle\redo\redo04B.log') SIZE 250M;
ALTER DATABASE ADD LOGFILE ('F:\oracle\redo\redo05A.log','F:\oracle\redo\redo05B.log') SIZE 250M;
ALTER DATABASE ADD LOGFILE ('F:\oracle\redo\redo06A.log','F:\oracle\redo\redo06B.log') SIZE 250M;

3. Check the status of all redo log files.

select group#,status from v$log;

GROUP# STATUS
------ ------------
1      ACTIVE
2      INACTIVE
3      ACTIVE
4      CURRENT
5      UNUSED
6      UNUSED

4. Check the size of redo log files added.

select l.bytes/1024/1024 "Meg",g.member from v$log l,v$logfile g where l.group# = g.group#;


5. Switch the logfile from active to inactive status:

alter system switch logfile;
alter system checkpoint;


6. Now drop the old redo log file.

Note : For drop the old log file status must be inactive.
SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;


7. Check the Redo log files is working.

SELECT GROUP#, ARCHIVED, STATUS FROM V$LOG;
GROUP# ARC STATUS
------- --- --------
4 NO  ACTIVE
5 NO  CURRENT
6 NO  ACTIVE


Note: If you want to add more member in one go, follow the following command:

alter database add logfile group 1 (
'E:\ORACLE\ORADATA\ORCL\REDO011.LOG',
'E:\ORACLE\ORADATA\ORCL\REDO012.LOG',
'E:\ORACLE\ORADATA\ORCL\REDO013.LOG') size 50m reuse;