Oracle Database Storage Structures: Files, Memory, and Physical Layout

TAKEAWAYS:

• Oracle stores data in datafiles, redo logs, control files, and temporary tablespaces, each with distinct roles and multiplexing options for fault tolerance.
• The System Global Area (SGA) and Program Global Area (PGA) are memory structures that optimize query performance and parallel processing, respectively.
• Tablespaces logically group database objects (tables, indexes) and map to physical datafiles, allowing flexible storage management.
• Multiplexing (duplicating redo logs/control files) prevents data loss from hardware failures—critical for mission-critical systems like Ncell’s billing database.
• Undo segments and redo logs work together to enable point-in-time recovery, a feature used by banks to reverse fraudulent transactions.
• Automatic Storage Management (ASM) simplifies disk management by pooling storage resources, used by eSewa to scale during festival seasons.

1. Oracle Database Physical Storage: Files and Their Roles

Oracle stores all database data and metadata in physical files on disk. These files are organized into logical structures called tablespaces, which group related database objects (e.g., tables, indexes). Understanding these files is crucial for a DBA to manage storage, backups, and recovery.

Key Physical Files in Oracle

Oracle’s physical files and their relationships (datafiles → tablespaces → database). (Image: Scottie33, CC0, via Wikimedia Commons)

How Datafiles Work

• Each tablespace maps to one or more datafiles (e.g., users01.dbf, system01.dbf).
• Datafiles are not portable between databases (unlike tablespaces, which can be transported).
• Worked Example: NTC’s Traffic Route Database Suppose NTC stores traffic data in a tablespace called TRAFFIC_DATA with two datafiles:

  CREATE TABLESPACE traffic_data
    DATAFILE '/u01/app/oracle/oradata/NTC/traffic1.dbf' SIZE 10G AUTOEXTEND ON,
             '/u02/app/oracle/oradata/NTC/traffic2.dbf' SIZE 10G AUTOEXTEND ON
    EXTENT MANAGEMENT LOCAL;
  

  • Why two datafiles?
    • Performance: Spreads I/O across disks.
    • Reliability: If one disk fails, the other remains operational.
  • Multiplexing: Not applied here (datafiles are not critical for recovery like redo logs), but NTC could mirror these files for backup.

2. Memory Structures: SGA vs. PGA

Oracle uses two primary memory areas to process requests efficiently:

1. System Global Area (SGA): Shared memory for all database sessions.
2. Program Global Area (PGA): Private memory for each server process.

SGA: Shared Memory for the Entire Database

The SGA is divided into subcomponents that cache data and control structures to speed up queries. Its size directly impacts performance.

graph TD
    A["SGA"] --> B["Buffer Cache"]
    A --> C["Redo Log Buffer"]
    A --> D["Shared Pool"]
    A --> E["Large Pool (Optional)"]
    A --> F["Java Pool (Optional)"]
    B --> B1["Holds blocks read from datafiles"]
    C --> C1["Stores redo entries before writing to redo logs"]
    D --> D1["Holds parsed SQL, execution plans, and data dictionary"]

Key SGA Components:

How SGA Improves Performance:

• Buffer Cache Hit Ratio: If a query reuses data from the cache (hit), it avoids disk I/O.

  -- Check buffer cache efficiency
  SELECT name, value FROM v$sysstat
  WHERE name IN ('physical reads', 'db block gets');
  

  • Goal: Keep the ratio (db block gets - physical reads) / db block gets > 95%.

PGA: Private Memory per Process

Unlike SGA, the PGA is process-specific and holds:

• Sort operations (e.g., ORDER BY, GROUP BY).
• Session-specific data (e.g., PL/SQL variables).
• Cursor state.

SGA vs. PGA: Key Differences

Worked Example: Khalti’s Transaction Processing During Dashain, Khalti processes 10,000 transactions/minute. How does Oracle manage this?

1. SGA’s Shared Pool: Caches frequent queries like SELECT user_balance FROM accounts WHERE user_id = ?.
2. PGA for Sorting: If a user requests ORDER BY transaction_date DESC, the PGA handles the sort in memory.
3. Buffer Cache: Reads account data blocks into memory to avoid repeated disk reads.

