Oracle Database Architecture & Components: Memory, Processes, Storage & CDB/PDB

Core Concepts: Oracle’s Three-Layer Architecture

Oracle’s architecture follows a three-layer model (logical, physical, and memory) to separate concerns and optimize performance. Below is the breakdown:

1. Logical Layer: The Database and Instance

classDiagram
    class Database {
        +Datafiles (physical storage)
        +Control files (metadata)
        +Redo logs (transaction journal)
    }
    class Instance {
        +SGA (Shared Global Area)
        +PGA (Program Global Area)
        +Background processes
    }
    Database "1" --> "1" Instance : Manages
    Instance "1" --> "1..*" Database : Hosts

• Database: A collection of schemas, tables, and objects stored on disk.
• Instance: A runtime environment (memory + processes) that accesses the database.
  • Key Idea: An instance hosts a database, but a database can have multiple instances (e.g., for high availability).

2. Memory Structure: SGA vs. PGA

Oracle uses two primary memory areas:

Shared Global Area (SGA)

• Buffer Cache: Stores frequently accessed data blocks (reduces disk I/O).
• Redo Log Buffer: Holds uncommitted transactions before writing to redo logs.
• Shared Pool: Stores SQL query plans, metadata, and shared cursors.
• Large Pool: Used for backup/restore operations (e.g., RMAN).

Worked Example: Ncell Billing System

• Scenario: During peak hours (e.g., 6–9 PM), Ncell’s Oracle database handles 10,000+ transactions/sec.
• Problem: Frequent disk reads slow down the system.
• Solution: Increase the buffer cache size in SGA to cache hot data (e.g., user balances, call records).

  ALTER SYSTEM SET db_block_buffers = 20000; -- Adjust based on available RAM
  

Program Global Area (PGA)

• Purpose: Private memory for each server process (e.g., SQL execution, sorting).
• Key Difference from SGA:

  Feature	SGA	PGA
  Scope	Shared across all sessions	Private per process
  Usage	Caching, redo logs	Sorting, hash joins
  Size	Fixed (configured)	Dynamic (auto-tuned)
  Example	Buffer cache for user tables	Temp space for ORDER BY

Real-World Tie-In: eSewa Transactions

• When you pay a bill via eSewa, Oracle uses PGA to:
  1. Sort transactions by merchant ID (temporary sort area).
  2. Validate signatures (crypto operations in PGA memory).
  3. Release memory after completion.

3. Physical Layer: Storage Components

Oracle stores data in three critical files:

Datafiles

• Store actual database objects (tables, indexes).
• Example: SYSTEM01.dbf (contains system tables), USERS01.dbf (user data).
• Multiplexing: Copying datafiles to multiple disks for redundancy (e.g., DATA1 and DATA2).

Control Files

• Metadata about the database (e.g., datafile locations, redo log status).
• Multiplexing: Always keep at least 2 copies (e.g., CONTROL01.ctl, CONTROL02.ctl).

  CREATE CONTROLFILE REUSE DATABASE ORCL
  MAXLOGFILES 16
  MAXLOGMEMBERS 5
  MAXDATAFILES 100
  MAXINSTANCES 8
  MAXLOGHISTORY 1
  LOGFILE
    GROUP 1 ('/u01/app/oracle/oradata/ORCL/redo01.log') SIZE 100M,
    GROUP 2 ('/u01/app/oracle/oradata/ORCL/redo02.log') SIZE 100M;
  

Redo Logs

• Record all changes (DML/DDL) for crash recovery.
• Multiplexing: Write to at least 2 members per group (e.g., REDO01.log and REDO01_001.log).

  sequenceDiagram
    participant User as User Process
    participant SGA as SGA (Redo Log Buffer)
    participant Disk as Redo Log Files (Multiplexed)
    User->>SGA: Writes transaction (e.g., UPDATE balance)
    SGA->>Disk: Writes to GROUP 1 (REDO01.log)
    SGA->>Disk: Writes to GROUP 2 (REDO02.log)
    Disk-->>SGA: Acknowledges write

Why Multiplexing?

• Reliability: If one disk fails, the database continues using the copy.
• Performance: Parallel writes to multiple disks reduce I/O bottlenecks.

4. Process Structure

Oracle uses background and user processes:

Background Processes

User Processes

• Each client connection (e.g., SQL*Plus, application server) spawns a server process.
• Dedicated vs. Shared Server:
  • Dedicated: 1 process per user (high overhead).
  • Shared: Multiple users share processes (efficient for web apps like Daraz).

5. Multitenant Architecture: CDB and PDB

Introduced in Oracle 12c, this architecture isolates databases (Pluggable Databases, PDBs) within a Container Database (CDB).

Key Components

Worked Example: Bank Loan Processing

• Scenario: A bank uses Oracle Multitenant to separate:
  • CORPORATE_LOANS_PDB: For business loans (high-security).
  • PERSONAL_LOANS_PDB: For retail loans (lower security).
• Benefits:
  • Isolation: A breach in PERSONAL_LOANS_PDB doesn’t affect CORPORATE_LOANS_PDB.
  • Resource Control: Limit CPU/memory for PERSONAL_LOANS_PDB during peak hours.

Creating a CDB and PDB

