Oracle: increasing redo log size and monitoring log switches
Procedure to replace redo log groups with larger ones, with queries to monitor log switches and redo generation.
Procedure to increase the size of Oracle redo log groups: if log switches happen more than a few times per hour, consider increasing the redo log size. Paths and sizes below are examples to adapt.
1. List the groups and their members
set linesize 300
column GROUP# format 99999
column STATUS format a9
column TYPE format a7
column MEMBER format a100
column IS_RECOVERY_DEST_FILE format a100
select GROUP#,STATUS,TYPE,MEMBER,IS_RECOVERY_DEST_FILE from v$logfile;
column REDOLOG_FILE_NAME format a50
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# ;
Sample output (3 groups, 2 members each):
GROUP# THREAD# SEQUENCE# ARC STATUS REDOLOG_FILE_NAME SIZE_MB
------ ------- --------- --- --------- --------------------------------------- -------
4 1 20338 NO CURRENT /u02/oradata/ORCL/redo04a.log 600
4 1 20338 NO CURRENT /u01/oradata/ORCL/redo04b.log 600
5 1 20336 YES INACTIVE /u02/oradata/ORCL/redo05a.log 600
5 1 20336 YES INACTIVE /u01/oradata/ORCL/redo05b.log 600
6 1 20337 YES INACTIVE /u02/oradata/ORCL/redo06a.log 600
6 1 20337 YES INACTIVE /u01/oradata/ORCL/redo06b.log 600
2. Understand the switch commands
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM ARCHIVE LOG CURRENT;
ALTER SYSTEM CHECKPOINT;
SWITCH LOGFILEonly changes the current group (theCURRENTgroup becomesACTIVE, another one becomesCURRENT) and does not guarantee immediate archiving.ARCHIVE LOG CURRENTarchives the current group immediately.CHECKPOINTwrites committed transactions from the cache blocks to the datafiles. AllACTIVEgroups becomeINACTIVE.
3. Replace a group
A group can only be dropped if it is neither CURRENT nor needed for instance recovery. Work group by group, starting with an INACTIVE one.
-- The group to drop must not be CURRENT
select group#, status from v$log;
-- If needed, move the current group (sometimes up to 3 times)
alter system switch logfile;
select group#, status from v$log;
-- Drop the group
alter database drop logfile group 5;
If the drop fails (for example ORA-01624: log 5 needed for crash recovery of instance ...), force a checkpoint and retry:
alter system checkpoint;
-- or, on RAC:
alter system checkpoint global;
alter database drop logfile group 5;
Then delete the old files at the operating system level and re-create the group with the new size:
rm /u02/oradata/ORCL/redo05a.log
rm /u01/oradata/ORCL/redo05b.log
alter database add logfile group 5
('/u02/oradata/ORCL/redo05a.log', '/u01/oradata/ORCL/redo05b.log') size 1024M;
From SQL*Plus, operating system commands can also be run with ! (for example !sudo rm /u02/oradata/ORCL/redo05a.log).
Repeat for each group. Handle the CURRENT group last, after an alter system switch logfile; followed by an alter system checkpoint;. If the groups have a single member, the syntax is the same with one file:
alter database add logfile group 1 ('/u02/oradata/ORCL/redo01.log') size 2G;
4. Check the result
select group#, bytes/1024/1024 as size_mb, status from v$log;
Check that every group shows the new size. As a last resort, alter database clear logfile group N; re-initialises the content of a group.
Monitoring
Redo log switches per day and per hour
alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';
select
trunc(first_time) DAY,
trunc(first_time,'HH24') DATE_HOUR,
count(*) LOG_SWITCHES
from v$log_history
group by rollup( trunc(first_time) , trunc(first_time,'HH24') )
order by 1,2;
Running total of switches per day
WITH row_source AS
( select
trunc(first_time) DAY,
trunc(first_time,'HH24') DAY_HOUR,
count(*) LOG_SWITCHES_PER_HOUR
from v$log_history
group by trunc(first_time) , trunc(first_time,'HH24') )
select DAY, DAY_HOUR, LOG_SWITCHES_PER_HOUR,
sum(LOG_SWITCHES_PER_HOUR) OVER (PARTITION BY DAY ORDER BY DAY_HOUR) "TOTAL_SWITCHES_SOFAR_TODAY"
from row_source
order by 1,2;
Sessions generating the most redo
select
s.sid,
n.name,
s.value,
sn.username,
sn.program,
sn.type,
sn.module
from
v$sesstat s join v$statname n on n.statistic# = s.statistic#
join v$session sn on sn.sid = s.sid where name like '%redo entries%'
order by
value desc;
select s.inst_id, s.sid, serial#, program, module, username, value redo_size
from gv$session s, gv$sesstat ss, v$statname sn
where s.sid = ss.sid and ss.statistic# = sn.statistic# and sn.name = 'redo size'
and s.inst_id = ss.inst_id
order by redo_size desc;
Notes
- Sizing groups larger than the workload strictly needs avoids overly frequent log switches; the sizes seen in the examples (450 MB, 1 GB, 2 GB) should be chosen according to the redo volume generated.
- Since redo logs are multiplexed, keep the same number of members per group when re-creating them.
- The
LOG_CHECKPOINT_INTERVALparameter can also influence checkpoint frequency. - Reference: Resize redo log members.