Archive for the ‘Process Architecture’ Category

Oracle Database Architecture


Database Architecture                   

An Oracle database server consists of an Oracle database and an Oracle instance.

Oracle Database architecture is based on physical and logical structures. 


Oracle Database Physical Structure

Oracle database consists of physical files to store information. Basically there are three types of physical files required for database:

Core Files:

  • Data Files                    Contains all the data of database
  • Control Files              Contains data about database(metadata)
  • Redo Log Files          Contains all changes made to data, allows recovery

Other Files

  • Parameter File         Defines how instance will be configured when it starts up
  • Password File           Allow users to connect remotely to the database
  • Archive Log Files   Contains history of redo log files
  • Trace Files                 Contains messages written by server and background process
  • Alert File                    Special trace file, chronological log of messages and errors              



Oracle Database Logical Structure

Oracle database is divided into logical storage units known as Tablespaces, which can be used to group related logical structures together. Logical structure mainly consists of following structures starting with the bigger unit and going down to the smallest.


Each database is logically divided into one or more tablespaces. One or more datafiles are explicitly created for each tablespace to physically store the data of all logical structures in a tablespace. The combined size of the datafiles in a tablespace is the total storage capacity of the tablespace.


Tablespace consists of one or more segments. A segment is a set of extents allocated for a certain logical structure stored in the same tablespace. The segments can be of one of following types:

  • Data segment  (stores user data within the database)
  • Index Segment (store Indexes)
  • Temporary Segment ( stores rollback segment)
  • Rollback/Undo segment (created when sql statement needs a temporary work area)


An extent is a specific number of contiguous data blocks, obtained in a single allocation, used to store a specific type of information in one data file.

Data Blocks

Oracle database data is stored in data blocks(at the lowest logical level).  One data block corresponds to a specific number of bytes of physical database space on disk(default 8K). The standard block size is specified by the DB_BLOCK_SIZE initialization parameter.

Oracle Data blocks are the smallest unit of I/O in the database and they are mapped to OS block on the storage device




Oracle Instance

Oracle instance is comprised of memory structures and background processes that are required to perform different tasks to keep the instance and database running. Instance does not exist until database is started, which involves reading initialization parameter file and configuring instance according to the information within the parameter file.

Memory Structures

Instance has two major memory structures:

Background Processes

Oracle creates a set of background processes for an instance that  manage the memory structures, asynchronously perform I/O to write data to disk, and do general housekeeping.
The most common background processes are the following:

  • System monitor (SMON): Performs crash recovery when the instance starts after a failure
  • Process monitor (PMON): Performs process cleanup when a user process fails
  • Database writer (DBWn): Writes modified blocks from the database buffer cache to the files on disk
  • Checkpoint (CKPT): Signals DBWn at checkpoints and updates all of the data files and control files of the database to indicate the most recent checkpoint
  • Log writer (LGWR): Writes redo log entries to disk
  • Archiver (ARCn): Copies the redo log files to archival storage when the log files are full or a log switch occurs


Background Processes

Background Processes

The Oracle architecture has five mandatory background processes that are discussed further in this lesson. In addition to the mandatory list, Oracle has many optional background process that are started when their option is being used. These optional processes are not within the scope of this course, with the exception of the background process, ARCn. Following is a list of some optional background processes:

  • RECO: Recoverer
  • QMNn: Advanced Queuing
  • ARCn: Archiver
  • LCKn: RAC Lock Manager–Instance Locks
  • LMON: RAC DLM Monitor–Global Locks
  • LMDn: RAC DLM Monitor–Remote Locks
  • CJQ0: Coordinator Job Queue background process
  • Dnnn: Dispatcher
  • Snnn: Shared Server
  • Pnnn: Parallel Query Slaves

 Database Writer (DBWn)

The server process records changes to undo and data blocks in the Database Buffer Cache. DBWn writes the dirty buffers from the Database Buffer Cache to the datafiles. It ensures that a sufficient number of free buffers (buffers that can be overwritten when server processes need to read in blocks from the datafiles) are available in the Database Buffer Cache. Database performance is improved because server processes make changes only in the Database Buffer Cache.

DBWn defers writing to the datafiles 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 find any
  • Timeout occurs
  • A ping request in Real Application Clusters (RAC) environment
  • Placing a normal or temporary tablespace offline
  • Placing a tablespace in read-only mode
  • Dropping or truncating a table

Log Writer (LGWR)

LGWR performs sequential writes from the Redo Log Buffer to the redo log file under the following situations:

  • When a transaction commits
  • When the Redo Log Buffer is one-third full
  • When there is more than 1 MB of changes recorded in the Redo Log Buffer
  • Before DBWn writes modified blocks in the Database Buffer Cache to the datafiles
  • Every three seconds

Because the redo is needed for recovery, LGWR confirms the commit operation only after the redo is written to disk.

LGWR can also call on DBWn to write to the datafiles.

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 datafiles 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 datafiles.
  • It deallocates temporary segments to return them as free space in datafiles.

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)