Problem: If the SGA is too small, Khalti’s system slows down due to physical reads (disk I/O). Solution: Monitor with:

SELECT * FROM v$sga;
SELECT * FROM v$pga_statistics;

3. Tablespaces: Logical Storage Containers

Tablespaces group database objects and map to physical datafiles. They allow:

• Storage management (e.g., separate tablespaces for SYSTEM, USERS, TEMP).
• Backup flexibility (backup a tablespace instead of the entire database).
• Security (restrict access to specific tablespaces).

Types of Tablespaces

Creating a Tablespace for a Bank’s Loan Data

Suppose Nabil Bank wants to store loan records in a separate tablespace for performance and backup isolation:

-- Create a tablespace for loan data with two datafiles
CREATE TABLESPACE loan_data
  DATAFILE '/u01/oracle/loan1.dbf' SIZE 5G AUTOEXTEND ON NEXT 1G,
           '/u02/oracle/loan2.dbf' SIZE 5G AUTOEXTEND ON NEXT 1G
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO;

-- Create a table in the new tablespace
CREATE TABLE loan_applications (
  application_id NUMBER PRIMARY KEY,
  customer_name VARCHAR2(100),
  amount NUMBER,
  status VARCHAR2(20)
) TABLESPACE loan_data;

Why This Design?

• Performance: Loan queries don’t compete with other bank operations for I/O.
• Backup: The DBA can back up only loan_data if needed (saves time).
• Security: Restrict access to loan_data to authorized users.

4. Redo Logs and Multiplexing: Ensuring Data Durability

Redo logs are the journal of all changes to the database. They enable:

• Crash recovery (replaying redo entries after a failure).
• Data durability (ensuring committed transactions survive failures).

How Redo Logs Work

1. When a transaction commits, Oracle writes redo entries to the redo log buffer (in SGA).
2. The LGWR (Log Writer) process writes these entries to online redo log files on disk.
3. If the database crashes, Oracle uses the redo logs to replay changes and restore consistency.

sequenceDiagram
    participant User as User Transaction
    participant DB as Database
    participant SGA as SGA (Redo Log Buffer)
    participant Disk as Online Redo Log Files
    participant LGWR as LGWR Process

    User->>DB: COMMIT (e.g., "Update account balance")
    DB->>SGA: Write redo entry to buffer
    SGA->>LGWR: Signal new redo entry
    LGWR->>Disk: Write redo entry to Group 1
    Disk-->>LGWR: Acknowledge write
    LGWR->>SGA: Clear buffer
    DB->>User: COMMIT successful

Multiplexing Redo Logs and Control Files

Multiplexing means creating multiple copies of critical files to protect against disk failures.

Worked Example: Ncell’s Billing System Ncell’s billing database uses 3-member redo log groups and 3 control files for reliability:

-- Configure multiplexed redo logs
ALTER DATABASE ADD LOGFILE GROUP 3 ('/u01/oracle/redo03a.log', '/u02/oracle/redo03b.log') SIZE 100M;

Why?

• If /u01 fails, the redo logs on /u02 ensure no data loss.
• Control file multiplexing prevents corruption from a single disk failure.

5. Temporary Tablespaces and Undo Management

Temporary Tablespaces

• Store sort operations, hash joins, and PL/SQL temp tables.
• Data is automatically deleted when the session ends.
• Example: eSewa’s TEMP tablespace handles temporary reports during sales.

-- Check temporary tablespace usage
SELECT tablespace_name, used_space, free_space
FROM v$temp_space_header;

Undo Tablespace

• Stores undo data for:
  • Read consistency (MVCC: Multi-Version Concurrency Control).
  • Rollback operations.
• Oracle automatically manages undo retention (configurable via UNDO_RETENTION).

Worked Example: Bank Transaction Rollback Suppose a user at Global IME Bank transfers ₹50,000 but the system crashes before completion. The undo tablespace allows:

