Validate Oracle RMAN Backup and Recovery (Hands-on Tutorial)

By Vijay

By Vijay

I'm Vijay, and I've been working on this blog for the past 20+ years! I’ve been in the IT industry for more than 20 years now. I completed my graduation in B.E. Computer Science from a reputed Pune university and then started my career in…

Full Bio >

Follow
Learn about our editorial policies.
 Updated June 23, 2024

How to Create and Validate Oracle RMAN Backup: Learn with RMAN Commands and Recovery process

In this tutorial, we shall discuss verifying and testing your Oracle database backups. We will be explaining concepts such as what, why, and how about Database backups and methods to test the backup.

We will take the Oracle database as a case study for this tutorial.

Case Study: Testing Oracle RMAN Database Backups:

Oracle RMAN Backup and Recovery Testing

Oracle Database Backup Validation Process Using RMAN

We have categorized it into the following four sections

  1. What is a backup?
  2. Why backup?
  3. How to backup?
  4. How to test/validate your database backup – Recovery Strategies?

Also read => All about Database testing

What is a Database Backup?

Before we begin to learn more about backups, we need to understand an organization’s most important asset – Data. Considering your organization runs on Oracle database. To understand the term “database” you could refer to the Oracle Database Testing series here.

Data of an organization is the most integral part of an organization. Consider a retailing, banking company. They all have enormous amounts of data – user, system, etc. As a database administrator, System administrator or any personnel who has been assigned the job to protect this data should be aware of how important data is to an organization. How to make sure the data is always available? Backup this data.

A backup is an exact copy of your database which can help you reconstruct your data in case of any data loss.

Test database backup 1

Why Backup Database?

Consider a simple case where your banking organization who has data regarding millions of customers in terms of account numbers, names, nominees, bank balance and the organization lost all of their data, how would their customers react to it? How would the organization deal with the pressure of losing so much data? How would they be answerable to so many customers dissatisfaction?

This is why we backup this data so that in case of any failure of a disk (storage), the disk controller (storage controller) we can always rely on our backup from where we can restore it into the database i.e. storage filesystem and not have customers lose any of their data.

Hypothetically speaking, suppose there are millions of customer and each of them performing millions of transactions and the database accidentally crashes and lose their data, would we ask all these customers to re-enter their data again? How would cope with losing so much data? That would be highly unacceptable.

Similarly, consider a telecommunications company which supports millions of customers and have all of their data regarding phone numbers, addresses, credit availed, pending payments. What if we lose all of their data? The company is doomed and would have to bear huge costs potentially bringing the organization to a halt. It would certainly be a huge catastrophe.

How to Backup the Database?

To backup data in an Oracle Database, we have several methods. They can be broadly classified as physical and logical backups

Method #1) Physical Backups:

  1. 3rd party backups – such as Veritas NetBackup, SAP, IBM Tivoli Manager, EMC, HP
  2. User-managed backups – Backup of a database using OS utilities such as copy( windows), cp (Unix).
  3. Oracle Secure Backup
  4. My favorite and the most preferred recommended Oracle utility – Recover Manager (RMAN).

Method #2) Logical backups:

  1. Conventional Export/import utilities and Datapump utilities. A logical backup is a backup of logical data – objects such as tables, indexes etc which are constituents of a database independent of the location of the above objects.

To understand physical and logical storage structures of a database you could refer to this and this oracle documentation.

Which is the best method for Database Backup?

Each of these backup strategies has their own pros and cons and we shall not deal too much with them in this article.

We need to understand that unless you have a physical backup in place, just having a logical backup isn’t always safe against physical data corruption, hardware storage issues. Having a valid, good physical backup makes it a good backup and recovery strategy. Always make sure you have a physical backup in place.

In reality, we can use any of the above methods but we always need to make sure we have a good backup and recovery strategy in place to avoid any unnecessary hiccups during the course of the operation of a database. Testing your back and recovery strategies on a mirrored test system is always advised so that we can predict the amount of time it takes to get your database up and running in case of any unforeseen situations.

In this article, we shall mainly focus on RMAN backups. This brings us to a point of knowing how exactly we perform the backup.

Oracle RMAN (Oracle Recovery Manager) Backup Commands

We can backup data either with help of Enterprise Manager (GUI) mode or through OS command line prompt.

Test database backup 2

RMAN is a robust, sophisticated tool provided by Oracle to perform backup and recovery.

RMAN is automatically installed when you install the Oracle database so there isn’t any additional installation required to use RMAN.

The RMAN environment comprises of two components:

