Oracle RMAN – Backup & Recovery

Oracle RMAN – Backup & Recovery

In this article I would like to first go through a quick Q&A and then cover some of the RMAN fundamentals in a little more detail. If you are not already backing up your database, read on and start doing it.

Note: I will be making this information as easy to digest as possible. If you would like all the details with a more technical description please read the Oracle documentation

First, let’s start with a quick Q&A of common questions and the move on to some more details.

Q&A

Q: Why Backup your Database?

A: To safeguard your data against accidental file deletion, corruption, user errors, malicious attacks or complete disaster at your physical site. Without a backup your data is unprotected in the event of any one of these problems.

Q: Which files should you back up?

A: All of them, including:  Data files  Archived REDO log files  Control Files  SPFile  Password File

Q: What types of backup are there

A: A few different types

• Physical backups – where you take a copy of a physical file
• Logical backups – export and data pump are examples of logical backups
• Hot backups – taken while the database is still open and operational, also known as inconsistent backups
• Cold backup – the database should be down and consistent (shutdown cleanly) before the backup is taken

Q: What does restore and recovery mean?

A: Physically taking the files from a backup and putting them back on disk to form part of your database is a restore operation. It’s like a physical copy. Database recovery is the process of updating those restored files to make them valid as of a specific point in time.

For example, you may restore a file which was backed up on Sunday and then apply the changes to that file to make it valid as of Monday morning. Both the restore and recover operations require that you have valid backups available.

Q: What’s the difference between a consistent and inconsistent backup?

A: A consistent backup is taken when the database has been shutdown cleanly (shutdown, shutdown transactional or shutdown immediate commands) and remained down for the duration of the backup process. It could be taken by issuing file system copy commands or using a tool such as RMAN. If you restore from a consisten backup you can open the database immediately.

An inconsistent backup is taken while the database is still up and operational. RMAN can take a hot backup while the database is open and being used. Your database must be in archivelog mode for an inconsistent backup to be taken, because these files keep a record of all the changes which happened while the files were being backed up but users were making changes in the database. These files must be backed up at the same time so that they can be used to recover the files to a consistent state if you need to restore and recover from the backup.

Inconsistent backups are the best way to backup your database because they require no disruption to availability. Providing you have all of the files they are no less safe than using a consistent backup. RMAN is great for backing up all of the required files.

Hopefully that quick backup Q&A has answered some of the fundamentals, so let’s now look at RMAN and the backup process in a little more detail.

What is RMAN?

How RMAN looks from the command line

RMAN stands for Recover Manager and is a utility used for backing up your Oracle database. It can backup your database to physical disk, tape devices and even compress the files as it is backing them up. It automatically decompresses them when restoring. Both compression and decompression comsume more CPU resource and time. It is free and you can use it when you have installed the Oracle software. It will also integrate with many third party software manufacturers products. In short, RMAN is the number one and recommended way to back up your Oracle database.

How does RMAN Work?

RMAN takes backups in 2 main ways:

1. Takes an exact copy (Image copy) of the file as it is on the system, byte-for-byte
2. Creates Oracle formatted files, optimised for using minimum space into sets

RMAN is able to keep track of the image copies and backup pieces by keeping a record of them in a catalog. The catalog can just be stored in the database’s control file that you are backing up or it can be kept separately in a schema called a recovery catalog. Ensuring you have copies of the control files and/or the recovery catalog is critical. If you have a separate RMAN catalog, also known as an RMAN repository, this also needs to be backed up.

Note: For any “image copy” or RMAN “backup set” to be used it must be known about by RMAN, which means it has to be catalogued. You can catalog both image copies and backup sets which are not already in the catalog and use them, in certain situations.

Top Tip: Set the control_file_record_Keep_time long enough so that the information about all the backups you want to keep does not get deleted from the catalog.

Incremental Backups – Differential vs Cumulative Backups

Most RMAN backup strategies will consist of a full, level 0 incremental backup followed by a many level 1 backups. You have to be a little careful with the terminology because a full database backup and a level 0 incremental backup are not the same thing…They are the same in that they backup the whole database, but if you plan on using an incremental backup strategy (in other words taking a level 1 backup any time later), you must use the incremental level 0. A full database backup cannot be used in an incremental backup strategy.

Once you have understood that, you will find that there are two distinct types of level 1 incremental backups:

1. Differential – Backups blocks changed since the previous level 1 or level 0
2. Cumulative – Backups blocks changed since the previous level 0

Differential Vs Cumulative – The Short Story

In short, a cumulative backup will backup any block which has changed since the last level 0 backup, regardless of whether or not it was backed up in a subsequent level 1 backup.

A differential backup will backup any blocks which have changed since the last level 0 backup or level 1 backup, so there will most likely be fewer blocks to backup and your level 1 backup will be smaller but you will have to recover using ALL of the level 1 backups since the previous level 0.

Differential vs Cumulative – The Details

Although the difference between them may sound small, it is very significant. Let’s use an example to illustrate the differences. Say that you take an incrememntal level 0 backup on Sunday. On Monday, Tuesday and Wednesday you take incremental level 1 backups (you’ll see the different between the differential and cumulative in a minute). Now imagine that we have a block which was backed up in the level 0, as all used database blocks are, and we’ll call this “block A”. On Monday “Block A” remained unchanged, so it was not backed up again. On Tuesday a user modified some data in ”Block A”, so it was backed up. Up to this point the differential and cumulative level 1 backups would be the same. On Wednesday “Block A” was not changed. This is where the distinction between the differential and cumulative strategies lies.

Using the differential backup “Block A” would not be backed up, because it was backed up on Tuesday and in order to restore using differential level 1s you need ALL of the backups since the previous lower level backup (in theory this could be a level 0 or 1, since you can only go to a level 2 backup, but in reality this is usually a level 0 as most people use a level 0 and 1 approach).

Using the cumulative approach “Block A” would be backed up, however, because a cumulative backup will backup any block which has changed since the previous successful backup at one level lower.

Configuring RMAN

 

The DB must be in archivelog mode to use RMAN effectively

I will now attempt to give you the details about how to configure your database and RMAN so that it runs optimally. Here is a checklist of items which you need as an absolute minimum:

1. Make sure that the database is in archivelog mode
2. Check that you have enough space on disk to store a backup
3. You must have access to a user account who has the SYSDBA role and appropriate operating system privileges, such as administrator on Windows
4. You have decided on a backup strategy (covered later in “An Example RMAN Recovery Strategy“)

Note: It is up to you whether you decide to use the Flash Recovery Area (FRA) or just a disk file location. Using the FRA will mean that the space and files are managed more automatically by Oracle.

List of RMAN Configuration Settings

You can see a full list of your RMAN configuration settings by using the RMAN “SHOW ALL” command from the command prompt, like this:

RMAN> show all;

using target database control file instead of recovery catalog RMAN configuration parameters are:

  CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 1 DAYS;

  CONFIGURE BACKUP OPTIMIZATION OFF;

  CONFIGURE DEFAULT DEVICE TYPE TO DISK;

  CONFIGURE CONTROLFILE AUTOBACKUP ON;

  CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO 'D:\rman_level1\%d_%F';

  CONFIGURE DEVICE TYPE DISK PARALLELISM 4;

  CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

  CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1;

  CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'D:\rman_level0\%d_%T_%s_%t';

  CONFIGURE MAXSETSIZE TO 24G;

  CONFIGURE ENCRYPTION FOR DATABASE OFF;

  CONFIGURE ENCRYPTION ALGORITHM 'AES128';

  CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

  CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'D:\RMAN_LEVEL1\SNCF_DB.ORA';

Note: You can change the default RMAN date and time format, which I have written about in another post. I always prefer to have the full time available to see when I am working with RMAN.

You can find out all of the details for the settings above from the Oracle RMAN documentation, so I won’t list out all of the information again here. It is important to note that these are the settings which will be used if you do not override them in your backup command. What this means is that when you execute an RMAN backup command you can specify different parameters. It’s called a run block in RMAN terminology, which we will look at later on.

