Skip to content

Log Switch and Duplexing Redo Log Group in Oracle 10g

April 3, 2008

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

kick it on DotNetKicks.com

About these ads
One Comment leave one →

Trackbacks

  1. where is the group switch

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Archives



  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video


  • Software Blogs - BlogCatalog Blog Directory
  • Follow

    Get every new post delivered to your Inbox.

    Join 29 other followers

    %d bloggers like this: