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.
The first step is to check the initialization parameters, important for controlling the backup process using RMAN, are set.
These parameters are:
The listing 01 shows an example excerpt of an initialization parameters file.
#other init parameters are above or below
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 /
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;
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:
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;
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>)';
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;
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;
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
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.
RMAN> SQL 'ALTER SYSTEM ARCHIVE LOG CURRENT';
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> startup force pfile=init.ora
RMAN> startup mount pfile=init.ora
Next step is to backup the database as shown in the listing 10.
RMAN> backup database;
And finally, we need to open the database and resume the normal operations as shown in the listing 11.
RMAN> alter database open;
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';
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;
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;
We can validate a particular backup set as shown below in the listing 15.
RMAN> validate backupset 5;
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.
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:
We can execute a level 0 backup to the entire database as shown below in the listing 16.
RMAN> backup incremental level 0 database;
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;
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.
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;
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.