For any company the most important asset is data and the most challenging job is to recovery the database with less downtime with out any data loss, in the event of database failure. In many situations users end up with incomplete recovery of the database with out knowing which data files backed and which data files need to backup. You should ensure that your database is backed up efficiently and should restore successfully when needed. The RMAN reporting provides effective and easy way to determine database backup for a successful recovery.
In general, a catalog view contains the metadata of all databases and you need to write a complex query to extract usable backup information, but with RMAN reporting you can obtain the same information with LIST and REPORT commands. RMAN Reporting has been enhanced from version to version and now you can determine which database files have not been backed up and you can preview the backups required for successful restore...etc.
I have seen numerous postings on Metalink and Oracle forums regarding database restore and recovery issues as they don’t know what they have backed up and what backups they need for a successfully recovery. Using RMAN reporting effectively you can prevent such situations and ending up with incomplete recovery.
As part of your backup and recovery strategy, you should periodically run the reports that indicate
1) What you have backed up.
2) Which datafiles need backups or which files have not been backed up recently.
3) Which backups RMAN would need to restore if a problem occurs.
4) Historical information about RMAN jobs
What you have backed up?
To know what you have backed up, use LIST command to get the lists of backups, copies and other objects relating to backup and recovery (for example, backups that expired, restricted)
For example, you can list backups of all files in database
RMAN> LIST BACKUP OF DATABASE;
You can list copy of specified datafile
RMAN> LIST COPY OF DATAFILE ‘/oradata/users01.dbf';
Besides backups and copies, the RMAN can list other types of data using LIST command and it supports a number of options that enables you to control how output is displayed.
For example, you can list restore points know to repository.
RMAN> LIST RESTORE POINT;
You can list the names of recovery catalog scripts created with the CREATE SCRIPT or REPLACE SCRIPT command
RMAN> LIST SCRIPT NAMES;
List all of the expired backup sets
RMAN> LIST EXPIRED BACKUP SET;
Which datafiles need backups?
To know which files need backup, Use REPORT command that analyzes the available backups in the repository and return results about which files need a backup, which files have had unrecoverable operations on them, which files are obsolete etc.
Use the REPORT NEED BACKUP command to determine which database files need backup under a specific retention policy.
For example, display objects requiring backup to satisfy a recovery window-based retention policy.
RMAN> REPORT NEED BACKUP RECOVERY WINDOW OF n DAYS;
Use REPORT UNRECOVERABLE command to determine which database files need backup when a database file has been affected by unrecoverable operation, such as insert, direct-path insert, normal media recovery cannot be used to recover the file, because an unrecoverable operation does not generate redo. You must perform either a full or incremental backup of affected datafiles after such operations, to ensure that data blocks affected by the unrecoverable operation can be recovered using RMAN.
For example, identify the datafiles affected by an unrecoverable operation.
RMAN > REPORT UNRECOVERABLE;
You should also know which backups are obsolete and can therefore delete. Because disk full can create performance problems or can cause the database to halt. Use CROSSCHECK command to update the status of backups in the RMAN repository compared to their backup status on disk or tape and run REPORT OBSOLETE to identify which backups are obsolete and are no longer needed for recovery.
For example, crosschecks all backups and copies on the disk
RMAN>CROSSCHECK BACKUP DEVICE TYPE DISK;
RMAN> CROSSCHECK BACKUP;
Identify which backups are obsolete and no longer needed for recovery
RMAN> REPORT OBSOLETE;
Delete obsolete backup information from RMAN repository.
RMAN> DELETE OBSOLETE;
This way you can effectively manage the backups and space.
Which backups RMAN would need to restore if a problem occurs?
Use RESTORE... PREVIEW to know which backups would need to restore if a problem occurs. The RESTORE…PREVIEW command does not actually restore, but identifies the backups required to complete a given restore operation based on the information in the repository. This command ensures that all required backups are available or identify the backups required for successful recovery. In case any backup is unavailable, use the CHANGE…UNAVAILABLE command to mark the backup status to UNAVAILABLE and re-run the RESTORE…PREVIEW to see the backups required to perform a restore operation with out using the unavailable backup.
You can use RESTORE…VALIDATE HEADER as an alternative to RESTORE…PREVIEW. In addition to listing of the files needed for restore and recovery, this command validates the backup file headers to determine whether the files on disk or in the media management catalog correspond to the metadata in the RMAN repository.
For example, identify the backups required to complete a database restore.
RMAN> RESTORE DATABASE PREVIEW
Use REVIEW...SUMMARY command to summarize the lengthy output.
RMAN> RESTORE DATABASE PREVIEW SUMMARY;
The RESTORE... PREVIEW RECALL can be used, in cases a restore fails due to a needed backup being stored remotely. Using this RECALL mode with media manager which supports vaulted backups simplifies the RECALL operation, by recalling the backups which are needed for restore from remote storage.
For example, to recall backups stored offsite
RMAN>RESTORE DATABASE PREVIEW RECALL;
You can also use RMAN validation to be sure they can use used for a restore (for ex, check for block corruption and missing backup sets). The RESTORE…VALIDATE command tests weather you can restore from backup and users can validate the restore of the backup control file, SYSTEM tablespace, and all archived logs.
For example, to validate RMAN backups
RMAN> RESTORE DATABASE VALIDATE;
By default VALIDATE does not check for logical corruption but to identify logical corruption use CHECK LOGICAL clause with RESTORE…VALIDATE command.