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
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
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?