Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
If you want to learn What the Redo Log is, you should read the article named “Oracle Redo Log And Archive Log”. I will tell you finding and changing log switch frequency in this article.
If there is a performance problem in the database, you should look at the REDO LOG Switch frequency. If the log switch frequency is too much, it causes a significant performance problem in the database.
For example; if a very high IMPORT or DELETE operation is to be performed and the database FORCE LOGGING cannot be disabled, then it makes sense to add a new ONLINE REDO LOG as much as the size of the data to be IMPORTED, and then delete this ONLINE REDO LOG after the operation is finished.
Check Redo Log Switch Frequency
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 | SQL> set linesize 300 set pagesize 5000 set trimspool on column 00 format 99 heading "00:00" column 01 format 99 heading "1am" column 02 format 99 heading "2am" column 03 format 99 heading "3am" column 04 format 99 heading "4am" column 05 format 99 heading "5am" column 06 format 99 heading "6am" column 07 format 99 heading "7am" column 08 format 99 heading "8am" column 09 format 99 heading "9am" column 10 format 99 heading "10am" column 11 format 99 heading "11am" column 12 format 99 heading "12:00" column 13 format 99 heading "1pm" column 14 format 99 heading "2pm" column 15 format 99 heading "3pm" column 16 format 99 heading "4pm" column 17 format 99 heading "5pm" column 18 format 99 heading "6pm" column 19 format 99 heading "7pm" column 20 format 99 heading "8pm" column 21 format 99 heading "9pm" column 22 format 99 heading "10pm" column 23 format 99 heading "11pm" column 24 format 99 heading "12pm" column "Day" format a3 prompt prompt Redo Log Switches prompt SELECT trunc (first_time) "Date", to_char (trunc (first_time),'Dy') "Day", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 0, 1)) "00", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 1, 1)) "01", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 2, 1)) "02", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 3, 1)) "03", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 4, 1)) "04", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 5, 1)) "05", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 6, 1)) "06", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 7, 1)) "07", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 8, 1)) "08", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 9, 1)) "09", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 10, 1)) "10", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 11, 1)) "11", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 12, 1)) "12", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 13, 1)) "13", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 14, 1)) "14", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 15, 1)) "15", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 16, 1)) "16", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 17, 1)) "17", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 18, 1)) "18", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 19, 1)) "19", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 20, 1)) "20", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 21, 1)) "21", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 22, 1)) "22", sum (decode (to_number (to_char (FIRST_TIME, 'HH24')), 23, 1)) "23" from v$log_history where trunc (first_time) >= (trunc(sysdate) - 14) -- last X days. 0 = today only. 1 = today and yesterday group by trunc (first_time) order by trunc (first_time) DES |
In addition, the Mbps values of the generated redo log can be calculated as follows.
1 2 3 4 | select round(avg(mb)) mb_avg, round(avg(mb)*8) mbit_avg, round(avg(mb)*8/86400,2) mbps_avg, round(max(mb)*8/86400,2) mbps_max from ( select trunc(first_time) tdate, sum(blocks*block_size)/1024/1024 as mb from v$archived_log where first_time<trunc(sysdate) and dest_id=1 group by trunc(first_time)); |
The information of Redo Logs in the database can be found as follows;
1 2 3 4 | SELECT l.thread#, lf.group#, l.bytes, l.members, lf.member, lf.status, lf.type FROM v$log l, v$logfile lf WHERE l.group# = lf.group# ORDER BY 1, 2, 5; |
Change Redo Log Switch Frequency
The log switch operation takes place when Online Redo Logs are full. If you increase redo log size your log switch frequenct will be reduced. But you may want to perform the Log switch operation periodically. This can be done with the ARCHIVE_LAG_TARGET parameter. ARCHIVE_LAG_TARGET parameter forces log switch.
Let’s test it. We suppose that we have a data guard in this example.
Query the current value of the parameter
1 2 3 4 5 | [Primary-1] SQL> show parameter archive_lag_target; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ archive_lag_target integer 0 |
Check current archive log sequence numbers
1 2 3 4 5 6 | [Primary-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 312 1 167 2 |
1 2 3 4 5 6 | [Physical-1] SQL> select max(sequence#),thread# from v$archived_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#; MAX(SEQUENCE#) THREAD# -------------- ---------- 312 1 167 2 |
1 2 3 4 5 6 | [Logical-1] SQL> SELECT max(sequence#),thread#,applied FROM dba_logstdby_log where first_time > to_date('27/01/2017 13:41:50', 'DD-MM-YYYY HH24:MI:SS') group by thread#, applied; MAX(SEQUENCE#) THREAD# APPLIED -------------- ---------- -------- 312 1 YES 167 2 YES |
Change the ARCHIVE_LAG_TARGET Parameter
1 2 3 | [Primary-1] SQL> alter system set archive_lag_target=60 scope=both sid='*'; System altered. |
When the value of the parameter is changed, the changes in ALERT LOG are as follows:
[Primary-1]—–>ALERT_LOG
1 2 3 4 5 6 7 8 9 10 11 12 | Thu Feb 02 07:24:06 2017 ALTER SYSTEM SET archive_lag_target=60 SCOPE=BOTH; Thu Feb 02 07:24:07 2017 Thread 1 advanced to log sequence 314 (LGWR switch) Current log# 2 seq# 314 mem# 0: +DATA/primary/onlinelog/group_2.262.932222811 Thu Feb 02 07:24:07 2017 Deleted Oracle managed file +FRA/primary/archivelog/2017_01_23/thread_2_seq_61.529.934026075 Thu Feb 02 07:24:07 2017 Archived Log entry 5586 added for thread 1 sequence 313 ID 0x69a3f5e6 dest 1: LNS: Standby redo logfile selected for thread 1 sequence 314 for destination LOG_ARCHIVE_DEST_2 Thu Feb 02 07:24:07 2017 LNS: Standby redo logfile selected for thread 1 sequence 314 for destination LOG_ARCHIVE_DEST_3 |
[Primary-2]—–>ALERT_LOG
1 2 3 4 5 6 7 8 9 10 | Thu Feb 02 07:24:14 2017 Thread 2 advanced to log sequence 169 (LGWR switch) Current log# 3 seq# 169 mem# 0: +DATA/primary/onlinelog/group_3.266.932223145 Thu Feb 02 07:24:14 2017 Deleted Oracle managed file +FRA/primary/archivelog/2017_01_23/thread_1_seq_62.527.934026069 LNS: Standby redo logfile selected for thread 2 sequence 169 for destination LOG_ARCHIVE_DEST_2 Thu Feb 02 07:24:15 2017 Archived Log entry 5589 added for thread 2 sequence 168 ID 0x69a3f5e6 dest 1: Thu Feb 02 07:24:15 2017 LNS: Standby redo logfile selected for thread 2 sequence 169 for destination LOG_ARCHIVE_DEST_3 |
[Standby-1]—–>ALERT_LOG
1 2 3 4 5 6 7 8 9 | Thu Feb 02 07:23:53 2017 Media Recovery Waiting for thread 1 sequence 314 (in transit) Recovery of Online Redo Log: Thread 1 Group 5 Seq 314 Reading mem 0 Mem# 0: +DATA/standby/onlinelog/group_5.267.932283683 Mem# 1: +FRA/standby/onlinelog/group_5.260.932283683 Media Recovery Waiting for thread 2 sequence 169 (in transit) Recovery of Online Redo Log: Thread 2 Group 8 Seq 169 Reading mem 0 Mem# 0: +DATA/standby/onlinelog/group_8.270.932283685 Mem# 1: +FRA/standby/onlinelog/group_8.263.932283685 |
[Standby-2]—–>ALERT_LOG
1 2 3 4 5 6 7 8 | Thu Feb 02 07:24:08 2017 RFS[1]: Selected log 5 for thread 1 sequence 314 dbid 1769705496 branch 934378910 Thu Feb 02 07:24:08 2017 Archived Log entry 3069 added for thread 1 sequence 313 ID 0x69a3f5e6 dest 1: Thu Feb 02 07:24:09 2017 Archived Log entry 3070 added for thread 2 sequence 168 ID 0x69a3f5e6 dest 1: Thu Feb 02 07:24:09 2017 RFS[2]: Selected log 8 for thread 2 sequence 169 dbid 1769705496 branch 934378910 |
How we can force a log switch?
You can force log switch with the below command;
1 | ALTER SYSTEM SWITCH LOGFILE; |
Very useful query to check redo log size in Oracle.
Very helpful article, thanks for sharing.
You’re welcome.
65 row> order by trunc (first_time) DES
missing DESC, so copy paste will produce a error.
Great article by the way!