DBMS & Oracle Architecture: Files, Roles, Security & Startup Modes

TAKEAWAYS:

• Oracle’s architecture is layered (memory, processes, files) and uses physical files (control files, redo logs, datafiles) to store critical metadata and transactions.
• A DBA’s role spans security (privileges, auditing), performance tuning, backup/recovery, and user management—critical for systems like eSewa or Ncell’s billing databases.
• Startup/shutdown modes (NOMOUNT, MOUNT, OPEN) control database accessibility, while privileges (SYSTEM vs. OBJECT) enforce security in multi-tenant environments like NEPSE’s stock trading system.
• Multiplexing (duplicating control/redo files) prevents data loss, a technique used by banks to safeguard transaction logs.
• Flashback technology allows time-based recovery, essential for Khalti’s payment rollbacks or Daraz’s order corrections.
• Oracle’s CDB/PDB architecture enables efficient resource sharing, mirroring how Google’s cloud databases host multiple client apps under one instance.

1. What is a Database Management System (DBMS)?

A DBMS is software that manages databases by:

• Storing data in organized structures (tables, indexes).
• Enforcing security (user access, encryption).
• Ensuring data integrity (transactions, constraints).
• Providing tools for backup, recovery, and optimization.

Why Oracle?

Oracle is a relational DBMS (RDBMS) used globally for:

• Mission-critical systems (banks, airlines, e-commerce).
• High availability (24/7 uptime).
• Scalability (handles petabytes of data).

Oracle’s layered architecture: memory, processes, and files (Image: Brickcompass, CC BY 4.0, via Wikimedia Commons)

classDiagram
    class Memory {
        +SGA (Shared Global Area)
        +PGA (Program Global Area)
    }
    class Processes {
        +Background (SMON, PMON, etc.)
        +User Processes
    }
    class Files {
        +Control Files
        +Redo Log Files
        +Datafiles
    }
    Memory --> Processes : "Contains"
    Processes --> Files : "Uses"
    note for Memory "Stores data in RAM"
    note for Files "Persistent storage on disk"

2. Oracle’s Architecture: Layers and Components

Oracle’s architecture is divided into three layers:

Key Files in Oracle

1. Control Files

   • Stores metadata (database name, tablespace locations, redo log files).
   • Critical for recovery—if lost, the database cannot start.
   • Multiplexing (duplicating) control files prevents data loss.

2. Redo Log Files

   • Records all changes (INSERT, UPDATE, DELETE) for recovery.
   • Operates in circular fashion (filled logs are reused after archiving).
   • Example: If a user accidentally deletes a record in NEPSE’s trading database, redo logs help restore it.

3. Datafiles

   • Store actual database data (tables, indexes).
   • Each tablespace has at least one datafile.

Physical files in Oracle: control, redo log, and datafiles (Image: Scifipete, CC BY-SA 3.0, via Wikimedia Commons)

graph LR
    A["Oracle Database"] --> B["Control Files<br/>(Metadata)"]
    A --> C["Redo Log Files<br/>(Transaction Logs)"]
    A --> D["Datafiles<br/>(Actual Data)"]
    B -->|"Multiplexed"| E["Backup Control Files"]
    C -->|"Archived"| F["Archived Redo Logs"]

3. Database Administrator (DBA) Roles

A DBA manages:

• Security: Grants/revokes privileges (e.g., CREATE TABLE, DROP USER).
• Performance: Optimizes queries, tunes indexes.
• Backup/Recovery: Ensures data availability (e.g., Khalti’s daily backups).
• User Management: Creates/modifies users (e.g., eSewa’s citizen portal users).

Types of DBAs

flowchart TD
    A["DBA Role"] --> B["Security Management"]
    A --> C["Performance Tuning"]
    A --> D["Backup & Recovery"]
    A --> E["User Management"]
    B -->|"Example"| F["Ncell: Restrict<br/>Sensitive Data Access"]
    C -->|"Example"| G["Daraz: Optimize<br/>Order Queries"]
    D -->|"Example"| H["Banks: Daily<br/>Automated Backups"]
    E -->|"Example"| I["eSewa: Create<br/>Citizen Accounts"]

4. Oracle Startup and Shutdown Modes

Oracle databases can be started in three modes:

Shutdown Modes

WORKED EXAMPLE: Startup for a Bank’s Database A bank’s DBA runs:

-- Step 1: Start in NOMOUNT (replace control file)
STARTUP NOMOUNT PFILE='init.ora';

-- Step 2: Mount and recover (if needed)
ALTER DATABASE MOUNT;
RECOVER DATABASE;

-- Step 3: Open for users
ALTER DATABASE OPEN;

Why?

• Ensures no data corruption during file replacements.
• Used before applying security patches in Nepal Rastra Bank’s core banking system.

5. Database Security: Privileges and Auditing

Privileges in Oracle

Example Command:

-- Grant SELECT on a table to a user
GRANT SELECT ON employees TO hr_manager;

