Written by: Juan Carlos Olamendy Turruellas


This is the fourth article in a series where we’re being learning about the principles, concepts and real world scripts for doing backups to Oracle database.

In the first article, I’ve talked about the most important terms related to backups in Oracle databases. In this second article, I’ve talked about doing low-level manual backups in order to illustrate the principles and concepts of the first article. In the third article, I’ve talked about the architecture, key concepts and terms related to Recovery Manager (also know as RMAN). And in this last fourth article, I’m talking about applying the concepts learned about RMAN with practical examples in real-world scenarios.

Initialization parameters

The first step is to check the initialization parameters, important for controlling the backup process using RMAN, are set.

These parameters are:

  • DB_RECOVERY_FILE_DEST. Specifies the location of the location of the Fast Recovery Area. This is a location on disk to store all RMAN backups. In order to mitigate the risks of losing important data and not impact on the overall performance, we need to locate it on a file system area different from any database files, control files, or redo log files (online or archived).
  • DB_RECOVERY_FILE_DEST_SIZE. Specifies the upper limit on the amount of space used by the Fast Recovery Area.

The listing 01 shows an example excerpt of an initialization parameters file.

#other init parameters are above or below



Listing 01

Connecting using RMAN

We can connect using RMAN to an Oracle instance by calling the rman command. This will shows the RMAN> prompt, at which point we can type in the various commands.

Use the CONNECT TARGET command to connect to a target database instance. RMAN connections to a database instance are specified and authenticated the same way SQL*Plus is. RMAN connections require SYSDBA privilege.

For example, let’s connect to our current instance (identified by ORACLE_SID environment variable) by using OS as shown below in the listing 02.

RMAN> connect target /

Listing 02

Configuration parameters

RMAN has several configuration parameters, which are set to their default values when you first use RMAN. We can display the default settings using the SHOW ALL command as shown in the listing 03.

RMAN> show all;

Listing 03

We if we want to change some default settings, we need to execute the CONFIGURE command.

For example, we can change the default retention policy. This policy specifies when to consider a backup as obsolete. It’s remarkable to say that when we tell RMAN to consider a backup file obsolete after a certain time period, RMAN only marks the file obsolete—it doesn’t delete it explicitly. We need to delete the obsolete files manually.

Backups can be retained by:

  • Recovery window. RMAN remains as many backups as necessary to bring a database to a point in time within a recovery window. For example, a recovery window of 7 days will cause RMAN to maintain image copies, incremental backups, and archived redo log files to restore/recover a database to any point in the last 7 days.
  • Redundancy. RMAN retains a specified number of backups.

Let’s configure a retention policy for 28 days (4 weeks of retention) as shown below in the listing 04.

RMAN> configure retention policy to recovery window of 28 days;

Listing 03

Another important item to configure is the device for backup. The default device is the disk. If no pathname is specified, RMAN uses the Fast Recovery Area for all backups (see listing 01).

Keeping all backups in the disk is very risk. If we want to configure a tape device as the default backup device, we need to configure to sbt (this is the device type for any tape backup system for any vendor) as shown below in the listing 04.

RMAN> configure channel device type sbt parms ='ENV=(<vendor specific arguments>)';

Listing 04

If we want to switch the default device back to disk, we can do as shown in the listing 05.

RMAN> configure default device type to disk;

Listing 05

Full Database Backup - ARCHIVELOG Mode

In ARCHIVELOG mode, we can backup a database while it is open. Remember that redo archivelog mode is required.

Using RMAN, the full backup is very simple comparing to the manual counterpart scripts as shown in the listing 06.

RMAN> backup database plus archivelog;

Listing 06

The backupset is located at the Fast Recovery Area under the directory [database_name]. For example, we can see the backups using the ls OS command as shown in the listing 07.

$ ls -al /u05/oracle/db_test/fast_recovery_area/DBTEST

Listing 07

The next statement as shown in the listing 08 ensures that all transactions are represented in an archive log including any that occurred during the backup. This enables media recovery after restoring this backup.


