Backend interview prep

Start

Oracle Backup and Recovery Interview Questions

Master Oracle Backup and Recovery with interview-focused questions covering RMAN, Physical Backup, Logical Backup, Full Backup, Incremental Backup, Archived Redo Logs, Flashback Database, Flashback Table, PITR, Control Files, Media Recovery, and enterprise disaster recovery best practices.

Introduction

Backup and Recovery is one of the most critical responsibilities of an Oracle DBA.

Hardware failures, accidental deletions, software bugs, ransomware attacks, and natural disasters can all result in data loss.

Oracle provides a comprehensive backup and recovery solution through

  • RMAN (Recovery Manager)
  • Archived Redo Logs
  • Flashback Technology
  • Data Guard
  • Point-in-Time Recovery (PITR)

A solid backup strategy ensures

  • Business Continuity
  • Disaster Recovery
  • High Availability
  • Regulatory Compliance

Oracle Backup and Recovery Architecture

flowchart LR

OracleDatabase["Oracle Database"] --> RMAN

RMAN --> BackupFiles

OracleDatabase["Oracle Database"] --> RedoLogs

RedoLogs --> ArchiveLogs

ArchiveLogs --> Recovery

BackupFiles --> Recovery

1. What is Oracle Backup?

Answer

An Oracle Backup is a copy of database files that can be used to restore data after failure.

A backup protects

  • Data Files
  • Control Files
  • Redo Log Metadata
  • Archived Redo Logs
  • Server Parameter Files (SPFILE)

2. Why is Backup important?

Without backups

Disk Failure

↓

Permanent Data Loss

With backups

Failure

↓

Restore

↓

Recover

↓

Database Online

3. What is Oracle Recovery?

Recovery is the process of restoring the database to a consistent state after failure.

Recovery uses

  • Backup Files
  • Archived Redo Logs
  • Online Redo Logs

Backup and Recovery Workflow

flowchart LR

Failure --> Restore --> Recover --> OpenDatabase

4. What is RMAN?

RMAN (Recovery Manager) is Oracle's backup and recovery utility.

Features

  • Physical Backups
  • Incremental Backups
  • Block Recovery
  • Compression
  • Backup Validation
  • Automatic Recovery

5. Why use RMAN?

Benefits

  • Oracle-aware backups
  • Faster recovery
  • Incremental backups
  • Block-level recovery
  • Backup compression
  • Backup validation

RMAN Architecture

flowchart LR

RMAN --> OracleDatabase

RMAN --> BackupPieces

BackupPieces --> Disk

BackupPieces --> Tape

6. What is a Physical Backup?

A Physical Backup copies Oracle database files.

Includes

  • Data Files
  • Control Files
  • Archived Logs
  • SPFILE

Used by RMAN.

7. What is a Logical Backup?

Logical Backup exports

database objects

instead of physical files.

Tools

  • Data Pump Export (expdp)
  • Data Pump Import (impdp)

Useful for

  • Migration
  • Object Recovery

Physical vs Logical Backup

Physical Backup Logical Backup
Copies Files Copies Objects
Faster Recovery Easier Migration
RMAN Data Pump

8. What is a Full Backup?

Copies the entire database.

Includes

  • All Data Files
  • Control Files
  • SPFILE

Usually scheduled weekly.

9. What is an Incremental Backup?

Backs up only

changed blocks

since the previous backup.

Benefits

  • Smaller Backup Size
  • Faster Backup
  • Less Storage

Full vs Incremental Backup

Full Incremental
Entire Database Changed Blocks Only
Larger Smaller
Slower Faster

10. What are Archived Redo Logs?

Archived Redo Logs contain completed redo log files.

Used for

  • Media Recovery
  • Point-in-Time Recovery
  • Data Guard

Archive Log Flow

flowchart LR

RedoLog --> ARCn --> ArchiveLog --> Recovery

11. What is ARCHIVELOG mode?

ARCHIVELOG mode copies filled redo logs into archive log files.

Required for

  • RMAN Recovery
  • PITR
  • Data Guard
  • Online Backups

12. What is NOARCHIVELOG mode?

Redo logs are overwritten after reuse.

Recovery options are limited.

Suitable only for

  • Development
  • Testing

Not recommended for production.

ARCHIVELOG vs NOARCHIVELOG

ARCHIVELOG NOARCHIVELOG
Online Backup Offline Backup
Full Recovery Limited Recovery
Production Development

13. What is Flashback Database?

Flashback Database rewinds

the entire database

to a previous point in time

without restoring backups.

Useful after

  • User Errors
  • Bad Deployments

Flashback Database

flowchart LR

CurrentTime --> FlashbackLogs --> PreviousTime

14. What is Flashback Table?

Recovers

one table

to a previous point in time.

Example

FLASHBACK TABLE employees

