Find Redo Log Size / Switch Frequency / Location in Oracle

Redo logs are the most crucial part of the Oracle database. As a part of tuning, you must check the redo log size and switch frequency. For proper functioning of the Oracle database you must avoid frequent log switches.

Redo log size, member, status, location in Oracle.

Oracle redo logs consists of two or more pre-allocated files which record all changes made to the database. Every instance of Oracle database has its own redo logs which protects the database in case of instance failure.

The database require minimum of two redo log files to guarantee that one is always available for writing while the other is being archived or copied to the other offline location.

Types of Redo Log Files

  • Current: The redo log file that log writer (LGWR) is actively writing is called the current redo log file.
  • Active: Redo log file that are required for instance recovery are called active redo log files.
  • Inactive: Redo log files that are no longer required for instance recovery are called inactive redo log files.

Check Redo Log Size in Oracle

SQL> set lines 250
SQL> col FILE_NAME for a55
SQL> SELECT
a.GROUP#,
a.THREAD#,
a.SEQUENCE#,
a.ARCHIVED,
a.STATUS,
b.MEMBER AS 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#;

GROUP# THREAD# SEQUENCE# ARC STATUS FILE_NAME    SIZE_MB
------------   -------------   ----------------   ---- ----------- --------------------------------
7   1   64557   YES   INACTIVE +DATAC1/dmwmckpf/onlinelog/group_7.819.1040821865   1024
8   1   64558   YES   INACTIVE +DATAC1/dmwmckpf/onlinelog/group_8.848.1040821881   1024
9   1   64559    NO   CURRENT +DATAC1/dmwmckpf/onlinelog/group_9.849.1040821889   1024
10   1   64556   YES   INACTIVE +DATAC1/dmwmckpf/onlinelog/group_10.850.1040821897   1024
11   2   80343   YES   INACTIVE +DATAC1/dmwmckpf/onlinelog/group_11.851.1040821905   1024
12   2   80344   YES   INACTIVE +DATAC1/dmwmckpf/onlinelog/group_12.852.1040821913   1024
13   2   80345   YES   INACTIVE +DATAC1/dmwmckpf/onlinelog/group_13.853.1040821921   1024
14   2   80346   NO   CURRENT +DATAC1/dmwmckpf/onlinelog/group_14.854.1040821929   1024

Find Member and Location of Redo Log Files

SQL> col member for a60
SQL> select GROUP#,TYPE,MEMBER from v$logfile;
GROUP# TYPE MEMBER
---------------------------------------------------
7   ONLINE   +DA_QTR1/dmw253pf/onlinelog/group_7.2277.1051493809
8   ONLINE   +DA_QTR1/dmw253pf/onlinelog/group_8.2278.1051493821
9   ONLINE   +DA_QTR1/dmw253pf/onlinelog/group_9.2279.1051493829
10   ONLINE   +DA_QTR1/dmw253pf/onlinelog/group_10.2280.1051493839
11   ONLINE   +DA_QTR1/dmw253pf/onlinelog/group_11.2281.1051493859
12   ONLINE   +DA_QTR1/dmw253pf/onlinelog/group_12.2282.1051493865
13   ONLINE   +DA_QTR1/dmw253pf/onlinelog/group_13.2283.1051493871
14   ONLINE   +DA_QTR1/dmw253pf/onlinelog/group_14.2284.1051493877

Find Redo Log File Size

SQL> select GROUP#,THREAD#,SEQUENCE#,bytes/1024/1024, MEMBERS,STATUS from v$log;
GROUP# THREAD# SEQUENCE# BYTES/1024/1024 MEMBERS STATUS
---------------------------------------------------------------------------------------------------
7   1   1302   1024   1   INACTIVE
8   1   1303   1024   1   INACTIVE
9   1   1304   1024   1   INACTIVE
10   1   1305   1024   1   CURRENT
11   2   1552   1024   1   INACTIVE
12   2   1553   1024   1   INACTIVE
13   2   1554   1024   1   INACTIVE
14   2   1555   1024   1   CURRENT 

Check Redo Log Switch Frequency

Below are some of the queries to find out redo log switches. In this query we are using V$LOG_HISTORY to get the redo log frequency.

COL DAY FORMAT a15;
COL HOUR FORMAT a4;
COL TOTAL FORMAT 999;
SELECT TO_CHAR(FIRST_TIME,'YYYY-MM-DD') DAY,
TO_CHAR(FIRST_TIME,'HH24') HOUR,
COUNT(*) TOTAL
FROM V$LOG_HISTORY
GROUP BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')
ORDER BY TO_CHAR(FIRST_TIME,'YYYY-MM-DD'),TO_CHAR(FIRST_TIME,'HH24')
ASC;
set pages 999 lines 400
col Day form a10
col h0 format 999
col h1 format 999
col h2 format 999
col h3 format 999
col h4 format 999
col h5 format 999
col h6 format 999
col h7 format 999
col h8 format 999
col h9 format 999
col h10 format 999
col h11 format 999
col h12 format 999
col h13 format 999
col h14 format 999
col h15 format 999
col h16 format 999
col h17 format 999
col h18 format 999
col h19 format 999
col h20 format 999
col h21 format 999
col h22 format 999
col h23 format 999
SELECT TRUNC (first_time) "Date", inst_id, TO_CHAR (first_time, 'Dy') "Day",
COUNT (1) "Total",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '00', 1, 0)) "h0",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '01', 1, 0)) "h1",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '02', 1, 0)) "h2",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '03', 1, 0)) "h3",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '04', 1, 0)) "h4",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '05', 1, 0)) "h5",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '06', 1, 0)) "h6",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '07', 1, 0)) "h7",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '08', 1, 0)) "h8",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '09', 1, 0)) "h9",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '10', 1, 0)) "h10",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '11', 1, 0)) "h11",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '12', 1, 0)) "h12",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '13', 1, 0)) "h13",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '14', 1, 0)) "h14",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '15', 1, 0)) "h15",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '16', 1, 0)) "h16",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '17', 1, 0)) "h17",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '18', 1, 0)) "h18",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '19', 1, 0)) "h19",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '20', 1, 0)) "h20",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '21', 1, 0)) "h21",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '22', 1, 0)) "h22",
SUM (DECODE (TO_CHAR (first_time, 'hh24'), '23', 1, 0)) "h23",
ROUND (COUNT (1) / 24, 2) "Avg"
FROM gv$log_history
WHERE thread# = inst_id
AND first_time > sysdate -7
GROUP BY TRUNC (first_time), inst_id, TO_CHAR (first_time, 'Dy')
ORDER BY 1,2;
col c1 format a10 heading "Month"
col c2 format a25 heading "Archive Date"
col c3 format 999 heading "Switches"
compute AVG of C on A
compute AVG of C on REPORT
break on A skip 1 on REPORT skip 1
select
to_char(trunc(first_time), 'Month') c1,
to_char(trunc(first_time), 'Day : DD-Mon-YYYY') c2,
count(*) c3
from
v$log_history
where
trunc(first_time) > last_day(sysdate-100) +1
group by
trunc(first_time)
order by
trunc(first_time);
Summary:
  • There should not be frequent log switches per hour. Ideally redo log switch frequency should be 4-5 log switches per hour.
  • If there is frequent log switches then check the size of redo logs and increase accordingly.
  • V$LOG_HISTORY is used to check history of redo log generation in Oracle.
  • V$LOGFILE is used to check the file name and location of redo logs.
  • V$LOG and V$LOGFILE view can be combined to get the detailed information like redo log size, redo log members, thread, groups, sequence, status etc.