DBA Fundamentals: Roles, Architecture, and Core Tasks

TAKEAWAYS:

• A DBA manages database lifecycle (design, backup, security, performance) using Oracle’s architecture (instance + physical files) and tools like RMAN.
• Oracle’s memory structure splits into SGA (shared, for queries) and PGA (private, per-session), with components like buffer cache, redo logs, and shared pool.
• Backup policies must balance RPO/RTO (e.g., Ncell’s daily RMAN backups with 1-hour recovery target).
• User management in Oracle uses roles (e.g., CONNECT, RESOURCE) and privileges (e.g., SELECT, EXECUTE) with commands like GRANT/REVOKE.
• Multiplexing (duplicating redo logs/control files) prevents single-point failures, critical for NEPSE’s high-availability trading system.
• ADR (Automatic Diagnostic Repository) automates troubleshooting by storing alerts, traces, and logs in $ORACLE_BASE/diag.

1. What is Database Administration (DBA)?

A DBA (Database Administrator) is a specialized role responsible for:

• Designing database schemas (tables, indexes, constraints).
• Ensuring data integrity (backups, recovery, security).
• Optimizing performance (query tuning, storage management).
• Managing users/roles (privileges, auditing).
• Monitoring database health (ADR, alerts, logs).

Why is DBA critical?

• Ncell uses DBAs to manage subscriber data (10M+ records) with zero downtime during peak hours.
• eSewa relies on DBAs to secure online transactions (e.g., GRANT EXECUTE ON wallet_transfer TO app_user).
• NEPSE needs DBAs to handle real-time stock trades (e.g., REVOKE DELETE ON trades FROM anonymous to prevent fraud).

classDiagram
    class DBA {
        +Design schemas
        +Backup/recover data
        +Tune performance
        +Manage users
        +Monitor health
    }
    class OracleDatabase {
        -Instance (SGA + background processes)
        -Physical files (datafiles, redo logs, control files)
        -CDB/PDB (Multitenant)
    }
    DBA --> OracleDatabase : "Administers"
    note for DBA "Roles: DBA, Security Admin, Backup Admin"
    note for OracleDatabase "Components: Memory, Processes, Storage"

2. Oracle Database Architecture

Oracle’s architecture divides into three layers:

1. Memory Structure (SGA + PGA)
2. Process Structure (Oracle instance)
3. Storage Structure (physical files)

2.1 Memory Structure: SGA vs. PGA

2.2 Oracle Instance: Background Processes

An instance is the Oracle engine (SGA + background processes). Key processes:

• PMON: Recover failed sessions.
• SMON: Recover crashed instance (after SHUTDOWN ABORT).
• DBWn: Write dirty buffers to disk.
• LGWR: Write redo logs to disk (critical for recovery).

Worked Example: Ncell’s Call Detail Records (CDR) Backup

• Scenario: Ncell’s database crashes at 3 AM. How does Oracle recover?
  1. SMON detects the crash and starts recovery.
  2. LGWR ensures redo logs are written to disk (even if instance crashes).
  3. RMAN (Recovery Manager) restores datafiles from backups.
  4. Users reconnect; uncommitted transactions are rolled back.

2.3 Storage Structure: Physical Files

Oracle stores data in three types of files:

1. Datafiles: Store actual database data (e.g., system01.dbf).
2. Redo Log Files: Record all changes (for recovery).
3. Control Files: Metadata (e.g., datafile locations, redo log status).

Multiplexing: Duplicating critical files (e.g., 2+ control files) to prevent data loss if one fails.

• Example: NEPSE multiplexes control files across 3 disks to survive disk failures.

3. Backup, Recovery, and ADR

sequenceDiagram
    participant User as Ncell User
    participant DB as Oracle Database
    participant RMAN as RMAN Tool
    participant ADR as ADR (Automatic Diagnostic Repository)

    User->>DB: Transaction (e.g., top-up)
    DB->>DB: Logs to Redo Log Buffer
    DB->>ADR: Writes alert (if error)
    DB-->>User: Confirmation

    alt Database Crash
        DB->>RMAN: SMON triggers recovery
        RMAN->>ADR: Checks logs for last backup
        RMAN->>DB: Restores datafiles
        RMAN->>DB: Applies redo logs
        DB-->>User: Database online (RTO: 4 hours)
    end

3.1 Backup Strategies

A backup policy defines:

• RPO (Recovery Point Objective): Max data loss (e.g., 1 hour for Ncell).
• RTO (Recovery Time Objective): Max downtime (e.g., 4 hours for eSewa).

Methods:

Worked Example: eSewa’s Daily Backup Policy

• RPO: 15 minutes (critical for transactions).
• RTO: 1 hour.
• Commands:

  -- Full backup every Sunday
  RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
  
  -- Incremental backup daily (Mon-Sat)
  RMAN> BACKUP DATABASE TAG 'daily_incremental';
  

3.2 Recovery Scenarios

3.3 Automatic Diagnostic Repository (ADR)

ADR stores:

• Alert logs: Critical errors (e.g., ORA-00600).
• Traces: SQL execution details.
• Incident dumps: Crash diagnostics.

Location:

$ORACLE_BASE/diag/rdbms/<DB_NAME>/<INSTANCE>/trace/

• Example: Ncell’s DBA finds a ORA-01578 (snapshot too old) in ADR and adjusts undo retention.

4. User Management and Privileges

4.1 Roles and Privileges

