Database Backup, Restore & Recovery: Strategies, RMAN, Failures & Recovery

TAKEAWAYS:

• Backup policies define what to back up (full/incremental/differential), when (frequency), where (on-prem/cloud), and how (RMAN/export), ensuring data durability and compliance.
• RMAN (Recovery Manager) automates backups, deduplication, and recovery with commands like BACKUP, RESTORE, and RECOVER, reducing manual errors and storage costs.
• Recovery types depend on failure: media failure (corrupted datafiles) → restore from backup; instance failure (crash) → recover using redo logs; logical corruption (user error) → point-in-time recovery (PITR).
• Multiplexing (duplicating control files/redo logs) prevents single-point failures, improving reliability—critical for banks like Nabil Bank or eSewa’s transaction logs.
• Automatic Diagnostic Repository (ADR) logs errors and diagnostics, helping DBAs troubleshoot issues without manual logs.
• Worked example: Recovering a Daraz order database after a disk crash requires restoring datafiles from backup, then applying redo logs to reach the latest transaction.

1. Why Backup, Restore, and Recovery Matter

Databases are the backbone of modern systems. A single failure—whether a disk crash, human error, or cyberattack—can wipe out years of data. Backup creates copies of data; restore brings it back; recovery ensures the database is consistent after a failure.

Real-world examples in Nepal

• eSewa: Uses automated RMAN backups to protect transaction records (e.g., electricity bill payments). If a server fails, RMAN restores the latest backup and reapplies redo logs to avoid losing payments.
• Nabil Bank: Multiplexes redo logs across multiple disks to prevent data loss during power outages (common in Nepal). If one disk fails, the bank recovers using the duplicate logs.
• NTC (Nepal Telecom): Stores daily backups in a geographically separate data center. If Kathmandu’s primary server crashes (e.g., during a monsoon flood), NTC restores from the backup site.

2. Backup Strategies: What, When, Where, How

A backup policy answers four key questions:

1. What to back up?
   • Full backup: Copies all data (slow but complete).
   • Incremental backup: Copies only changes since the last backup (fast, storage-efficient).
   • Differential backup: Copies changes since the last full backup (slower than incremental but simpler to restore).
   • Archived redo logs: Critical for recovery; contain all committed transactions.

2. When to back up?

   • Daily full backups (e.g., at midnight for banks).
   • Hourly incremental backups (e.g., for eSewa’s transaction logs).
   • Before major changes (e.g., schema updates).

3. Where to store backups?

   • On-premises (fast but vulnerable to disasters).
   • Cloud storage (e.g., AWS S3, Google Cloud; used by Daraz for offsite backups).
   • Tape libraries (cheap but slow; rarely used today).

4. How to back up?

   • User-managed backups: Manual exports (e.g., EXPDP for data pump).
   • RMAN (Recommended): Oracle’s built-in tool for automated, efficient backups.

Worked Example: Backup Policy for a Nepalese Bank

Why this works:

• Full weekly backups ensure recoverability from major failures.
• Frequent archived redo logs allow point-in-time recovery (e.g., undo a mistaken transfer).
• Multiplexed control files prevent corruption if one disk fails.

3. RMAN: Oracle’s Backup and Recovery Tool

RMAN (Recovery Manager) automates backups, deduplication, and recovery. It reduces storage costs and speeds up recovery.

sequenceDiagram
    participant DB as Oracle Database
    participant RMAN as RMAN Tool
    participant Disk as Storage

    DB->>RMAN: BACKUP DATABASE PLUS ARCHIVELOG
    RMAN->>Disk: Writes backup set (compressed)
    Disk-->>RMAN: Acknowledges
    RMAN->>DB: CONFIRMS BACKUP COMPLETE

    Note right of RMAN: Deduplication reduces storage by 60% (typical for Daraz’s order DB)

Key RMAN Concepts

1. Backup Types in RMAN:

   • Full backup: All datafiles.

     RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
     

   • Incremental backup: Only changed blocks.

     RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;
     

   • Differential backup: Changes since last full backup.

     RMAN> BACKUP DIFFERENTIAL DATABASE;
     

2. Backup Sets vs. Image Copies:

   • Backup sets: Compressed, deduplicated (default; saves space).
   • Image copies: Exact copies of datafiles (used for point-in-time recovery).

3. Channels: RMAN uses parallel channels to speed up backups.

   RMAN> RUN {
     ALLOCATE CHANNEL c1 TYPE DISK;
     ALLOCATE CHANNEL c2 TYPE DISK;
     BACKUP DATABASE PLUS ARCHIVELOG;
   }
   

Configuring RMAN

1. Initialize RMAN:

   RMAN> CONNECT TARGET sys/password@CDB_TEST;
   RMAN> CONNECT CATALOG rman_user/password@ORCL; -- Optional: for RMAN repository
   

2. Set Backup Retention Policy:

   RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
   

3. Automate Backups with Scripts:

   -- Example: Daily full backup script
   RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
   RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;
   

Real Picture: RMAN in Action

4. Restore and Recovery: Step-by-Step

Recovery depends on the type of failure:

A. Media Failure (Corrupted Datafile)

Cause: Disk crash, accidental deletion. Solution: Restore the datafile from backup, then recover using redo logs.

-- Step 1: Restore the datafile
RMAN> RESTORE DATAFILE 3;

-- Step 2: Recover the datafile
RMAN> RECOVER DATAFILE 3;

B. Instance Failure (Database Crash)

Cause: Server reboot, OS crash. Solution: Start the database in MOUNT mode, then recover using archived redo logs.

-- Step 1: Start in MOUNT mode
SQL> STARTUP MOUNT;

-- Step 2: Recover the database
RMAN> RECOVER DATABASE;

C. Logical Corruption (User Error)

Cause: Wrong DELETE query, schema change. Solution: Point-in-Time Recovery (PITR) to roll back to a known good state.

-- Step 1: Restore control file and datafiles to a backup
RMAN> RESTORE CONTROLFILE FROM AUTOBACKUP;
RMAN> RESTORE DATABASE;

-- Step 2: Recover to a specific time (e.g., 2 hours ago)
RMAN> RECOVER DATABASE UNTIL TIME "TO_TIMESTAMP('2023-10-01 14:00:00', 'YYYY-MM-DD HH24:MI:SS')";

Mermaid: Recovery Process Flow

flowchart TD
    A["Failure Occurs"] --> B{"Type of Failure?"}
    B -->|"Media Failure"| C["Restore Datafile<br/>RMAN> RESTORE DATAFILE X"]
    B -->|"Instance Crash"| D["Start in MOUNT<br/>SQL> STARTUP MOUNT"]
    B -->|"Logical Corruption"| E["PITR<br/>RMAN> RECOVER UNTIL TIME"]
    C --> F["Recover with Redo Logs<br/>RMAN> RECOVER DATAFILE X"]
    D --> F
    E --> F
    F --> G["Database Open<br/>SQL> ALTER DATABASE OPEN"]

5. Multiplexing: Protecting Critical Files

Multiplexing creates multiple copies of critical files to prevent single-point failures.

What to Multiplex?

How to Multiplex Redo Logs

-- Create 2 members for each redo log group
ALTER DATABASE ADD LOGFILE GROUP 1 ('/u01/oracle/redo01.log', '/u02/oracle/redo01.log') SIZE 100M;

Advantages of Multiplexing

• Fault tolerance: If one copy fails, others take over.
• Performance: Parallel I/O for faster writes.
• Disaster recovery: Easier to restore from a secondary copy.

Real-world Example: NTC’s Redo Log Strategy

NTC multiplexes redo logs across three disks in different racks. If one disk fails (e.g., due to a power surge), the database continues using the other two logs. After recovery, NTC restores the failed disk from a backup.

6. Automatic Diagnostic Repository (ADR)

ADR is Oracle’s built-in diagnostic and troubleshooting tool. It logs:

• Alert logs (errors, warnings).
• Trace files (SQL execution details).
• Incidents (critical failures).

Key ADR Components

How ADR Helps DBAs

1. Quick troubleshooting: Instead of searching manual logs, DBAs check ADR.

   -- View ADR contents
   SELECT name, value FROM v$diag_info;
   

2. Automatic incident capture: Oracle captures incidents (e.g., ORA-600) in ADR.
3. Integration with RMAN: RMAN backups include ADR logs for recovery.

