Backup & Recovery (RMAN)

Backup and recovery is how you protect an Oracle database against data loss — whether from hardware failure, human error, or software corruption. A backup you cannot restore is worthless. Understanding how Oracle's backup tools work, and practising recovery, is one of the most important responsibilities in database administration.

What Is RMAN?

RMAN (Recovery Manager) is Oracle's built-in tool for backing up, restoring, and recovering Oracle databases. It ships with every Oracle installation and is the recommended approach over manually copying datafiles. RMAN is aware of Oracle's internal structure — it knows which blocks have changed, can compress backups automatically, detects and skips unused blocks, and integrates directly with Oracle's redo logs and archive logs. A manual file copy has none of these capabilities.

• RMAN connects to the target database and the optional recovery catalog — a separate schema that stores RMAN metadata and backup history
• Without a recovery catalog, RMAN stores backup metadata in the database's own control file — simpler but vulnerable if the control file is lost
• RMAN backups are stored in backup sets (RMAN's compressed proprietary format) or image copies (exact copies of datafiles, immediately usable)
• RMAN must run in ARCHIVELOG mode for online backups — in NOARCHIVELOG mode only offline (cold) backups are possible
• ARCHIVELOG mode means Oracle saves every filled redo log to the archive log destination rather than overwriting it — this enables point-in-time recovery

Checking and Enabling ARCHIVELOG Mode

Before taking online backups with RMAN, the database must be in ARCHIVELOG mode. Check the current mode and enable it if needed. Switching to ARCHIVELOG mode requires a brief downtime — the database must be mounted but not open while the mode change is applied.

-- Check the current archive log mode (run in SQL*Plus or SQL Developer as SYSDBA)
SELECT log_mode FROM v$database;
-- To enable ARCHIVELOG mode -- requires SYSDBA privilege
-- Step 1: shut down the database cleanly
SHUTDOWN IMMEDIATE;
-- Step 2: mount the database (control file loaded, datafiles not open)
STARTUP MOUNT;
-- Step 3: switch to ARCHIVELOG mode
ALTER DATABASE ARCHIVELOG;
-- Step 4: open the database for normal use
ALTER DATABASE OPEN;
-- Verify the change
SELECT log_mode FROM v$database;
-- Confirm archiving is active
ARCHIVE LOG LIST;

• SHUTDOWN IMMEDIATE waits for active transactions to finish and performs a clean checkpoint — safer than ABORT for this purpose
• STARTUP MOUNT loads the control file and makes the database available to RMAN and ALTER DATABASE commands without opening it to users
• Once in ARCHIVELOG mode the database must have enough disk space for archive logs — a full archive destination stops the database from operating
• V$DATABASE is a dynamic performance view — querying it requires the SYSDBA or SELECT ANY DICTIONARY privilege

Taking Backups with RMAN

RMAN is invoked from the operating system command line, not from SQL. Once connected to the target database you issue RMAN commands inside its own shell. A full backup copies every used block in every datafile. An incremental backup copies only the blocks that have changed since the last backup of the same or lower level — level 0 is equivalent to a full backup and serves as the base for subsequent level 1 incrementals.

-- Connect to RMAN from the OS command line
-- rman target /
-- The following commands are typed at the RMAN> prompt
-- Full database backup including archived logs -- backs up all used datafiles
BACKUP DATABASE PLUS ARCHIVELOG;
-- Incremental level 0 backup -- the base for an incremental strategy
-- Equivalent to a full backup but tagged for incremental tracking
BACKUP INCREMENTAL LEVEL 0 DATABASE;
-- Incremental level 1 backup -- copies only blocks changed since the last level 0 or 1
BACKUP INCREMENTAL LEVEL 1 DATABASE;
-- Back up only the archived redo logs and delete them after backup
BACKUP ARCHIVELOG ALL DELETE INPUT;
-- List all backups recorded in the control file or recovery catalog
LIST BACKUP SUMMARY;
-- Check all backups for corruption or missing pieces
CROSSCHECK BACKUP;
-- Remove backup records for files that no longer exist on disk
DELETE EXPIRED BACKUP;

• PLUS ARCHIVELOG includes the archived redo logs in the same backup — essential for a complete recovery point
• A level 0 incremental backup looks identical to a full backup but is tracked by RMAN as the base of an incremental chain — use it to start an incremental strategy, not BACKUP DATABASE
• DELETE INPUT removes the archived logs from disk after backing them up — keeps the archive destination from filling up
• CROSSCHECK BACKUP reconciles RMAN's metadata with what is physically present on disk — always run it before relying on a backup for recovery

Restore and Recovery

Restoring puts the physical datafiles back from a backup. Recovering applies redo and archive log changes on top of the restored files to bring the database forward to the desired point in time. These are two separate steps in Oracle — restore first, then recover. A complete recovery brings the database all the way forward to the moment of failure. A point-in-time recovery (PITR) stops at a specific SCN or timestamp — useful for undoing accidental large deletes or data corruption that was committed.

-- Complete recovery after a datafile loss
-- Run from the RMAN> prompt
-- Step 1: restore the missing datafile from the most recent backup
RESTORE DATAFILE 5;
-- Step 2: apply all archived logs to bring the datafile up to date
RECOVER DATAFILE 5;
-- Full database restore and complete recovery (e.g. after total disk failure)
STARTUP MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;
-- Point-in-time recovery: restore and recover to a specific timestamp
-- Stops applying redo at the given time -- useful to undo an accidental mass DELETE
STARTUP MOUNT;
SET UNTIL TIME "TO_DATE('2026-03-07 09:00:00','YYYY-MM-DD HH24:MI:SS')";
RESTORE DATABASE;
RECOVER DATABASE;
-- RESETLOGS is required after incomplete recovery
ALTER DATABASE OPEN RESETLOGS;

• RESTORE copies datafiles from backup — RECOVER applies redo logs on top of them — both steps are required for a working database
• SET UNTIL TIME tells RMAN to stop applying redo at a specific moment — archive logs covering that time range must be available
• RESETLOGS is mandatory after any incomplete recovery — it resets the redo log sequence and starts a new incarnation of the database
• After opening with RESETLOGS, take a new full backup immediately — previous incremental chains are no longer valid
• Test your recovery procedure regularly in a non-production environment — a backup you have never practised restoring from is an untested backup

Retention Policy and Housekeeping

A retention policy tells RMAN how long to keep backups before they are considered obsolete. Without one, old backups accumulate indefinitely and fill your backup storage. RMAN marks backups as obsolete according to the policy but does not delete them automatically — you must run DELETE OBSOLETE to reclaim space. A common strategy is a redundancy of 2 (always keep the two most recent full backups) or a recovery window of 7 days (always keep enough backups to recover to any point in the last seven days).

-- Set a retention policy: keep enough backups to recover within the last 7 days
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
-- Alternatively: always keep the 2 most recent backups
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
-- Show the current RMAN configuration
SHOW ALL;
-- Mark obsolete backups according to the retention policy
-- Does not delete yet -- just flags them
REPORT OBSOLETE;
-- Delete all backups marked as obsolete
DELETE OBSOLETE;
-- Configure automatic backup of the control file after every backup
CONFIGURE CONTROLFILE AUTOBACKUP ON;

• CONFIGURE CONTROLFILE AUTOBACKUP ON ensures the control file is backed up automatically after every RMAN backup and every structural change — critical for recovery if the control file is lost
• A recovery window policy keeps all backups needed to satisfy any restore request within the window — it may keep more than one backup set if a single one is not sufficient
• Run DELETE OBSOLETE as part of a regular maintenance schedule — weekly is typical for most environments
• SHOW ALL prints every RMAN persistent configuration parameter — useful for auditing and documenting the backup strategy

Lesson Summary

Practice Questions

Quiz

Next up — Security Best Practices — How to harden an Oracle database against common threats, including least-privilege access, auditing, encryption, and protecting sensitive data.