Oracle Database Architecture (Part 03)

A - Required Oracle background processes by the Oracle instance (found in all Oracle instances) :

1. Database Writer (DBWn) :Writes modified database blocks from the SGA’s database buffer cache to the data files on disk.
2. Checkpoint (CKPT) : Updates the data file headers following a checkpoint event.
3. Log Writer (LGWR) : Writes transaction recovery information from the SGA’s redo log buffer to the online redo log files on disk.
4. Process Monitor (PMON) : Cleans up failed user database connections
5. System Monitor (SMON) : Performs instance recovery following an instance crash, coalesces free space in the database, and manages space used for sorting .
6. Listener Registration (LREG) : Registers information about the database instance and dispatcher processes with the listener.
7. Recoverer (RECO) : Recovers failed transactions that are distributed across multiple databases when using Oracle’s distributed database feature.
8. Memory Monitor (MMON) : Gathers and analyzes statistics used by the Automatic Workload Repository feature. 
9. Memory Monitor Light (MMNL) : Gathers and analyzes statistics used by the Active Session History feature.
10. Virtual Keeper of Time (VKTM) : Responsible for providing a wall-clock time (updated every second) and reference-time counter.
11. Diagnosability (DIAG) : Performs diagnostic dumps.
12. Diagnosability (DIA0) : Diagnostic process responsible for hang detection and deadlock resolution.

B - Optional Oracle Background Processes  (may or may not be used, depending on the features that are being used in the database)

1. Archiver (ARCn) : Copies the transaction recovery information from the redo log files to the archive location.Nearly all production databases use this optional process. You can have up to 30 archival processes (ARC0-ARC9, ARCa-ARCt).
2. Recovery Writer (RVWR):  Writes flashback data to flashback database logs in the fast recovery area.
3. ASM Disk (ASMB) : Present on databases using Automatic Storage Management disks.
4. ASM Balance (RBAL) : Coordinates rebalance activity of disks in an ASM disk group.
5. Job Queue Monitor (CJQn) Assigns jobs to the job queue processes when using Oracle’s job scheduling feature.
6. Job Queue (Jnnn) : Executes database jobs that have been scheduled using Oracle’s job-scheduling feature.
7. Queue Monitor (QMNn) : Monitors the messages in the message queue when Oracle’s Advanced Queuing feature is used.
8. Event Monitor (EMNC) : Process responsible for event-management coordination and notification.
9. Flashback Data Archive (FBDA) : Archives historical records from a table when the flashback data archive feature is used.
10. Parallel Query Slave (Qnnn) : Carries out portions of a larger overall query when Oracle’s Parallel Query feature is used.
11. Dispatcher (Dnnn) : Assigns user’s database requests to a queue where they are then serviced by shared server processes when Oracle’s Shared Server feature is used.
12. Shared Server (Snnn) : Server processes that are shared among several users when Oracle’s Shared Server feature is used.
13. Memory Manager (MMAN) : Manages the size of each individual SGA component when Oracle’s Automatic Shared Memory Management feature is used. 
14. Change Tracking Writer (CTWR) : Keeps track of which database blocks have changed when Oracle’s incremental Recovery Manager feature is used.
15. Space Management Coordinator (SMCO) : Coordinates various space management tasks. Worker processes are identified with Wnnn.

1-Database Writer (DBWN)

The Purpose of the Data Writer Process (DBWN) is to write the contens of the dirty Buffers to the Data Files. By Default, Oracle Starts One Database When Instance Starts. 

For Multiuser and Busy Systems, You can have up to 100 database writers Processes to Improving Performance. 

The Names of the First 36 Database Writer Processes Are DBW0- DBW9 and DBWA -DBWz. 

The Names of the 37th Through 100th Database Writer Processes Are BW36 -BW99. 

The Parameter DB_WRITER_PROCESESS Determines The Additional Number of Database Writer Processes to Be Started. Having More DBWN Processes Than the Number Of cpus is normally not beneficial.

The DBWN Process Writes The Modified Buffer Blocks to Disk, So More Free Buffers Are Available in the Buffer Cache. Writes are Always Perform in Bulk to Reduce Disk Contentation;

The Number of Blocks Written in Each I/O is OS-Dependent.

When Does Database Writer Write?

