Resize the redo log file size in Oracle database

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;

--Use the following syntax for increase the members if you want to add two files in a group

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;

1 thought on “Resize the redo log file size in Oracle database

  1. Pingback: Checkpoint not complete in Oracle | Smart way of Technology

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.