Oracle Scratchpad

April 12, 2007

Log File Switch

Filed under: Infrastructure,redo,Tuning — Jonathan Lewis @ 9:55 pm BST Apr 12,2007

A few days ago, I published a simple query you could run against v$log_history to show the time between log file switches.  So if you use this script and decide that your log file switches are happening at inappropriate intervals, what can you do ?

If the intervals are too short (which could cause excessive activity from the database writer(s) as the log file checkpoint occurs) you can add new, larger, log files and drop the old ones.

If the intervals are too long – or if the timing fluctuates wildly and you want to make the switches more regular – then you can use the parameter archive_lag_target (introduced in 9i) to set a timeout interval in seconds for log file switches. If a log file switch has not occurred within the specified amount of time then a switch is forced (it’s actually a little more subtle than that, but it’s a fair approximation) and the portion of the online redo log that has been used is archived (which is why you can see some systems where the archived redo log files vary in size during the course of the day).

Historically people used to use tools like cron or dbms_job to issue a ‘alter system switch logfile’ command. From 9i it’s much tidier to take advantage of the appropriate parameter.


There are many different types of checkpoint, and the list varies with version of Oracle. The range of options for seems to be:

  • Instance Recovery Checkpoint
  • Media Recovery Checkpoint
  • Thread Checkpoint
  • Interval Checkpoint
  • Tablespace Checkpoint
  • PQ Tablespace Checkpoint
  • Close Database Checkpoint
  • Incremental Checkpoint
  • Local Database Checkpoint
  • Global Database Checkpoint
  • Object Reuse Checkpoint
  • Object Checkpoint

Of these, I think the checkpoint we see at a log file switch is probably the one called the Thread Checkpoint.

Addendum 2 (Sept 2010)

I’ve  been prompted by a discussion on the OTN database forum to add a couple of updates. First, seems to have two more checkpoint types:

  • RBR Checkpoint
  • Multiple Object Checkpoint

(RBR checkpoint is probably short for “re-use block range”).

Secondly, it’s worth noting that different checkpoints requests can have different priorities – some have to complete as rapidly as possible, some can be performed fairly casually. For example, a Parallel Query (or serial direct path read) needs to issue a checkpoint, and the query can’t start running until the checkpoint is complete – so it’s high priority checkpoint; on the other hand when a log file switch occurs it’s “nice” to get the checkpoint complete before too many more log file switches have completed, but clearing the checkpoint queue is not urgent.

Finally, the thread raised a quote from the OCA guide for 11g that ‘checkpoints don’t occur on log file switch and haven’t since 8i’. The checkpoint behaviour may have changed at 8i, but this statement is essentially wrong, as can be seen if you set parameter log_checkpoints_to_alert to true and then issue a few calls to switch logfiles. The alert log (11g) will show text like:

Wed Sep 22 23:06:21 2010
ALTER SYSTEM SET log_checkpoints_to_alert=TRUE SCOPE=MEMORY;
Wed Sep 22 23:06:26 2010
Thread 1 cannot allocate new log, sequence 1908
Private strand flush not complete
  Current log# 2 seq# 1907 mem# 0: C:\ORACLE\ORADATA\D11G\D11G\REDO02.LOG
Beginning log switch checkpoint up to RBA [0x774.2.10], SCN: 17046722
Thread 1 advanced to log sequence 1908
  Current log# 3 seq# 1908 mem# 0: C:\ORACLE\ORADATA\D11G\D11G\REDO03.LOG
Wed Sep 22 23:06:53 2010
Beginning log switch checkpoint up to RBA [0x775.2.10], SCN: 17046733
Thread 1 advanced to log sequence 1909
  Current log# 1 seq# 1909 mem# 0: C:\ORACLE\ORADATA\D11G\D11G\REDO01.LOG
Wed Sep 22 23:07:31 2010
Thread 1 cannot allocate new log, sequence 1910
Checkpoint not complete
  Current log# 1 seq# 1909 mem# 0: C:\ORACLE\ORADATA\D11G\D11G\REDO01.LOG
