Friday , November 8 2024

Oracle Background Processes

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.

Or it can be seen by querying v$process view.

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.

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

  1. 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.
  2. If there are too many Dirty Buffers, the Database Writer is triggered even if there is no other reason.
  3. DBWn is triggered every 3 seconds even if the problems in the above two items do not occur.
  4. If a tablespace is set to OFFLINE,
  5. If a tablespace is set to read-only.
  6. If a table is dropped,
  7. If a table is truncated,
  8. If a tablespace is set to BACKUP mode,
  9. If a Checkpoint operation is performed,
  10. 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.

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.

Loading

About dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *