The most important two components of Oracle Instance are SGA and Background Processes. Each Oracle Database process on the operating system is responsible for one task. Each process runs in harmony with the other process.
Background Processes are actively running as long as the Oracle Instance is running.
If you are working on unix and linux systems, each process is separate and each has different tasks.
On Windows, there is only one process called oracle.exe, and all processes are executed through this process.
Some background processes can be found below.
Before you read this article, I suggest you read the article “Oracle Memory Components(SGA and PGA)” to understand some of the concepts and content I use on the article.
How To Find Background Running Process in Linux
Running background processes can be seen as follows.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 | -bash-3.2$ ps -ef | grep ora_ oracle 913 884 0 16:06 pts/1 00:00:00 grep ora_ oracle 31003 1 0 13:03 ? 00:00:00 ora_pmon_ONURDB oracle 31007 1 0 13:03 ? 00:00:00 ora_psp0_ONURDB oracle 31011 1 0 13:03 ? 00:00:04 ora_vktm_ONURDB oracle 31017 1 0 13:03 ? 00:00:00 ora_gen0_ONURDB oracle 31021 1 0 13:03 ? 00:00:00 ora_diag_ONURDB oracle 31025 1 0 13:03 ? 00:00:00 ora_dbrm_ONURDB oracle 31029 1 0 13:03 ? 00:00:03 ora_dia0_ONURDB oracle 31033 1 0 13:03 ? 00:00:00 ora_mman_ONURDB oracle 31037 1 0 13:03 ? 00:00:00 ora_dbw0_ONURDB oracle 31041 1 0 13:03 ? 00:00:00 ora_lgwr_ONURDB oracle 31045 1 0 13:03 ? 00:00:01 ora_ckpt_ONURDB oracle 31049 1 0 13:03 ? 00:00:00 ora_smon_ONURDB oracle 31053 1 0 13:03 ? 00:00:00 ora_reco_ONURDB oracle 31057 1 0 13:03 ? 00:00:01 ora_mmon_ONURDB oracle 31061 1 0 13:03 ? 00:00:02 ora_mmnl_ONURDB oracle 31065 1 0 13:03 ? 00:00:00 ora_d000_ONURDB oracle 31069 1 0 13:03 ? 00:00:00 ora_s000_ONURDB oracle 31087 1 0 13:03 ? 00:00:00 ora_arc0_ONURDB oracle 31091 1 0 13:03 ? 00:00:00 ora_arc1_ONURDB oracle 31095 1 0 13:03 ? 00:00:00 ora_arc2_ONURDB oracle 31099 1 0 13:03 ? 00:00:00 ora_arc3_ONURDB oracle 31103 1 0 13:03 ? 00:00:00 ora_qmnc_ONURDB oracle 31131 1 0 13:03 ? 00:00:01 ora_cjq0_ONURDB oracle 31143 1 0 13:03 ? 00:00:00 ora_q000_ONURDB oracle 31147 1 0 13:03 ? 00:00:00 ora_q001_ONURDB oracle 31200 1 0 13:08 ? 00:00:00 ora_smco_ONURDB oracle 31745 1 0 13:58 ? 00:00:00 ora_w000_ONURDB |
Or it can be seen by querying v$process view.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 | SQL> desc v$process; Name Null? Type ----------------------------------------- -------- ---------------------------- ADDR RAW(8) PID NUMBER SPID VARCHAR2(24) PNAME VARCHAR2(5) USERNAME VARCHAR2(15) SERIAL# NUMBER TERMINAL VARCHAR2(30) PROGRAM VARCHAR2(48) TRACEID VARCHAR2(255) TRACEFILE VARCHAR2(513) BACKGROUND VARCHAR2(1) LATCHWAIT VARCHAR2(16) LATCHSPIN VARCHAR2(16) PGA_USED_MEM NUMBER PGA_ALLOC_MEM NUMBER PGA_FREEABLE_MEM NUMBER PGA_MAX_MEM NUMBER SQL> SELECT PID, USERNAME, PROGRAM FROM V$PROCESS; PID USERNAME PROGRAM ---------- --------------- ------------------------------------------------ 1 PSEUDO 2 oracle oracle@DBONUR (PMON) 3 oracle oracle@DBONUR (PSP0) 4 oracle oracle@DBONUR (VKTM) 5 oracle oracle@DBONUR (GEN0) 6 oracle oracle@DBONUR (DIAG) 7 oracle oracle@DBONUR (DBRM) 8 oracle oracle@DBONUR (DIA0) 9 oracle oracle@DBONUR (MMAN) 10 oracle oracle@DBONUR (DBW0) 11 oracle oracle@DBONUR (LGWR) PID USERNAME PROGRAM ---------- --------------- ------------------------------------------------ 12 oracle oracle@DBONUR (CKPT) 13 oracle oracle@DBONUR (SMON) 14 oracle oracle@DBONUR (RECO) 15 oracle oracle@DBONUR (MMON) 16 oracle oracle@DBONUR (MMNL) 17 oracle oracle@DBONUR (D000) 18 oracle oracle@DBONUR (S000) 19 oracle oracle@DBONUR (SMCO) 20 oracle oracle@DBONUR (ARC0) 21 oracle oracle@DBONUR (ARC1) 22 oracle oracle@DBONUR (ARC2) PID USERNAME PROGRAM ---------- --------------- ------------------------------------------------ 23 oracle oracle@DBONUR (ARC3) 24 oracle oracle@DBONUR (QMNC) 25 oracle oracle@DBONUR (W000) 26 oracle oracle@DBONUR (Q000) 27 oracle oracle@DBONUR (Q001) 28 oracle oracle@DBONUR (CJQ0) 29 oracle oracle@DBONUR (TNS V1-V3) 33 oracle oracle@DBONUR (TNS V1-V3) 30 rows selected. |
Oracle Background Processes
SMON(The System Monitor)
SMON’s main task is to mount and open the database. When doing this, it finds and validates ControlFile.
It then finds all the data and log files with the information in the ControlFile, validates it, and finally opens the database. After the database is opened, SMON has some tasks. For example, clears the empty spaces in the datafile. And if necessary, perform the recover operation at the start of the instance.
Its responsible for Instance recovery. If the database is suddenly shut down in some way, it recovers Instance using Online Redo Log files in NOMOUNT step when the database is restarted. It makes the database consistent. It is also responsible for cleaning up “Temporary Segments”. In addition, suddenly terminated “Transactions” are recovered by SMON.
If the SMON process is killed on Linux, Instance will close suddenly.
1 2 3 4 5 6 7 8 9 10 11 12 | -bash-3.2$ ps -ef | grep smon | grep -v grep oracle 1087 1 0 16:17 ? 00:00:00 ora_smon_ONURDB -bash-3.2$ kill -9 1087 -bash-3.2$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Tue Jun 23 16:22:27 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> |
PMON(The Process Monitor):
A user session is a user processes that connect to server processes. Server processes start when the user session starts and ends when the session ends. If the user session is not properly shut down, PMON follows this process, closes the relevant server processes and returns the PGA memory it allocates. If there is an uncommitted transaction, it rolls back.
So, it is responsible for clearing the server processes of failed and suddenly terminated user processes, releasing the resources used by those processes and returning them to the operating system.
DBWn(Database Writer):
Sessions do not write directly to disk (update, insert, delete). First it is written in the Database Buffer Cache.
Database Writer is responsible for transfering dirty(changed data blocks by DML) data blocks in the Database Buffer Cache to the Physical Data Files. In addition, if you want to read data and if the data is not in the Database Buffer Cache, it is responsible for transfering data to Database Buffer Cache from the Physical Data Files.
There can be more than one Database Writer in an instance. The maximum number is 20. Therefore, there is an “n” at the end of the name. By default, there is a Database Writer for every 8 CPUs.
The Database Writer does not write all of the dirty data in the buffer (the records that have changed since reading the disk) waiting to be written.
When dbwr writes to data files
- When there is no free buffer in the Database Buffer Cache, a server process should first find a free buffer when it wants to throw a block into the Database Buffer Cache. If it does not find any free buffers or if it takes too much time to find the free buffer, it sends a message to the Database Writer to write a portion of the dirty buffer (writes starting from the longest unused time) to disk.
- If there are too many Dirty Buffers, the Database Writer is triggered even if there is no other reason.
- DBWn is triggered every 3 seconds even if the problems in the above two items do not occur.
- If a tablespace is set to OFFLINE,
- If a tablespace is set to read-only.
- If a table is dropped,
- If a table is truncated,
- If a tablespace is set to BACKUP mode,
- If a Checkpoint operation is performed,
- In any of the above mentioned items, the entire dirty buffer do not written to the disk(only one part is written). If checkpoint process occurs, all dirty buffers are written directly to disk. This is a costly process. When Checkpoint operation is performed, CPU usage and disk I/O increases.
To change the number of Database Writer, we need to set the DB_WRITER_PROCESS parameter.
1 2 3 4 5 | SQL> show parameter db_writer NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_writer_processes integer 1 |
LGWR(The Log Writer)
LGWR is responsible for writing the blocks in the Redo Log Buffer to the Online Redo Log file on the Physical disk. Every transaction that occurs in the database is stored in the Redo Log Buffer. Unlike the DBW (n) process, LGWR (n) is a hardworking process.
There is a Redo Log Buffer area in the SGA for the Redo Log Files.
Processes are not written directly to the redo log file. The data that needs to be transferred to disk is stored in the redo log buffer first.
However, in order to ensure that the changes are not lost, the data in the redo log buffer is also written to the redo log on disk almost in real time.
LGWR writes the data in the Redo Log Buffer to the Redo Log in the disk.
If a commit operation is performed, LGWR writes the redo log in the “redo log buffer” in real time.
Because the COMMITTED data must be guaranteed.
When lgwr writes to redo log files
- In each commit transaction,
- When 1/3 of the Redo Log buffer area is full,
- When a 1 MB redo occurs,
- Every 3 seconds,
- When the online Redo Log file is switched
CKPT(The Checkpoint Process)
CKPT is the process that performs Checkpoint. If the database is shut down for any reason, the instance will be recovered by SMON using Online Redo Log Files when opening the instance. This recovery process directly affects the recovery time of the database. The task of the Checkpoint process is to minimize this time.
In Oracle 8 and earlier, Checkpoint was performed at regular intervals to ensure that the database can perform a quick recovery in case of instance failure.
The checkpoint process writes to the dirty buffer disk.
Thus, when the recovery process is performed after the crash, the process of writing the records in the redo log to the data files is minimized and the recovery time of the database becomes shorter.
This will open the database faster. The CKPT process triggers Checkpoint regularly.
After 8i, incremental checkpoint introduced. With the incremental checkpoint, the CKPT process instructs the DBWn to write a partial dirty buffer. Thus, the interval between DBWn and LGWR is not allowed. Since the difference between DBWn and LGWR does not increase much, the recovery time is shortened. Therefore, the incremental checkpoint makes the recovery time shorter.
However, Incremental Checkpoint reduces performance. Because, DBWn will write the dirty buffer more frequently to the disk.
With the incremental checkpoint, the current state of the checkpoint (the point at which recovery starts after the crash) is known as the redo byte address (RBA) and is stored in the control file. After each incremental checkpoint operation, this point in the control file is updated.
Full Checkpoint is only executed when the Checkpoint command is executed or when the database is properly shut down.
So, checkpoint process; checks and updates the headings of the data files. There are a number of disadvantages to whether checkpoint is frequent or rare;
Advantages or Disadvantages of Checkpoint Frequency
- If checkpoints occur frequently, files on the physical disk will be updated frequently, causing database slowness.
- If checkpoint occurs rare, in case of a sudden shutdown, the database recovery time will be take longer.
MMON(The Manageability Monitor)
The MMON process introduced with 10g for self-monitoring and tuning purposes.
Many statistics about performance and activity are collected. These statistics are accumulated in the SGA. The MMON process writes these statistics in the SGA to the Data Dictionary each time. So we can perform performance tuning and reporting using these statistics in Data Dictionary. By default, these statistical data are stored in the dictionary for 8 days.
MMON runs the Automatic Database Diagnostic Monitor (ADDM) to perform analysis and performance recommendations on this aggregate data.
MMON periodically checks the instance and database within the statistics collection period and reports if there is an alert.
These alerts can be configured by the DBA.
MMNL(The Manageability Monitor Light)
MMNL is the assistant of MMON.
If the area used to aggregate statistics in the SGA expires before the MMON passes these statistics to the Data Dictionary, then MMNL will do this instead of MMON.
MMAN(The Memory Manager)
The MMAN process instroduced with 10g. Its responsible for Automatic Memory Management operations.
With 10g, it can automatically adjust the memory in the SGA.
With 11g, it can automatically adjust the memory for both SGA and PGA.
For the moment I think it would be useful to automatically manage the memory in the SGA, but I think the PGA should be set manually.
ARCn(The Archiver)
It is a background process that is enable if the database is being used in archivelog mode.
Its responsible for copying redo log files to archive log files. This way LGWR can start to rewrite the redo log files.
If a standby database is used, it also manages the process of sending redo records from the primary database to the standby database.
The ARCn service should be properly monitored using the alert system. If archiving fails, the database will hang.
RECO(The Recoverer Process)
Two phase commit occurs in Distributed Transaction.
So imagine that you update both a local table and a remote table in a transaction. In the first phase of the commit, the LGWR of each database flushes the redo log buffer to the redo log files. In the second phase, these transactions are marked as committed in the redo log files. If any of these processes fail, the RECO process rolls back the transaction in all databases. In this way, the consistency of the data is guaranteed.
Conclusion
There are many background processes for many operations in Oracle, such as running jobs, managing deadlocks, and so on.
In this article I listed most important background processes from my point of view.
List Oracle Processes
You can see other processes with the following query.
1 | select program from v$process order by program; |