-- Rollback the failed transaction
ROLLBACK;

How Undo Works:

1. Before updating the account, Oracle writes undo entries to the undo tablespace.
2. If the transaction fails, Oracle uses these entries to reverse the changes.

6. Automatic Storage Management (ASM)

ASM is Oracle’s disk management tool that:

• Pools disks or disk groups into a single storage layer.
• Automatically strips and mirrors data for performance and redundancy.
• Simplifies storage administration.

Why Use ASM?

• eSewa uses ASM to scale storage during festival seasons without manual disk management.
• NTC uses ASM to mirror traffic data across disks for high availability.

Example ASM Command:

-- Create an ASM disk group for a bank
ASMCMD> mkdiskgroup DATA external redundancy disk '/dev/sdb' '/dev/sdc';

Redundancy Levels:

In the Real World

1. eSewa’s Festival Season Scaling

   • Idea Used: Temporary Tablespaces + ASM
   • How: During Dashain, eSewa’s TEMP tablespace handles temporary transaction reports. ASM automatically stripes data across disks to handle the load spike (10x normal traffic).

2. Ncell’s Billing Database Reliability

   • Idea Used: Multiplexed Redo Logs + Control Files
   • How: Ncell’s billing system uses 3-member redo log groups and 3 control files across separate disks. If one disk fails, the system remains operational, preventing revenue loss.

3. Global IME Bank’s Loan Processing

   • Idea Used: Tablespaces + Undo Management
   • How: Loan applications are stored in a dedicated LOAN_DATA tablespace. The undo tablespace ensures that if a loan approval fails mid-process, the bank can roll back without data corruption.

4. Daraz’s Order Fulfillment

   • Idea Used: SGA Buffer Cache
   • How: Daraz’s database caches frequent queries (e.g., "SELECT product_stock FROM inventory") in the shared pool. This reduces disk I/O during Black Friday sales, improving response time from 500ms to 50ms.

Exam Tip

What Examiners Look For

1. SQL Commands: Always provide exact commands for creating tablespaces, multiplexing logs, or configuring ASM. Partial credit is given for syntax errors.

   • Example: Creating a multiplexed redo log group must include both file paths.

2. Diagrams: Draw layered models (e.g., SGA components) or flowcharts (e.g., redo log process) to explain concepts visually. Label every part.

3. Real-World Mapping: Relate concepts to Nepali examples (e.g., Ncell’s redo logs, eSewa’s temp tablespace). Examiners reward contextual understanding.

4. Failure Scenarios: Explain how multiplexing helps in failures (e.g., "If Disk 1 fails, the multiplexed redo log on Disk 2 ensures no data loss").

5. Performance Metrics: Know how to check SGA/PGA usage:

   -- Check SGA efficiency
   SELECT name, value FROM v$sga_dynamic_components;
   -- Check PGA usage
   SELECT * FROM v$pga_statistics;
   

Common Pitfalls

• Forgetting multiplexing rules: Always state the minimum copies for redo logs (2) and control files (2).
• Confusing SGA and PGA: Remember SGA is shared, PGA is private per session.
• Ignoring tablespaces: Questions often ask to create a tablespace for a specific use case (e.g., "Design storage for a bank’s loan data").

Sample Exam Question Breakdown

Question: "Describe the memory structure of Oracle Database. Explain how PGA differs from SGA. Also explain about Oracle instance and its components." Expected Answer Structure:

1. Draw a diagram of SGA and PGA (use Mermaid).
2. List SGA components (buffer cache, redo log buffer, shared pool) with their roles.
3. Compare PGA vs. SGA in a table (scope, purpose, memory management).
4. Define Oracle instance: "A set of memory structures (SGA) and background processes."
5. List background processes: PMON, SMON, LGWR, CKPT, ARCn.
6. Real-world tie-in: "eSewa’s high SGA hit ratio reduces disk I/O during Diwali."

Oracle instance components (memory + background processes). (Image: Brickcompass, CC BY 4.0, via Wikimedia Commons)