Checkpoint in Oracle

LGWR or CKPT writes the redo log sequence to the datafile headers and control files and tells the DBWR to write dirty buffers from the dirty buffer write queue (buffer cache) to disk. It is a record indicating the point in the redo log where all DB changes prior to this point have been saved in the datafiles.

The database ALWAYS has transactions going on, ALWAYS.  SMON and many other background processes are always doing work, the database (unless it is opened read only) is always doing transactions. Now, since the database never sleeps. Most of those other “programs” do transactions and commit.

SQL>select username, program from v$session;

In Oracle checkpoint are of two types INCREMENTAL and COMPLETE. Further COMPLETE checkpoint can be divided further into PARTIAL and FULL.

In INCREMENTAL Checkpoint, checkpoint information is written to the controlfile in following cases:

• Every three second.
• 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 or Full Checkpoint, checkpoint information is written in controlfile, datafile header and also dirty block is written by DBWR to the datafiles.

Cases:

• fast_start_mttr_target
• Before Clean Shutdown
• 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 happening when the Redo Log files are small in size and continuous transactions are taking place.
• When the 'alter system checkpoint' command is issued

Partial Checkpoint happens in the following cases.

• Before begin backup.
• Before tablespace offline.
• Before placing tablespace in read only.
• Before dropping tablespace.
• Before taking datafile offline.
• When checkpoint queue exceeds its threshold.
• Before segment is dropped.
• Before adding and removing columns from table.

Checkpoints DO NOT cause log switches, but log switches cause checkpoints. For Manual check point use “alter system checkpoint”.