Oracle RMAN Backup and Restore Examples
This document provides practical examples of RMAN (Recovery Manager) scripts for performing various backup and restore operations in an Oracle Database environment. These examples cover full database backups, incremental backups, archive log backups, and database restores to a point in time.
RMAN Backup Examples
1. Full/Level 0 Incremental Backup to Tape (SBT_TAPE) with Archive Log Backup and Deletion
This script performs a Level 0 incremental backup of the database, including the current control file, to tape. It then archives the current log, crosschecks existing archive logs, backs up archive logs not yet backed up to tape, and finally deletes archive logs that have been backed up at least once to tape and are older than 1/24th of a day (2.5 minutes).
RUN {
ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL CH2 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL CH3 TYPE 'SBT_TAPE';
ALLOCATE CHANNEL CH4 TYPE 'SBT_TAPE';
-- Perform a Level 0 incremental backup of the database
-- filesperset 1: Each backup set will contain only one datafile.
-- format: Defines the naming convention for backup pieces.
-- include current controlfile: Ensures the latest control file is part of the backup.
backup incremental level 0 filesperset 1 format '%d_LEVEL0_%s_%t_%p.dbf' database include current controlfile;
-- Force an archive log switch to ensure all committed transactions are in an archive log.
sql 'alter system archive log current';
-- Crosscheck archive logs to update their status in the RMAN catalog (e.g., AVAILABLE/EXPIRED).
change archivelog all crosscheck;
-- Backup archive logs that have not been backed up at least once to tape.
backup NOT BACKED UP 1 TIMES archivelog all filesperset 1 format '%d_arch_%s_%t_%p.arc';
-- Delete archive logs that have been backed up at least once to tape and are older than 2.5 minutes.
DELETE ARCHIVELOG ALL BACKED UP 1 TIMES TO DEVICE TYPE sbt COMPLETED BEFORE 'SYSDATE-1/24';
-- Release the allocated channels.
RELEASE CHANNEL CH1;
RELEASE CHANNEL CH2;
RELEASE CHANNEL CH3;
RELEASE CHANNEL CH4;
}
2. Archive Log Backup and Deletion to Tape (SBT_TAPE)
This script focuses solely on backing up and deleting archive logs to tape. It's often run more frequently than full database backups.
RUN {
ALLOCATE CHANNEL CH1 TYPE 'SBT_TAPE' ;
ALLOCATE CHANNEL CH2 TYPE 'SBT_TAPE' ;
ALLOCATE CHANNEL CH3 TYPE 'SBT_TAPE' ;
ALLOCATE CHANNEL CH4 TYPE 'SBT_TAPE' ;
-- Crosscheck archive logs to update their status in the RMAN catalog.
change archivelog all crosscheck;
-- Backup archive logs that have not been backed up at least once to tape.
backup NOT BACKED UP 1 TIMES archivelog all filesperset 1 format '%d_arch_%s_%t_%p.arc' ;
-- Delete archive logs that have been backed up at least once to tape and are older than 2.5 minutes.
-- NOPROMPT: Suppresses the confirmation prompt for deletion.
DELETE NOPROMPT ARCHIVELOG ALL BACKED UP 1 TIMES TO DEVICE TYPE sbt COMPLETED BEFORE 'SYSDATE-1/24';
-- Release the allocated channels.
RELEASE CHANNEL CH1;
RELEASE CHANNEL CH2;
RELEASE CHANNEL CH3;
RELEASE CHANNEL CH4;
}
3. Level 1 Incremental Backup to Disk (Compressed)
This example performs a Level 1 incremental backup (only blocks changed since the last Level 0 or Level 1 backup) to disk, with compression.
run {
-- Allocate disk channels. Note: Each channel should ideally have a unique format string
-- if you intend to write to different physical locations or distinct backup pieces.
-- The example below uses the same format for all, which might overwrite or lead to issues
-- if not managed carefully (e.g., using %U for unique filenames).
ALLOCATE CHANNEL backup_disk1 TYPE DISK FORMAT '/migration/level0_bkp/db_%s_%t_%p.dbf';
ALLOCATE CHANNEL backup_disk2 TYPE DISK FORMAT '/migration/level0_bkp/db_%s_%t_%p.dbf'; -- Corrected channel name
ALLOCATE CHANNEL backup_disk3 TYPE DISK FORMAT '/migration/level0_bkp/db_%s_%t_%p.dbf'; -- Corrected channel name
ALLOCATE CHANNEL backup_disk4 TYPE DISK FORMAT '/migration/level0_bkp/db_%s_%t_%p.dbf'; -- Corrected channel name
-- Perform a Level 1 incremental backup.
-- as compressed backupset: Enables compression for the backup.
-- filesperset 1: Each backup set contains one datafile.
-- tag: Assigns a tag to the backup set for easier identification.
-- include current controlfile: Includes the control file in the backup.
backup as compressed backupset incremental level 1
filesperset 1 tag 'TEST_STANDARD_LEVEL1' database include current controlfile;
-- Release the allocated channels.
RELEASE CHANNEL backup_disk1;
RELEASE CHANNEL backup_disk2; -- Corrected channel name
RELEASE CHANNEL backup_disk3; -- Corrected channel name
RELEASE CHANNEL backup_disk4; -- Corrected channel name
}
Correction Note: In the original allocate channel commands for disk backup, all channels were named backup_disk1. For parallel operation, they should be distinct (e.g., backup_disk1, backup_disk2, etc.) and released accordingly. The example above has been corrected for clarity.
RMAN Restore Examples
1. Restoring Control File from Tape (SBT_TAPE)
This script is used to restore the control file from a tape backup. This is typically done when the control file is lost or corrupted, and the database cannot be mounted.
run
{
ALLOCATE CHANNEL ch1 DEVICE TYPE sbt_tape;
-- Send environment variables to the media management software (MMS).
-- NSR_SERVER, NSR_CLIENT, NSR_DATA_VOLUME_POOL are NetWorker specific variables.
send 'NSR_ENV=(NSR_SERVER=*****,NSR_CLIENT=*****,NSR_DATA_VOLUME_POOL=*****)'; -- Replace with actual values
-- Restore the control file using its backup piece name or tag.
restore controlfile from 'MYSID_c-569933170-20171012-03'; -- Replace with actual backup piece name/tag
RELEASE CHANNEL ch1;
}
2. Full Database Restore to a Point in Time (using Shell Script)
This section shows how to automate a full database restore to a specific point in time using a shell script to invoke RMAN.
invoke_restore_MYSID.ksh (Shell Script):
export ORACLE_SID=MYSID # Set the Oracle SID
export ORACLE_HOME=/applic/oracle/product/11.2.0/dbhome_1 # Set the Oracle Home
export PATH=$PATH:$ORACLE_HOME/bin # Add Oracle binaries to PATH
# Execute RMAN, targeting the database, logging output, and using a command file.
rman target / log=MYSID_restore_$(date '+%Y%m%d_%H%M%S').log cmdfile=restore_MYSID.rcv
restore_MYSID.rcv (RMAN Command File for Restore):
run
{
ALLOCATE CHANNEL ch1 DEVICE TYPE sbt_tape;
ALLOCATE CHANNEL ch2 DEVICE TYPE sbt_tape;
ALLOCATE CHANNEL ch3 DEVICE TYPE sbt_tape;
ALLOCATE CHANNEL ch4 DEVICE TYPE sbt_tape;
ALLOCATE CHANNEL ch5 DEVICE TYPE sbt_tape;
ALLOCATE CHANNEL ch6 DEVICE TYPE sbt_tape;
-- Set the point in time for the restore operation.
set until time "to_date('10/12/2017 15:01:39', 'mm/dd/yyyy hh24:mi:ss')";
-- Send environment variables to the media management software (MMS).
send 'NSR_ENV=(NSR_SERVER=*****,NSR_CLIENT=*****,NSR_DATA_VOLUME_POOL=*****)'; -- Replace with actual values
-- Set new names for datafiles if relocating the database or restoring to a different storage.
-- This example sets all datafiles to an ASM diskgroup named +DATA_DG.
set newname for database to '+DATA_DG';
-- Restore the entire database.
restore database;
-- Update the control file with the new datafile locations after restore.
switch datafile all;
RELEASE CHANNEL ch1;
RELEASE CHANNEL ch2;
RELEASE CHANNEL ch3;
RELEASE CHANNEL ch4;
RELEASE CHANNEL ch5;
RELEASE CHANNEL ch6;
}
3. Database Recovery to a Point in Time (using Shell Script)
This script is used after a database restore to apply archive logs and recover the database to the specified point in time.
recover_MYSID.rcv (RMAN Command File for Recover):
run
{
ALLOCATE CHANNEL ch1 DEVICE TYPE sbt_tape;
ALLOCATE CHANNEL ch2 DEVICE TYPE sbt_tape;
ALLOCATE CHANNEL ch3 DEVICE TYPE sbt_tape;
ALLOCATE CHANNEL ch4 DEVICE TYPE sbt_tape;
ALLOCATE CHANNEL ch5 DEVICE TYPE sbt_tape;
ALLOCATE CHANNEL ch6 DEVICE TYPE sbt_tape;
-- Set the point in time for the recovery operation. This must match the restore point.
set until time "to_date('10/12/2017 15:01:39', 'mm/dd/yyyy hh24:mi:ss')";
-- Send environment variables to the media management software (MMS).
send 'NSR_ENV=(NSR_SERVER=*****,NSR_CLIENT=*****,NSR_DATA_VOLUME_POOL=*****)'; -- Replace with actual values
-- Recover the database by applying necessary archive logs.
recover database;
RELEASE CHANNEL ch1;
RELEASE CHANNEL ch2;
RELEASE CHANNEL ch3;
RELEASE CHANNEL ch4;
RELEASE CHANNEL ch5;
RELEASE CHANNEL ch6;
}
4. Restore Specific Archive Log Range
This command can be used to restore a specific range of archive logs.
restore archivelog from logseq 330570 until logseq 330590;
This restores archive logs with sequence numbers from 330570 up to (and including) 330590.