How can I take an RMAN backup

This is just a quick example of how you can create a level 0 (full) backup of your database. The only two conditions that you must meet are:

1. You have the database in archivelog mode
2. You have enough space available on disk

This will usually be around 20% of the total size of your database when using a compressed backup like this:

run

{

  ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT 'D:\rman_level0\%d_%T_%s_%t';

  ALLOCATE CHANNEL c2 DEVICE TYPE DISK FORMAT 'D:\rman_level0\%d_%T_%s_%t';

  ALLOCATE CHANNEL c3 DEVICE TYPE DISK FORMAT 'D:\rman_level0\%d_%T_%s_%t';

  ALLOCATE CHANNEL c4 DEVICE TYPE DISK FORMAT 'D:\rman_level0\%d_%T_%s_%t';

  BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL = 0 DATABASE PLUS ARCHIVELOG NOT BACKED UP 1 TIMES;

}

The explanation of the above commands:

1. It will spawn 4 threads to complete the backup, so running a parallelism of 4
2. It backups the database to the rman_level0 directory on disk
3. The backup will be compressed as it is being backed up
4. INCREMENTAL LEVEL = 0 means that it can be used as part of an incremental backup strategy, so level 1s can be taken afterwards
5. The archivelogs will also be backed up if they have not been backed up already

An Example RMAN Recovery Strategy

Note: I have used this strategy for many years now, and successfully restored using it, but that does not mean that it will always work in your situation. Therefore, you should fully test any strategy that you implement before using it in your production environment.

The first step in designing any RMAN backup strategy is to find out what is needed and what you are trying to solve. Here are some questions to help you establish exactly what is required:

• What is the maximum point point in the past will you need to restore your database to? For example, maybe you need to be able to always go back 3 days
• Will the backups be taken at the primary site or from a physical standby
• Where will you store the backups? On disk or tape, for example
• Do you have a time window within which you need to be able to restore the whole database?

The answers to those questions will change the decisions that you make about how best to backup your database. I couldn’t possibly cover all of the scenarios here, but I will go in to detail about one approach.

Backup Strategy Key Points

• I always want to be able to go back at least 4 days, because if a problem is identified after a bank holiday weekend we can always go back to a good copy of the data
• The backups will be taken from our production database server, so we want to take them outside of peak hours and when there are few overnight batch jobs running
• The backups will initially be stored on disk and then backed up to tape and off-sited
• There is no strict requirement, but as soon as possible using all of the processing power available. Our contracts with clients have a recovery window time of 8 hours from a total system failure.

Top Tip: You can use something called a “tag” to label a backup. The benefit of this is that you could, for example, create a level 0 backup and call it FULL_SAT. This backupset would then be easily identifiable within your RMAN catalog. All operations, such as a restore, can also use the tag name to reference all of the required files. By default an RMAN backup will have a unique name, but not a meaningful one.

RMAN Implementation

Based on the above requirements, we opted for the following backup strategy:

• RMAN Level 0 every Saturday
• RMAN Level 1 every evening, excluding Sat
• Archive log backups twice a day, early morning and early evening
• Deletion of backups and archivelogs according to the recovery window required, running every day

I will now provide the RMAN scripts which I use to implement the above backup strategy.

RMAN Level 0

Scheduled for every Saturday at 20:00.

run

{

  ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT 'D:\DB\rman_level0\%d_%T_%s_%t';

  ALLOCATE CHANNEL c2 DEVICE TYPE DISK FORMAT 'D:\DB\rman_level0\%d_%T_%s_%t';

  ALLOCATE CHANNEL c3 DEVICE TYPE DISK FORMAT 'D:\DB\rman_level0\%d_%T_%s_%t';

  ALLOCATE CHANNEL c4 DEVICE TYPE DISK FORMAT 'D:\DB\rman_level0\%d_%T_%s_%t';

  backup as compressed backupset incremental level = 0 database plus archivelog NOT BACKED UP 1 TIMES;

}

RMAN Level 1

