What is oracle database ? Part IV

    1.   PMON
    2.   SMON
    3.   DBWn
    4.   LGWR
    5.   CKPT
    6.   ARCn
    7.   RECO
    8.   RVWR
    9.   MMON
  10.   MMNL
  11.   MMAN
  12.   CJQn

         1.  PMON

PMON is an oracle background process called process monitor.
PMON responsible for releasing Locks & cleaning up cache post abrupt termination of user process holding resources
PMON also communicates workload advisory to listener in real application cluster environment which helps listener to redirect connection to least loaded instance
PMON restarts dispatcher & server process if it fails
PMON is responsible for service registration to listener which avoids manual configuration of service in listener.ora file by supplying information like database/instance name & its current/maximum load, dispatcher/server mode (shared/dedicated) info.
         2.  SMON

SMON is system monitor process.
SMON is responsible for performing instance recovery during startup of DB post abrupt shutdown/termination of instance.
SMON performs two operation while performing instance recovery i.e. Roll forward & Roll Back during which it refers UNDO & Online Redo log file
SMON coalesces adjacent free extents into large free extents in dictionary managed tablespace
It wakes up every 5 min to perform Housekeeping activity, killing SMON background process terminates instance
In RAC, SMON process of one instance can perform instance recovery of other instance that has failed

SMON cleans up temporary segments that are no longer in use and recovers dead transactions which were skipped during system failure/instance recovery because of file-read errors or offline file status. Such transactions are recovered by SMON when the tablespace or data file is brought back online.  

        3. DBWn

DBWn is database writer background process, number of process n ranging from 0-9 , a-j  total 20 max
The initialization parameter DB_WRITER_PROCESSES specifies the number of DBWn processes.
It writes modified blocks from the database buffer cache to the datafiles.
Database selects an appropriate default setting for this initialization parameter based upon the number of CPUs and processor groups if it is not set at startup

DBWn writes buffers when:
- A DBWn timeout occurs (every 3 seconds).
- A normal or incremental checkpoint occurs.
- The number of dirty buffers reaches a threshold value.
- A process scans a specified number of blocks when scanning free buffers and cannot find any.
- A normal or temporary tablespace is placed offline.
- A tablespace is put into read-only mode. 
- A tablespace is dropped or truncated.
- ALTER TABLESPACE <tablespace_name> BEGIN BACKUP; is executed

Modified or new data is not necessarily written to a datafile immediately. To reduce the amount of disk access and to increase performance, data is pooled in memory and written to the appropriate data files all at once (in bulk mode), as determined by the background process database writer process (DBWn).

The disk write can happen before or after the commit.

After a COMMIT, the database writes the redo buffers to disk but does not immediately write data blocks to disk. Instead, database writer (DBWn) performs lazy writes in the background.

        4. LGWR

LGWR is Log writer background oracle process
LGWR process writes redo log buffer in to online redo log files & members simultaneously
It also signals archival process when a redo log group is about to get overwritten if DB is in ARCHIVELOG Mode

Oracle Database uses a fast commit mechanism to improve performance for committed transactions.
When a user issues a COMMIT statement, the transaction is assigned a system change number (SCN). LGWR puts a commit record in the redo log buffer and writes it to disk immediately, along with the commit SCN and transaction's redo entries.

The redo log buffer is circular. When LGWR writes redo entries from the redo log buffer to an online redo log file, server processes can copy new entries over the entries in the redo log buffer that have been written to disk.
LGWR normally writes fast enough to ensure that space is always available in the buffer for new entries, even when access to the online redo log is heavy.

The atomic write of the redo entry containing the transaction's commit record is the single event that determines the transaction has committed. Oracle Database returns a success code to the committing transaction although the data buffers have not yet been written to disk. The corresponding changes to data blocks are deferred until it is efficient for DBWn to write them to the data files.

LGWR can write redo log entries to disk before a transaction commits.
The redo entries become permanent only if the transaction later commits.
When activity is high, LGWR can use group commits.
For example, a user commits, causing LGWR to write the transaction's redo entries to disk. During this write to disk LGWR can write the list of redo entries of waiting transactions for other user processes (not yet committed) in one operation instead of performing IO followed by every user process commit. In this way, the database minimizes disk I/O and maximizes performance. If commits requests continue at a high rate, then every write by LGWR can contain multiple commit records.

LGWR writes synchronously to the active mirrored group of online redo log files. If a log file is inaccessible, then LGWR continues writing to other files in the group and writes an error to the LGWR trace file and the alert log. If all files in a group are damaged, or if the group is unavailable because it has not been archived, then LGWR cannot continue to function and DB hangs.

In the following circumstances, LGWR writes to redo log file:
 -A user commits a transaction
 -An online redo log switch occurs.
 -Three seconds have passed since LGWR last wrote.
 -The redo log buffer is one-third full or contains 1 MB of buffered data.
 -DBWn must write modified buffers to disk.

       5.  CKPT

CKPT is an oracle background process ensures that database is consistent by ensuring that all committed transactions have been written to datafiles
A checkpoint manages/ensures following three operations:
               a)       Every dirty block in the buffer cache is written to the data files.
        b)    The latest SCN is written (updated) into the datafile header.
        c)    The latest SCN is also written to the control files.

Events that trigger a checkpoint are:
- Redo log switch
- LOG_CHECKPOINT_INTERVAL has been reached
- alter system checkpoint; is executed
- Tablespaces/database put in  hot backup

Note: redo log switches cause a checkpoint, checkpoints don't cause a log switch.
Set LOG_CHECKPOINTS_TO_ALERT=TRUE to see checkpoint information in alert log

Types of Checkpoint

In Incremental Checkpoint, checkpoint information is written to the control file. In the following cases:

1. Every three second.
2. At the time of log switch - Sometimes log switches may trigger a complete checkpoint, if the next log where the log switch is to take place is Active.

In Complete Checkpoint, checkpoint information is written in control file, datafile header and also dirty block is written by DBWR to the datafiles.

 Full Checkpoint happens in the following cases.

 1. To meet fast_start_mttr_target
 2. before Clean Shutdown
 3. Some log switches may trigger a complete checkpoint , if the next log where the log switch is to take place is Active.
 This has more chance of happening when the Redo Log files are small in size and continuous transactions are taking place.
 4. when the 'alter system checkpoint' command is issued

Partial Checkpoint happens in the following cases.

 1. Before begin backup.
 2. before tablespace offline.
 3. before placing tablespace in read only.
 4. before dropping tablespace.
 5. before taking datafile offline.
 6. When checkpoint queue exceeds its threshold.
 7. Before segment is dropped.

 8. Before adding and removing columns from table.

No comments:

Post a Comment