Search this blog for other interesting articles

Thursday 21 July 2011

Oracle Background Processes




This article answers the following queries :

  • What are the different background processes of oracle database ?
  • What are the functionalities of different background processes of oracle?
  • What is dirty buffer ?
  • What is cold buffer?
  • What is write-ahead-logging ?
  • How to list or view the different background processes of oracle ?


Background processes are created from the oracle binary when an instance is started.  As the name suggests background processes run in background and they are meant to perform certain specific activities or to deal with abnormal scenarios that arise during the runtime of an instance.

From SAP perspective,  the following are the 6 most important background processes of oracle database.

Database Writer (DBWR) :

The database writer writes dirty blocks from the database buffer to the datafiles.

Dirty blocks need to be flushed out to disk to make room for new blocks in the cache. When a buffer in the database buffer cache is modified it is marked as dirty buffer. A cold buffer is a buffer that has not been recently used according to the least recently used (LRU) algorithm. The database writer writes cold, dirty buffers to disk so that new blocks can be read into the cache.

The initialization parameter DB_WRITER_PROCESSES specifies the number of database writer processes. The maximum number of database writer processes is 20.

The database writer writes the dirty buffers to disk under the following conditions :

  1. When a checkpoint occurs
  1. Every 3 seconds
  1. When a server process couldn’t find a clean reusable buffer after scanning a threshold number of buffers

Log Writer (LGWR) :

The log writer process writes data from the redolog buffers to the redolog files on disk.

The redolog buffer is a circular buffer. When LGWR writes redo entries from the redolog buffer to the redolog file, server processes can overwrite the  entries that are already copied with new entries in redolog buffer. LGWR writes at a faster pace so that space is always available in the buffer for new entries.

The log writer gets activated under the following conditions :

  1. When a transaction is commited
  1. Every  3 seconds
  1. When the redo-log buffer is 0ne third full
  1. When a database writer writes modified buffers to disk, if necessary
Note : Before database writer can write dirty blocks to disk it should make sure that all redo entries are written from the redolog buffer to the disk. This is also known as write-ahead logging. If database writer finds that some redo-records are not written, it signals LGWR to write to disk and waits for LGWR to complete writing the redolog buffer before it can write out the databuffers.

Checkpoint (CKPT) :

Checkpoint signals the synchronization of all database files with the check point information. It ensures data consistency and faster database recovery in case of a crash.

The checkpoint process regularly initiates a checkpoint. Whenever a check point occurs following things are carried out  :

  1. Updating the file headers of the data files with information about the last checkpoint performed
  1. Update control files about the last checkpoint
  1. Initiates LGWR to flush the redolog buffer entries to redolog files.
  1. Writes the checkpoint record to the redolog file
  1. Initiates DBWR to write all dirty blocks to disk and thus synchronizes database

Archiver Process(ARCH) :

The archiver process copies online redolog files to the designation archive log location after the occurrence of a log switch. It is an optional process. Archiver is present only when database is running in archive log mode and automatic archiving is enabled.

You can specify multiple archiver processes with initialization parameter LOG_ARCHIVE_MAX_PROCESSES.  ALTER SYSTEM command can be used to increase or decrease the number of archiver processes.

However it is not recommended for us to change this value, as Log writer starts a new archiver process automatically when the current archive processes are insufficient to handle the workload

Process Monitor (PMON) :

The process monitor performs process recovery when a user process fails. PMON is responsible for cleaning up the database buffer cache and freeing resources that the user process was using like releasing locks, removing process ids from active processes list etc.

PMON checks the running status of dispatcher and server processes periodically and restarts in case any have stopped. Please note that this won’t start processes that are intentionally stopped by Oracle.

PMON also registers information about the instance and dispatcher processes with the network listener.

PMON wakes up every 3 seconds to perform house keeping activities and should be running always for an instance.

System Monitor (SMON) :

The system monitor performs instance recovery, if necessary, at instance startup. SMON is also responsible for cleaning up temporary segments that are no longer in use and for coalescing contiguous free extents within dictionary managed tablespaces.

SMON can be called by other processes in cases of need. SMON wakes up every 5 seconds to perform house keeping activities. SMON must always be running for an instance.
           

Query to view background processes of Oracle :

Goto SQL prompt of oracle database system and provide following command to view background processes.

SQL>

Select * from v$session where type =  ‘BACKGROUND’;








Enter your email address:


Delivered by FeedBurner




No comments:

Search this blog for other interesting articles

Please subscribe & activate link received to your email id to receive latest articles

Enter your email address:

Delivered by FeedBurner