Scheduled for every night, excluding Saturdays, at 20:00.

run

{

  ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT 'D:\DB\rman_level1\%d_%T_%s_%t';

  ALLOCATE CHANNEL c2 DEVICE TYPE DISK FORMAT 'D:\DB\rman_level1\%d_%T_%s_%t';

  ALLOCATE CHANNEL c3 DEVICE TYPE DISK FORMAT 'D:\DB\rman_level1\%d_%T_%s_%t';

  BACKUP AS COMPRESSED BACKUPSET INCREMENTAL LEVEL 1 DATABASE PLUS ARCHIVELOG NOT BACKED UP 1 TIMES;

}

RMAN Archivelogs

Scheduled to run at 05:30 and 17:30 every day.

run

{

  ALLOCATE CHANNEL c1 DEVICE TYPE DISK FORMAT 'D:\DB\rman_archlogs\%d_%T_%s_%t';

  ALLOCATE CHANNEL c2 DEVICE TYPE DISK FORMAT 'D:\DB\rman_archlogs\%d_%T_%s_%t';

  BACKUP AS COMPRESSED BACKUPSET ARCHIVELOG ALL NOT BACKED UP 1 TIMES;

}

Deletion of Archivelogs and Backups

run

{

  DELETE NOPROMPT BACKUP OF DATABASE COMPLETED BEFORE 'TRUNC(SYSDATE-6)';

  DELETE NOPROMPT BACKUP OF ARCHIVELOG UNTIL TIME 'TRUNC(SYSDATE-3)';

  DELETE NOPROMPT BACKUP OF CONTROLFILE COMPLETED BEFORE 'TRUNC(SYSDATE-6)';

  DELETE NOPROMPT ARCHIVELOG ALL COMPLETED BEFORE '(TRUNC(SYSDATE-2))' BACKED UP 1 TIMES TO DEVICE TYPE = DISK;

}

I would schedule all of these jobs through the Oracle Enterprise Manager.

NOTE: In the Oracle documentation they recommend using a slightly different approach, called “Incrementally Updated Backups“. Have a read of that and see what you think. It’s a great idea, but I have not had the need to fully test or implement this strategy yet so I cannot comment on its effectiveness.

Validation of RMAN Backups

It’s all well and good having all of these backups, but without testing an actual restore and recovery operation you will not know if it works. The best way to check it is to actually perform the restore and recovery operation, for real, on a testing system somewhere. This can be time consuming but is definitely worth it. Quite often you will find that a company will have a disaster recovery (DR) test periodically, such as once or twice a year.

Another way to validate Oracle backups is to use some of the built in functionality which run checks against your backup but don’t restore the files. See the link to the Oracle docs for more information on that.

Block Change Tracking

The block change tracking feature in Oracle is designed to improve the speed of incremental backups. It works by using a small file stored on disk to track changes to each database block. This file is then read by RMAN when it takes an incremental backup (excluding level 0 because this includes all blocks) and it will only backup the blocks which are included within the file. This removes the need for scans of all the database blocks to see if they have been used since the previous backup.

Read more about Oracle block change tracking in the official documentation, as there is a lot more detail in there any it is explained very well.

Crosschecking

Occassionally you may find that the files which are on disk and reported to be on disk in the RMAN catalog differ. For example, you might delete an archivelog from disk with an operating system command. This will mean that the catalog thinks the file is there when it is not. These differences can cause issues when taking backups because the database attempts to backup a file which is not present, and generate the error message like RMAN-03002 and RMAN-06059 expected archived log not found.

Start of RMAN crosscheck archivelogs command

 

If you do remove a file manually from the disk system, perhaps because the drive becomes full unexpectedly, you can run an RMAN crosscheck command and a subsequent RMAN deletion command to clear up the references to the files in the repository.

Conclusion

I hope that this has helped you to understand RMAN a little more, and that the use of RMAN to backup your Oracle database is fundamental to having a solid backup strategy. You can use 3rd party applications which underneath link in to RMAN, which is also a good choice. RMAN is flexible, unobtrusive and efficient for backing up your Oracle database.