Understanding Oracle Database Architecture: From Instances to Multitenancy

Overview

Oracle Database is a flagship relational database system that underpins a large portion of Oracle's revenue and is widely used in on‑premises, cloud, and hybrid environments.

Physical Storage Structure

Data files : store all user data; a database can consist of multiple data files.

Control files : small binary files that describe the physical structure of the database; loss of these files renders the database unusable.

Redo log files : record every change for recovery and backup; a log group may contain up to five files.

Additional files include parameter files, backup files, archived redo logs, and trace logs.

Logical Storage Structure

Tablespaces : the largest logical unit, mapping to one or more data files.

Segments : storage allocated for a specific type of data (data, index, temporary, rollback).

Extents : groups of contiguous data blocks within a segment.

Data blocks : the smallest unit of storage, containing a header, row directory, and free space.

Instance Structure

Process structure (background processes):

DBWn (Database Writer): writes modified buffers to data files.

LGWR (Log Writer): flushes the redo log buffer to redo log files.

CKPT (Checkpoint): creates checkpoints to shorten recovery time.

SMON (System Monitor): performs crash recovery and cleans temporary segments.

PMON (Process Monitor): cleans up failed user processes.

ARCn (Archive Process): copies filled redo logs to archive storage when archiving is enabled.

RECO (Recovery): handles distributed transaction recovery.

Memory structure :

System Global Area (SGA) : shared memory that includes the data buffer cache, redo log buffer, shared pool, large pool, and Java pool.

Program Global Area (PGA) : private memory for each user process.

Evolution of Oracle Database

Pre‑9i

Focus on high availability, performance, manageability, security, and data‑service capabilities.

9i‑12c (Real Application Clusters)

Introduction of Real Application Clusters (RAC) enabled grid computing, providing 24×7 availability, load balancing, and fault tolerance across multiple nodes.

12c and Beyond (Multitenant Architecture)

Oracle 12c introduced the multitenant architecture with a Container Database (CDB) that can host multiple Pluggable Databases (PDBs), allowing a single instance to serve many databases. This paved the way for cloud‑native deployments and Database‑as‑a‑Service (DBaaS).

Oracle Database 18c Highlights

Multitenant architecture for cost savings and flexibility.

In‑memory column store for real‑time analytics.

Native sharding for high availability of large‑scale web applications.

Enhanced performance, availability, security, and development capabilities.

Relational vs. Non‑Relational Databases

Relational databases use the relational model (tables and relationships) and must satisfy ACID properties (Atomicity, Consistency, Isolation, Durability). Non‑relational (NoSQL) databases sacrifice strict ACID compliance for scalability, flexible schemas, and high‑throughput workloads (key‑value, document, column‑family, graph).

SQL and Database Interaction

SQL (Structured Query Language) is the standard language for defining, querying, and manipulating relational data. It was introduced in 1974 by Boyce and Chamberlin and remains the primary interface for Oracle and other relational DBMSs.