Redo Log File

How to Resize Redo Logs in Oracle

Resize Redo Logs

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 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.

  1. Check Current Redo Logs
  2. Add 3 Groups with New Size (1GB)
  3. Switch Logfile to New Groups
  4. Wait For Group 1, 2, 3 to be INACTIVE
  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 to support resize directly, we have to workaround it.

1. 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.

2. 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.

3. 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.

4. 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.

5. 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

6. 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

7. 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.

8. Switch Logfile Several Times

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL> /

System altered.

9. 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 repeated dropping and adding process, no magic inside.

Further reading: How to Determine Archived Log Size

Leave a Reply

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