LLI Wiki — My Digital Knowledge Base

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;

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

Mastodon LLIMastodon SocialFosstodon