Oracle Backup, Restore & Recovery: Policies, Methods & Real-World Scenarios

Core Concepts: Backup, Restore, and Recovery Defined

Oracle’s backup, restore, and recovery (BRR) trio ensures data durability and availability. These terms are not interchangeable:

Why all three?

• Backup = "Save a copy."
• Restore = "Put the copy back."
• Recovery = "Fix what’s broken in the copy."

---

1. Backup Strategies in Oracle

Oracle supports three backup types, each with trade-offs in speed, storage, and recovery flexibility.

A. Full Backup

• Definition: Copies all database files (datafiles, redo logs, control files) in one operation.
• Use case: Weekly full backups for disaster recovery (e.g., restoring an entire bank’s core banking system after a server failure).
• Command:

  RMAN> BACKUP DATABASE FULL;
  

• Pros/Cons:

  Pros	Cons
  Simple to manage.	High storage cost.
  Fastest for full recovery.	Long backup window.
  No dependency on prior backups.	Inflexible for partial restores.

B. Incremental Backup

• Definition: Copies only changed blocks since the last backup (full or incremental).
  • Cumulative: All changes since last full backup.
  • Differential: Only changes since the last incremental backup.
• Use case: Daily incremental backups for e-commerce platforms (e.g., Daraz’s order database) to minimize downtime.
• Command:

  -- Cumulative incremental
  RMAN> BACKUP DATABASE INCREMENTAL LEVEL 1;
  -- Differential incremental
  RMAN> BACKUP DATABASE INCREMENTAL FROM TIME 'SYSDATE-1';
  

• Pros/Cons:

  Pros	Cons
  Reduces storage and backup time.	Requires full backup as baseline.
  Faster recovery for recent corruption.	Complex to manage chains.

C. RMAN (Recovery Manager) Backups

• Definition: Oracle’s built-in tool for automated backups, compression, and recovery.
• Key Features:
  • Channel multiplexing: Uses multiple I/O channels for parallel backups.
  • Proxy copies: Backs up files to a staging area before final destination.
  • Compression: Reduces backup size (e.g., BACKUP ... COMPRESSED 2).
• Example Workflow:
  1. Backup:

     RMAN> BACKUP DATABASE PLUS ARCHIVELOG
     >   FORMAT '/backup/%U'
     >   CHANNEL 1: CHANNEL2: TYPE DISK;
     

  2. Restore (after a crash):

     RMAN> RESTORE DATABASE;
     RMAN> RECOVER DATABASE;
     

MERMAID DIAGRAM: RMAN Backup Process

---

2. Restore and Recovery Processes

Recovery in Oracle follows a structured cycle to handle failures (crash, media corruption, user errors).

A. Crash Recovery (Instance Recovery)

• Trigger: Oracle instance fails (e.g., power outage, OS crash).
• Steps:
  1. Detect failure: Oracle checks redo logs for uncommitted transactions.
  2. Apply redo: Rolls forward transactions from redo logs.
  3. Consistency check: Validates datafiles using RECOVER DATABASE.
• Command:

  SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
  

B. Media Recovery (File Corruption)

• Trigger: Datafile corruption (e.g., HR.EMPLOYEES table damaged after a Daraz system update).
• Steps:
  1. Restore the corrupted file from backup:

     RMAN> RESTORE TABLESPACE HR;
     

  2. Recover using redo logs:

     RMAN> RECOVER TABLESPACE HR;
     

• Worked Example: Daraz Order System Recovery
  • Scenario: A Daraz database administrator notices ORDERS table corruption after a failed update.
  • Solution:

    -- Step 1: Restore the corrupted tablespace
    RMAN> RESTORE TABLESPACE ORDERS;
    -- Step 2: Recover using archived redo logs
    RMAN> RECOVER TABLESPACE ORDERS;
    -- Step 3: Verify data integrity
    SQL> SELECT COUNT(*) FROM ORDERS;  -- Should match pre-crash count.
    

C. Point-in-Time Recovery (PITR)

• Definition: Restores the database to a specific point in time (e.g., before a rogue SQL DELETE).
• Requirements:
  • Archived redo logs enabled (LOG_ARCHIVE_DEST).
  • Full backup + incremental backups.
• Command:

  RMAN> RESTORE DATABASE TO TIME 'TO_TIMESTAMP('2023-10-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS')';
  RMAN> RECOVER DATABASE TO TIME 'TO_TIMESTAMP('2023-10-15 14:30:00', 'YYYY-MM-DD HH24:MI:SS')';
  

MERMAID DIAGRAM: Recovery Types

---

