Oracle DBWR, LGWR, CKPT, ARCH Trigger Conditions Summary

One . DBWR writes disk data trigger conditions

1. The DBWR process is triggered when a process scans on the secondary LRU list and the main LRU list to find buffer headers that can be overwritten, if the number of buffer headers that have been scanned reaches a certain limit (determined by hidden parameters: _db_block_max_scan_pct). 。 _db_block_max_scan_pct represents the number of header buffers that have been scanned as a percentage of the total number of header buffers on the entire LRU list. At this point, the process searching for the available buffer header hangs, appears in v$session_wait waiting for the "free buffer wait" event, and increases the value of "dirty buffers inspected" in v$sysstat. (can query by x$kvit)

This section also has a note in my blog:

Oracle Buffer Cache principle

2. When DBWR looks for a header buffer on the main LRUW list that has been updated and is waiting to be written to a data file, dbWR if the number of buffer headers found exceeds a certain limit (as determined by the hidden parameter: _db_writer_scan_depth_pct).
Instead of continuing to scan down, go to the secondary LRUW list and write dirty blocks of data on it to the data file. _db_writer_scan_depth_pct represents the percentage of dirty blocks that have been scanned as a percentage of the total number of buffer headers on the entire main LRUW list.
The DBWR process is also triggered if the total number of dirty blocks on the main LRUW list and the secondary LRUW list exceeds a certain limit. This limit is determined by the hidden parameter: _db_large_dirty_queue.
DBWR is triggered when an incremental checkpoint occurs or a full checkpoint occurs.
Start DBWR every three seconds.
DBWR is triggered when the table space is set offline.
Issue a command: Table alterspace ... start backup, which triggers DBWR when the table space is set to a hot backup state.
DBWR is triggered when the table space is set to read-only.
DbWR is triggered when an object is deleted, such as a table.

Two . . LGWR writes the online log file trigger condition

1. timeout

When LGWR is idle, it relies on rdbms ipc message waiting and is dormant until the 3 second timeout timeout. If LGWR finds that there is redo to write out, then LGWR will perform a write-out operation and log file parallel write waiting event will occur.

Threshold reaches
The number of log buffers that are already used is calculated as long as a process allocates space in the log buffer. If the number of blocks used is greater than or equal to the _log_io_size parameter setting, the LGWR write operation is triggered. If the LGWR is not active at this time, the LGWR is notified to perform background writes.
The default _log_io_size is equal to 1/3 log buffer size, with an upper limit of 1M, and the value of 0 displayed in X$KSPPSV means the default value.
That is, LGWR will be triggered at Min (1M, 1/3 log size buffer).

Note that the log buffer size here is measured in log block. This value is typically 512 bytes. 3. Submit When something is committed, a submission flag is recorded in redo stream.

This thing is unrecoverable until these redos are written to disk.
Therefore, you must wait for the LGWR write to complete before the transaction returns a success flag to the user. The process notifies LGWR to write and starts hibernating with the log file sync event, with a timeout of 1 second. Oracle's implied parameter _wait_for_sync parameter can be set to false to avoid waiting for redo file sync, but there is no guarantee of the recovery of the transaction
Note that commits in recurring calls, such as those in procedures, do not need to synchronize redo until a response is required to return to the user. Therefore, recursive calls only need to be synchronized back to the last time before the user call The RBA of the Commit operation. There is an SGA variable that records the log block number that the redo thread needs to synchronize.
If multiple commits occur before the LGWR wakes up, the log block number with the highest log number recorded by this variable, before all redo will be written to disk.
This is sometimes referred to as group commit

4. Before DBWR writes, DBWR will post LGWR if the high RBA of the data to be written by DBWR exceeds the on-Disk RBA of LGWR
Go and write. Before Oracle8i, DBWR would wait for the log file sync event.
Starting with Oracle8i, DBWR puts these Blocks into a defer queue and notifies LGWR to redo write-out, which allows DBWR to continue to write data without waiting.

Three . . The trigger condition for CKPT to send a CHECKPOINT signal

1.log the time of the time reached 2. .
The current redo log has been written enough to log_checkpoint_internavl's operating system block size
3. redo log switch
4. alteration system checkpoint
5. alter tablespace XXX begin backup
, end backup time 6. Table alterspace, datafile offline, shutdown immediate, direct read time;

Four . . The ARCH process log archive triggers the condition

1. If automatic archiving mode is set, the ARCH process is triggered by the LGWR process to archive when the log switches. This is the most common way.

2. Can be manually archived. Using the command: System alter archive log current indicates that the ARCH process is started to archive the current log file.

3. If the ARCH process does not receive an LGWR notification after 5 minutes, a timeout occurs and arch is woken up to check for log files that need to be archived. ARCH reads the information in the control file to decide which log files need to be archived and which should be archived. However, the ARCH process does not start during instance recovery or media recovery.

reprinted from:



Email:[email protected]


DBA1 Group: 62697716 (full); DBA2 Group: 62697977 (full) DBA3 group: 62697850 (full).

DBA Supergroup: 63306533 (full); DBA4 Group: 83829929 DBA5 Group: 142216823

DBA6 Group: 158654907 Chat Group: 40132017 Chat Group 2: 69087192

-- The group needs to explain the relationship between oracle table space and data files in the notes, otherwise the request will be rejected