Listing 08

Full Database Backup - NOARCHIVELOG Mode

Remember that in NOARCHIVELOG mode, only a consistent backup is valid. That’s, the database must be in MOUNT mode after a consistent shutdown and no recovery is required after restoring a backup.

First step is to shutdown and startup the instance by just mounting the database as shown in the listing 09.

RMAN> shutdown immediate;

RMAN> connect target /

RMAN> startup force pfile=init.ora

RMAN> shutdown immediate;

RMAN> startup mount pfile=init.ora

Listing 09

Next step is to backup the database as shown in the listing 10.

RMAN> backup database;

Listing 10

And finally, we need to open the database and resume the normal operations as shown in the listing 11.

RMAN> alter database open;

Listing 11

Partial backups

We can backup a particular tablespace and/or database files without backing up the entire database as shown in the listing 12.

RMAN> backup tablespace sales_data;

RMAN> backup datafile '/u04/oradata/DBTEST/sales_data01.dbf';

Listing 12

Listing backups

The LIST BACKUP command (as shown in the listing 13) shows all the completed backups registered by RMAN. The command shows all backup sets and image copies, as well as the individual database files, control files, archived redo log files, and SPFILEs in the backup. We can also list all backups by querying V$BACKUP_FILES and RC_BACKUP_FILES views.

RMAN> list backup;

Listing 13

Validating backups

The VALIDATE command confirms that all database files exist, and they are in their correct location, and free of physical corruption. The CHECK LOGICAL option also checks for logical block corruption.

We can validate the entire database including the archived redo log files as shown below in the listing 14.

RMAN> backup validate check logical database archivelog all;

Listing 14

We can validate a particular backup set as shown below in the listing 15.

RMAN> validate backupset 5;

Listing 15

Deleting obsolete backups

The DELETE command removes physical backups, updates control file records to indicate that the backups are deleted, and also removes their records from the recovery catalog (if you use one). We can delete backup sets, archived redo logs, and database files copies.

We can use the DELETE OBSOLETE command to remove all backups no longer needed. We should run DELETE OBSOLETE periodically to delete all backups that are obsolete. A backup is obsolete if it’s no longer needed for database recovery, according to the current retention policy.

We can use the DELETE EXPIRED command to remove the recovery catalog records for expired backups and marks them as DELETED. An expired backup is the one that cannot be found by RMAN. We can then use the DELETE EXPIRED command to remove the records for these files from the control file and the recovery catalog.

Incremental backups

All the BACKUP commands in the preceding examples are full backup commands. We can also perform incremental backups using RMAN, and in fact, this is one of the great advantages of this tool. Incremental backups are much faster than backing up the entire database because only those data blocks that changed since a previous backup are copied.

Incremental backups can be:

  • level 0. It copies all data blocks just like a full backup, and acts as the base for subsequent incremental backups.
  • Level 1. It’s really an incremental backup. In order to perform a level 1 incremental backup, we must first have a base level 0 backup. This level contains only changed blocks since the initial level 0 backup. A cumulative level 1 incremental backup records all changed blocks since the last initial incremental backup

We can execute a level 0 backup to the entire database as shown below in the listing 16.

RMAN> backup incremental level 0 database;

Listing 16

Once we have level 0 backup, we’re ready to execute level 1 backups as shown in the listing 17.

RMAN> backup incremental level 1 database;

Listing 17

The size of your incremental backup file depends on the number of changed data blocks and the incremental level. Cumulative backups will, in general, be larger than differential backups, since they duplicate the data copied by backups at the same level. However, cumulative backups have the advantage that they reduce recovery time, because you apply only one backup.

Control file auto backup

If we set the CONTROLFILE AUTOBACKUP setting to ON, each time we perform a backup, then the control file is automatically backed up along with the SPFILE as shown in the listing 18.

RMAN> configure controlfile autobackup on;

Listing 18


In this fourth part, I've showed how to use RMAN using real-world examples. Now you can use the scripts as the starting point to backup your own Oracle databases.