Oracle Backup, Restore & Recovery: Methods, Tools & Real-World Failover

Core Concepts: Backup, Restore, and Recovery

Oracle’s backup and recovery system ensures data integrity and minimal downtime. Unlike manual backups, Oracle uses Recovery Manager (RMAN) for automated, efficient backups. Recovery involves restoring data to a consistent state after failures (crashes, corruption, or user errors).

1. Why Backup and Recovery Matter

• Data Loss Prevention: Hard drives fail (~1 in 5 servers per year). Oracle’s recovery ensures no permanent data loss.
• Disaster Recovery: Earthquakes (like 2015 Nepal) can destroy servers. Offsite backups (cloud or remote data centers) restore operations in hours.
• Compliance: Banks (NMB, Global IME) and stock exchanges (NEPSE) must meet audit trails for financial transactions.

2. Types of Backups

Oracle supports three primary backup methods:

Mermaid Table:

| Backup Type | Scope | Storage Impact | Recovery Complexity | | Full | All data | High | Low | | Incremental | Changes since last | Low | High | | Differential | Changes since full | Medium | Medium |

Worked Example: Scenario: Ncell’s 99.99% Uptime Guarantee

• Backup Strategy: Full backup every Sunday, incremental backups daily.
• Recovery Plan: If a server crashes on Wednesday, Ncell restores the Sunday full backup + Monday–Wednesday increments in <1 hour.
• Tools Used: RMAN automates backups to tape and cloud storage (AWS S3).

3. Oracle Recovery Manager (RMAN)

RMAN is Oracle’s automated backup and recovery tool. It supports:

• Backup Types: Full, incremental, archived logs.
• Storage Media: Disk, tape, cloud (AWS, Azure).
• Recovery Scenarios: Crash recovery, media recovery, point-in-time recovery (PITR).

How RMAN Works:

1. Backup Command:

   RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
   

   • Copies all datafiles and archived redo logs to disk/tape.
2. Restore Command:

   RMAN> RESTORE DATABASE FROM BACKUPSET '...';
   

   • Reconstructs datafiles from backups.
3. Recovery Command:

   RMAN> RECOVER DATABASE UNTIL TIME '2023-10-01 10:00:00';
   

   • Applies archived logs to roll forward to a specific time.

4. Recovery Methods

Oracle provides multiple recovery approaches based on failure type:

Mermaid State Diagram:

stateDiagram-v2
    [*] --> Normal_Operation
    Normal_Operation --> Crash: Power failure
    Crash --> Crash_Recovery: Apply redo logs
    Crash_Recovery --> Normal_Operation
    Normal_Operation --> Data_Corruption: Disk failure
    Data_Corruption --> Media_Recovery: Restore + logs
    Media_Recovery --> Normal_Operation
    Normal_Operation --> User_Error: Accidental delete
    User_Error --> PITR: Restore to time T
    PITR --> Normal_Operation

5. Backup and Recovery Best Practices

1. 3-2-1 Rule:
   • 3 copies of data (original + 2 backups).
   • 2 media types (disk + tape).
   • 1 offsite copy (cloud or remote data center).
   • Example: NEPSE stores backups in Kathmandu (primary) + Pokhara (secondary) + AWS (cloud).

2. Automate RMAN:

   • Schedule backups via DBMS_SCHEDULER (Unit 5).
   • Example script:

     BEGIN
       DBMS_SCHEDULER.CREATE_JOB(
         job_name => 'daily_backup',
         job_type => 'SQL_SCRIPT',
         job_action => 'RMAN COMMAND BACKUP DATABASE',
         start_date => SYSTIMESTAMP,
         repeat_interval => 'FREQ=DAILY; BYHOUR=2',
         enabled => TRUE
       );
     END;
     

3. Test Restores:

   • Simulate failures monthly. Example: Ncell tests restoring call records from a 2-week-old backup.

4. Use Data Guard for High Availability:

   • Data Guard (Unit 10) replicates databases across sites. If the primary fails, the standby takes over with minimal downtime.
   • Example: NMB’s banking systems use Data Guard to switch to a backup server in <30 seconds during maintenance.

6. Real-World Examples

1. eSewa’s Transaction Backup

• Idea Used: Incremental + Cloud Backups
• How: eSewa uses RMAN to back up transaction logs to AWS S3 every 15 minutes. If a payment fails, they restore from the last successful backup + apply logs.
• Why: Ensures no lost transactions during server crashes (common in Nepal’s unstable power grid).

2. NEPSE’s Market Data Recovery

• Idea Used: Point-in-Time Recovery (PITR)
• How: NEPSE’s trading database is backed up hourly. If a corrupt trade entry is detected at 3 PM, they restore to 2 PM and reapply logs.
• Why: Prevents financial losses from bad data (e.g., fake trades).

3. Daraz’s Order Queue Recovery

• Idea Used: Media Recovery + Flashback
• How: Daraz’s order database uses RMAN for daily full backups. If a server crashes during peak sales (e.g., Eid), they:
  1. Restore the latest full backup.
  2. Apply archived logs to catch up to the crash time.
  3. Use FLASHBACK DATABASE to undo incomplete orders.
• Why: Ensures no lost sales during high-traffic periods.

7. Exam Tip: How This Unit is Tested

• Definitions (20%):

  • Explain RMAN, PITR, media recovery, and Data Guard in 3–4 lines each.
  • Example Question: "Differentiate between incremental and differential backups with an example."

• Process Flow (30%):

  • Draw a sequence diagram of RMAN’s backup/restore/recovery steps.
  • Example Question: "Describe the steps to recover an Oracle database after a disk failure."

• Real-World Application (25%):

  • Link concepts to Nepali companies (eSewa, Ncell, NEPSE) or global examples (Google’s spanner database).
  • Example Question: "How would you design a backup strategy for Pathao’s driver scheduling system?"

• Code Snippets (25%):

  • Write RMAN commands for:
    • Full backup.
    • Restoring a corrupted datafile.
    • Point-in-time recovery.
  • Example Question: "Write SQL to schedule a weekly full backup using RMAN."

Mermaid Sequence Diagram for RMAN Recovery:

sequenceDiagram
    participant User
    participant RMAN
    participant Database

    User->>RMAN: RMAN> RESTORE DATABASE FROM BACKUPSET '...'
    RMAN->>Database: Reconstructs datafiles
    User->>RMAN: RMAN> RECOVER DATABASE UNTIL TIME '...'
    RMAN->>Database: Applies archived logs
    Database-->>User: Database is recovered to desired state

Key Takeaways

• RMAN is Oracle’s automated backup tool for full, incremental, and archived log backups.
• Recovery methods (crash, media, PITR) depend on the failure type and data criticality.
• Best practices include the 3-2-1 rule, automation, and regular restore tests.
• Real-world use: eSewa (cloud backups), NEPSE (PITR), Daraz (media recovery).
• Exam focus: Define terms, draw flowcharts, write RMAN commands, and apply to Nepali companies.