3. Backup Policies: Designing a Robust Strategy

A DBA’s backup policy must align with Recovery Point Objective (RPO) and Recovery Time Objective (RTO).

A. The 3-2-1 Rule

• 3 copies of data (primary + 2 backups).
• 2 different media (disk + tape).
• 1 offsite backup (e.g., cloud storage).

Example for a Nepali Bank (e.g., NMB Bank):

• RPO: 15 minutes (lose no more than 15 mins of transactions).
• RTO: 2 hours (restore within 2 hours).
• Policy:
  • Daily full backup (tape).
  • Hourly incremental backups (disk).
  • Offsite replica (cloud storage in Singapore).

B. Oracle-Specific Best Practices

---

4. Advanced Recovery Tools

Oracle provides time-saving recovery features beyond basic RMAN.

A. Flashback Database

• Definition: Enables instantaneous rollback to a past point in time without restoring backups.
• Requirements:
  • Flashback Database enabled (FLASHBACK_DATABASE_ON).
  • Sufficient undo retention (UNDO_RETENTION).
• Steps to Enable:

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

• Worked Example: Kathmandu Traffic Route Correction
  • Scenario: A traffic management system (like NTC’s route optimizer) accidentally updates routes due to a bug.
  • Recovery:

    SQL> SHUTDOWN IMMEDIATE;
    SQL> STARTUP MOUNT;
    SQL> FLASHBACK DATABASE TO TIME 'TO_TIMESTAMP('2023-10-15 10:00:00', 'YYYY-MM-DD HH24:MI:SS')';
    SQL> RECOVER DATABASE;
    SQL> ALTER DATABASE OPEN;
    

B. Flashback Drop

• Definition: Recovers dropped tables/objects without full restores.
• Command:

  SQL> FLASHBACK TABLE HR.EMPLOYEES TO BEFORE DROP;
  

---

5. Disaster Recovery Planning

A comprehensive DR plan includes:

1. Backup strategy (full/incremental/RMAN).
2. Offsite storage (cloud, tape vault).
3. Failover testing (quarterly drills).
4. Documentation (runbooks for DBAs).

Example DR Scenario for NEPSE (Nepal Stock Exchange):

• Risk: Server failure during trading hours.
• Solution:
  • Primary site: Oracle RAC (2-node cluster).
  • Secondary site: Standby database in Chitwan (synchronized via Data Guard).
  • RTO: 10 minutes (failover to standby).
  • RPO: 0 minutes (real-time replication).

---

In the Real World

1. eSewa (Nepali Digital Payment System)

   • Backup Policy: Uses RMAN with multiplexed redo logs to ensure transaction integrity during peak hours (e.g., Dashain/Tihar).
   • Recovery: Flashback Database to revert fraudulent transactions within minutes.

2. Ncell (Telecom Provider)

   • Challenge: Millions of daily call records must survive hardware failures.
   • Solution:
     • Hourly incremental backups to disk.
     • Daily full backups to tape (stored in a secure vault).
     • PITR to recover from billing system errors.

3. Daraz (E-Commerce Platform)

   • Backup Strategy:
     • Order database: Incremental backups every 30 minutes.
     • User data: Full backup nightly + offsite replica.
   • Recovery Example: After a failed inventory update, Daraz uses FLASHBACK TABLE to restore product quantities.

---

Exam Tip

1. Define vs. Explain:

   • Backup = Copying files.
   • Restore = Replacing files.
   • Recovery = Fixing corruption via redo logs.
   • Exam trick: Use the BRR cycle in your answer to show understanding.

2. Commands Are Key:

   • Memorize RMAN syntax for backup/restore/recovery (e.g., BACKUP DATABASE, RESTORE TABLESPACE, RECOVER DATABASE).
   • Worked examples (like Daraz’s order recovery) fetch high marks.

3. Policy Questions:

   • For backup policy questions, structure your answer as:
     1. RPO/RTO goals.
     2. Backup types (full/incremental/RMAN).
     3. Multiplexing/offsite storage.
     4. Testing and documentation.

4. Visuals in Exams:

   • Draw a layered diagram of Oracle files (datafiles, redo logs, control files) when asked about architecture.
   • Use a timeline for PITR (e.g., "Backup at T0, corruption at T1, recover to T0.5").

5. Common Pitfalls:

   • ❌ Forgetting multiplexing for redo logs/control files.
   • ❌ Confusing incremental levels (Level 0 = full, Level 1 = cumulative, Level 2 = differential).
   • ❌ Skipping post-recovery verification (e.g., SELECT COUNT(*) FROM TABLE).

---