Resizing Redo Log file

In prod env. its often required to resize the redo log file size in order to tune the log file related wait events. Hence in this post we’ll see how to resize a redo log file size:-

1. Before initiating let’s collect the stats of the current logs:

set pages 1000
set lines 300
col REDOLOG_FILE_NAME form a60
SELECT
    a.GROUP#,
    a.THREAD#,
    a.SEQUENCE#,
    a.ARCHIVED,
    a.STATUS,
    b.MEMBER    AS REDOLOG_FILE_NAME,
    (a.BYTES/1024/1024) AS SIZE_MB
FROM v$log a
JOIN v$logfile b ON a.Group#=b.Group#
ORDER BY a.GROUP# ASC;
    GROUP#    THREAD#  SEQUENCE# ARC STATUS           REDOLOG_FILE_NAME      SIZE_MB
---------- ---------- ---------- --- ---------------- -------------------- --------
         1          1         10 NO  INACTIVE         /redo/redo01.log        1

         2          1         11 NO  INACTIVE         /redo/redo02.log        1

         3          1         12 NO  CURRENT          /redo/redo03.log        1

From above queries output we can see that the Logs are 1MB, let’s size them to 10MB.

2. Retrieve all the log member names for the groups:


 select group#, member from v$logfile; 

 GROUP# MEMBER
 --------------- ----------------------------------------
 1 /redo/redo01.dbf
 2 /redo/redo02.dbf
 3 /redo/redo03.dbf

3. Now let’s create 3 new log groups and name them groups 4, 5, and 6, each 10MB in
size:

alter database add logfile group 4 '/redo/redo04.log' size 10M;

alter database add logfile group 5 '/redo/redo05.log' size 10M;

alter database add logfile group 6 '/redo/redo06.log' size 10M;

4. Now run a query to view the v$log status:

select group#, status from v$log;

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

Below is the description of the status column of v$log:-

UNUSED – Online redo log has never been written to. This is the state of a redo log that was just added, or just after a RESETLOGS, when it is not the current redo log.
CURRENT – Current redo log. This implies that the redo log is active. The redo log could be open or closed.
ACTIVE – Log is active but is not the current log. It is needed for crash recovery. It may be in use for block recovery. It may or may not be archived.
CLEARING – Log is being re-created as an empty log after an ALTER DATABASE CLEAR LOGFILE statement. After the log is cleared, the status changes to UNUSED.
CLEARING_CURRENT – Current log is being cleared of a closed thread. The log can stay in this status if there is some failure in the switch such as an I/O error writing the new log header.
INACTIVE – Log is no longer needed for instance recovery. It may be in use for media recovery. It might or might not be archived.
INVALIDATED – Archived the current redo log without a log switch.

From the output of the query ran above we can see that log group 2 is current, and this is one of the smaller groups we must drop. Therefore let’s switch out of this group into one of the newly created log groups. The reason we need to do this is that we cannot drop a log group having status as current or active.

5. Now to Switch the log group execute the below command until we are into log group 4, so we can drop log groups 1, 2, and 3:

alter system switch logfile;
** repeat as necessary until group 4 is CURRENT **

6. Run the query again to verify the current log group is group 4:

select group#, status from v$log;

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

If any of the log group with smaller size that we have to drop is CURRENT then issue “ALTER SYSTEM SWITCH LOGFILE” and if the status is ACTIVE then issue “ALTER SYSTEM CHECKPOINT”. And once the status is INACTIVE proceed further.

7. Now drop redo log groups 1, 2, and 3:

alter database drop logfile group 1;
alter database drop logfile group 2;
alter database drop logfile group 3;

Verify the groups were dropped, and the new groups’ sizes are correct.

select group#, bytes, status from v$log;

GROUP# BYTES STATUS
--------- --------- ----------------
4 10485760 CURRENT
5 10485760 UNUSED
6 10485760 UNUSED

8. At this point, to be on the safer side you should consider taking a backup of the database.

9. Drop logfile does not removes the logfile from the Operating system, hence as a final step remove the old logfiles.

$ rm /redo/redo01.log
$ rm /redo/redo02.log
$ rm /redo/redo03.log

Hope this helps, would share another post covering WHEN to resize the redo logfile size and HOW to determine the optimum size soon.

If you have any concerns or jump into any issue when resizing the log file, please comment below and we’ll try to assist you.

Share this: