Redo Log File

How to Resize Redo Log Files

Resize Redo Log Files

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

As for the databases that you took over from other DBA, 50MB of redo logs could be very annoying because it makes log switching very frequently and causes a lot of trivial files.

Here in this post, I will show you how to resize redo logs from 50MB to 1GB step by step. But don’t expect too much, there’s no syntax to support resize directly, we have to workaround it.

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

SQL> alter system switch logfile;

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.

Wait For Group 1, 2, 3 to be INACTIVE

Now, we have to wait for all of the original groups to be INACTIVE, then drop them. An ACTIVE or CURRENT redo log group cannot be dropped.

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

6 rows selected.

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 drop and add process, no magic inside.

Leave a Reply

Your email address will not be published. Required fields are marked *