Difference in checkpoint and commit in oracle

Difference in checkpoint and commit in oracle

 succinctly, the gist is:

dbwr isn't a commit process, lgwr is

lgwr isn't a checkpointing process, dbwr is

CKPT is responsible for maintaining the data file headers
before 8 oracle lgwr used to do ckpt things ..

A log switch initiates the checkpoint, but it does not wait for it to complete.

 CKPT doesn't do the checkpoint, DBWR checkpoints the blocks, CKPT is responsible for maintaining the data file headers.

If you have three online redo logs r1,r2,r3 - as you switch from r1 to r2 we will trigger checkpoint C-1. C-1 needs to write all of the blocks protected by the redo in r1 to disk. As you advance from r2 to r3 - that will trigger another checkpoint C-2. As you advance from r3 to r1 - we need to make sure that checkpoint C-1 is complete - that all of the blocks protected by r1 are on disk.

In your case, they are not - so we pause everything else and tell dbwr "get to work - go go go - get the data to disk". When that is done - we advance into r1.

Solutions:

a) configure more redo log files to get you through your peak times without encountering this message

b) use fast_start_mttr_target to make dbwr more aggressive about keeping the buffer cache clean - it will be more or less continuously checkpointing instead of waiting too long and building a backlog of work to do.

So, that is the relationship between the two. The reason a log switch fires a checkpoint is because the system wants to get the blocks in the buffer cache that are "protected" by the online redo log we just filled flushed to disk (so we can reuse that redo log file). If we did not flush these blocks out -- we would need to keep that filled online redo log file until we did flush them out (it is needed to recover those dirty blocks in the event of a failure). So, the log switch fires a checkpoint in anticipation of needing to reuse that redo log file.

SCNs are recorded in the datafile headers after during a checkpoint. That lets us know to which point in time a datafile has consistent data for. The SCN is like our internal clock. The SCN is also present in the redo log files. These SCN's are used during recovery to see what log files need to be applied to what datafiles to make them totally consistent.