Oracle RMAN Reporting

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.

Historical information about RMAN jobs:

In some cases, the V$ views supply information that is not available through use of the LIST and REPORT commands. You should periodically run the reports to obtain historical information about RMAN jobs. For example, you may want to know how many backup jobs have been issued, the status of each backup job (for example, whether it failed or completed), when a job started and finished, and what type of backup was performed.
The views V$RMAN_BACKUP_JOB_DETAILS and V$RMAN_BACKUP_SUBJOB_DETAILS provide details about RMAN backup jobs.
RMAN Reporting has been enhanced through V$VIEWS from version to version. Backup operations are more transparent and now DBA’s can easily embed queries to these views in SQL scripts, and generate historical reports. These reports help to analyze the issues and provide corrective action plan to prevent further failures.
Enhanced v$views from Oracle 10g

The V$RMAN_OUTPUT view is an in-memory view and shows the output of all currently running and recently completed RMAN jobs.

The following query shows the output of a RMAN job

SQL> Select OUTPUT from V$RMAN_OUTPUT where SESSION_STAMP='698172913';

OUTPUT
-----------------------------------------------------------------
connected to target database: PINDBTS (DBID=3952868985)

executing command: SET SNAPSHOT
using target database control file instead of recovery catalog
snapshot control file name set to: /backup/dvtestrac01/rman/pindbts1/snapcf.f
new RMAN configuration parameters are successfully stored

allocated channel: d1
channel d1: sid=311 instance=pindbts1 devtype=DISK

sql statement: alter system archive log current

Starting backup at 21-SEP-09
channel d1: starting full datafile backupset
channel d1: specifying datafile(s) in backupset
including current control file in backupset
channel d1: starting piece 1 at 21-SEP-09

---- (Some detail removed for brevity) ----

Finished backup at 21-SEP-09
released channel: d1

95 rows selected.

The V$RMAN_STATUS view shows the status of completed and running RMAN jobs. The running RMAN job information is stored in memory and once the job is completed the information is stored in the control file.

The following query shows the status of completed, completed with errors, failed and running RMAN jobs.

SQL> select OPERATION, START_TIME, END_TIME, OBJECT_TYPE, STATUS
from v$RMAN_STATUS order by START_TIME;

OPERATION START_TIME END_TIME OBJECT_TYPE STATUS
----------- -------------------- ---------------------- ----------- ----------------------
RMAN 09/20/2009 20:00:06 09/20/2009 20:01:21 COMPLETED
BACKUP 09/20/2009 20:00:09 09/20/2009 20:01:06 DB FULL COMPLETED
BACKUP 09/20/2009 20:01:11 09/20/2009 20:01:21 ARCHIVELOG COMPLETED
RMAN 09/20/2009 20:05:03 09/20/2009 20:05:28 COMPLETED WITH ERRORS
BACKUP 09/20/2009 20:05:16 09/20/2009 20:05:28 ARCHIVELOG FAILED
RMAN 09/20/2009 20:10:15 09/20/2009 20:10:47 RUNNING
BACKUP 09/20/2009 20:10:39 09/20/2009 20:10:47 ARCHIVELOG RUNNING

The V$BACKUP_FILES view simulates the LIST BACKUP and LIST COPY RMAN commands. Also this view provides the information used as the basis of the REPORT OBSOLETE command. Now you can get the exact physical size of the backup set from BS_BYTES column, as the BYTES column shows the size of the physical file.

CONCLUSION

This article discuss about ensuring database backup for a successful recovery using RMAN reporting.

Author Information:-

Name: Satishbabu Gunukula
Email: