DBA Roles, DBMS Architecture, Security & Oracle Basics

TAKEAWAYS:

• A DBA’s role spans database design, security, performance tuning, and disaster recovery, with specialized tasks like multiplexing and flashback technology.
• Oracle’s three-layer architecture (physical, logical, memory) maps to files like control01.ctl and system01.dbf, visible in the V$DATAFILE view.
• Security is enforced via privileges (e.g., SELECT ANY TABLE vs. EXECUTE), with auditing tracking actions like DROP TABLE in the DBA_AUDIT_TRAIL.
• Backup policies (full, incremental, RMAN) must align with RPO/RTO—e.g., Ncell’s 24-hour recovery target for customer data.
• Flashback Database lets you revert to a past state (e.g., undo a mistaken UPDATE on NEPSE’s stock records) using FLASHBACK DATABASE TO TIMESTAMP.
• Startup/shutdown modes (e.g., RESTRICTED mode) control access during maintenance, critical for banks like NMB processing end-of-day transactions.

1. What is a Database Administrator (DBA)?

A DBA is the guardian of an organization’s data, responsible for ensuring availability, integrity, and security of databases. Their tasks range from designing schemas to recovering from failures—think of them as the IT equivalent of a hospital’s chief medical officer, but for data.

Key Roles of a DBA

mindmap
  root((DBA Roles))
    Design
      Schema Design
      Normalization (1NF–5NF)
      Data Modeling (ER Diagrams)
    Administration
      User Management
      Backup & Recovery
      Performance Tuning
    Security
      Privilege Assignment
      Auditing
      Encryption
    Maintenance
      Patching
      Upgrades
      Monitoring (AWR Reports)

Real-world example: At Ncell, DBAs manage the customer billing database (storing ~10M subscribers). Their tasks include:

• Designing tables for call logs, usage records, and promotions (e.g., CUSTOMER, SERVICE_PLAN).
• Securing data by revoking DELETE privileges from call-center agents (only SELECT allowed).
• Recovering from failures if a power outage corrupts transaction logs during peak hours.

2. Database Management System (DBMS) and Oracle Architecture

Oracle DBMS is built on a three-layer architecture, each layer serving a distinct purpose:

Layered Architecture of Oracle DBMS

Key Files in Oracle Database

Worked Example: When you run CREATE TABLE employee (id NUMBER, name VARCHAR2(50)) in NEPSE’s stock database, Oracle:

1. Allocates space in the USERS tablespace (logical layer).
2. Writes the table definition to the control file (control01.ctl).
3. Stores the actual data in a datafile (e.g., users01.dbf).

Physical layout of Oracle datafiles, redo logs, and control files in a server rack (Image: Scifipete, CC BY-SA 3.0, via Wikimedia Commons)

3. Database Security: Privileges and Auditing

Security in Oracle is managed via privileges (permissions) and auditing (tracking actions).

Types of Privileges

Example:

-- Grant SELECT on NEPSE's stock table to analysts
GRANT SELECT ON stock_prices TO analyst_role;

-- Revoke DELETE from call-center agents at Ncell
REVOKE DELETE ON customer_data FROM call_center_agent;

Auditing in Oracle

Auditing records who did what, when, and from where. Two types:

1. Standard Auditing: Tracks SQL statements (e.g., DROP TABLE).
2. Fine-Grained Auditing (FGA): Tracks row-level changes (e.g., who updated a salary in the EMPLOYEE table).

Example Audit Trail:

-- Enable auditing for DROP operations
AUDIT DROP ANY TABLE BY user_id;

-- Query audit logs
SELECT username, sql_text, timestamp
FROM dba_audit_trail
WHERE sql_text LIKE '%DROP%';

Real-world tie-in: Khalti uses auditing to track:

• Who initiated a transaction reversal (e.g., UPDATE transactions SET status = 'REVERSED').
• Failed login attempts (e.g., INSERT INTO login_attempts VALUES (..., 'FAILED')).

4. Backup, Restore, and Recovery

