Oracle RMAN Database Restore and Recovery Guide

calendar Feb 4, 2026 / · 6 min read · Oracle RMAN Database Recovery Backup DBA SQL Point-in-Time Recovery Controlfile  ·
Share on: linkedin copy

Oracle RMAN Database Restore and Recovery Guide

Purpose

This guide provides complete Oracle RMAN (Recovery Manager) scripts for restoring and recovering Oracle databases in different scenarios. These scripts help database administrators perform full database recovery, point-in-time recovery (PITR), and verify controlfile backup records using RMAN commands. The scripts use parallel channel allocation to optimize backup and restore operations by running multiple channels simultaneously.

Overview of Database Restore and Recovery

RMAN provides automated database restoration that handles three main recovery scenarios: full database recovery from complete loss, point-in-time recovery to restore database state before corruption occurred, and partial recovery when archive logs already exist on the system. Each scenario requires different startup modes and recovery steps to ensure successful database restoration.

Breakdown of Code

Full Restore and Recovery

Code:

 1startup nomount;
 2run {
 3  allocate channel  t1 type disk;
 4  allocate channel  t2 type disk;
 5  allocate channel  t3 type disk;
 6  allocate channel  t4 type disk;
 7  restore controlfile;
 8  restore archivelog all;
 9  alter database mount;
10  restore database;
11  recover database;
12}
13sql 'alter database open resetlogs';

Explanation:

  • startup nomount: Starts the Oracle instance without mounting the database, which is required when the controlfile needs to be restored from backup
  • allocate channel t1-t4 type disk: Creates four parallel server processes (channels) that perform backup and restore operations to disk storage, improving performance by distributing I/O operations
  • restore controlfile: Recovers the controlfile from the most recent backup, which contains critical database structure information
  • restore archivelog all: Restores all archived redo logs needed for database recovery
  • alter database mount: Mounts the database using the restored controlfile, making datafiles accessible for restoration
  • restore database: Restores all datafiles from the backup to their original locations
  • recover database: Applies archived redo logs to bring the database to a consistent state
  • sql 'alter database open resetlogs': Opens the database and creates a new redo log sequence, which is required after controlfile restoration or incomplete recovery

Point-in-Time Recovery (PITR) with Archive Log Restoration

Code:

 1startup nomount;
 2run {
 3  set until time ="to_date('30/08/2006 12:00','dd/mm/yyyy hh24:mi')";
 4  allocate channel  t1 type disk;
 5  allocate channel  t2 type disk;
 6  allocate channel  t3 type disk;
 7  allocate channel  t4 type disk;
 8  restore controlfile;
 9  restore archivelog all;
10  alter database mount;
11  restore database;
12  recover database;
13}
14sql 'alter database open resetlogs';

Explanation:

  • set until time: Specifies the target recovery point, allowing recovery to a specific date and time before corruption or data loss occurred
  • The recovery process restores the database state to exactly 30/08/2006 at 12:00 PM, discarding all changes after that timestamp
  • This scenario is useful when you need to recover from logical corruption, accidental data deletion, or application errors
  • All other steps follow the same pattern as full recovery, but with time-based limitation

Point-in-Time Recovery with Existing Archive Logs

Code:

 1startup mount;
 2run {
 3  set until time ="to_date('08/02/2007 14:00','dd/mm/yyyy hh24:mi')";
 4  allocate channel  t1 type disk;
 5  allocate channel  t2 type disk;
 6  allocate channel  t3 type disk;
 7  allocate channel  t4 type disk;
 8  restore database;
 9  recover database;
10}
11sql 'alter database open resetlogs';

Explanation:

  • startup mount: Starts the database directly in mount mode because the controlfile already exists and is valid
  • This scenario assumes archive logs are already available in the archive log destination
  • Skips controlfile and archive log restoration steps, saving time during recovery
  • Useful for quick recovery when only datafiles need restoration

Simple Recovery with Existing Datafiles

Code:

1startup mount;
2run {
3  allocate channel  t1 type disk;
4  recover database;
5}

Explanation:

  • This minimal recovery script applies available archive logs to existing datafiles
  • Used when database crashed cleanly and only needs redo log application
  • Single channel allocation is sufficient for small-scale recovery operations
  • No resetlogs required because no restore operation occurred

Controlfile Backup Verification Script

Code:

 1set pages 999 lines 100
 2col name format a60
 3break on set_stamp skip 1
 4select set_stamp
 5,to_char(ba.completion_time, 'HH24:MI DD/MM/YY') finish_time
 6,df.name
 7from v$datafile df
 8,v$backup_datafile ba
 9where df.file# = ba.file#
10and ba.file# != 0
11order by set_stamp, ba.file#
12/

Explanation:

  • v$datafile: Dynamic performance view containing information about all datafiles in the database
  • v$backup_datafile: View displaying backup information for datafiles and controlfiles stored in backup sets
  • set_stamp: Unique identifier grouping datafiles backed up together in the same backup set
  • completion_time: Timestamp when each datafile backup completed
  • ba.file# != 0: Filters out controlfile records (which have file# = 0) to show only datafile backups
  • This query helps verify which datafiles were backed up together and when, useful for recovery planning

Key Points

  • Always use startup nomount when restoring controlfiles from backup
  • Allocating multiple channels (typically 2-4) significantly improves restore and recovery performance
  • The resetlogs option is mandatory after restoring from backup controlfile or performing incomplete recovery
  • Point-in-time recovery requires specifying exact target time using set until time with proper date format
  • Archive logs must be available (either restored or already present) for successful database recovery
  • Take immediate full backup after opening database with resetlogs to ensure future recoverability
  • The number of channels should match available disk devices or CPU cores for optimal performance
  • Verify backup records using v$backup_datafile before attempting recovery operations

Insights and Best Practices

Recovery strategy selection depends on whether controlfile exists and archive logs are available. Full recovery from complete database loss requires all restoration steps starting from nomount mode, while partial recovery can skip controlfile restoration. Point-in-time recovery is essential when recovering from logical corruption or user errors, but requires careful selection of recovery target time.

Channel allocation directly impacts recovery performance - using multiple parallel channels reduces total recovery time by distributing I/O operations across multiple processes. However, allocating too many channels can overwhelm system resources, so match channel count to available hardware.

The resetlogs operation creates a new database incarnation with fresh redo log sequence numbers. This prevents confusion between redo logs generated before and after recovery, but requires immediate full backup afterward. Without post-resetlogs backup, future recovery becomes impossible because previous backups belong to different database incarnation.

Archive log management is critical for recovery success - ensure sufficient space in archive destination and verify archive logs exist for entire recovery window. Missing archive logs cause incomplete recovery and potential data loss.

References

Posts in this series