In a metalink note, one says
Oralce writes the dirty buffers to disk only on certain conditions :
A shadow process must scan more than one-quarter of the db_block_buffer
- Every three seconds.*
- When a checkpoint is produced.
A checkpoint is realized on five types of events:
- At each switch of the redo log files.
- When the delay for LOG_CHECKPOINT_TIMEOUT is reached.
- When the size in bytes corresponding to :
(LOG_CHECKPOINT_INTERVAL* size of IO OS blocks)
is written on the current redo log file.
- Directly by the ALTER SYSTEM SWITCH LOGFILE command.
- Directly with the ALTER SYSTEM CHECKPOINT command.
During a checkpoint the following occurs:
- The database writer (DBWR) writes all modified database
blocks in the buffer cache back to datafiles,
- Log writer (LGWR) updates both the controlfile and
the datafiles to indicate when the last checkpoint
So, I am a little bit confused (*, **):
* it is said that every 3s, Oracle writes dirty buffers to disk. So a checkpoint is occured every 3s or what ?
and why there is the file LOG_CHECKPOINT_TIMEOUT ?
** SCN is related to commited transaction, is there another SCN for checkpoint ?...
Oracle does not write dirty buffers to disk every 3 seconds - The oracle log writer writes the contents of the redo log buffers to disk (into the redo logs) every 3 seconds. A checkpoint will occur when the redo log fills or one of the other scenarios you mentioned occurs.
Also the log writer does not update the control files or the datafiles with the SCN. This is done by the Checkpoint process (CKPT).
The SCN does relate to commited transactions. On a checkpoint the control files and database files are updated with the last SCN to indicate the contents of the datafile.
On an automatic recovery Oracle will know which transaction redo to apply to the datafiles according to the SCN in the file header.