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