knowledge-base/ORACLE.en.md

🏛️ Oracle Database

Overview

Oracle Database is a proprietary relational database with the broadest range of enterprise features — RAC clustering, Active Data Guard, partitioning, advanced compression, in-memory options, and Oracle Exadata integration. Dominant in the corporate world, finance, telecommunications, and mainframe ecosystem.

Architecture

Oracle instance + database

Oracle Instance (memory + processes)
  ├── System Global Area (SGA)
  │   ├── Database Buffer Cache
  │   ├── Shared Pool (library cache, dictionary cache)
  │   ├── Redo Log Buffer
  │   ├── Java Pool
  │   ├── Large Pool (backup, parallel)
  │   └── In-Memory Column Store (option)
  ├── Program Global Area (PGA) — per session
  └── Background processes
      ├── PMON (process monitor)
      ├── SMON (system monitor)
      ├── DBWn (database writer)
      ├── LGWR (log writer)
      ├── CKPT (checkpoint)
      ├── ARCn (archiver)
      └── MMON (manageability monitor)

Multitenant architecture (12c+)

Container Database (CDB)
  ├── Root (CDB$ROOT) — metadata, system objects
  ├── Seed (PDB$SEED) — template for new PDBs
  ├── Pluggable Database 1 (PDB1) — application A
  ├── Pluggable Database 2 (PDB2) — application B
  └── Pluggable Database 3 (PDB3) — application C

Each PDB looks like a separate database but shares SGA and background processes. Advantage: higher density, simpler patching (CDB level), resource management per PDB.

Oracle RAC (Real Application Clusters)

Multi-instance architecture — multiple servers access the same storage:

Node 1 ─── Oracle ASM ─── Shared Storage (SAN/NFS)
Node 2 ─── Oracle ASM ─── Shared Storage (SAN/NFS)
Node 3 ─── Oracle ASM ─── Shared Storage (SAN/NFS)
              │
        Cache Fusion (private interconnect)

• Up to 64 nodes in a cluster
• Cache Fusion — transfer of dirty blocks between instances via private interconnect (RAC-specific)
• ASM (Automatic Storage Management) — clustered filesystem + volume manager
• Service — workload routing (primary, report, batch)

Oracle Data Guard

Mode	Protection	Latency	Use case
Maximum Protection	Zero data loss (sync)	Highest	Critical systems
Maximum Availability	Zero data loss (sync, fallback to async)	High	Enterprise standard
Maximum Performance	Async	Lowest	Remote DR

• Active Data Guard — standby for reads (reporting, backup) — requires license
• Far Sync — synchronous write to remote standby via async (compromise)

Oracle Exadata

Hardware+software platform for Oracle DB:

Component	Description
Database Servers	x86 (Xeon), 2-8× per rack, NVMe, 1.5-6 TB RAM
Storage Servers	Total capacity up to 2.7 PB raw per rack
Smart Scan	Predicate filtering at the storage layer (instead of DB server)
Smart Flash Cache	Multiple caching layers (RAM, Flash, disk)
RDMA over Converged Ethernet	Low latency between DB and storage servers

Suitable for: largest OLTP, data warehousing, consolidation.

Key enterprise features

Feature	Description	Competition
RAC	Shared-everything cluster up to 64 nodes	MSSQL AlwaysOn FCI (2 nodes)
Active Data Guard	Standby for reads, far sync, automatic failover	MSSQL AlwaysOn AG, PostgreSQL streaming
Partitioning	Range, List, Hash, Composite, interval, reference	PostgreSQL (declarative partitioning 10+)
Advanced Compression	Columnar, HCC (Exadata), OLTP compression	InnoDB page compression, PG TOAST
In-Memory	Column store in SGA for real-time analytics	PG (no native), MSSQL (columnstore index)
Advanced Security	TDE, data redaction, VPD, audit vault, database firewall	PG (pgcrypto, pgaudit), MSSQL (TDE, Always Encrypted)
Flashback	Querying historical data (Flashback Query, Table, Database)	PG (temporal tables via extension), MSSQL (system-versioned)
Sharding	System-managed, composite, user-defined	MongoDB (native), Vitess (MySQL), Citus (PG)
ASM	Clustered filesystem + volume manager	VMware VMFS, Windows CSV

Oracle licensing detail

Editions

Edition	Metric	Price (indicative)	Limitations
Oracle Database Standard Edition 2 (SE2)	Per core (core factor 0.5)	~$17,500/core	Max 16 CPU threads (per server), max 2 sockets, no RAC (only Oracle RAC One), no partitioning, in-memory, compression
Oracle Database Enterprise Edition (EE)	Per core (core factor 0.5)	~$47,500/core	No limits, all features (RAC, partitioning, in-memory, compression, Advanced Security) — but all as optional licenses
Oracle Database Enterprise Edition (RAC)	Per core (EE + RAC option)	~$47,500 + $23,000/core	EE + RAC clustering

Optional licenses (options) — EE only

Option	Price (indicative / core)	Use case
Real Application Clusters (RAC)	~$23,000	Multi-instance cluster
Active Data Guard	~$10,000	Standby for reads, far sync, automatic failover
Partitioning	~$11,500	Range, list, hash, interval, reference, system
Advanced Compression	~$11,500	OLTP compression, HCC (Exadata), JSON compression
Advanced Security	~$15,000	TDE, data redaction, database firewall
In-Memory Database	~$23,000	Column store in SGA for real-time analytics
Database Vault	~$5,750	Separation of duties, multi-tenancy security
Multitenant (EE)	Free (since 21c)	CDB/PDB — max 3 PDB per CDB in EE without license. Unlimited with Multitenant option (~$17,500)
Spatial / Graph	~$5,750	Geospatial data, property graph
Label Security	~$5,750	Row-level security with classifications

