Double Checking Backups

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:

LIST BACKUP SUMMARY;

To list backup by file use the command below:

LIST BACKUP BY FILE;

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:

LIST BACKUP;

LIST COPY;

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 DEVICE TYPE DISK;

RMAN> CROSSCHECK BACKUP DEVICE TYPE SBT;

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

RMAN> REPORT OBSOLETE;

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:

RMAN>REPORT OBSOLETE RECOVERY WINDOW OF 3 DAYS;

RMAN>REPORT OBSOLETE REDUNDANCY 1;

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.

BACKUP VALIDATE DATABASE ARCHIVELOG ALL;

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

BACKUP VALIDATE CHECK LOGICAL DATABASE ARCHIVELOG ALL;

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.