Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will be explaining Redo Log Switching, Redo Log Switch Frequency and Determining Redo Log Switch Duration.
Redo Log Switching
1 2 3 | SQL> alter system switch logfile; System altered. |
Redo Log Switch Frequency
We can query the time intervals in which Redo Log files are the most “SWITCH” as follows;
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 | SQL> select count(*), to_char(first_time,'dd:mm:yyyy:hh24') from v$log_history group by to_char(first_time,'dd:mm:yyyy:hh24') order by 2 desc; COUNT(*) TO_CHAR(FIRST ---------- ------------- 1 30:06:2014:18 1 30:06:2014:17 1 30:06:2014:03 1 29:06:2014:17 1 29:06:2014:06 1 29:06:2014:03 1 28:06:2014:18 1 28:06:2014:08 1 28:06:2014:03 1 27:06:2014:22 1 27:06:2014:03 COUNT(*) TO_CHAR(FIRST ---------- ------------- 1 26:06:2014:22 1 26:06:2014:04 1 25:06:2014:22 1 25:06:2014:03 1 24:06:2014:22 1 24:06:2014:00 1 23:06:2014:22 1 22:06:2014:23 1 22:06:2014:16 1 22:06:2014:09 1 22:06:2014:00 COUNT(*) TO_CHAR(FIRST ---------- ------------- 1 21:06:2014:18 1 21:06:2014:10 2 20:06:2014:22 1 20:06:2014:04 1 19:06:2014:20 2 19:06:2014:13 28 rows selected. |
Determining the Redo Log Switch Time
By setting the following parameter, even if the Redo Log files are not full, we can make the switch after the time we allow.
For example, we can switch even if it is not full after 30 minutes.
30 min = 1800 sec
1 2 3 4 5 | SQL> show parameter archive_lag_target; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ archive_lag_target integer 0 |
