A process on a server is a section of a computer program in memory that performs a specific task. When the Oracle server starts, multiple processes are started on the server to perform various functions as part of the Oracle instance. While a detailed discussion of all Oracle background processes is beyond the scope of this book, we will discuss a few of the key processes: Database Writer (DBWn), Log Writer (LGWR), and System Monitor (SMON). These processes communicate with various areas of the SGA, such as the database buffer cache and the redo log buffer, as indicated in the earlier illustration.
Process : An executing computer program in memory that performs a specific task.
Database Writer (DBWn)
There may be anywhere from one to 20 copies (DBW0 through DBW9 and DBWa through DBWj) of the Database Writer process running in an Oracle instance. As noted earlier in the section on the SGA, new and modified data is stored in buffers in the database buffer cache, which are marked as dirty buffers. At some point (for example, when the number of free buffers is low), these buffers need to be written out to disk, which is what the DBWn process does, allowing subsequent SELECT statements and other DML statements access to those buffers in the buffer cache.
Note: Program Global Area (PGA) : A nonshared area of memory used for storing all connection information, including SQL statement information, in a dedicated server configuration for a user who is connected to the database. In a shared server configuration, a large portion of the memory for each connection is stored in the SGA instead of the PGA.
If there is enough memory and the demand on the system is high, having more than one copy of this process may dramatically improve the performance and reduce the response time when a query or DML statement is run.
Log Writer (LGWR)
The Log Writer process writes the buffers in the SGA’s redo log buffer out to disk to the redo log files. The Log Writer process must be able to write redo log buffers fast enough to make sure that there is room in the redo log buffer for entries from new transactions. By writing all changes to the database to the redo logfiles, the changes made to the database can be recovered by reissuing the commands in the logs if an instance failure occurs.
Log Writer writes under a variety of conditions: when a user issues a COMMIT, when the redo log buffer is one-third full, when DBWn writes dirty buffers, or every three seconds.
System Monitor (SMON)
SMON performs a number of different functions in the database. If there is a system crash, the SMON process will apply the changes in the redo log files (saved to disk previously by the LGWR process) to the datafiles the next time the instance is started. This ensures that no committed transactions are lost because of the system crash. (SMON also performs a number of other tasks.)
Process Monitor (PMON)
The background process PMON cleans up after failed processes by
- Rolling back the user’s current transaction
- Releasing all currently held table or row locks
- Freeing other resources currently reserved by the user
- Restarts dead dispatchers
Checkpoint (CKPT)
Every three seconds the CKPT process stores data in the control file to identify that place in the online redo log file where recovery is to begin, which is called a checkpoint. The purpose of a checkpoint is to ensure that all of the buffers in the Database Buffer Cache that were modified prior to a point in time have been written to the data files. This point in time (called the checkpoint position) is where database recovery is to begin in the event of an instance failure. DBWn will already have written all of the buffers in the Database Buffer Cache that were modified prior to that point in time. Prior to Oracle9i, this was done at the end of the online redo log file. In the event of a log switch CKPT also writes this checkpoint information to the headers of the data files.
Checkpoints are initiated for the following reasons:
- To ensure that modified data blocks in memory are written to disk regularly so that data is not lost in case of a system or database failure.
- To reduce the time required for instance recovery. Only the online redo log file entries following the last checkpoint need to be processed for recovery to occur.
- To ensure that all committed data has been written to the data files during shut down.
Checkpoint information written by CKPT includes checkpoint position, system change number, location in the online redo log file to begin recovery, information about logs, and so on
Note: CKPT does not write data blocks to disk or redo blocks to the online redo log files.
Archiver (ARCn)
ARCn is an optional background process, however, it is crucial to recovering a database after the loss of a disk. As online redo log files get filled, the Oracle server begins writing to the next online redo log file. The process of switching from one online redo log file to another is called a log switch. The ARCn process initiates backing up, or archiving, of the filled log group at every log switch. It automatically archives the online redo log file before the log can be reused, so all of the changes made to the database are preserved. This enables recovery of the database to the point of failure even if a disk drive is damaged.
Archiving Online Redo Log Files :One of the important decisions that a DBA has to make is whether to configure the database to operate in ARCHIVELOG or in NOARCHIVELOG mode.
NOARCHIVELOG mode: In NOARCHIVELOG mode, the online redo log files are overwritten each time a log switch occurs. LGWR does not overwrite an online redo log file group until the checkpoint for that group is complete. This ensures that committed data can be recovered if there is an instance crash. During the instance crash, only the SGA is lost. There is no loss of disks, only memory. For example, an operating system crash causes an instance crash.