Lesson 1

Oracle Instance Architecture

This module explains the moving parts that make a running Oracle database work. After completing it, you will be able to:

Explain the difference between a database and an instance.
Identify major background processes and what they do.
List an instance’s processes and memory components using quick checks.
Recognize modern memory settings and avoid legacy parameters.

Instance vs. Database

An instance is a set of memory structures and background processes (SGA + background tasks). A database is the set of physical files (datafiles, control files, redo logs, etc.). Clients talk to the instance; the instance reads/writes the database files.

CDB/PDB note (19c+): In multitenant, one instance opens a Container Database (CDB) that can host multiple Pluggable Databases (PDBs). Background processes still belong to the instance.
RAC: Multiple instances on different servers can open and access the same database concurrently.

Memory Architecture at a Glance

The instance allocates the System Global Area (SGA) and each server process uses its own Program Global Area (PGA).

Oracle SGA: 1) Buffer cache, 2) SGA overview, 3) Shared pool (library & data dictionary caches) — Key SGA components: 1) Buffer cache, 2) Overall SGA, 3) Shared pool (library & dictionary caches).

Database Buffer Cache - caches data blocks to reduce physical I/O.
Shared Pool - includes the library cache (parsed SQL, PL/SQL, cursors) and data dictionary cache (object/user metadata).
Redo Log Buffer - holds change vectors until LGWR flushes them to online redo.
Other SGA areas (as configured): Large Pool (parallel/backup), Java Pool, Streams Pool, In-Memory Column Store (if enabled).

Background Processes (core set)

LGWR - writes redo from redo buffer to online redo logs; commits are durable after this flush.
DBWn - writes dirty buffers to datafiles.
CKPT - signals checkpoints; updates control file/datafile headers.
SMON - instance recovery and space maintenance.
PMON - process monitor; cleans up failed sessions.
ARCn - copies full online redo logs to archive destinations (ARCHIVELOG mode).
MMON/MMNL - AWR/ADDM snapshots and advisories.

How work flows (simplified)

Server process parses SQL (shared pool), reads needed blocks (buffer cache).
Changes are made in memory; redo is generated in the redo buffer.
COMMIT → LGWR flushes redo; commit completes.
Dirty buffers are written later by DBWn; checkpoints bound recovery work.

Oracle 19c DBA on AWS

Quick checks you’ll use all the time

Which instance and database?


-- Instance identity and status
SELECT instance_name, host_name, version, status
FROM   v$instance;

-- Database identity
SELECT name, dbid, open_mode, database_role
FROM   v$database;

Background processes (concise list)


-- Background process names (non-null, running)
SELECT pname
FROM   v$bgprocess
WHERE  paddr IS NOT NULL
ORDER  BY pname;

SGA components (current sizes)


SELECT component,
       ROUND(current_size/1024/1024) AS size_mb
FROM   v$sga_dynamic_components
WHERE  current_size > 0
ORDER  BY component;

From the OS (Linux):


ps -ef | grep -E "ora_.*|pmon|smon|lgwr|dbw" | grep -v grep

11gR2 vs. 19c (what actually changed for instances)

Same fundamentals: SGA + background processes; redo-first (WAL) commit model.
Additions in newer releases: multitenant (CDB/PDB), optional In-Memory Column Store, more specialized background tasks to support new features and manageability.

Modern memory management (skip legacy knobs)

Avoid deprecated parameters like db_block_buffers. Use automatic memory features instead.
AMM: MEMORY_TARGET/MEMORY_MAX_TARGET (single knob for SGA+PGA, where supported).
ASMM: SGA_TARGET/ SGA_MAX_SIZE with DB_CACHE_SIZE, etc., and PGA_AGGREGATE_TARGET for PGA.

Practice prompts

Try these in your lab:


-- Show key memory targets
SHOW PARAMETER memory_target
SHOW PARAMETER sga_target
SHOW PARAMETER pga_aggregate_target

-- See redo buffer size
SHOW PARAMETER log_buffer