Wed Sep 22 23:07:34 2010
Completed checkpoint up to RBA [0x774.2.10], SCN: 17046722
Beginning log switch checkpoint up to RBA [0x776.2.10], SCN: 17046748
Thread 1 advanced to log sequence 1910
  Current log# 2 seq# 1910 mem# 0: C:\ORACLE\ORADATA\D11G\D11G\REDO02.LOG

The whole “what is a checkpoint” has many more variations than you might expect so any simple statements about “checkpointing” should be treated with a little caution – they may give the right sort of indication of what the purpose is but there’s a strong chance that there are far more details behind the scenes that you might (in special cases) need to know.

Consider, for example the parameter _defer_log_count which has the description: “Number of log boundaries media recovery checkpoint lags behind”. This probably tells us that when parameter _defer_log_boundary_ckpt is set to true Oracle will allow some log file switches to occur without trying to force a “log switch checkpoint”. (But those are 11g parameters, and they’re hidden parameters anyway so you shouldn’t be playing with them).


  1. Thanks Jonathan !!
    I never read anywhere about that many check points types.

    Comment by Amit Saraswat — November 27, 2012 @ 8:00 pm GMT Nov 27,2012 | Reply

  2. […] For the complete list of the checkpoint event, you can check this blog post of Jonathan Leiws, . As the question asked was about Checkpoint SCN, so we shall just discuss only one type of […]

    Pingback by Checkpoint Count/SCN, What It Is …. « AristaDBA's Oracle Blog…. — February 1, 2013 @ 5:09 pm GMT Feb 1,2013 | Reply

  3. Dear Jonathan,

    I have read a doc on Oracle Support – Checkpoint Tuning and Troubleshooting Guide [ID 147468.1]. There are some things I can’t understand. Please help me to clarify.

    1. “The lag between the incremental checkpoint target and the log tail is also limited by 90% of the smallest online log file size” –> is that mean if when the smallest online log file size is up to 90%, there is no incremental checkpoint happen until switching to new redo log file ?
    2. How to know where the log of incremental checkpoint located ? the mechanism of the incremental checkpoint ?
    3. If dirtied buffers are written during the incremental checkpoint, there will be a little dirtied buffer written when happen log switch. Is that right ?

    Thank you so much.

    Comment by K.P — July 8, 2013 @ 11:44 am BST Jul 8,2013 | Reply

    • K.P.
      For a careful and detailed answer to this and related questions you need to read my book Oracle Core.
      In outline:
      1) the database writer tries to limit how long a blocks can stay dirty in the buffer cache, based on the SCN. The 90% limit says (roughly) if the smallest log file is X (redo) blocks, then go back 0.9X (redo) blocks from the current position in the redo log file and check the SCN that appears in that redo block; that’s the target SCN for the next incremental (3 second) checkpoint.

      2) (a) not a trivial question, it is possible to query one of the x$ views to find the position of lgwr, and you could work out the relevant file and block address, but getting the SCN from that block is tricky; (b) you have to read the book, there’s too much to explain

      3) Absolutely – that’s precisely the point of the incremental checkpoint, it stops the build-up of an extreme number of dirty buffers and sudden I/O bursts by maintaining a constant trickle of writes to disc.

      Comment by Jonathan Lewis — July 10, 2013 @ 8:58 pm BST Jul 10,2013 | Reply

      • If your explanation of 1) is true that means that for 90% of time incremental checkpoint is inside previous logfile. That means that logswitch cannot flush all dirty pages recorded in switched log file. Otherwise incremental checkpoint would be working only 10% of time when current log position is > than 90% of current log file.

        Comment by rk — September 2, 2013 @ 1:31 pm BST Sep 2,2013 | Reply

        • RK
          You’re assuming that all dirty buffers are immediately copied to disc, clearing the checkpoint queue, the moment a log file switch occurs, and that is no longer the case.

          Comment by Jonathan Lewis — September 2, 2013 @ 6:19 pm BST Sep 2,2013

  4. Dear Jonathan,

    Thank you so much for replying my questions. I have read your book Oracle Core.


    Comment by K.P — July 17, 2013 @ 5:04 am BST Jul 17,2013 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: