Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Greg.

Asked: March 14, 2011 - 9:19 am UTC

Last updated: November 01, 2013 - 7:50 pm UTC

Version: 11.2.0

Viewed 10K+ times! This question is

You Asked

I think I know the answer to this question but I really have a need to confirm it and I think you are the perfect person to do that. I am studying for my OCA and in the study book "Oracle Administration Exam Guide I" by John Watson there is a section on Checkpointing. On page 560 it says "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." All the other documentation I have read states basically the opposite, including your posts and books. They state that a checkpoint does occur following a log switch (among other times).

Can you clarify this point? (If they do happen at a switch).

The reason I feel the need to ask this is that I was sure they did switch but this is the latest book I had read so I figured it was the most up to date. I answered this way in an interview and it basically cost me the job because I was very adamant that I was right about this. Now, I think studying for the OCA cost me the job.

Thanks a million.

and Tom said...

Well, I just altered my system to log checkpoints to the alert log and then switched logfiles:

Mon Mar 14 14:52:26 2011
ALTER SYSTEM SET log_checkpoints_to_alert=TRUE SCOPE=BOTH;
Mon Mar 14 15:01:44 2011
Beginning log switch checkpoint up to RBA [0x3c.2.10], SCN: 7979763
Thread 1 advanced to log sequence 60 (LGWR switch)
  Current log# 3 seq# 60 mem# 0: /home/ora11gr2/app/ora11gr2/oradata/ora11gr2/redo03.log
Mon Mar 14 15:01:44 2011
Completed checkpoint up to RBA [0x3c.2.10], SCN: 7979763
Beginning log switch checkpoint up to RBA [0x3d.2.10], SCN: 7979766
Thread 1 advanced to log sequence 61 (LGWR switch)
  Current log# 1 seq# 61 mem# 0: /home/ora11gr2/app/ora11gr2/oradata/ora11gr2/redo01.log
Completed checkpoint up to RBA [0x3d.2.10], SCN: 7979766
Beginning log switch checkpoint up to RBA [0x3e.2.10], SCN: 7979769
Thread 1 advanced to log sequence 62 (LGWR switch)
  Current log# 2 seq# 62 mem# 0: /home/ora11gr2/app/ora11gr2/oradata/ora11gr2/redo02.log
Completed checkpoint up to RBA [0x3e.2.10], SCN: 7979769
Beginning log switch checkpoint up to RBA [0x3f.2.10], SCN: 7979772
Thread 1 advanced to log sequence 63 (LGWR switch)
  Current log# 3 seq# 63 mem# 0: /home/ora11gr2/app/ora11gr2/oradata/ora11gr2/redo03.log
Completed checkpoint up to RBA [0x3f.2.10], SCN: 7979772




Now, there was nothing going on in this database other than:

ops$tkyte%ORA11GR2> alter system switch logfile;

System altered.

ops$tkyte%ORA11GR2> alter system switch logfile;

System altered.

ops$tkyte%ORA11GR2> alter system switch logfile;

System altered.

ops$tkyte%ORA11GR2> alter system switch logfile;

System altered.




and the checkpoints happened fast and furious......


So.....

I would say their claims are unfounded.

See this thread:

http://forums.oracle.com/forums/thread.jspa?messageID=4596352&tstart=0


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Logswitch and incremental checkpoint

chakra, May 30, 2012 - 10:22 am UTC

Tom,

As per my knowledge, the checkpoint is classified as below (correct me if I am wrong somewhere)
Checkpoint types can be divided as INCREMENTAL and COMPLETE.

Also COMPLETE CHECKPOINT can be divided further into
PARTIAL and FULL.

In Incremental Checkpoint,checkpoint information is written to the
controlfile. In the following cases:

1.Every three second.
2.At the time of log switch - Sometimes log switches may trigger a complete checkpoint , if the next log where the log switch is to take place is Active.

In complete Checkpoint,checkpoint information is written
in controlfile,datafile header and also dirty block is
written by DBWR to the datafiles.

Full Checkpoint
1.fast_start_mttr_target
2.Before Clean Shutdown
3.Some log switches may trigger a complete checkpoint , if the next log where the log switch is to take place is Active.
This has more chance of happenning when the Redo Log files are small in size and continuous transactions are taking place.
4.when the 'alter system checkpoint' command is issued

Partial Checkpoint happens in the following cases.

1.before begin backup.
2.before tablespace offline.
3.before placing tablespace in read only.
4.Before dropping tablespace.
5.before taking datafile offline.
6.When checpoint queue exceeds its threshold.
7.before segment is dropped.
8.Before adding and removing columns from table.


But my doubt is mentioned below.

2.At the time of log switch - Sometimes log switches may trigger a complete checkpoint , if the next log where the log switch is to take place is Active.

Why is behaves like this?

Regards,
Chakra

Tom Kyte
May 30, 2012 - 10:49 am UTC

How about this, this is currently how I think about it:

the database is almost constantly checkpointing, it is a continual process these days in 2012. DBWR is frequently cleaning the cache. Some events like a log switch put pressure on the idea, but it is always happening.

dbwr writes out some stuff, advances the checkpoint scn, writes more stuff and so on.

It has been a long time since a checkpoint was sort of a "point in time event", it just happens in response to so many things these day - I just think of it as an ongoing thing.

And frankly - that is sufficient - to conceptualize it. The internal gory details that change from version to version, patch to patch - not worth storing in the head.



as for #2, we need to make sure all of the blocks protected by an online redo log have been written to disk. If there are some blocks in the cache still protected by an online redo log - we have to flush them all first before reusing that log file (else we'd lose their protection)


Why so many checkpoints?

Jason, October 16, 2013 - 2:00 pm UTC

Tom

Can you think of a reason or reasons why I would get multiple Checkpoints so quickly?

This is a 2 node RAC database and during this time there was a huge spike in Active Sessions Waiting: Cluster.

Beginning log switch checkpoint up to RBA [0x77b8b.2.10], SCN: 8221302453299
Wed Oct 16 09:18:16 BST 2013
Thread 2 advanced to log sequence 490379 (LGWR switch)
Current log# 16 seq# 490379 mem# 0: +DATA/racp/onlinelog/group_16.2464.795116129
Wed Oct 16 09:20:00 BST 2013
Completed checkpoint up to RBA [0x77b87.2.10], SCN: 8221302105854
Completed checkpoint up to RBA [0x77b86.2.10], SCN: 8221302016536
Completed checkpoint up to RBA [0x77b8b.2.10], SCN: 8221302453299
Completed checkpoint up to RBA [0x77b8a.2.10], SCN: 8221302355408
Completed checkpoint up to RBA [0x77b89.2.10], SCN: 8221302287716
Completed checkpoint up to RBA [0x77b88.2.10], SCN: 8221302204233
Wed Oct 16 09:25:58 BST 2013
Beginning log switch checkpoint up to RBA [0x77b8c.2.10], SCN: 8221302763643
Wed Oct 16 09:25:58 BST 2013
Thread 2 advanced to log sequence 490380 (LGWR switch)
Current log# 17 seq# 490380 mem# 0: +DATA/racp/onlinelog/group_17.2170.809180133
Wed Oct 16 09:28:10 BST 2013


Thanks

Jason
Tom Kyte
November 01, 2013 - 7:50 pm UTC

check your log related settings like fast_start_mttr_target, archive_lag_target

show us your non-default settings

select name || ' = ' || value from v$parameter where isdefault = 'FALSE';

Checkpoint differences with Active Dataguard

Yusuf Hussain, March 10, 2014 - 4:43 am UTC

Hi Tom,

With a primary database when a checkpoint occurs, during a log switch event, basically a number of triggered events happen within background activities. DBWR starts flushing dirty blocks, chpt will update data file headers etc. But there is really no effect of user activity(assuming well tuned system), user transactions continue to process with no delay, selection of data by users also continues with no delay.

The same does not seem to apply to an active dataguard database that is using lgwr replication. When the primary does a checkpoint, if the standby is caught up, also initiates the same checkpoint on the standby database. However it appears that parallel recovery cannot continue processing transactions until the checkpoint is completed. During checkpoints I see "checkpoint completed" wait events for all parallel recovery processes, and apply lag climbing on the standby while the checkpoint is processing. Thus at a checkpoint on the standby the focus from an Oracle perspective is DBWR/Chpt pushing as fast as possible to complete the checkpoint so recovery can continue.

Would you agree this is a correct analysis of log switch checkpointing at the standby?