Oracle Cloud (OCI) licensing

Service	Model	Price	Note
OCI Base Database (RDS-like)	BYOL or License Included	~$1-5/hour (BYOL cheaper)	Single instance or RAC, automatic backup, patching
OCI Exadata Database Service	BYOL or License Included	~$5-30/hour (depending on shape)	Exadata X9M/X10M in OCI, elastic, full Exadata features
OCI Autonomous Database	Per CPU (ECPU)	~$0.50-3.00/ECPU/hour	Auto-tuning, auto-scaling, auto-patching
BYOL (Bring Your Own License)	Own Oracle license in OCI	Infrastructure only	Can use existing perpetual license, including support

RAC sizing — license cost

4-node RAC, each node 2× EPYC 9654 (96C) = 192 cores per node
  Core factor 0.5 → 96 Oracle licenses per node
  4 × 96 = 384 Oracle EE licenses

EE: 384 × $47,500 = $18,240,000
RAC option: 384 × $23,000 = $8,832,000
Support 22% annually: ($18,240,000 + $8,832,000) × 0.22 = $5,955,840/year

Tip: For RAC, consider smaller CPUs (e.g., 64C instead of 96C) — license cost often exceeds hardware cost.

Oracle vs PostgreSQL — comparison

Area	Oracle	PostgreSQL
License	Proprietary (per core, ~$17.5k-47.5k/core + 22% support annually)	Open source (PostgreSQL license, MIT-like)
RAC clustering	Native, shared-everything	None (Citus = shared-nothing)
Multitenant	CDB/PDB architecture	None (schemas per tenant)
Parallel execution	Mature (auto DOP, parallel index scan)	Good (parallel seq/index scan, join)
Storage management	ASM (integrated)	OS volume / LVM
Materialized views	With refresh on commit, query rewrite	No query rewrite
Partitioning	40+ options (interval, referential, system)	Declarative (range, list, hash since 10+)
In-memory	Columnar in SGA	Not native
Standby usage	Active Data Guard (read-only, license)	Hot standby (read-only, free)
Cloud	OCI (Oracle Cloud), AWS RDS, Azure	All clouds (native)

Recommendations — where Oracle is better

Area	Oracle	Competition	Why Oracle
License cost (4-node RAC, 384 cores)	~$50M (1st year incl. support)	PostgreSQL: $0	Oracle: 22% support annually on license fee
Vendor lock-in	High (GoldenGate migration difficult, PL/SQL specific)	PostgreSQL: none	MySQL and PG have migration tools from Oracle (ora2pg, AWS DMS)
Enterprise OLTP	RAC + ASM, zero-downtime patching	MSSQL (FCI limit 2 nodes)	Shared-everything cluster, transparent failover
Finance / Banking	Audit Vault, Database Vault, TDE, VPD	PG (pgaudit, row-level security)	Compliance certifications (SOX, PCI, GDPR)
Consolidation	Multitenant (CDB/PDB) — hundreds of DBs on 1 instance	PG (citizen schemas)	Lower overhead, simpler management
Data Warehouse	Exadata Smart Scan, parallel execution, in-memory	ClickHouse (specialized)	Hybrid workload (OLTP + DW in one DB)
High-end hardware	Exadata engineered system	PG (runs on anything)	Full-stack HW+SW optimization
Partitioning	Range of options (reference, interval, system)	PG (basic)	10+ years lead in implementation
Flashback / recovery	Flashback Database, Table, Query — any point in time	PG (PITR, point-in-time)	Faster, more granular recovery
Ecosystem	OEM, Data Pump, SQL Developer, Toad, GoldenGate	PG (pgAdmin, pg_dump, Patroni)	Decades of enterprise tooling

When to use Oracle

• Critical OLTP systems — banking, payment processing, trading
• Enterprise consolidation — hundreds of DBs on one RAC cluster (multitenant)
• Regulated environments — finance, healthcare, government (audit, security, compliance)
• Oracle ecosystem — E-Business Suite, PeopleSoft, Siebel, JD Edwards
• Exadata customers — maximum performance for hybrid workload (OLTP + DW)
• GoldenGate replication — heterogeneous replication (Oracle → Kafka, Oracle → PostgreSQL)
• Cloud migration — OCI, AWS RDS for Oracle, Azure Oracle Database Service

When to use something else

• Startup / SME → PostgreSQL (free, sufficient performance, no vendor lock-in)
• Web / LAMP stack → MySQL (simpler, cheaper, broad support)
• Cloud-native → Aurora, CockroachDB (architecture for cloud, not port of on-prem to cloud)
• Need only SQL → PostgreSQL (Oracle overhead not worth it)
• Horizontal write scaling → Cassandra (RAC scales reads, writes go through one node)

Sources

References, books, and standards: sources/databases/sources.en.md

Recommended reading

Book	Authors	ISBN	Description
Oracle Database 23ai New Features	Oracle Corporation	—	Official guide to new features — AI Vector Search, JSON Relational Duality, property graphs, schema privileges
Expert Oracle Architecture (3rd ed.)	Thomas Kyte, Darl Kuhn	978-1484249602	Comprehensive explanation of Oracle architecture — from storage to RAC and Data Guard

Last revision: 2026-06-03