Select Page

Double Checking Backups

Author: Patrick Gates | | July 14, 2016

What are backup and recovery?

The terms backup and recovery refer to the various strategies and procedures involved in protecting your database against data loss and reconstructing the database after any kind of data loss.

There are two types of backups:

  • Physical backup
  • Logical backup

Physical backups are the foundation of any sound backup and recovery strategy. Logical backups are a useful supplement to physical backups in many circumstances, but they do not provide sufficient protection against data loss without physical backups. In our case, when we mention “backup,” it refers to the “physical” backup.

There are two solutions for doing database backup:

  • Recovery Manager, a tool (with command-line client and Enterprise Manager GUI interfaces) that integrates with sessions running on the Oracle server to perform a range of backup and recovery activities, as well as maintain a repository of historical data about your backups
  • The traditional user-managed backup and recovery, where you directly manage the files that make up your database with a mixture of host operating system commands and SQL*Plus backup and recovery-related capabilities

However, Oracle Corporation recommends the Recovery Manager. This blog post discusses how to check database backups using RMAN-based backup and recovery method.

After file has been backed up using RMAN, it can be listed and checked if it’s valid and can be used for recovery, or if it’s obsolete. You can also check how many backup jobs have been issued, the status of each backup job (for example, whether it failed or was completed), when a RMAN backup job started and finished, and what type of backup was performed.

Listing backups and copies

Specify the desired objects with the listObjList or recordSpec clause (refer to Oracle Database Backup and Recovery Reference). By default, RMAN serially lists each backup or proxy copy and then identifies the files included in the backup.

You can also list backups by file. By default, RMAN lists in verbose mode, which means that it provides extensive, multiline information. You can also list backups in a summary mode if the verbose mode generates too much output.

To list backups and copies:

1. Start RMAN and connect to a target database and recovery catalog (if used).

2. To view a summary report of all backups and copies, execute the LIST command with the SUMMARY option.

The following command prints a summary of all RMAN backups:


To list backup by file use the command below:


3. To view verbose output for backups and copies, execute the LIST command without the SUMMARY option.

The following commands list RMAN backups and copies with the default verbose output:



RMAN reporting

The RMAN REPORT command analyzes the available backups and your database, to check if the backup is obsolete and can be deleted.

To report obsolete backups:

1. Connect to your target database and your recovery catalog (if you are using a recovery catalog)

2. To ensure that your RMAN repository has current information about the status of different backups, you may issue CROSSCHECK commands to update the status of backups in the repository compared to their status on disk, or use the CHANGE, CATALOG, UNCATALOG, and DELETE commands to directly specify the status of individual backups

In the simplest case, you could crosscheck all backups on disk, tape or both, using any one of the following commands:



RMAN> CROSSCHECK BACKUP; # crosshecks all backups on all devices

3. Run the command below to identify which backups are obsolete because they are no longer needed for recovery


4. You can also check which backups are obsolete under different recovery window-based or redundancy-based retention policies, by using REPORT OBSOLETE with RECOVERY WINDOW and REDUNDANCY options, as shown in these examples:



Validating database backups

The main purpose of RMAN validation is to check for corrupt blocks and missing files and whether the backup can be restored.

Use the BACKUP VALIDATE command to do the following:

  • Check datafiles for physical and logical block corruption
  • Confirm that all database files exist and are in the correct locations

When you run BACKUPVALIDATE, RMAN reads the files to be backed up in their entirety, as it would during a real backup. RMAN does not, however, actually produce any backup sets or image copies.

You cannot use the BACKUPSET, MAXCORRUPT, or PROXY parameters with BACKUP VALIDATE. To validate specific backup sets, run the VALIDATE command.

To validate backups with the BACKUP VALIDATE command:

  1.    Start RMAN and connect to a target database and recovery catalog (if used)
  2.    Run the BACKUP VALIDATE command

For example, you can validate that all database files and archived logs can be backed up by running a command as shown in the following example. This command checks for physical corruptions only.


  1.    To check for logical corruptions in addition to physical corruptions, run the following variation of the preceding command:


If you need your database backed up, or to learn more about database backup and recovery, contact Datavail today. With nearly 500 DBAs worldwide, Datavail is the largest database services provider in North America. With 24×7 managed database services, including database design, architecture and staffing, Datavail can support your organization as it works with MySQL, regardless of the build you ultimately select.

How to Solve the Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

The “ORA-12154: TNS Oracle error message is very common for database administrators. Learn how to diagnose & resolve this common issue here today.

Vijay Muthu | February 4, 2021

Using Nulls in DB2

Nulls are clearly one of the most misunderstood features of DB2. Here are some examples, tips & guidelines from Craig Mullins to demystify the use of nulls.

Craig Mullins | April 6, 2015

How to Recover a Table from an Oracle 12c RMAN Backup

Our database experts explain how to recover and restore a table from an Oracle 12c RMAN Backup with this step-by-step blog. Read more.

Megan Elphingstone | February 2, 2017

Subscribe to Our Blog

Never miss a post! Stay up to date with the latest database, application and analytics tips and news. Delivered in a handy bi-weekly update straight to your inbox. You can unsubscribe at any time.

Work with Us

Let’s have a conversation about what you need to succeed and how we can help get you there.


Work for Us

Where do you want to take your career? Explore exciting opportunities to join our team.