Oracle Database Architecture

Understanding Oracle Database architecture is essential for effective database administration, performance tuning, and troubleshooting. Oracle separates the instance (memory + processes) from the database (physical files), and this distinction is central to how everything works.

---

Instance vs Database

Component	What It Is
Instance	Memory structures (SGA) + background processes — exists in RAM and CPU
Database	Physical files on disk — datafiles, redo logs, control files, archived logs

An instance can only open one database at a time (in traditional architecture). Multiple instances can open the same database simultaneously using Real Application Clusters (RAC).

---

System Global Area (SGA)

The SGA is a shared memory region that all server and background processes access. Key components:

Database Buffer Cache

• Caches data blocks read from disk
• Uses an LRU (Least Recently Used) algorithm to manage memory
• Larger buffer cache = fewer physical disk reads = better performance

Shared Pool

• Library Cache — stores parsed SQL statements and execution plans
• Data Dictionary Cache — caches metadata (table definitions, user privileges)
• Reduces the cost of re-parsing identical SQL statements

Redo Log Buffer

• Temporarily stores redo entries before they are written to online redo log files
• Records all changes made to the database for recovery purposes

Large Pool

• Optional memory area for:
  • RMAN backup/restore operations
  • Shared server session memory
  • Parallel query message buffers

Java Pool

• Memory for Java stored procedures and the Oracle JVM

Streams Pool

• Memory for Oracle Streams, Advanced Queuing, and GoldenGate capture/apply

---

Program Global Area (PGA)

The PGA is private memory allocated to each server process:

• Sort Area — used for ORDER BY, GROUP BY, and hash joins
• Session Memory — variables, login information, session state
• SQL Work Area — memory for complex operations like hash joins and bitmap merges

PGA is not shared — each connection gets its own PGA.

---

Background Processes

Oracle uses several background processes that run automatically:

Process	Name	Purpose
DBWn	Database Writer	Writes dirty buffers from the buffer cache to datafiles
LGWR	Log Writer	Writes redo log entries from the redo log buffer to online redo log files
CKPT	Checkpoint	Signals DBWn to write and updates datafile headers with checkpoint information
SMON	System Monitor	Performs instance recovery on startup; coalesces free space
PMON	Process Monitor	Cleans up failed user processes; releases locks and resources
ARCn	Archiver	Copies filled online redo log files to the archive log destination
MMON	Manageability Monitor	Collects statistics for AWR snapshots
MMAN	Memory Manager	Manages automatic memory allocation (AMM/ASMM)
RECO	Recoverer	Resolves distributed transaction failures

---

Physical Storage Structure

Datafiles