Problem solve Get help with specific problems with your technologies, process and projects.

Checkpoint vs. commit process

An Oracle user asks if there is a relationship between CKPT and commit in Oracle.

I have been trying to understand the relationship between CKPT and commit process. Please let me know the actions taken by LGWR, DBWN and CKPT and the final picture of SCN entry, RBA entry (both in control file and data file headers), datafile status and redo file status in the following scenarios:

1) User A has a transaction going on, he has not commited it. CKPT is fired.

2) User A has a transaction going on, he commits it.

3) User A has a transaction going on , he commits it and at the same time CKPT fires.

Even if you could give me the reference of some article which describes the interdependent operation of CKPT (Incremental) and commit, that may suffice.

Technically, the CKPT process has nothing to do with commits. Checkpoints serve one function, to write changed blocks to the database datafiles under certain conditions. It is possible for uncommitted changes to be written to the database datafiles. CKPT will cause DBWn to write changes to disk at a time interval or after a certain number of changes have occurred. Both of these are configurable by INIT.ORA parameters.

When a transaction changes data, the COMMIT is not complete until the changes are written to the online redo logs. The process responsible for this is LGWR, not CKPT.

Please read the Oracle Concepts manual for more information.

Dig Deeper on Oracle database administration