Mermaid: ADR Structure

7. Worked Example: Recovering Daraz’s Order Database

Scenario: Daraz’s primary database server crashes during a Black Friday sale. The disk containing the ORDERS tablespace fails.

Steps to Recover

1. Identify the failure:
   • The ORDERS tablespace is offline due to a corrupted datafile (orders01.dbf).

2. Restore the datafile from backup:

   RMAN> RESTORE DATAFILE '/u01/oracle/data/orders01.dbf';
   

3. Recover using redo logs:

   RMAN> RECOVER DATAFILE '/u01/oracle/data/orders01.dbf';
   

4. Open the database:

   SQL> ALTER DATABASE DATAFILE '/u01/oracle/data/orders01.dbf' ONLINE;
   SQL> ALTER DATABASE OPEN;
   

5. Verify recovery:

   SQL> SELECT COUNT(*) FROM ORDERS; -- Should match pre-failure count.
   

Why this works:

• RMAN’s incremental backups ensure minimal data loss.
• Multiplexed redo logs guarantee recovery even if some logs are lost.
• ADR logs help Daraz’s DBA diagnose the root cause (e.g., disk failure).

8. Common Pitfalls and Best Practices

Best Practices for Nepalese DBAs

1. Follow the 3-2-1 rule:
   • 3 copies of data (e.g., primary + 2 backups).
   • 2 different media (disk + tape/cloud).
   • 1 offsite copy (e.g., AWS or a secondary data center).
2. Automate everything: Use RMAN scripts and cron jobs.
3. Monitor ADR: Set up alerts for critical errors.
4. Document recovery procedures: Include step-by-step guides for failures.

In the Real World

1. eSewa’s Transaction Backups

   • Idea Used: Incremental backups + RMAN
   • How: eSewa runs RMAN every 15 minutes to back up transaction logs. If a payment fails (e.g., due to a server crash), eSewa restores the latest backup and reapplies redo logs to ensure no money is lost. Their multiplexed redo logs ensure even if one disk fails, transactions are recoverable.

2. Nabil Bank’s Multiplexed Redo Logs

   • Idea Used: Multiplexing + Archived Redo Logs
   • How: Nabil Bank stores redo logs on three separate disks in different racks. During a power outage (common in Nepal), if one disk fails, the bank recovers using the other two logs. Their backup policy includes daily full backups and hourly incremental backups of critical tables (e.g., CUSTOMER_ACCOUNTS).

3. NTC’s Disaster Recovery Plan

   • Idea Used: Point-in-Time Recovery (PITR) + Cloud Backups
   • How: NTC stores backups in a secondary data center in Lalitpur. If Kathmandu’s primary server is damaged (e.g., by flooding), NTC restores from the backup site. Their RMAN configuration includes:

     CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '/backup/ntc_%U';
     CONFIGURE BACKUP OPTIMIZATION ON; -- Deduplicates backups
     

   • Real-world test: During the 2022 floods, NTC recovered their billing system in under 2 hours using PITR.

Exam Tip

This unit is highly practical and often tested with scenario-based questions. Expect:

1. SQL/RMAN commands: Be ready to write commands for backups, restores, and recovery (e.g., BACKUP, RESTORE, RECOVER).
   • Example: "Write RMAN commands to back up a database and its archived logs." (5 marks)
2. Backup policy design: Explain what, when, where, and how for a given scenario (e.g., a bank or e-commerce site).
   • Example: "Design a backup strategy for a hospital’s patient records database." (10 marks)
3. Recovery scenarios: Describe steps to recover from media failure, instance crash, or logical corruption.
   • Example: "The EMPLOYEE tablespace is corrupted. Write the steps to restore it." (5 marks)
4. Multiplexing and ADR: Explain why multiplexing improves reliability and how ADR helps in troubleshooting.
   • Example: "Why does Nabil Bank multiplex its redo logs? How does ADR help in diagnosing failures?" (5 marks)

Key formulas/commands to memorize:

Pro Tip: Always include real-world examples in your answers (e.g., eSewa, Nabil Bank). Examiners love seeing how you apply concepts to Nepalese scenarios!