[updated]
A log switch is a common event in Oracle databaseS. Well, simply put, a Log Switch is an event in which the log writer (LGWR process) stops logging in one log group and jumps to another log group. It is normal that a log group fills up with records. When records fills up the log group, Log Switch event is triggered. You can actually trigger the switch manually by issuing the command:
ALTER SYSTEM SWITCH LOGFILE;
Log Switch also holds a critical role in case of redo log file corruption. If a redo log file is corrupted within the log group, LGWR will still continue writing to the other member files in that group without disrupting the database operations.
When the database is created, it comes with three redo log groups with one file in each group. It is a good practice to add at least one file to these groups. This is what we call "Duplexing" the Redo Log Group.
Like our previous Oracle HowTo's, lets name our database "MARLONDB":
We connect to the database:
SQL> conn SYS/Password@MARLONDB AS SYSDBA;
Let's see what we currently have:
SQL> SELECT GROUP#, STATUS, ARCHIVED, MEMBERS FROM V$LOG;
GROUP# STATUS ARC MEMBERS
---------- ---------------- --- ----------
1 INACTIVE YES 1
2 INACTIVE YES 2
3 CURRENT NO 1
For Best Practice purposes, let us store the log files in a separate disk.
SQL> ALTER DATABASE ADD LOGFILE MEMBER 'D:\oracle\product\10.2.0\oradata\MARLONDB\redo01a.log' to GROUP 1;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE MEMBER 'D:\oracle\product\10.2.0\oradata\MARLONDB\redo02a.log' to GROUP 2;
Database altered.
SQL> ALTER DATABASE ADD LOGFILE MEMBER 'D:\oracle\product\10.2.0\oradata\MARLONDB\redo03a.log' to GROUP 3;
Database altered.
Now, we check again:
SQL> SELECT GROUP#, STATUS, ARCHIVED, MEMBERS FROM V$LOG;
GROUP# STATUS ARC MEMBERS
---------- ---------------- --- ----------
1 INACTIVE YES 2
2 INACTIVE YES 3
3 CURRENT NO 2
Update: This link will show you how to “calculate both the number of log switches per day and the average per month for the last couple of months”:
http://www.oracle.com/technology/oramag/code/tips2006/032006.html
-Marlon Ribunal [Also posted in my other blogs: http://dbalink.wordpress.com & http://mytechnotes.spaces.live.com