RMAN Backup Scripts


run {
 allocate channel d1 type disk;
 allocate channel d2 type disk;
 allocate channel d3 type disk;
 incremental level 0
 tag hot_db_bk_level0
 filesperset 5
 # recommended format
format '/backup/$ORACLE_SID/%d_bk_%s_%p_%t'
 sql 'alter system archive log current';
  # backup all archive logs
 filesperset 20
 format '/backup/$ORACLE_SID/%d_al_%s_%p_%t'
 (archivelog all
 delete all input);

Format Description
%a Current database activation id
%A Zero-filled activation ID
%c The copy number of the backup piece within a set of duplexed backup pieces.bMaximum value is 256
%d Database name
%D Current day of the month from the Gregorian calendar in format DD
%e Archived log sequence number
%f Absolute file number
%F Combines the DBID, day, month, year, and sequence into a unique and repeatable generated name
%h Archived redo log thread number
%M Month in the Gregorian calendar in the format MM
%n Database name, padded on the right with x characters to a total length of eight characters
%N Tablespace name. Only valid when backing up datafiles as image copies.
%p Piece number within the backup set. This value starts at 1 for each backup set and is incremented by 1 for each backup piece created. If a PROXY is specified, the %p variable must be included in the FORMAT string either explicitly or implicitly within %U.
%r Resetlogs ID
%s Backup set number. This number is a counter in the control file that is incremented for each backup set. The counter value starts at 1 and is unique for the lifetime of the control file. If you restore a backup control file, then duplicate values can result. CREATE CONTROLFILE initializes the counter at 1.
%S Zero-filled sequence number
%t Backup set time stamp, a 4-byte value derived as the number of seconds elapsed since a fixed reference time. The combination of %s and %t can be used to form a unique name for the backup set.
%T Year, month, and day in the Gregorian calendar in the format: YYYYMMDD
%u An 8-character name constituted by compressed representations of the backup set or image copy number and the time the backup set or image copy was created
%U A system-generated unique filename (default). %U is different for image copies and backup pieces. For a backup piece, %U is a shorthand for %u_%p_%c and guarantees uniqueness in generated backup filenames. For an image copy of a datafile, %U means the following:
For an image copy of an archived redo log, %U means the following:
For an image copy of a control file, %U means the following:
%Y Year in this format: YYYY
%% Percent (%) character. For example, %%Y translates to the string %Y

Create Consistent Backup Script

posted Mar 18, 2011, 9:21 AM by Sachchida Ojha   [ updated Apr 5, 2011, 12:19 PM ]

You can create a RMAN script to generate a database backup without a recovery catalog or with a recovery catalog. You can set the autocontrolfile autobackup feature during configuration so the backup controlfile is used as the catalog to avoid putting creating a separate RMAN database to use its catalog.

connect target;
run {
shutdown immediate;
startup mount pfile=’/opt/app/oracle/admin/ORACLE_SID/pfile/initORCLSID.ora’;
backup incremental level 0 database format ‘/opt/app/oracle/oradata/ORCLSID/backup/%d_bckup_%U’ tag=”closed_bkup”;
startup pfile=’/opt/app/oracle/admin/ORACLE_SID/pfile/initORCLSID.ora’;

RMAN> list summary;
RMAN> list backup summary;
RMAN> list backup by file;

If you use for example 2 channels for your backup you’ll see 2 backupsets in the earlier query if you run that query again because RMAN will split the backup across 2 channels which means there will be 2 backupsets created plus there will be a 3rd controlfile backup that will be generated by the CONTROLFILE AUTOBACKUP feature.

A controlfile autobackup will generate a backup controlfile for each of the following commands if they are executed:


To determine which datafiles need a backup execute the following command:

RMAN> report need backup;

To check and see if any RMAN Backupsets are obsolete and can be removed (deleted), important if you are generating disk backupsets and you don’t have unlimited disk space available to you.

RMAN> report obsolete;

SQL> select recid, stamp, completion_time, incremental_level from v$backup_set;

———- ———- ————— —————–
74 669457740 30-OCT-08

118 669647764 01-NOV-08 0
119 669647775 01-NOV-08

column handle format a32
column tag format a18
select recid, set_stamp, tag, status, handle
from v$backup_piece
order by set_stamp

———- ———- —————— — ——————————–
93 669465816 TAG20081030T104336 A +PS90HR_DISK3/ps90hrqa/autobacku

94 669501607 TAG20081030T204007 A /dbbackup/PS90HRQA/al_t669501607

95 669501610 TAG20081030T204010 A +PS90HR_DISK3/ps90hrqa/autobacku

96 669502225 TAG20081030T205024 A +PS90HR_DISK2/ps90hrqa/backupset

97 669502227 TAG20081030T205027 A +PS90HR_DISK3/ps90hrqa/autobacku

100 669502244 PS90HRQA A /opt/app/oracle/admin/backup/PS9

101 669502244 PS90HRQA A /opt/app/oracle/admin/backup/PS9

98 669502245 PS90HRQA A /opt/app/oracle/admin/backup/PS9

Recover Database Using an Autobackup of the Controlfile without a Recovery Catalog

Set the NLS_LANG environment variable to its proper setting depending on the values you created your database with. Example:


sqlplus> startup nomount;

Start RMAN but do not connect to the target database.

$ rman

Set the database identifier for the target database with the SET DBID command. RMAN displays the DBID whenever you connect to the target. You can also get it by running LIST or by querying the catalog.

Recovery Manager: Release – Production on Sat Nov 1 20:57:08 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: PS90HRDV (DBID=2173713801)

RMAN> SET DBID 2173713801;

You can use log files to determine the DBID also, every time RMAN connects to the database the DBID is displayed as shown above, if the database is open or mounted.

Connect to the target database:


Restore the backup controlfile, then perform the recovery by carrying out the following steps:

Optionally, specify the most recent backup time stamp that RMAN can use when searching for a controlfile autobackup to restore.

If a non-default format was used to create the control file, then specify a non-default format for the restore of the control file.

If the channel that created the control file autobackup was device type sbt (tape), then you must allocate one or more sbt (tape) channels. Because you don’t have a repository available you can’t use the automatic channels. If the autobackup was created on a disk channel, however, then you do not need to manually allocate a disk channel.

Restore the autobackup of the control file, optionally set the maximum number of days backward that RMAN can search (up to 366) and the initial sequence number that is should use in its search for the first day.

Mount the database, and because the repository is now available any automatic channels that you configured are now also available.

If the online logs are inaccessible, then restore and recover the database as described in the incomplete restore and recovery post. You must first terminate recovery by setting the UNTIL clause to a time, log sequence number or SCN before the online redo logs are processes. If the online logs are useable, then restore and recover the database as described in the complete restore and recovery post.

In the following example, the online redo logs have been lost. This example limits the restore of the control file autobackup, then performs the recovery of the database to log sequence 13456, which is the most recent archived log:

run {
set controlfile autobackup format for device type disk to ‘/opt/app/oracle/oradata/ORCLSID/backup/%F’;
allocate channel d1 device type disk;
restore controlfile from autobackup
maxseq 5 # start at sequence 5 and count down
maxdays 5; # start at UNTIL TIME and search back 5 days
mount database;

RMAN> run {
restore database;
recover database;
alter database open resetlogs;

Backup the database after resetlogs.

1-1 of 1