Backup, Recovery and High Availability

Backup as an Economic Risk Management Exercise

Planning for backup and recovery is fundamentally an exercise in minimizing expected loss. Let $V_d$ denote the economic value of the database information. The probability of a catastrophic data loss event is $p(f, I_b)$, where $I_b$ is the DBA's investment in backup infrastructure. Assuming $\frac{\partial p}{\partial I_b} < 0$, the DBA seeks to minimize the total cost $TC = I_b + p(f, I_b) \cdot V_d$. The first-order condition yields:

$$-\frac{\partial p}{\partial I_b} = \frac{1}{V_d}$$

The DBA should continue investing in backup and recovery mechanisms until the marginal reduction in the probability of failure equals the inverse of the data's value. For high-value transactional databases (large $V_d$), the optimal strategy demands heavy investment in zero-data-loss architectures.

Backup Terminology

Whole vs. Partial Backup: A whole backup includes all datafiles and the control file. A partial backup includes only a subset (e.g., a specific tablespace or datafile).

Full vs. Incremental Backup: A full backup copies all used blocks in the datafiles. An incremental backup copies only blocks that have changed since a previous backup. Incremental backups are further classified as:

• Level 0: A baseline backup of all used blocks — functionally equivalent to a full backup but serves as the base for subsequent incremental backups.
• Level 1 Differential: Copies blocks changed since the most recent Level 0 or Level 1 backup.
• Level 1 Cumulative: Copies blocks changed since the most recent Level 0 backup.

Cold vs. Hot Backup: A cold backup (consistent backup) is taken while the database is shut down (NORMAL, IMMEDIATE, or TRANSACTIONAL). All datafiles, control files, and redo logs are consistent. A hot backup (inconsistent backup) is taken while the database is open and operational. The database must be in ARCHIVELOG mode, and the backup is made consistent during recovery by applying archived redo logs.

Logical vs. Physical Backup: Physical backups copy the physical database files (datafiles, control files, redo logs). Logical backups extract the logical content (table definitions, data rows) using utilities like Data Pump Export.

Oracle Recovery Manager (RMAN)

RMAN is Oracle's recommended backup and recovery tool. It maintains a repository of backup metadata, automates backup scheduling and retention, validates backups for corruption, and provides incremental backup capabilities. RMAN can back up to disk or directly to tape (via the Media Management Layer).

Key RMAN Benefits: Block-level validation during backup (detects physical corruption), incremental backups with block change tracking, backup compression and encryption, automatic backup of control file and SPFILE, parallel backup and restore operations, and integration with Data Guard and RAC.

-- RMAN configuration
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
RMAN> CONFIGURE BACKUP OPTIMIZATION ON;
RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;
RMAN> CONFIGURE DEVICE TYPE DISK PARALLELISM 4;

-- Full database backup
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

-- Incremental backup
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

-- Restore and recover the entire database
RMAN> STARTUP MOUNT;
RMAN> RESTORE DATABASE;
RMAN> RECOVER DATABASE;
RMAN> ALTER DATABASE OPEN;

RPO and RTO

Two critical metrics define recovery objectives:

• Recovery Point Objective (RPO): The maximum acceptable amount of data loss measured in time. An RPO of 15 minutes means the database can lose at most 15 minutes of transactions. Achieved through frequent backups and redo log archiving.
• Recovery Time Objective (RTO): The maximum acceptable time to restore the database to operational status. An RTO of 4 hours means recovery must complete within 4 hours. Achieved through sufficient hardware, parallelism, and standby databases.

The fundamental equation for archive storage: let the database generate redo at rate $R$ (MB/day), and the recovery window policy require $W$ days. The minimal archive storage is $S_{\text{min}} = R \times W$. The number of archive files per day is $N_{\text{daily}} = \lceil R / S_{\text{log}} \rceil$, yielding actual storage $N_{\text{daily}} \times S_{\text{log}} \times W$.

Backup Guidelines

• Maintain a redundancy set: keep at least two copies of each backup on different media or locations.
• Use the Fast Recovery Area (FRA) for automated disk-based backup management.
• Validate backups regularly by performing restore tests.
• Back up the control file and SPFILE automatically (RMAN's CONFIGURE CONTROLFILE AUTOBACKUP ON).
• Implement a backup retention policy: redundancy-based (e.g., keep 2 backups) or recovery-window-based (e.g., retain backups needed for 7-day recovery).
• Document the backup strategy and recovery procedures.

Oracle databases are susceptible to several categories of failure:

• Statement Failure: A single SQL statement fails due to a logical error (e.g., invalid data, constraint violation, insufficient privileges). Oracle automatically rolls back the statement. No DBA intervention required.
• User Process Failure: A user's client application crashes or disconnects abnormally. PMON automatically detects the failure, rolls back uncommitted transactions, and releases locks.
• Network Failure: The network connection between client and server is interrupted. PMON cleans up the abandoned session. The client may need to reconnect.
• User Error: A user accidentally drops a table, deletes rows, or modifies data incorrectly. Requires logical recovery techniques: Flashback Query, Flashback Table, Flashback Drop (Recycle Bin), or point-in-time recovery.
• Instance Failure: The Oracle instance crashes due to a hardware fault, power outage, or SHUTDOWN ABORT. SMON performs automatic instance recovery at the next startup, rolling forward committed changes from redo and rolling back uncommitted transactions. No DBA intervention required beyond restarting the instance.
• Media Failure: A physical disk failure corrupts or makes inaccessible datafiles, control files, or redo logs. Requires DBA intervention: restore from backup and apply archived redo logs to recover to the desired point in time.

The instance recovery time $T_{cr}$ is bounded by the redo generated since the last checkpoint: $T_{cr} \approx \frac{R_{\text{gen}}}{\text{redo\_apply\_rate}}$, where $R_{\text{gen}}$ is the redo bytes generated since the last checkpoint and the redo apply rate depends on I/O throughput and CPU speed.