Master Oracle Database Architecture: Instance, SGA & Key Background Processes

Overall Structure

Oracle's database server consists of two main parts: the instance (the upper half) and the database (the lower half). Users do not connect directly to the database; a user process talks to a server process, which interacts with the instance, which finally accesses the underlying database.

Instance Memory (SGA) Components

The instance is composed of SGA (System Global Area) and background processes. Memory in Oracle is divided into SGA and PGA . The SGA includes several caches and pools:

Database Buffer Cache : stores copies of data blocks read from data files, improving performance by allowing repeated access without additional I/O. It can be resized dynamically and managed automatically.

Redo Log Buffer : records all changes to database blocks before they are written to disk, enabling recovery. Its size is controlled by the log_buffer parameter.

Shared Pool :

Library Cache : holds recently used SQL and PL/SQL statements to avoid hard parses.

Data Dictionary Cache : stores definitions of tables, indexes, columns, users, and other objects.

Large Pool (optional): provides memory for shared server sessions, reducing pressure on the shared pool; size can be adjusted automatically.

Java Pool (optional): memory for Java execution within the database, also dynamically sized.

Stream Pool (optional): supports stream-related data, enhancing cache efficiency.

Background Processes

The instance runs several essential background processes:

PMON (Process Monitor) : cleans up after failed user sessions, rolls back transactions, releases locks, and deregisters resources.

SMON (System Monitor) : performs instance recovery (roll‑forward redo logs, rollback of uncommitted transactions) and releases temporary tablespaces.

DBWn (Database Writer) : writes dirty blocks from the database buffer cache to data files. It triggers on checkpoint events, when the cache reaches a threshold (e.g., 1/4 full), or when no free buffers are available.

LGWR (Log Writer) : flushes the redo log buffer to online redo log files. It writes on commit, when the buffer is 1/3 full, reaches 1 MB, every three seconds, or before DBWn writes data.

CKPT (Checkpoint Process) : signals DBWn to write dirty blocks, updates the SCN in data file headers and control files, and runs roughly every three seconds. Faster checkpoints reduce recovery time but increase I/O load.

ARCn (Archiver) : copies online redo log files to archive log files when the database is in ARCHIVELOG mode. Redo logs are organized in two rotating groups; if one group fills before being archived, the database can stall.