TO TIMESTAMP

(SYSTIMESTAMP - INTERVAL '10' MINUTE);

15. What is Flashback Query?

Retrieve historical data without restoring backups.

Example

SELECT *

FROM employees

AS OF TIMESTAMP

(SYSTIMESTAMP - INTERVAL '5' MINUTE);

16. What is Point-in-Time Recovery (PITR)?

Recovers the database

to a specific time,

SCN,

or log sequence.

Useful when

accidental data deletion occurs.

PITR Workflow

flowchart LR

Backup --> Restore --> ArchiveLogs --> TargetTime --> Database

17. What is Complete Recovery?

Restores

all committed transactions

using archived redo logs.

Database returns to the latest consistent state.

18. What is Incomplete Recovery?

Stops recovery

before the latest transaction.

Used when

corrupted changes

must be skipped.

19. What is Control File?

Control File stores

  • Database Name
  • Data File Information
  • Checkpoints
  • Backup Metadata
  • Redo Log Information

Database cannot start without it.

20. Why backup the Control File?

If the control file is lost,

Oracle cannot locate

database files.

Always include

control file backups.

21. What is SPFILE?

Server Parameter File

stores

database initialization parameters.

RMAN can back up SPFILE automatically.

22. What is Media Recovery?

Recovery after

physical storage failure.

Uses

  • Backup
  • Archive Logs
  • Redo Logs

23. What is Block Media Recovery?

RMAN restores

only damaged blocks

instead of the entire data file.

Benefits

  • Faster Recovery
  • Minimal Downtime

24. How do you validate backups?

RMAN validates backups without restoring them.

Example

RMAN>

VALIDATE DATABASE;

25. Banking Example

Production database

↓

Nightly Incremental Backup

↓

Weekly Full Backup

↓

Archive Logs Every Few Minutes

↓

Fast Recovery

26. E-Commerce Example

Accidental order deletion

↓

Flashback Table

↓

Restore Data

↓

No Downtime

27. Production Disaster Example

Disk Failure

↓

Restore Full Backup

↓

Apply Incremental Backup

↓

Apply Archive Logs

↓

Open Database

28. Common Backup Problems

  • ARCHIVELOG disabled
  • Missing control file backup
  • Corrupted backup
  • Full backup never tested
  • Archive log deletion
  • No recovery documentation

29. Enterprise Backup Strategy

Typical schedule

Weekly Full Backup

↓

Daily Incremental Backup

↓

Archive Logs Every Few Minutes

↓

Monthly Restore Test

Backup Strategy

flowchart TD

WeeklyFullBackup --> DailyIncremental --> ArchiveLogs --> RecoveryTest

Enterprise Best Practices

  • Use RMAN for production backups.
  • Enable ARCHIVELOG mode.
  • Back up the control file and SPFILE.
  • Schedule regular full and incremental backups.
  • Validate backups periodically.
  • Test recovery procedures regularly.
  • Store backups offsite or in cloud storage.
  • Monitor archive log generation.
  • Document disaster recovery procedures.
  • Automate backup verification and alerting.

Quick Revision

Topic Key Point
Backup Copy of Database
Recovery Restore Database
RMAN Oracle Backup Utility
Physical Backup Database Files
Logical Backup Export Objects
Full Backup Entire Database
Incremental Backup Changed Blocks
Archive Logs Redo History
Flashback Database Restore Whole Database
Flashback Table Restore One Table
PITR Recover to Specific Time
Control File Database Metadata
Media Recovery Recover After Disk Failure

Interview Tips

Interviewers frequently ask

  • What is RMAN?
  • Physical vs Logical Backup.
  • Full vs Incremental Backup.
  • What is ARCHIVELOG mode?
  • Flashback Database vs Flashback Table.
  • What is PITR?
  • Why back up the control file?
  • What is Media Recovery?
  • How do you validate RMAN backups?
  • Describe your production backup strategy.

A strong interview explanation is:

"In production, Oracle databases typically run in ARCHIVELOG mode with RMAN managing backups. A common strategy is weekly full backups, daily incremental backups, continuous archived redo log generation, and periodic recovery testing. Features like Flashback Database and Flashback Table provide rapid recovery from user errors, while RMAN enables efficient media recovery after hardware failures."

Summary

Oracle provides a comprehensive backup and recovery framework through RMAN, ARCHIVELOG mode, Flashback Technology, Point-in-Time Recovery, and Media Recovery. These capabilities enable organizations to recover quickly from hardware failures, accidental data loss, and disasters while minimizing downtime.

Understanding backup strategies, recovery techniques, RMAN operations, archived redo logs, control files, and disaster recovery planning is essential for Oracle DBAs, Database Engineers, Performance Engineers, and Solution Architects responsible for enterprise Oracle environments.