Steps to Resize Redo Log Files in Oracle

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.

Steps to Increase the redo log file size in oracle database

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

SELECT group#, thread#, bytes/1024/1024 AS size_mb, status
FROM v$log
ORDER BY group#;

-- Check members:
SELECT group#, member
FROM v$logfile
ORDER BY group#;

2. Add the new log file for increased the size to 1 GB.

ALTER DATABASE ADD LOGFILE GROUP 4 ('/u01/oradata/redo04.log') SIZE 1G;
ALTER DATABASE ADD LOGFILE GROUP 5 ('/u01/oradata/redo05.log') SIZE 1G;
ALTER DATABASE ADD LOGFILE GROUP 6 ('/u01/oradata/redo06.log') SIZE 1G;

–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#, thread#, bytes/1024/1024 AS size_mb, status
FROM v$log
ORDER BY group#;

4. Check the size of redo log files added.

SELECT group#, bytes/1024/1024 AS size_mb
FROM v$log;

5. Switch the logfile from active to inactive status:

ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;

6. Now drop the old redo log file.

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.

-- Switch logfile
ALTER SYSTEM SWITCH LOGFILE;
-- Verify
SELECT group#, thread#, bytes/1024/1024 AS size_mb, status
FROM v$log
ORDER BY group#;