There are a number of data dictionary views available for querying about backup information. Table 13-1 describes RMAN-related data dictionary views. These views are available regardless of your use of a recovery catalog (the information in these views is derived from the control file).

Table13-1.DescriptionofRMAN Backup Data Dictionary Views

Sometimes, DBAs new to RMAN have a hard time grasping the concept of backups, backup sets, backup pieces, and data files and how they relate. I find the following query useful when discussing RMAN backup components. This query will display backup sets, the backup pieces with the set, and the data files that are backed up within the backup pieces:

SQL> SET LINES 132 PAGESIZE 100

SQL> BREAK ON REPORT ON bs_key ON completion_time ON bp_name ON file_name SQL> COL bs_key      FORM 99999 HEAD “BS Key”

The output here has been shortened to fit on the page:

BS Key COMPLETIO BP Name

Sometimes, it is useful to report on the performance of RMAN backups. The following query reports on the time taken for an RMAN backup per session.

Here is some sample output:

SESSION_RECID COMPRESSION_RATIO TIME_TAKEN_DISPLAY HOURS                                                              END_TIME

The contents of V $RMAN_BACKUP_JOB_DETAILS are summarized by a session connection to RMAN. Therefore, the report output is more accurate if you connect to RMAN (establishing a session) and then exit out of RMAN after the backup job is complete. If you remain connected to RMAN while running multiple backup jobs, the query output reports on all backup activity while connected (for that session).

You should have an automated method of detecting whether RMAN backups are running and if data files are being backed up. One reliable method of automating such a task is to embed SQL into a shell script and then run the script on a periodic basis from a scheduling utility such as cron.

I typically run two basic types of checks regarding the RMAN backups:

•     Have the RMAN backups run recently?

•     Are there any data files that have not been backed up recently?

The following shell script checks for these conditions. You will need to modify the script and provide it with a username and password for a user that can query the data dictionary objects referenced in the script and also change the email address of where messages are sent.

When running the script, you will need to pass in two variables: the Oracle SID and the threshold number of past days that you want to check for the last time the backups ran or for when a data file was backed up.

For example, to check if backups have been running successfully within the past two days, run the script (named rman_chk.bsh):

$ rman_chk.bsh INVPRD 2

The prior script is basic but effective. You can enhance it as required for your RMAN environment.

It is important to have good backups and verify that the correct files are being backed up on a regular basis, but these files do not matter much unless you can actually use them to restore a database that has been corrupted or had some sort of disaster occur. The next chapter will show you how to handle the restore and recovery.