A backup is a copy of database files; restore brings it back; recovery applies redo logs to fix corruption.

Types of Backups

Backup Policy Example (Ncell):

• Full Backup: Every Sunday at 2 AM (RPO: 24 hours).
• Incremental Backup: Every 6 hours (reduces recovery time).
• Offsite Storage: Backups sent to a disaster-recovery site in Chitwan.

Recovery Process

1. Restore datafiles from backup.
2. Recover using redo logs (RECOVER DATABASE).
3. Open the database (ALTER DATABASE OPEN).

Worked Example: If Daraz’s order database crashes during Black Friday:

1. Restore from the last full backup (Sunday).
2. Apply incremental backups (Monday–Thursday).
3. Replay redo logs to catch up to the crash time.

5. Flashback Database

Flashback Database lets you rewind the entire database to a past point in time (e.g., before a mistaken UPDATE).

Steps to Enable Flashback

1. Set retention period:

   ALTER DATABASE FLASHBACK RETENTION GOAL 2 DAYS;
   

2. Enable flashback mode:

   ALTER DATABASE FLASHBACK ON;
   

3. Revert to a past time:

   FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2023-11-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS');
   

Real-world use: NMB Bank uses Flashback to:

• Revert a failed batch job that updated all loan interest rates incorrectly.
• Roll back to the state before a hacker deleted 100 customer records.

6. Database Startup and Shutdown Modes

Oracle databases can start/stop in different modes to control access and maintenance.

Startup Modes

Shutdown Modes

Example: Before upgrading Pathao’s ride-hailing database, the DBA:

1. Shuts down in NORMAL mode to ensure no active rides are lost.
2. Applies the patch.
3. Starts in RESTRICTED mode to test queries.

In the Real World

1. Ncell’s Billing System

   • Idea Used: Backup & Recovery
   • How: Ncell’s DBAs take hourly incremental backups of call logs and usage data. If a power outage corrupts records during peak hours, they restore from the last clean backup and replay redo logs to recover lost transactions.

2. NEPSE’s Stock Database

   • Idea Used: Flashback Database + Auditing
   • How: During volatile market days, NEPSE’s DBAs enable Flashback Database to revert to the state before a mistaken UPDATE altered stock prices. Auditing tracks which analyst executed the command.

3. Khalti’s Payment Gateway

   • Idea Used: Privileges + Fine-Grained Auditing
   • How: Only the fraud team has UPDATE privileges on transaction records. Fine-grained auditing logs every change (e.g., SET status = 'DISPUTED'), helping detect fraudulent reversals.

Exam Tip

1. Define vs. Explain: For questions like “Explain the term backup, restore, and recovery”, always include:

   • Backup: Copy of datafiles.
   • Restore: Bringing back from backup.
   • Recovery: Applying redo logs to fix corruption.
   • Example: “Ncell restores from Sunday’s full backup and recovers using hourly redo logs.”

2. Privileges Table: For “Explain system and object privileges”, draw a comparison table (like above) and give one real example (e.g., GRANT SELECT ON stock_prices TO analyst).

3. Startup/Shutdown Modes: Memorize the 4 startup modes and 4 shutdown modes with one use case each. Example:

   • “A DBA would use SHUTDOWN TRANSACTIONAL before a patch to avoid losing active customer orders in Daraz’s database.”

4. Flashback Steps: For “Explain Flashback Database”, list the 3 steps (FLASHBACK RETENTION, FLASHBACK ON, FLASHBACK TO TIMESTAMP) and tie it to a real scenario (e.g., “NMB Bank reverted a failed loan interest update using Flashback.”).

5. Backup Policy: If asked to “design a backup policy”, structure it as:

   • RPO (Recovery Point Objective): Max data loss (e.g., 24 hours for Ncell).
   • RTO (Recovery Time Objective): Max downtime (e.g., 4 hours for NEPSE).
   • Backup Types: Full (weekly), incremental (daily), offsite storage.
   • Tools: RMAN for Oracle databases.

Final Visual Summary