1. A user’s server process has searched too long for a free buffer when reading a buffer into the buffer cache. (dirty buffers reach threshold)
2. The number of modified and committed, but unwritten, buffers in the database buffer cache is too large. (there are no free buffers)
3. At a database checkpoint event. 
4. The instance is shut down using any method other than a shutdown abort.
5. A tablespace is placed into backup mode.
6. A tablespace is taken offline to make it unavailable or is changed to READ ONLY / OFFLNE.
7. A segment/table is dropped , truncate.

2-Checkpoint (CKPT)

When a change is committed to a database, Oracle identifies the transaction with a unique number called the system change number (SCN).

The value of an SCN is the logical point in time at which changes are made to a database. 

A checkpoint is when the DBWn process writes all the dirty buffers to the data files. When a checkpoint occurs, Oracle must update the control file and each data file header to record the checkpoint. This update is done by the checkpoint process (CKPT);  the DBWn process writes the actual data blocks to the data files.

Checkpoints help reduce the time required for instance recovery. If checkpoints occur too frequently, disk contention becomes a problem with the data file updates. If checkpoints occur too infrequently, the time required to recover a failed database instance can be significantly

longer. Checkpoints occur automatically when an online redo log file is full (a log switch happens).

When a redo log switch happens, the checkpoint process needs to update the header of all the data files; this causes performance issues on databases with hundreds of data files.

To alleviate this situation, Oracle uses incremental checkpoints. Here the responsibility of updating the data file header is given to the DBWn process, when it writes dirty buffers to data files. The CKPT process updates only the control file with the checkpoint position, not the data files.

When Does Database Make CHECK POINT?

1. The database writes all the dirty buffers to data files. 
2. This happens during normal database shutdown, 
3. Online redo log switch, 
4. Dorced checkpoint using ALTER SYSTEM CHECKPOINT, 
5. The database is placed in backup mode using ALTER DATABASE BEGIN BACKUP.

3-Log Writer (LGWR)

The log writer process (LGWR) writes the blocks in the redo log buffer of the SGA to the online redo log files. When the LGWR writes log buffers to disk, Oracle server processes can write new entries in the redo log buffer. LGWR writes the entries to the disk fast enough to ensure that room is available for the server process to write the redo entries. There can be only one LGWR process in the database.

If the redo log files are multiplexed, LGWR writes simultaneously to all the members of the redo log group. Even if one of the log files in the group is damaged, LGWR writes the redo information to the available files. 

LGWR writes to the redo log files sequentially so that transactions can be applied in order in the event of a failure. As soon as a transaction commits, the information is written to redo log files. By writing the committed transaction immediately to the redo log files, the change to the database is never lost. Even if the database crashes, committed changes can be recovered from the online redo log files and applied to the data files.

When Does Log Writer Write?

1. Three seconds since the last LGWR write.
2. When a user commits a transaction.
3. When the redo log buffer is a third full.
4. When the redo log buffer contains 1MB worth of redo information.
5. Whenever a database checkpoint occurs.

4-Process Monitor (PMON)

The process monitor process (PMON) cleans up failed user processes and frees up all the resources used by the failed process. It resets the status of the active transaction table and removes the process ID from the list of active processes.

 It reclaims all the resources held by the user and releases all locks on tables and rows held by the user. PMON wakes up periodically to check whether it is needed. Other processes can call PMON if they detect a need for a PMON process.

PMON also checks on some optional background processes and restarts them if any have stopped.

5-System Monitor (SMON) 

The system monitor process (SMON) performs instance or crash recovery at database startup by using the online redo log files. 

SMON is also responsible for cleaning up temporary segments in the tablespaces that are no longer used and for coalescing the contiguous free space in the dictionary-managed tablespaces.

Rolls forward changes in redo logs , open database for users , rolls back uncommitted trans .

 If any dead transactions were skipped during instance recovery because of file-read or offline errors, SMON recovers them when the tablespace or data file is brought back online. SMON wakes up regularly to check whether it is needed.

Other processes can call SMON if they detect a need for an SMON process.

Practical :

To view these background processes from the operating system using the ps command

$ps -ef  | grep $ORACLE_SID

The dynamic view V$BGPROCESS shows the background processes availabe : 

SELECT min(name || ': '|| description) process_description

FROM v$bgprocess 

group by substr(name,1,3) ORDER BY 1