1) Target database (the database which you would backup, perform recovery of and

2) RMAN client which is the client which interprets user commands and executes them on behalf of the user while connecting to the Target Database.

A simple command to connect to the database using RMAN is as follows:

C:\Users\xyz> rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Sun Sep 28 17:32:48 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1361070653)

RMAN>

DBID here is the unique identifier which is unique to each database we are planning to work with.

In this example, we are dealing with a database named ORCL.

We shall backup the data which belongs to the ORCL database.

Since a backup is a physical copy of your database, we need a location/directory where we can save them.

To achieve this, we can make use of a special directory named db_recovery_file_dest which serves as the backup location. Define the size of this parameter with db_recovery_file_dest_size which marks the size of this backup location.

Although we have several ways to compress your backups and several techniques which can reduce the size of a backup, try to at least set the DB_RECOVERY_FILE_DEST_SIZE to a size of your actual data in your database. Make sure you account for archive logs as well which is nothing but offline redo logs which records changes to your data blocks.

Your backup strategy would consist of all the files related to the database such are datafiles, control files, parameter files, network related files, archived redo log files.

RMAN or any other physical backup tool can backup datafiles, control files, parameter files, archived redo log files. Network related files need to be backed up manually using OS utilities such as cp or copy.

To backup a database we use:

“Backup database” – it’s as simple as that. So, let’s begin to backup our ORCL database.

Since we have already connected to the Target database (ORCL), we fire the “backup database” command.

Test database backup 3
RMAN> backup database;

Starting backup at 05-OCT-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=198 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=D:\APP1\SUNTYADA\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP1\SUNTYADA\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00005 name=D:\APP1\SUNTYADA\ORADATA\ORCL\EXAMPLE01.DBF
input datafile file number=00003 name=D:\APP1\SUNTYADA\ORADATA\ORCL\UNDOTBS01.DBF
input datafile file number=00004 name=D:\APP1\SUNTYADA\ORADATA\ORCL\USERS01.DBF

channel ORA_DISK_1: starting piece 1 at 05-OCT-14
channel ORA_DISK_1: finished piece 1 at 05-OCT-14

piece handle=D:\APP1\SUNTYADA\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2014_10_05\O1_MF_NNNDF_TAG20141005T162412_B328TXQG_.BKP tag=TAG20141005T162412 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:04:27
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set

including current control file in backup set
including current SPFILE in backup set

channel ORA_DISK_1: starting piece 1 at 05-OCT-14
channel ORA_DISK_1: finished piece 1 at 05-OCT-14

piece handle=D:\APP1\SUNTYADA\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2014_10_05\O1_MF_NCSNF_TAG20141005T162412_B3293806_.BKP tag=TAG20141005T162412 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04

Finished backup at 05-OCT-14

Here, we observe that the backup of all related files of the database – datafiles, control files, spfile (parameter file) has been completed. The backup operation took about 4 minutes and 27 seconds (Elapsed time). This is a small test database with just 5 datafiles so it took very less time to backup.

In cases where we want to backup data from databases of giant organizations, there could be hundreds of data files and each datafile could be in terabyte sizes and taking a complete backup of the database could potentially take hours of time.

To know the details regarding the backup we just created, we shall execute:

RMAN> list backup;

Test database backup 4
List of Backup Sets
===================

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full   1.39G     DISK       00:04:23     05-OCT-14

BP Key: 4   Status: AVAILABLE Compressed: NO Tag: TAG20141005T162412
Piece Name: D:\APP1\SUNTYADA\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2014_10_05\O1_MF_NNNDF_TAG20141005T162412_B328TXQG_.BKP
List of Datafiles in backup set 4

File LV Type Ckp SCN   Ckp Time Name
---- -- ---- ---------- --------- ----
1       Full 9684060   05-OCT-14 D:\APP1\SUNTYADA\ORADATA\ORCL\SYSTEM01.DBF
2       Full 9684060   05-OCT-14 D:\APP1\SUNTYADA\ORADATA\ORCL\SYSAUX01.DBF
3       Full 9684060   05-OCT-14 D:\APP1\SUNTYADA\ORADATA\ORCL\UNDOTBS01.DBF
4       Full 9684060   05-OCT-14 D:\APP1\SUNTYADA\ORADATA\ORCL\USERS01.DBF
5       Full 9684060   05-OCT-14 D:\APP1\SUNTYADA\ORADATA\ORCL\EXAMPLE01.DBF

BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full   9.58M     DISK       00:00:06     05-OCT-14

