There are many Oracle background processes. DBA should have knowledge of atleast Mandatory background processes i.e.,DBWR, PMON, CKPT,LGWR, SMON
Below are the Optional background processes
ARCn, LMON, Snnn,QMNn, LMDn,CJQ0, Pnnn,LCKn, Dnnn
Lets have detailed knowledge of mandatory background processes:
Database Writer(DBWR)
The server process records changes to rollback and data blocks in the buffer cache. Database
Writer (DBWn) writes the dirty buffers from the database buffer cache to the data files. It
ensures that a sufficient number of free buffers—buffers that can be overwritten when server
processes need to read in blocks from the data files—are available in the database buffer
cache. Database performance is improved because server processes make changes only in the
buffer cache.
DBWn defers writing to the data files until one of the following events occurs:
• Incremental or normal checkpoint
• The number of dirty buffers reaches a threshold value
• A process scans a specified number of blocks when scanning for free buffers and cannot fine any.
• Timeout occurs.
• A ping request in Real Application Clusters environment.
• Placing a normal or temporary tablespace offline.
• Placing a tablespace in read only mode.
• Dropping or Truncating a table.
ALTER TABLESPACE tablespace name BEGIN BACKUP
LOG Writer(LGWR)
LGWR performs sequential writes from the redo log buffer cache to the redo log file under the following situations:
• When a transaction commits
• When the redo log buffer cache is one-third full
• When there is more than a megabyte of changes records in the redo log buffer cache
• Before DBWn writes modified blocks in the database buffer cache to the data files
• Every 3 seconds.
Because the redo is needed for recovery, LGWR confirms the commit only after the redo is written to disk.
LGWR can also call on DBWn to write to the data files.
Note: DBWn does not write to the online redo logs.
System Monitor(SMON)
If the Oracle instance fails, any information in the SGA that has not been written to disk is lost. For example, the failure of the operating system causes an instance failure. After the loss
of the instance, the background process SMON automatically performs instance recovery when the database is reopened. Instance recovery consists of the following steps:
1. Rolling forward to recover data that has not been recorded in the data files but that has been recorded in the online redo log. This data has not been written to disk because of
the loss of the SGA during instance failure. During this process, SMON reads the redo log files and applies the changes recorded in the redo log to the data blocks. Because all
committed transactions have been written to the redo logs, this process completely recovers these transactions.
2. Opening the database so that users can log on. Any data that is not locked by unrecovered transactions is immediately available.
3. Rolling back uncommitted transactions. They are rolled back by SMON or by the individual server processes as they access locked data.
SMON also performs some space maintenance functions:
• It combines, or coalesces, adjacent areas of free space in the data files.
• It deallocates temporary segments to return them as free space in data files. Temporary segments are used to store data during SQL statement processing.
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)
An event called a checkpoint occurs when the Oracle background process DBWn writes all the modified database buffers in the SGA, including both committed and uncommitted data,to the data files.
Checkpoints are implemented for the following reasons:
• Checkpoints ensure that data blocks in memory that change frequently are written to data files regularly. Because of the least recently used algorithm of DBWn, a data block that changes frequently might never qualify as the least recently used block and thus might never be written to disk if checkpoints did not occur.
• Because all database changes up to the checkpoint have been recorded in the data files,redo log entries before the checkpoint no longer need to be applied to the data files if instance recovery is required. Therefore, checkpoints are useful because they can expedite instance recovery.
At a checkpoint, the following information is written:
• Checkpoint number into the data file headers
• Checkpoint number, log sequence number, archived log names, and system change numbers into the control file.
CKPT does not write data blocks to disk or redo blocks to the online redo logs.
Below are the Optional background processes
ARCn, LMON, Snnn,QMNn, LMDn,CJQ0, Pnnn,LCKn, Dnnn
Lets have detailed knowledge of mandatory background processes:
Database Writer(DBWR)
The server process records changes to rollback and data blocks in the buffer cache. Database
Writer (DBWn) writes the dirty buffers from the database buffer cache to the data files. It
ensures that a sufficient number of free buffers—buffers that can be overwritten when server
processes need to read in blocks from the data files—are available in the database buffer
cache. Database performance is improved because server processes make changes only in the
buffer cache.
DBWn defers writing to the data files until one of the following events occurs:
• Incremental or normal checkpoint
• The number of dirty buffers reaches a threshold value
• A process scans a specified number of blocks when scanning for free buffers and cannot fine any.
• Timeout occurs.
• A ping request in Real Application Clusters environment.
• Placing a normal or temporary tablespace offline.
• Placing a tablespace in read only mode.
• Dropping or Truncating a table.
ALTER TABLESPACE tablespace name BEGIN BACKUP
LOG Writer(LGWR)
LGWR performs sequential writes from the redo log buffer cache to the redo log file under the following situations:
• When a transaction commits
• When the redo log buffer cache is one-third full
• When there is more than a megabyte of changes records in the redo log buffer cache
• Before DBWn writes modified blocks in the database buffer cache to the data files
• Every 3 seconds.
Because the redo is needed for recovery, LGWR confirms the commit only after the redo is written to disk.
LGWR can also call on DBWn to write to the data files.
Note: DBWn does not write to the online redo logs.
System Monitor(SMON)
If the Oracle instance fails, any information in the SGA that has not been written to disk is lost. For example, the failure of the operating system causes an instance failure. After the loss
of the instance, the background process SMON automatically performs instance recovery when the database is reopened. Instance recovery consists of the following steps:
1. Rolling forward to recover data that has not been recorded in the data files but that has been recorded in the online redo log. This data has not been written to disk because of
the loss of the SGA during instance failure. During this process, SMON reads the redo log files and applies the changes recorded in the redo log to the data blocks. Because all
committed transactions have been written to the redo logs, this process completely recovers these transactions.
2. Opening the database so that users can log on. Any data that is not locked by unrecovered transactions is immediately available.
3. Rolling back uncommitted transactions. They are rolled back by SMON or by the individual server processes as they access locked data.
SMON also performs some space maintenance functions:
• It combines, or coalesces, adjacent areas of free space in the data files.
• It deallocates temporary segments to return them as free space in data files. Temporary segments are used to store data during SQL statement processing.
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)
An event called a checkpoint occurs when the Oracle background process DBWn writes all the modified database buffers in the SGA, including both committed and uncommitted data,to the data files.
Checkpoints are implemented for the following reasons:
• Checkpoints ensure that data blocks in memory that change frequently are written to data files regularly. Because of the least recently used algorithm of DBWn, a data block that changes frequently might never qualify as the least recently used block and thus might never be written to disk if checkpoints did not occur.
• Because all database changes up to the checkpoint have been recorded in the data files,redo log entries before the checkpoint no longer need to be applied to the data files if instance recovery is required. Therefore, checkpoints are useful because they can expedite instance recovery.
At a checkpoint, the following information is written:
• Checkpoint number into the data file headers
• Checkpoint number, log sequence number, archived log names, and system change numbers into the control file.
CKPT does not write data blocks to disk or redo blocks to the online redo logs.
No comments:
Post a Comment