Worked Example: NEPSE’s Trading System

• Scenario: Give analyst read-only access to trades table.

  GRANT SELECT ON trades TO analyst;
  

• Scenario: Lock a hacked account (hacker_user).

  ALTER USER hacker_user ACCOUNT LOCK;
  

4.2 Password Management

Example: eSewa enforces:

ALTER PROFILE eSewa_users LIMIT
  PASSWORD_LIFE_TIME 60
  PASSWORD_REUSE_TIME 365
  PASSWORD_GRACE_TIME 10;

5. Network Configuration

To connect to Oracle remotely:

1. Listener: Listens on a port (default: 1521).
2. TNSNAMES: Maps service names to IPs (e.g., NEPSE_DB = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.100)(PORT=1521)))).
3. SQL*Net: Client-server communication protocol.

Worked Example: Pathao’s Driver App

• Scenario: Pathao’s app connects to Oracle DB to fetch ride requests.

  -- In tnsnames.ora:
  PATHAO_DB =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = pathao-db.example.com)(PORT = 1521))
      (CONNECT_DATA = (SERVICE_NAME = pathao_prod))
    )
  

• Connection:

  CONNECT app_user/Secure@123@PATHAO_DB;
  

## In the Real World

1. Ncell’s Subscriber Database

   • Idea: Multiplexed redo logs ensure no call detail records (CDRs) are lost during network outages.
   • How: Redo logs are written to 3 disks; if one fails, LGWR uses the others.
   • Impact: Zero CDR loss during peak hours (10M+ calls/day).

2. eSewa’s Transaction Security

   • Idea: Roles and privileges restrict access to sensitive tables.
   • How:

     CREATE ROLE wallet_admin;
     GRANT SELECT, INSERT, UPDATE ON wallet_transactions TO wallet_admin;
     

   • Impact: Prevents fraud by limiting UPDATE access to admins only.

3. NEPSE’s High-Availability Trading

   • Idea: Automated RMAN backups with RPO=15 mins.
   • How:

     # Scheduled via cron (Linux)
     0 3 * * * rman target / BACKUP DATABASE PLUS ARCHIVELOG;
     

   • Impact: Stock trades recover in <1 hour even after crashes.

4. Daraz’s Order Processing

   • Idea: PGA memory handles concurrent user sessions.
   • How: Each user’s ORDER BY price uses PGA’s sort area.
   • Impact: Supports 10K+ simultaneous shoppers during sales.

## Exam Tip

1. Architecture Questions (5-10 marks)

   • Draw the SGA/PGA diagram and label all components.
   • Explain multiplexing with a real example (e.g., NEPSE’s control files).
   • Common pitfalls:
     • Confusing SGA (shared) with PGA (private).
     • Forgetting SMON recovers after crashes.

2. Backup/Recovery (5-10 marks)

   • Must mention:
     • RPO/RTO for the organization (e.g., Ncell: RPO=15 mins).
     • RMAN commands for full/incremental backups.
     • Recovery steps for instance crash vs. datafile corruption.
   • Example answer starter:

     "For Ncell, we use RMAN with daily incremental backups and a weekly full backup. RPO is 15 minutes (backups every 15 mins), and RTO is 1 hour. Recovery involves restoring datafiles from backup and applying redo logs using RECOVER DATABASE."

3. User Management (5-10 marks)

   • Key commands to memorize:

     GRANT SELECT ON trades TO analyst;
     ALTER USER hacker_user ACCOUNT LOCK;
     ALTER PROFILE default LIMIT PASSWORD_LIFE_TIME 90;
     

   • Compare roles vs. privileges:

     Roles	Privileges
     Predefined sets (e.g., DBA)	Granular access (e.g., SELECT)
     Simplifies management	More secure (least privilege)

4. ADR and Troubleshooting (2-3 marks)

   • Where to find logs:

     $ORACLE_BASE/diag/rdbms/<DB_NAME>/<INSTANCE>/trace/alert_<SID>.log
     

   • Example: "The ADR alert log showed ORA-00600 due to a corrupted block. We used RECOVER TABLE trades to fix it."

5. Network Configuration (3-5 marks)

   • Must include:
     • Listener port (1521).
     • TNSNAMES entry format.
     • CONNECT syntax with @service_name.

Pro Tip: For 5-mark questions, use bullet points with 1-2 lines per point. For 10-mark questions, structure your answer as:

1. Definition (e.g., "DBA manages database lifecycle...").
2. Key Components (e.g., "Oracle architecture has SGA, PGA, and storage files").
3. Real-World Example (e.g., "Ncell uses multiplexed redo logs...").
4. Commands/Steps (e.g., RMAN> BACKUP DATABASE;).

In the real world

• Ncell: Uses multiplexed redo logs (2+ members) and RMAN backups (RPO: 1 hour) to ensure zero downtime during peak call volumes (10M+ subscribers). The SGA caches frequently accessed subscriber data (e.g., call records) for low-latency responses.
• eSewa: Implements Flashback Database to recover from fraudulent transactions (e.g., FLASHBACK TABLE wallet_transfers TO TIMESTAMP '2080-05-15:14:30') within 5 minutes, meeting its RTO of 1 hour.
• NEPSE: Relies on Oracle’s Automatic Diagnostic Repository (ADR) to log trading system errors (e.g., failed stock orders) and multiplexed control files (3 disks) to survive disk failures during high-frequency trades.