This discussion is archived
1 2 Previous Next 25 Replies Latest reply: Sep 23, 2010 7:49 PM by Aman.... Go to original post RSS
  • 15. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    Aman.... Oracle ACE
    Currently Being Moderated
    David wrote:
    OCA Oracle Database 11g Administration I Exam Guide states like that, "There is
    no checkpoint following a log switch. This has been the case since release 8i,
    though to this day many DBAs do not realize this."
    I was going to say that you should immediately ask the author to prove it but JL has already given a reply. Before you get into more confusions, read this presentation from Oakie Harald van Breederode and send a mail to the author also,

    http://prutser.files.wordpress.com/2008/12/checkpointsukoug.pdf

    HTH
    Aman....
  • 16. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    Ahmed AANGOUR Newbie
    Currently Being Moderated
    Hi Jonathan,

    What does Oracle actually mean by "thread checkpoint"?
    If a thread checkpoint occurs during an online redo log switch, why is the database not synchronized (see the first posts of this thread)?

    I have also read in the John Watson's book when I prepared the OCA exam that as of 8i checkpoint does not longer occur after a redo log switch.
  • 17. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    Aman.... Oracle ACE
    Currently Being Moderated
    Though you have asked Jonathan, I shall attempt to reply with whatever little I know.
    What does Oracle actually mean by "thread checkpoint"?
    The checkpoint for the logfiles for a specific thread. Please see the official docs for the definition,
    http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/startup.htm#CNCPT89045

    This is more specifically applicable to the RAC systems where each of the instance would own a single thread and checkpoint issued by one instance would cause only its dirty buffers checkpointed leaving the other threads intact.
    If a thread checkpoint occurs during an online redo log switch, why is the database not synchronized (see the first posts of this thread)?
    You should try things out. There is a difference between the Full Checkpoint and Thread Checkpoint. What is demonstrated above(assuming that you are referring to my demo) , with the Full checkpoint, the datafile headers were updated. Since there was nothing dirty , there was nothing to checkpoint with the thread checkpoint. With some dirty buffers, the checkpoint is updated over the datafiles,
    SQL> select checkpoint_change# from V$datafile;
    
    CHECKPOINT_CHANGE#
    ------------------
               2023206
               2023206
               2023206
               2023206
               2023206
               2023206
               2023206
    
    7 rows selected.
    
    SQL> create table t as select * from all_objects;
    
    Table created.
    
    SQL> select checkpoint_change# from V$datafile;
    
    CHECKPOINT_CHANGE#
    ------------------
               2023206
               2023206
               2023206
               2023206
               2023206
               2023206
               2023206
    
    7 rows selected.
    
    SQL> alter system switch logfile;
    
    System altered.
    
    SQL> select checkpoint_change# from V$datafile;
    
    CHECKPOINT_CHANGE#
    ------------------
               2023213
               2023213
               2023213
               2023213
               2023213
               2023213
               2023213
    
    7 rows selected.
    
    SQL> select * from V$logfile;
    
        GROUP# STATUS  TYPE
    ---------- ------- -------
    MEMBER
    --------------------------------------------------------------------------------
    IS_
    ---
             3         ONLINE
    D:\APP\ARISTADBA\ORADATA\ORCL112\REDO03.LOG
    NO
    
             2         ONLINE
    D:\APP\ARISTADBA\ORADATA\ORCL112\REDO02.LOG
    NO
    
        GROUP# STATUS  TYPE
    ---------- ------- -------
    MEMBER
    --------------------------------------------------------------------------------
    IS_
    ---
    
             1         ONLINE
    D:\APP\ARISTADBA\ORADATA\ORCL112\REDO01.LOG
    NO
    
    
    SQL> select * from  V$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
    PL/SQL Release 11.2.0.1.0 - Production
    CORE    11.2.0.1.0      Production
    TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
    NLSRTL Version 11.2.0.1.0 - Production
    
    SQL>
    HTH
    Aman....
  • 18. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Ahmed AANGOUR wrote:
    Hi Jonathan,

    What does Oracle actually mean by "thread checkpoint"?
    It's in the manuals - look it up.
    If a thread checkpoint occurs during an online redo log switch, why is the database not synchronized (see the first posts of this thread)?
    Synchronized with what ?
    I have also read in the John Watson's book when I prepared the OCA exam that as of 8i checkpoint does not longer occur after a redo log switch.
    I don't tend to trust books that apparently don't use the correct terminology. But I can be fairly forgiving about that since Oracle Corp. is so sloppy with its terminology. Which type of checkpoint no longer occurs after a redo log switch ?, and how does the author know.

    Here's a thought - people sometimes ask why they see the messages in their log files about "unable to switch log files, checkpoint not complete" - what checkpoint might it be that hasn't completed ?

    Regards
    Jonathan Lewis
  • 19. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Aman.... wrote:
    Though you have asked Jonathan, I shall attempt to reply with whatever little I know.
    What does Oracle actually mean by "thread checkpoint"?
    The checkpoint for the logfiles for a specific thread. Please see the official docs for the definition,
    http://download.oracle.com/docs/cd/E11882_01/server.112/e16508/startup.htm#CNCPT89045
    You have to love the way the manuals manage to confuse the issue - two different meanings of checkpoint in two consecutive sentences:

    <blockquote>
    "The checkpoint process (CKPT) is responsible for writing checkpoints to the data file headers and control file. Checkpoints occur in a variety of situations."
    </blockquote>

    Sentence 1 - a checkpoint is a data structure
    Sentence 2 - a checkpoint can "occur" - so it's an activity.

    This is more specifically applicable to the RAC systems where each of the instance would own a single thread and checkpoint issued by one instance would cause only its dirty buffers checkpointed leaving the other threads intact.
    Which means that in single instance Oracle, a thread checkpoint IS a full checkpoint.
    If a thread checkpoint occurs during an online redo log switch, why is the database not synchronized (see the first posts of this thread)?
    The answer to this question is that when a process issues a checkpoint request it wants all dirty blocks that were changed before a certain SCN to be copied from the buffer to the disc. This takes some time, and in that time some blocks that were modified AFTER that SCN may also be written to disc.

    Apart from the checkpoint that completes on a shutdown normal, we can never assume that the data files are in a completely consistent state with any point in time - we can only assume that the redo log generated prior to the most recently completed checkpoint call can be safely discarded.

    Regards
    Jonathan Lewis
  • 20. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    jgarry Guru
    Currently Being Moderated
    Farenheiit wrote:
    I often heard that it is recommended to size redo log files in a way that Oracle performs a log switch every 15 to 30 minutes.

    I would like to know if it is true or if it is a myth ?
    I would like to know the best way for sizing redo log files ?
    What happen exactly in the database when a log switch occurs ?

    Please help me to clarify these concepts.
    There is another reason for the times of a switch. If you are running a standby database or something happens to make you lose all online redo, it limits the amount of data loss. If you have to recover your database, it puts reasonable limits on the time it takes to recover. Also see fast_start_mttr_target in the docs.

    It is a generalization which may go out the window when you are in an non-standard situation, like a mass data update. On the other hand, if you are doing mass data loads/updates periodically, then you may want to size the redo logs for those and use archive_lag_target to avoid very long times between switching during normal operations. To me that's the normal way to do things, since I normally work on oltp systems with some batch stuff and some heavy usage times.

    On a loaded system, redo often becomes a choke point for I/O. When you switch logs, the switched-from log needs to be read to be copied wherever it is going. So you are likely to have a serial read (of redo) v. serial write (of the next redo file and archiving) conflict, since you have to have multiple copies of each redo log - it is the most critical part of your system, the fundamental Oracle way of avoiding data loss - and it is rare to have all those on different actual devices. So you have an I/O balancing act between that and all the actual data, including undo (which on the systems I work on tends to be the heaviest accessed normal data files). If you don't finish archiving before you cycle through all the redo logs, everything stops. Extreme situations can make using noarchivelog mode reasonable (for example, turn off archiving, mass load, turn on archiving, backup).

    So as far as rules-of-thumb go, 15-30 minutes for switches is one of the few that is pretty decent. It is just a starting point though, and may well be overridden by service level agreements and new hardware or application developments.
  • 21. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    Ahmed AANGOUR Newbie
    Currently Being Moderated
    Thanks for your answers,

    To be fair I have copy/paste below the excerpt from John Watson's book (OCA Oracle Database 11g Administration I) where he's saying that no checkpoint occurs after a redo log switch.
    Chapter 2:
    The only moment when a checkpoint is absolutely necessary is as the database
    is closed and the instance is shut down—a full description of this sequence is given
    in Chapter 5. A checkpoint writes all dirty buffers to disk: this synchronizes the
    buffer cache with the datafiles, the instance with the database. In normal running,
    the datafiles are always out of date: they may be missing changes (committed and
    uncommitted). This does not matter, because the copies of blocks in the buffer
    cache are up to date, and it is these that the sessions work on. But on shutdown,
    it is necessary to write everything to disk. Automatic checkpoints only occur on
    shutdown, but a checkpoint can be forced at any time with this statement:
    alter system checkpoint;
    Note that from release 8i onward, checkpoints do not occur on log switch (log
    switches are discussed in Chapter 15).
    The checkpoint described so far is a full checkpoint. Partial checkpoints that
    force DBWn to write all the dirty buffers containing blocks from just one or more
    datafiles rather than the whole database, occur more frequently: when a datafile
    or tablespace is taken offline; when a tablespace is put into backup mode; when a
    tablespace is made read only. These are less drastic than full checkpoints, and occur
    automatically whenever the relevant event happens.
    To conclude, the DBWn writes on a very lazy algorithm: as little as possible,
    as rarely as possible—except when a checkpoint occurs, when all dirty buffers are
    written to disk, as fast as possible.
    In the 15th chapter entitled "Backup and Recovery Concepts" he says:
    Manually initiated checkpoints should never be necessary in normal running,
    though they can be useful when you want to test the effect of tuning. There is
    no checkpoint following a log switch. This has been the case since release 8i,
    though to this day many DBAs do not realize this.
  • 22. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Ahmed AANGOUR wrote:
    Thanks for your answers,
    Manually initiated checkpoints should never be necessary in normal running,
    though they can be useful when you want to test the effect of tuning. There is
    no checkpoint following a log switch. This has been the case since release 8i,
    though to this day many DBAs do not realize this.
    Too bad the software hasn't read his book.
    alter system set log_checkpoints_to_alert = true;
    alter system switch logfile;
    alter system switch logfile;
    alter system switch logfile;
    From the alert log:
    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
    See the lines saying: "beginning log switch checkpoint up to ..." ?

    This was on 11g.

    The book needs a little editing. After using the expression "checkpoint" it says "we are talking about full checkpoints" - if that were the case then it should have said full checkpoint all the way through, and then it might be able to say something about log switch checkpoints not being full checkpoints.

    The book is also not quite right about dbwN being lazy - although it doesn't write a block as soon as the block is dirty (which is lazy - but good) it does wake up every three seconds to execute a rolling (or continuous) checkpoint, i.e. to make sure that the tail of the checkpoint queue isn't too far into the past (and that's not lazy - and is also good).

    Notice, by the way, how the checkpoint up to 774.2.10 doesn't finish until after the checkpoint to 776.2.10 has started.

    Regards
    Jonathan Lewis
  • 23. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    Aman.... Oracle ACE
    Currently Being Moderated
    Sir,
    You have to love the way the manuals manage to confuse the issue -
    I do love it sir that how so consistently they manage to keep this tradition alive in docs of all the versions :) .
    which means that in single instance Oracle, a thread checkpoint IS a full checkpoint.
    I mentioned it given the context that in the single instance, there can't be more than one thread active. If we would issue alter ....thread local and alter system , both would update the datafile and control file. Did I make a correct statement or not sir?
    The answer to this question is that when a process issues a checkpoint request it wants all dirty blocks that were changed before a certain SCN to be copied from the buffer to the disc. This takes some time, and in that time some blocks that were modified AFTER that SCN may also be written to disc.
    Thanks so much for the explanation sir!
    Apart from the checkpoint that completes on a shutdown normal, we can never assume that the data files are in a completely consistent state with any point in time - we can only assume that the redo log generated prior to the most recently completed checkpoint call can be safely discarded.
    Yes sir, that's how I understand it too!

    Regards
    Aman....
  • 24. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    Jonathan Lewis Oracle ACE Director
    Currently Being Moderated
    Aman,

    I replied to your posting largely because it linked to the (double-meaning) documentation - not because I wanted to criticize it. The follow-on comments were intended only as explanation.

    I wouldn't like to make any extremely detailed comments about your demonstration and the way in which the data file checkpoints are updated because there are too many variations across versions and timing is always an important consideration. However, in 11g Oracle introduced a couple of parameters that seem to allow log file switches to "fall behind" the media recovery checkpoint.

    If you repeated your test with (say) 6 log file groups, changed a bit of data then issued "switch logfile" three or four times in a row I think you might find that the datafile headers were updated on the third switch (the deferlog_count parameter defaults to 2 on my laptop). I'm not going to guarantee this, of course, but the point is that checkpolinting tries to bring the database files up to date with the buffer and minimise the number of redo logs needed for recovery - but it can be a little lazy doing so and the details of how lazy it gets are subject to lots of detailed changes.

    I've just updated a blog I wrote about log file switches and checkpoints a few years ago: http://jonathanlewis.wordpress.com/2007/04/12/log-file-switch/

    Regards
    Jonathan Lewis
  • 25. Re: log switch must occur every 15 to 30 minutes: good practice or myth?
    Aman.... Oracle ACE
    Currently Being Moderated
    Sir,

    Whenever you explain something, its always a new learning or correction for me. That's why I requested you to confirm that whether my statement was correct or not.

    I shall read the blog post and post doubts there sir.

    Regards
    Aman....
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points