How to Resize Redo Logs in Oracle

Increase Redo Log Size

The default size of redo logs during installation is 50MB which is too small for production databases. Of course, you can change the size of redo logs for your needs in DBCA if you know where to make the change.

As for production databases that you took over from other DBA, 50MB of redo logs could be very annoying because it makes log switching very frequently. Most likely, you'll see some symptions like:

• log file switch (checkpoint incomplete)
• log file switch (archiving needed)

Furthermore, it causes a lot of trivial and small sized files. In my opinion, 1GB of redo logs may be more appropriate for a production database.

To increase redo log size, we take the following steps to reach the goal.

1. Check Current Redo Logs
2. Add 3 Groups with New Size (1GB)
3. Switch Logfile to New Groups
4. Force a CheckPoint
5. Drop Group 1, 2, 3
6. Remove Redo Log Files
7. Add Group 1, 2, 3 with New Size (1GB)
8. Switch Logfile Several Times
9. Check Status of All Redo Logs

But don't expect too much, there's no syntax available in Oracle to resize redo logs directly. In fact, the technique we used is a process of file replacement, you need to do it carefully.

Check Current Redo Logs

SQL> column group# format 99999;
SQL> column status format a10;
SQL> column mb format 99999;
SQL> select group#, status, bytes/1024/1024 mb from v$log;

GROUP# STATUS         MB
------ ---------- ------
     1 CURRENT        50
     2 INACTIVE       50
     3 INACTIVE       50

They are all 50MB.

Add 3 Groups with New Size (1GB)

SQL> alter database add logfile group 4 ('/u01/app/oracle/oradata/ORCL/redo04.log') size 1g, group 5 ('/u01/app/oracle/oradata/ORCL/redo05.log') size 1g, group 6 ('/u01/app/oracle/oradata/ORCL/redo06.log') size 1g;

Database altered.

SQL> select group#, status, bytes/1024/1024 mb from v$log;

GROUP# STATUS         MB
------ ---------- ------
     1 CURRENT        50
     2 INACTIVE       50
     3 INACTIVE       50
     4 UNUSED       1024
     5 UNUSED       1024
     6 UNUSED       1024

6 rows selected.

Switch Logfile to New Groups

Make some redo log switching until you see Log Writer Process (LGWR) is working on the new redo.

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> select group#, status, bytes/1024/1024 mb from v$log;

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

6 rows selected.

To know the differences between ALTER SYSTEM ARCHIVE LOG CURRENT and ALTER SYSTEM SWITCH LOGFILE, you may take some time to read the post.

Force a CheckPoint

Now, we have to make all of the original groups to be INACTIVE by a checkpoint which is ALTER SYSTEM CHECKPOINT, then drop them. An ACTIVE or CURRENT redo log group cannot be dropped.

SQL> alter system checkpoint;

System altered.

SQL> select group#, status, bytes/1024/1024 mb from v$log;

GROUP# STATUS         MB
------ ---------- ------
     1 INACTIVE       50
     2 INACTIVE       50
     3 INACTIVE       50
     4 CURRENT      1024
     5 UNUSED       1024
     6 UNUSED       1024

6 rows selected.

Please note that, in a RAC database, you may need to use ALTER SYSTEM CHECKPOINT GLOBAL to make all instances checkpoint at the same time.

Drop Group 1, 2, 3

SQL> alter database drop logfile group 1, group 2, group 3;

Database altered.

SQL> select group#, status, bytes/1024/1024 mb from v$log;

GROUP# STATUS         MB
------ ---------- ------
     4 CURRENT      1024
     5 UNUSED       1024
     6 UNUSED       1024

Remove Redo Log Files

Now, you have to remove these physical log files by yourself, otherwise you will receive ORA-27038: created file already exists.

Be careful, don't remove online redo logs accidentally. A better practice to remove such sensitive files is to use interactive mode of rm.

[oracle@test ~]$ rm -i /u01/app/oracle/oradata/ORCL/redo0[1-3].log
rm: remove regular file `/u01/app/oracle/oradata/ORCL/redo01.log'? y
rm: remove regular file `/u01/app/oracle/oradata/ORCL/redo02.log'? y
rm: remove regular file `/u01/app/oracle/oradata/ORCL/redo03.log'? y

Add Group 1, 2, 3 with New Size (1GB)

SQL> alter database add logfile group 1 ('/u01/app/oracle/oradata/ORCL/redo01.log') size 1g, group 2 ('/u01/app/oracle/oradata/ORCL/redo02.log') size 1g, group 3 ('/u01/app/oracle/oradata/ORCL/redo03.log') size 1g;

Database altered.

SQL> select group#, status, bytes/1024/1024 mb from v$log;

GROUP# STATUS         MB
------ ---------- ------
     1 UNUSED       1024
     2 UNUSED       1024
     3 UNUSED       1024
     4 CURRENT      1024
     5 UNUSED       1024
     6 UNUSED       1024

6 rows selected.

Switch Logfile Several Times

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

Check Status of All Redo Logs

SQL> select group#, status, bytes/1024/1024 mb from v$log;

GROUP# STATUS         MB
------ ---------- ------
     1 ACTIVE       1024
     2 ACTIVE       1024
     3 ACTIVE       1024
     4 ACTIVE       1024
     5 ACTIVE       1024
     6 CURRENT      1024

6 rows selected.

SQL> column member format a40;
SQL> select group#, member from v$logfile;

GROUP# MEMBER
------ ----------------------------------------
     1 /u01/app/oracle/oradata/ORCL/redo01.log
     2 /u01/app/oracle/oradata/ORCL/redo02.log
     3 /u01/app/oracle/oradata/ORCL/redo03.log
     4 /u01/app/oracle/oradata/ORCL/redo04.log
     5 /u01/app/oracle/oradata/ORCL/redo05.log
     6 /u01/app/oracle/oradata/ORCL/redo06.log

6 rows selected.

As you can see, "resize" is actually a repeated dropping/adding process for releases like 12c, 19c and 21c. Afterwards, you need to recreate standby redo logfiles to make data guard work functionally.

The similar technique can also be use to move redo log locations.