A data structure that defines an SCN in the redo thread of a database. Checkpoints are recorded in the control file and each datafile header, and are a crucial element of recovery.

Every three seconds the CKPT process stores data in the control file to identify that place in the redo log file where recovery is to begin, this being called a checkpoint. The purpose of a checkpoint is to ensure that all of the buffers in the Database Buffer Cache that were modified prior to a point in time have been written to the datafiles. This point in time (called the checkpoint position) is where database recovery is to begin in the event of an instance failure. DBWn will already have written all of the buffers in the Database Buffer Cache that were modified prior to that point in time. Prior to Oracle9i, this was done at the end of the redo log. In the event of a log switch CKPT also writes this checkpoint information to the headers of the datafiles.

A checkpoint is the highest system change number (SCN) such that all data blocks less than or equal to that SCN are known to be written out to the data files. If a failure occurs, then only the redo records containing changes at SCNs higher than the checkpoint need to be applied during recovery.

A checkpoint performs the following three operations:

  1. Every dirty block in the buffer cache is written to the data files. That is, it synchronizes the data blocks in the buffer cache with the datafiles on disk.
    It’s the DBWR that writes all modified database blocks back to the datafiles.
  2. The latest SCN is written (updated) into the datafile header.
  3. The latest SCN is also written to the controlfiles.

When checkpoint occurs?

Checkpoint occurs

1) At redo log switch




Checkpoint information written by CKPT includes checkpoint position, system change number, location in the redo log to begin recovery, information about logs, and so on.

Note: CKPT does not write data blocks to disk or redo blocks to the online redo logs.

Archiver (ARCn)

All other background processes are optional, depending on the configuration of the database; however, one of them, ARCn, is crucial to recovering a database after the loss of a disk. As online redo log files get filled, the Oracle server begins writing to the next online redo log file. The process of switching from one redo log to another is called a log switch. The ARCn process initiates backing up, or archiving, of the filled log group at every log switch. It automatically archives the online redo log before the log can be reused, so that all of the changes made to the database are preserved. This enables the DBA to recover the database to the point of failure even if a disk drive is damaged.

Archiving redo log files:

One of the important decisions that a DBA has to make is whether to configure the database to operate in ARCHIVELOG or in NOARCHIVELOG mode.

NOARCHIVELOG mode: In NOARCHIVELOG mode, the online redo log files are overwritten each time a log switch occurs. LGWR does not overwrite a redo log group until the checkpoint for that group is complete. This ensures that committed data can be recovered if there is an instance crash. During the instance crash, only the SGA is lost. There is no loss of disks, only memory. For example, an operating system crash causes an instance crash.

Connections and Sessions

Establishing a Connection

Before users can submit SQL statements to an Oracle database, they must connect to an instance.

  • The user starts a tool such as SQL*Plus or runs an application developed using a tool such as Oracle Forms. This application or tool is executed as a user process.
  • In the most basic configuration, when a user logs on to the Oracle server, a process is created on the computer running the Oracle server. This process is called a server process. The server process communicates with the Oracle Instance on behalf of the user process that runs on the client. The server process executes SQL statements on behalf of the user.


A connection is a communication pathway between a user process and an Oracle server. A database user can connect to an Oracle server in one of three ways:

  • The user logs on to the operating system running the Oracle Instance and starts an application or tool that accesses the database on that system. The communication pathway is established using the interprocess communication mechanisms available on the host operating system.
  • The user starts the application or tool on a local computer and connects over a network to the computer running the Oracle Instance. In this configuration, called client-server, network software is used to communicate between the user and the Oracle server.
  • In a three-tiered connection, the user’s computer communicates over the network to an application or a network server, which is connected through a network to the machine running the Oracle Instance. For example, the user runs a browser on a computer on a network to use an application residing on an NT server that retrieves data from an Oracle database running on a UNIX host.


A session is a specific connection of a user to an Oracle server. The session starts when the user is validated by the Oracle server, and it ends when the user logs out or when there is an abnormal termination. For a given database user, many concurrent sessions are possible if the user logs on from many tools, applications, or terminals at the same time. Except for some specialized database administration tools, starting a database session requires that the Oracle server be available for use.

Note: The type of connection explained here, where there is a one-to-one correspondence between a user and server process, is called a Dedicated Server connection. When using a Shared Server configuration, it is possible for multiple user processes to share server processes.


User Process

A database user who needs to request information from the database must first make a connection with the Oracle server. The connection is requested using a database interface tool, such as SQL*Plus, and beginning the user process. The user process does not interact directly with the Oracle server. Rather it generates calls through the user program interface (UPI), which creates a session and starts a server process.

Server Process

Once a user has established a connection, a server process is started to handle the user processes requests. A server process can be either a Dedicated Server process or a Shared Server process. In a Dedicated Server environment, the server process handles the request of a single user process. Once a user process disconnects, the server process is terminated. In a Shared Server environment, the server process handles the request of several user processes. The server process communicates with the Oracle server using the Oracle Program Interface (OPI).