VirtualObjectives

Oracle RMAN Backup Scripting

The following describes how you should configure your RMAN backup script. This is a real world example of using RMAN to backup a two node Oracle RAC running on Windows servers. (We will not be using a catalog database)

The result of this RMAN script is a compressed backup set split into manageable 2GiB chunks. It will contain the database plus the archive logs as well as control files for use of both Primary and Data Guard Standby.

RMAN Script

The following is the complete script...

run {

allocate channel d1 device type disk maxpiecesize 2048M format 'N:\temp_rman_backup\%d_DF_%T_%s_%p';
allocate channel d2 device type disk maxpiecesize 2048M format 'N:\temp_rman_backup\%d_DF_%T_%s_%p';
allocate channel d3 device type disk maxpiecesize 2048M format 'N:\temp_rman_backup\%d_DF_%T_%s_%p';

sql 'alter system archive log current';

backup as compressed backupset database force noexclude plus archivelog format 'N:\temp_rman_backup\%d_DF_%T_%s_%p';

backup current controlfile for standby format 'N:\temp_rman_backup\%d_STANDBY_CTRL_%T_%s_%p';
backup current controlfile format 'N:\temp_rman_backup\%d_CTRL_%T_%s_%p';

crosscheck backup;
crosscheck archivelog all;
report obsolete;
delete force noprompt obsolete; 
delete noprompt expired backup;
delete noprompt backup completed before 'SYSDATE - 3';
delete noprompt archivelog until time 'SYSDATE - 7'; 
report unrecoverable;
report need backup;

release channel d1;
release channel d2;
release channel d3;
}

exit

Now let's break this script down so that you understand why it was composed this way.

ALLOCATE CHANNELS

allocate channel d1 device type disk maxpiecesize 2048M format 'N:\temp_rman_backup\%d_DF_%T_%s_%p';

ALLOCATE CHANNEL is specified three times with numbered channel names d1 to d3.
This allows the backup process to be multiplexed into three threads to increase throughput and ultimately make the backup faster.

You may not want to do this if the backup uses excessive disk resources which may affect performance. You may want include the RATE channel parameter to throttle the throughput from the disk. Example below...

allocate channel d1 device type disk maxpiecesize 2048M rate 10000k format 'N:\temp_rman_backup\%d_DF_%T_%s_%p';

The backup will be split into 2GiB pieces and written to disk path N:\temp_rman_backup\. (The use of this temporary path will be made evident in the following batch script)

The file name is formatted using the formatSpec. In this example...
Database name _ DF _ YYYYMMDD _ Backup set number _ Piece number
Search for 'oracle rman format formatSpec' to find the meaning of all the syntax elements.

ARCHIVE LOG CURRENT

sql 'alter system archive log current';

The above switches logs and archives all logs to make the backup set consistent. (Some people argue this step is not required.)

BACKUP DATABASE

backup as compressed backupset database force noexclude plus archivelog format 'N:\temp_rman_backup\%d_DF_%T_%s_%p';

Here we backup the entire database as a compressed backup set including the archive logs. The results are multiple 2GiB files.

BACKUP CONTROL FILES

backup current controlfile for standby format 'N:\temp_rman_backup\%d_STANDBY_CTRL_%T_%s_%p';
backup current controlfile format 'N:\temp_rman_backup\%d_CTRL_%T_%s_%p';

Here we are making a backup of the all important control files.
The first is a copy of the control files for use in restoring the database into a Standby environment. Why? well what if you needed to restore Standby? You have the database backup but no control files. And you certainly cannot use normal control files to do this. When run, you will see spurious messages in your Oracle alert log but these can be ignored.
The second line is to make a copy of the control files. Without this, restoring the database is difficult.

CROSS CHECKING, REPORTING, DELETING

crosscheck backup;
crosscheck archivelog all;
report obsolete;

Above, the cross check updates RMAN repository information and checks the validity of the headers of the backup files.

delete force noprompt obsolete; 
delete noprompt expired backup;
delete noprompt backup completed before 'SYSDATE - 3';
delete noprompt archivelog until time 'SYSDATE - 7'; 

Above, we remove expired and obsolete information from the RMAN repository and delete files no longer required. These include archive log files which have been backed up at least once.

report unrecoverable;
report need backup;

REPORT UNRECOVERABLE reports which database files require backup because they have been affected by some NOLOGGING operation.
REPORT NEED BACKUP reports which database files need to be backed up to meet a retention policy.
All this information will be written to the output log file (if you have specified one)

Please note that the order of the lines above is important. Swapping them around can result in various backup files being removed (deleted) because they have expired or were obsolete. This will seriously threaten your ability to successfully restore and recover the database.

RELEASE CHANNELS

release channel d1;

Finally, we need to release the channels.

Invoking the RMAN Script

Here's how you invoke the backup script...

Example Batch File

The following is a complete batch file that will backup the database, archive logs, control files, voting disk, and OCR
It will place all these backup components into a directory named with the date and time of when the backup occurred.
The script will detect if the backup failed and send and email (using blat) to alert the DBA.

In this example, both the RMAN script and batch file are located in E:\Scripts\
There is a dependency on a temporary path being \temp_rman_backup\ located on the same drive as the environment variable BackupRootPath. This is the same path as that specified in the RMAN script above.

The batch file has several environment variables:

BackupRootPath Root of where the backups are located.
BackupPath Path where the backup is written to. (Computed. Don't change.)
LogFile Location where the log file is written. (Computed. Don't change.)
DaysToKeepBackups The number of days the keep backups.
UserAndPassword User and password (example sys/password)
ORACLE_SID RAC node where RMAN will take the backup from.
ORACLE_HOME Path to Oracle Home.
RMAN_SCRIPT Location and name the RMAN script.
RAC_VOTING1 Location of voting disk.
RAC_OCR1 Location of cluster disk.