RMAN - Sample Backup Scripts

RMAN - Sample Backup Scripts

Making Whole Database Backups with RMAN

We can perform whole database backups with the database mounted or open. To perform a whole database backup from the RMAN prompt the BACKUP DATABASE command can be used. The simplest form of the command requires no parameters, as shown in this example:

RMAN> backup database;

In the following example no backup location was specified meaning that the backups will automatically be placed in the Flash Recovery Area (FRA). If the FRA has not been setup then all backups default to $ORACLE_HOME/dbs.

How to check if the RFA has been setup:

SQL> show parameter recovery_file_dest

NAME                                 TYPE           VALUE
------------------------------------ -----------    -------------------------db_recovery_file_dest                string         /recovery_area
db_recovery_file_dest_size           big integer    50G

If FRA is not setup (i.e. values are null) please refer to the following note for assistance in setting it up.
Check my Blog: What is a Flash Recovery Area and how to configure it?

If you wish to place backup outside the FRA then following RMAN syntax may be used.

RMAN> backup database format '/backups/PROD/df_t%t_s%s_p%p';

• Backing Up Individual Tablespaces with RMAN

RMAN allows individual tablespaces to be backed up with the database in open or mount stage.

RMAN> backup tablespace SYSTEM, UNDOTBS, USERS;

• Backing Up Individual Datafiles and Datafile Copies with RMAN
The flexibility of being able to backup a single datafile is also available. As seen below to reference the datafile via the file# or file name. Multiple datafiles can be backed up at a time.

RMAN> backup datafile 2;
RMAN> backup datafile 2 format '/backups/PROD/df_t%t_s%s_p%p';
RMAN> backup datafile 1,2,3,6,7,8;
RMAN> backup datafile '/oradata/system01.dbf';

• Backing Up the current controlfile & Spfile
The controlfile and spfile are backed up in similar ways. Whenever a full database backup if performed, the controlfile and spfile are backed up. In fact whenever file#1 is backed up these two files are backed up also.
It is also good practise to backup the controlfile especially after tablespaces and datafiles have been added or deleted.
If not using an RMAN catalog, it is more important that we frequently backup our controlfile. We can also configure another method of controlfile backup which is referred to as 'autobackup of controlfile'.
Refer to the manual for more information regarding this feature.

RMAN> backup current controlfile;
RMAN> backup current controlfile format '/backups/PROD/df_t%t_s%s_p%p';
RMAN> backup spfile;

• Backing Up Archivelogs
It is important that archivelogs are backed up in a timely manner and correctly removed to ensure the file system does not fill up. Below are a few different examples. Option one backs up all archive logs to the FRA or default location. Option two backs up all archivelogs generate between 7 and 30 days and option three backs up archive logs from log sequence number XXX until logseq YYY then deletes the archivelogs. It also backups the archive logs to a specified location.

RMAN> backup archivelog all;
RMAN> backup archivelog from time 'sysdate-30' until time 'sysdate-7';
RMAN> backup archivelog from logseq=XXX until logseq=YYY delete input format '/backups/PROD/%d_archive_%T_%u_s%s_p%p';

• Backing up the Whole database including archivelogs
Below is an example of how the whole database can be backed up and at the same time backup the archive logs and purge them following a successful backup.  The first example backups up to the FRA, however it we wish to redirect the output the second command shows how this is achieved.

RMAN> backup database plus archivelog delete input;
RMAN> backup database plus archivelog delete input format '/backups/PROD/df_t%t_s%s_p%p';

Reference
Oracle® Database Backup and Recovery Basics