BP Key: 5   Status: AVAILABLE Compressed: NO Tag: TAG20141005T162412
Piece Name: D:\APP1\SUNTYADA\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2014_10_05\O1_MF_NCSNF_TAG20141005T162412_B3293806_.BKP

SPFILE Included: Modification time: 05-OCT-14
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 9705762     Ckp time: 05-OCT-14

This backup is placed in the DB_RECOVERY_FILE_DEST location which is defined as D:\APP1\SUNTYADA\FLASH_RECOVERY_AREA

SQL> show parameter DB_RECOVERY_FILE_DEST
NAME                                 TYPE       VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest               string     D:\app1\suntyada\flash_recovery_area
db_recovery_file_dest_size           big integer 3912M

The size defined for our backup location is 3912 MB.

Use VALIDATE to check database files and backups:
RMAN> VALIDATE DATABASE;

Validate RMAN Backup

How do we test or validate that we can recover our database during any crisis?

If due to hardware failure or some corruption of your storage disks, we would need a good backup available to restore this corrupted data so that we do not lose any data that belonged to that storage files.

It all depends on how you have designed the backups, the intervals at which the backups are scheduled, whether you take a full backup and have incremental backups.

In case of user errors – such as an unnecessary manipulation of data, we can restore parts of data or all of data that have been changed through logical backups.

In practice, we should be aware of and foresee any errors that could occur in the future and test every strategy to evade them.

Use BACKUP VALIDATE command to validate backup files:

The command for only physical corruption check:

RMAN> BACKUP VALIDATE
DATABASE
ARCHIVELOG ALL;

The command for the physical and logical corruption check:

RMAN> BACKUP VALIDATE
CHECK LOGICAL
DATABASE
ARCHIVELOG ALL;

RMAN> BACKUP VALIDATE DATABASE;

Test database backup 5 new
Starting backup at 05-OCT-14
using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=D:\APP1\SUNTYADA\ORADATA\ORCL\SYSTEM01.DBF
input datafile file number=00002 name=D:\APP1\SUNTYADA\ORADATA\ORCL\SYSAUX01.DBF
input datafile file number=00005 name=D:\APP1\SUNTYADA\ORADATA\ORCL\EXAMPLE01.DB
input datafile file number=00003 name=D:\APP1\SUNTYADA\ORADATA\ORCL\UNDOTBS01.DB
input datafile file number=00004 name=D:\APP1\SUNTYADA\ORADATA\ORCL\USERS01.DBF
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:45

List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
1   OK     0             13430        106376         9708800
File Name: D:\APP1\SUNTYADA\ORADATA\ORCL\SYSTEM01.DBF

Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0             75217
Index     0             12706
Other     0             5015

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2   OK     0             21161       95409           9708826
File Name: D:\APP1\SUNTYADA\ORADATA\ORCL\SYSAUX01.DBF

Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0             23010
Index     0             21760
Other     0             29429

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
3   OK     0             0           5762           9708826
File Name: D:\APP1\SUNTYADA\ORADATA\ORCL\UNDOTBS01.DBF

Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0             0
Index     0             0
Other     0             5760

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
4   OK     1125           228         5765           9528788
File Name: D:\APP1\SUNTYADA\ORADATA\ORCL\USERS01.DBF

Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0             2295
Index     0             39
Other     0             3198

File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
5   OK     0             1687         10498           9585679
File Name: D:\APP1\SUNTYADA\ORADATA\ORCL\EXAMPLE01.DBF

Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data       0             4760
Index     0             1261
Other     0             2788

channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

List of Control File and SPFILE
===============================
File Type   Status Blocks Failing Blocks Examined
------------ ------ -------------- ---------------
SPFILE       OK     0             2
Control File OK     0             608

Finished backup at 05-OCT-14

As you can observe above the Status of each file is “OK” which means these are usable and can be used to restore the files at any point of time.

We can perform a preview of the database restore. This gives you a nice list of files and their availability without actually restoring the files.

Use RESTORE command to validate backup:

RMAN> RESTORE DATABASE VALIDATE;
RESTORE ARCHIVELOG ALL VALIDATE;

RMAN> RESTORE DATABASE PREVIEW;

Test database backup 6
Starting restore at 05-OCT-14
using channel ORA_DISK_1

