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?
- When user process commits a transaction
- Every three seconds
- When redo log buffer is one-third full
- When log switch
- 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: