Re: Does a commit cause a checkpoint?

On Thu, 19 Jun 2003 06:03:49 GMT, Peter <peter_at_nomorenewsspammin.ca> wrote:

System change number (SCN)
A stamp that defines a committed version of a database at a point in time. Oracle assigns every committed transaction a unique SCN This is from the manual and should answer the queston.

>On Thu, 19 Jun 2003 03:15:33 GMT, Hans Forbrich
><forbrich_at_telusplanet.net> wrote:
>
>A commit statement does not cause an incrrement in SCN. It causes the
>LGWR to flush out redo logs. I hope this answer the question.
>
>>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 Thu Jun 19 2003 - 01:35:16 CDT