Types of Checkpoint in Oracle

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.