Auditing

• Standard Auditing: Tracks login attempts, DML changes (INSERT/UPDATE/DELETE).
• Fine-Grained Auditing (FGA): Monitors specific rows (e.g., Ncell’s high-value transactions).

Example:

-- Audit all DELETE operations on 'customers' table
AUDIT DELETE ON customers BY USER;

sequenceDiagram
    User->>Database: DELETE from accounts
    Database->>Audit Trail: Logs action (User, Table, Timestamp)
    Database->>Security Team: Alerts for suspicious activity
    note right of Database: "FGA can track\nspecific rows"

6. Flashback Technology

Allows time-based recovery (e.g., restore a table to a past state).

Steps to Enable Flashback

1. Enable Flashback Database:

   SHUTDOWN IMMEDIATE;
   STARTUP MOUNT;
   ALTER DATABASE FLASHBACK ON;
   ALTER DATABASE OPEN;
   

2. Query Past Data:

   -- View data as of 1 hour ago
   SELECT * FROM employees AS OF TIMESTAMP (SYSTIMESTAMP - INTERVAL '1' HOUR);
   

3. Restore a Dropped Table:

   FLASHBACK TABLE employees TO BEFORE DROP;
   

Real-World Use:

• Khalti uses flashback to undo fraudulent transactions.
• Daraz recovers deleted product listings accidentally removed by admins.

7. Multiplexing: Protecting Critical Files

Multiplexing = duplicating control/redo files to prevent data loss.

Why Multiplex?

• If the primary control file fails, Oracle uses the backup.
• Redo log multiplexing ensures no transaction loss (e.g., bank transfers).

Steps to Multiplex Redo Logs

-- Add a member to redo log group 1
ALTER DATABASE ADD LOGFILE GROUP 1 ('/u01/oracle/redo01.log', '/u01/oracle/redo01b.log');

Example for a Bank:

• Primary redo log: /data/redo1.log
• Backup redo log: /backup/redo1_mirror.log
• If /data fails, the bank switches to /backup without downtime.

8. Oracle Multitenant Architecture (CDB and PDB)

Introduced in Oracle 12c, this architecture allows:

• One Container Database (CDB) hosts multiple Pluggable Databases (PDBs).
• Isolation: PDBs act like separate databases but share resources.

CDB vs. PDB

Example Command:

-- Create a PDB
CREATE PLUGGABLE DATABASE pdb1 ADMIN USER pdb_admin IDENTIFIED BY password;

Real-World Analogy:

• CDB = A server farm (e.g., NTC’s central data center).
• PDB = Individual departments (billing, customer service) running on the same farm.

In the Real World

1. eSewa’s Database Security

   • Uses Oracle’s fine-grained auditing to track citizen login attempts and payment transactions.
   • DBA Role: Monitors AUDIT_TRAIL for fraud detection.

2. Ncell’s Billing System

   • Multiplexed redo logs ensure no call-minute data loss during power outages.
   • Flashback technology recovers incorrectly billed customers.

3. Daraz’s Order Processing

   • PDB architecture isolates seller databases from buyer databases.
   • Startup in MOUNT mode before weekly inventory updates to avoid downtime.

4. Nepal Rastra Bank’s Core Banking

   • CDB hosts multiple PDBs for:
     • Retail banking (savings accounts).
     • Corporate banking (loans).
   • AWR reports optimize ATM transaction speeds.

Exam Tip

1. Diagrams are Key

   • Draw Oracle’s architecture layers (memory, processes, files).
   • Show control/redo/datafile relationships in exams.

2. Command-Based Questions

   • Memorize startup/shutdown commands (e.g., ALTER DATABASE OPEN).
   • Practice multiplexing commands (ADD LOGFILE GROUP).

3. Real-World Scenarios

   • Link backup policies to banking systems (e.g., "Daily RMAN backups for Ncell").
   • Explain flashback using Khalti’s fraud reversal.

4. Common Pitfalls

   • Don’t confuse SYSTEM vs. OBJECT privileges.
   • Multiplexing ≠ backup—it’s real-time redundancy, not archiving.

5. Short-Answer Tricks

   • For DBA roles, list 4 key tasks (security, backup, tuning, user management).
   • For startup modes, compare NOMOUNT vs. MOUNT (metadata vs. no data).

Final Visual Summary

mindmap
  root((Oracle DBMS))
    Architecture
      Memory (SGA, PGA)
      Processes (SMON, PMON)
      Files (Control, Redo, Data)
    DBA Roles
      Security (Privileges, Auditing)
      Backup/Recovery (RMAN, Flashback)
      Performance (AWR, Indexing)
    Startup Modes
      NOMOUNT (No files)
      MOUNT (Metadata only)
      OPEN (Fully operational)
    Security
      System Privileges (GRANT DBA)
      Object Privileges (SELECT on TABLE)
    Real-World
      eSewa (Auditing)
      Ncell (Multiplexing)
      Daraz (PDB Isolation)