List of Backup Sets
===================
BS Key Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full   1.39G     DISK       00:04:23     05-OCT-14
BP Key: 4   Status: AVAILABLE Compressed: NO Tag: TAG20141005T162412
Piece Name: D:\APP1\SUNTYADA\FLASH_RECOVERY_AREA\ORCL\BACKUPSET\2014_10_05\O1_MF_NNNDF_TAG20141005T162412_B328TXQG_.BKP
List of Datafiles in backup set 4
File LV Type Ckp SCN   Ckp Time Name
---- -- ---- ---------- --------- ----
1       Full 9684060   05-OCT-14 D:\APP1\SUNTYADA\ORADATA\ORCL\SYSTEM01.DBF
2       Full 9684060   05-OCT-14 D:\APP1\SUNTYADA\ORADATA\ORCL\SYSAUX01.DBF
3       Full 9684060   05-OCT-14 D:\APP1\SUNTYADA\ORADATA\ORCL\UNDOTBS01.DBF
4       Full 9684060   05-OCT-14 D:\APP1\SUNTYADA\ORADATA\ORCL\USERS01.DBF
5       Full 9684060   05-OCT-14 D:\APP1\SUNTYADA\ORADATA\ORCL\EXAMPLE01.DBF

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================
Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
367     1   366     A 02-OCT-14
Name: D:\APP1\SUNTYADA\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2014_10_05\O1_MF_1_366_B32925TJ_.ARC
Media recovery start SCN is 9684060
Recovery must be done beyond SCN 9704654 to clear datafile fuzziness

Finished restore at 05-OCT-14

Conclusion

These are just simple techniques to verify your Oracle RMAN backups. Hope you have a clear understanding of RMAN backup and recovery process with the help of various important RMAN commands.

Although in real case scenarios based on the size of the data, we could have several hundreds of data files and we need to make sure we backup each and every one of them to have a good backup strategy in place. Also, test the recovery on test systems to make sure you can use the same techniques on production.

We have dealt with different methods of backing up your critical/test databases and various methods to test them. As already suggested numerous times, having a good backup and recovery strategy will save your job and your organization.

Let us know if you have any queries related to Oracle or any other Database backup and recovery testing.

Was this helpful?

Thanks for your feedback!

Recommended Reading

16 thoughts on “Validate Oracle RMAN Backup and Recovery (Hands-on Tutorial)”

  2. Don’t you want to use “RESTORE VALIDATE DATABASE;” instead of “BACKUP VALIDATE DATABASE;”? The latter checks that your database can be backed up, but you had already backed it up by that point in the article.

    Reply

  5. @Bhushan Samane – RMAN utility is specific to Oracle. Having said that, Each database would have similar tools/utilities of their own like RMAN.
    @Nikolay – Thank you 😀
    @Uma – Thank you for your feedback.

    Reply

  6. Do u know maybe Xoperos solutions? I started use their product. Actually i am satisfied , but tell me if u know more backup solutions (for small company) or maybe you have some experience with Xopero too?

    Reply

  7. Good concept.. Good to know that its not that hard to backup and the importance of keeping the backup

    Next article should be how to test if backup is correct or not.. and how to switch to back up in case of failure

    Reply

  9. Hello Aleksander,

    There are many backup providers of course but everything depends on your needs. If Xopero fullfill your requiarements then i think you have it what you need 🙂
    I am familiar with this solution so if you need some advice, support let me know.

    Luke

    Reply

  10. Hi, I am working to develop and implement an application that integrate data from 6 different data source through entity resolution. I am assigning test users from each of the of data sources to validate the data. I need to develop a test matrix with the test steps and scenarios for testing the verification, validity, accuracy, and completeness of of the test data. I don’t know exactly where to start. Can you provide me with a completed sample of a data validation test matrix that can guide me that can instruct me with what the user should be specifically looking for to test. It would instruct me how to document the steps to check the data for accuracy. Thanks.

    Reply

  12. Hello.
    Thanks a lot for sharing kind of information. Your article provide such a great information about testing oracle database backups.

    Reply

  15. Parts of the post are misinforming:

    “This gets us to a point where we will have to validate our backups.

    To validate our backups we can use:

    RMAN> BACKUP VALIDATE DATABASE;”

    “backup validate” is not used to validate the backups by any stretch of imagination. It is used to perform physical and logical validate (as long as you include “check logical” in the command) of the physical structures of the database: data files, temp files, controlfiles but not the redologs.

    To validate the backups you would have to perform a “restore validate database plus archivelog all” a variation of it up to a specific point in time.

    Please do your homework before posting.

    See documentation:
    https://docs.oracle.com/database/121/BRADV/rcmvalid.htm#GUID-6BD7EA93-053E-4934-A453-6DA6726D093B

    Reply

Leave a Comment