Category Archives: OCA 1Z0 062

Log Writer Process (LGWR)

This is one background process that you can’t ignore for Oracle Certification Exam (1z0 062). Below are important points on LGWR from certification point of view:

Two important terms we need to understand before we learn more about LGWR:

(a) Redo log buffer: Buffer always refer memory. This buffer exists in memory. We have only one Redo Log Buffer. Server processes write change details (DDL/DML) into this Redo log buffer

(b) Redo log file: File always refers to data on disk.

DDL/DML  ==Server processes=> Redo Log Buffer  ==LGWR==> Redo Log file

When LGWR writes data from Redo log buffer to Redo log file?

  1. When user process commits a transaction
  2. Every three seconds
  3. When redo log buffer is one-third full
  4. When log switch
  5. When a DBWn process writes modified buffers to disk , if necessary

write-ahead protocol: Before DBWn can write a modified buffer, all redo records associated with the changes to the buffer must be written to disk (the write-ahead protocol). If DBWn finds that some redo records have not been written, it signals LGWR to write the redo records to disk and waits for LGWR to complete writing the redo log buffer before it write out the data buffers.

fast commit: When a transaction commits, LGWR write entries to log buffer and then to disk immediately. But, changed blocks are deferred until it is more efficient to write them.

System Change Number (SCN): When a user commits a transaction, the transaction is assigned a SCN. These SCN numbers are recorded in redo log

Oracle 12c: From 12c, you start seeing more than one log writer process. The Log Writer (LGWR) creates worker processes (slaves, LGnn) to improve the performance of writing to the redo log.

$ ps -ef | grep ora_lg
prod 5505318 1 0 Dec 10 – 51:14 ora_lg02_prod
prod 6095354 1 0 Dec 10 – 546:27 ora_lg01_prod
prod 3998342 1 0 Dec 10 – 15:24 ora_lg03_prod
prod 5309302 1 8 Dec 10 – 3113:41 ora_lg00_prod
prod 5505940 1 2 Dec 10 – 1083:48 ora_lgwr_prod
prod 11470186 60293956 0 07:48:39 pts/0 0:00 grep ora_lg

However, these multiple log writer slaves might cause a log writer deadlock as mentioned in below link. Temporary solution is to set _use_single_log_writer to TRUE, but applying patch is a permanent solution:






Checkpoint process (CKPT)

This is one background process that you can’t ignore for Oracle Certification Exam (1z0 062). Below are important points on CKPT from certification point of view:

  1. When checkpoint occurs, Oracle Database must update the headers of all datafiles to record the details of the checkpoint.
  2. The CKPT process doesn’t write blocks to disk; DBWn always perform that work
  3. When log switch happens, checkpoint is called
  4. Checkpoint is the position in the log file, from which instance recovery begins in case of crash!

The below parameters decides checkpoint interval:




Types of checkpoints:

As per Jonathan Lewis, below link has various types of checkpoints:

Check point priority:

As per Jonathan Lewis:

The checkpoints started by “alter system checkpoint” are high priority and synchronous – so you wait for the checkpoint to complete and you will see dirty buffers being copied to disc, and both start and end of background checkpoint will be incremented.  The checkpoint started by “alter system switch logfile” is (in all but very old – possibly pre 8.1 – databases) a low priority checkpoint which is asynchronous, so your session returns immediately, the background start is recorded, but no action need be taken, but over a period of time you will see buffers written.

If you have N log files and do N-1 switches you’ll see Oracle suddenly very desperate to catch up (at least a bit) on the background checkpoints because of the “unable to switch logfile checkpoint not complete” problem that appears.




Database writer process(DBWn)

This is one background process that you can’t ignore for Oracle Certification Exam (1z0 062). Below are important points on DBWn from certification point of view:

  1. Buffer cache  ==DBWn==>  datafiles
  2. dirty buffer: When buffer in the buffer cache is modified
  3. Cold buffer: as per LRU, the buffer that hasn’t been used in some time
  4. DBWn writes cold,dirty buffers to datafiles
  5. DB_WRITER_PROCESS: specifies number of DBWn processes
  6. When DBWn writes automatically?

(a) When server process cannot find a clean reusable buffer after scanning a threshold number of buffers.

(b) DBWn periodically writes buffers to advance the checkpoint