Re: Does a commit cause a checkpoint?

From: Hans Forbrich <forbrich_at_telusplanet.net>
Date: Thu, 19 Jun 2003 03:15:33 GMT
Message-ID: <3EF1293B.6DAB7D00@telusplanet.net>

Peter wrote:

> On Thu, 19 Jun 2003 00:41:45 GMT, Hans Forbrich
> <forbrich_at_telusplanet.net> wrote:
>
> A logswitch causes a checkpoint which causes LGWR to flush the redo
> log buffer, but no change in SCN

>
> A checkpoint causes the DBWn to write to file, and also causes the
> LGWR to flush the redo buffer, but no change in SCN

Basically correct .... the stuff below is from the various documentation to support this and expand into exceptions, strange conditions, etc.

/Hans

From "Oracle9i Database Performance Tuning Guide and Reference Release 2 (9.2) Part Number A96533-02", chapter 17, (where you can learn a bt more about the impact to a running database) ... in section "
Checkpointing and Cache Recovery"

>>>>>>>>>>

Periodically, Oracle records a checkpoint. A checkpoint is the highest system change number (SCN) such that all data blocks less than or equal to that SCN are known to be written out to the data files. If a failure occurs, then only the redo records containing changes at SCNs higher than the checkpoint need to be applied during recovery. <<<<<<<<<<

---

From Oracle9i Database Concepts, Release 2 (9.2), Part Number A96524-01, Chapter 1, in the section about Process Architecture, we see

<<<<<<<<
Checkpoint (CKPT)

At specific times, all modified database buffers in the SGA are written to the datafiles by DBWn. This event is called a checkpoint. The checkpoint process is responsible for signaling DBWn at checkpoints and updating all the datafiles and control files of the database to indicate the most recent checkpoint.
>>>>>>>>

---

Also note - from the "Oracle9i Backup and Recovery Concepts, Release 2 (9.2), Part Number A96519-01" the glossary has the following very useful definitions:

1. checkpoint A data structure that defines an SCN in the redo thread of a database. Checkpoints are recorded in the control file and each datafile header, and are a crucial element of recovery.
2. database checkpoint The thread checkpoint that has the lowest SCN. The database checkpoint guarantees that all changes in all enabled threads prior to the database checkpoint have been written to disk.

See Also: checkpoint

3) datafile checkpoint
The checkpoint structure stored in the header of each datafile. All redo in all threads prior to the datafile checkpoint SCN is guaranteed to have been saved to the datafile.

4) offline-end checkpoint
The SCN that specifies when a datafile was brought online after being offline, or made read/write after being read-only. This SCN is stored in the control file and is the last SCN in the offline range. The offline-end checkpoint is important because it indicates that changes after this SCN are required to recover the datafile.

5) thread checkpoint
A type of checkpoint stored in the control file indicating that all changes to online datafiles in a given thread prior to the checkpoint SCN have been saved to disk. Oracle updates the thread checkpoint every time an instance checkpoints its thread.

---

And finally I suggest you read the "Oracle9i Database Concepts, Release 2 (9.2) Part Number A96524-01", Chapter 8 "Process Architecture" which has some decent diagrams and an excellent section on the Log Writer which support and expands this. Received on Wed Jun 18 2003 - 22:15:33 CDT