-- Step 1: Create CDB (as SYSDBA)
CREATE DATABASE CDB_TEST
USER SYS IDENTIFIED BY password
USER SYSTEM IDENTIFIED BY password
LOGFILE GROUP 1 ('/u01/redo01.log') SIZE 100M,
GROUP 2 ('/u01/redo02.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/u01/system01.dbf' SIZE 1G AUTOEXTEND ON
SYSAUX DATAFILE '/u01/sysaux01.dbf' SIZE 1G AUTOEXTEND ON
DEFAULT TABLESPACE users
DATAFILE '/u01/users01.dbf' SIZE 1G AUTOEXTEND ON
DEFAULT TEMPORARY TABLESPACE temp
TEMPFILE '/u01/temp01.dbf' SIZE 500M AUTOEXTEND ON
UNDO TABLESPACE undotbs1
DATAFILE '/u01/undotbs01.dbf' SIZE 500M AUTOEXTEND ON;

-- Step 2: Create PDB
ALTER SESSION SET CONTAINER = CDB$ROOT;
CREATE PLUGGABLE DATABASE HR_PDB
ADMIN USER hr_admin IDENTIFIED BY password
FILE_NAME_CONVERT=('/u01/oradata/CDB_TEST/','/u01/oradata/HR_PDB/');

6. Network Configuration

Oracle uses Oracle Net Services to connect clients to the database. Key components:

TNS (Transparent Network Substrate)

• TNSNAMES.ORA: Maps aliases to database locations.

  HR_PDB =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = db-server)(PORT = 1521))
      (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = HR_PDB)
      )
    )
  

• Listener.ora: Configures which databases the listener accepts.

  LISTENER =
    (DESCRIPTION_LIST =
      (DESCRIPTION =
        (ADDRESS = (PROTOCOL = TCP)(HOST = db-server)(PORT = 1521))
      )
    )
  

Real-World Example: Pathao Driver App

• Scenario: When a driver logs into the Pathao app, it connects to Oracle via:
  1. TNS: Resolves PATHAO_DB to db.pathao.com:1521.
  2. Listener: Routes to the correct PDB (e.g., DRIVER_DATA_PDB).
  3. Service: Uses a dedicated service for driver queries.

7. Backup and Recovery Basics

Oracle’s recovery model relies on:

1. Redo Logs: For crash recovery (undoes uncommitted transactions).
2. Archived Logs: For point-in-time recovery (PITR).
3. RMAN (Recovery Manager): Tool for backup/restore.

RMAN Configuration Steps

-- Step 1: Enable archiving
ALTER DATABASE ARCHIVELOG;
ALTER SYSTEM SWITCH LOGFILE;

-- Step 2: Configure RMAN channels
RMAN> CONFIGURE CHANNEL 1 TYPE DISK FORMAT '/backup/%U';

-- Step 3: Take a full backup
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

Worked Example: NEPSE Stock Data Recovery

• Scenario: NEPSE’s Oracle database crashes during trading hours.
• Recovery Steps:
  1. Crash Recovery: SMON uses redo logs to roll back uncommitted trades.
  2. Media Recovery: If datafiles are corrupted, restore from RMAN backup:

     RMAN> RESTORE DATABASE;
     RMAN> RECOVER DATABASE;
     

In the Real World

1. eSewa (Nepal)

   • Idea Used: Multitenant Architecture (CDB/PDB)
   • How: eSewa’s Oracle database uses a CDB with multiple PDBs:
     • PAYMENTS_PDB: Handles transactions (high isolation).
     • USER_PROFILE_PDB: Manages customer data (lower security).
   • Benefit: Compliance with PCI-DSS (Payment Card Industry) by isolating sensitive data.

2. Ncell Billing System

   • Idea Used: SGA Tuning (Buffer Cache)
   • How: Ncell’s Oracle database caches frequently accessed tables (e.g., CUSTOMER_BALANCE) in the buffer cache to reduce disk I/O during peak call minutes (6–9 PM).
   • Impact: Reduces latency for prepaid balance checks by 40%.

3. Daraz Marketplace

   • Idea Used: Redo Log Multiplexing
   • How: Daraz’s Oracle database writes redo logs to three mirrored disks to ensure no data loss during high-order volumes (e.g., Dashain sales).
   • Result: Zero downtime during Black Friday promotions.

Exam Tip

1. SQL Commands: Always include full SQL syntax for creating CDBs/PDBs, configuring RMAN, or tuning SGA/PGA. Partial commands lose marks.

   • Example: Forgetting USER SYS IDENTIFIED BY in CREATE DATABASE costs 1 mark.

2. Diagrams: Draw layered architecture (logical/physical/memory) and CDB/PDB relationships in exams. Label all components (e.g., SGA subpools, background processes).

3. Real-World Scenarios: Link concepts to Nepali examples (e.g., Ncell’s buffer cache, eSewa’s PDBs). Examiners reward practical applications.

4. Multiplexing: Always state why multiplexing is used (e.g., "to prevent data loss if a disk fails") and how many copies are needed (minimum 2 for control files/redo logs).

5. Shortcuts:

   • SGA vs. PGA: Remember "SGA is shared, PGA is private."
   • CDB/PDB: "CDB is the container, PDBs are tenants inside it."