Reporting on RMAN Operations

Overview of RMAN Reporting

This section explains the purpose and basic concepts of RMAN reporting.

Purpose of RMAN Reporting

As part of your backup and recovery strategy, you should periodically runs reports that indicate what you have backed up. You should also determine which datafiles need backups or which files have not been backed up recently. Also, you can preview which backups RMAN would need to restore if a problem were to occur.

Another important aspect of backup and recovery is monitoring space usage. If you back up to disk, then it is possible for the disk to fill, which can create performance problems or even cause the database to halt. You can use RMAN to determine whether a backups is an obsolete backup and can therefore be deleted.

You may also need 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.

http://www.filibeto.org/sun/lib/nonsun/oracle/11.1.0.6.0/B28359_01_200708/backup.111/b28270/rcmreprt.htm

Querying Details of Past and Current RMAN Jobs

An RMAN job is the set of commands executed within an RMAN session. Thus, one RMAN job can contain multiple commands. For example, you may execute two separate BACKUP commands and a RECOVER COPYcommand in a single session. An RMAN backup job is the set of BACKUP commands executed in one RMAN job. For example, a BACKUP DATABASE and BACKUP ARCHIVELOG ALL command executed in the same RMAN job make up a single RMAN backup job.

The views V$RMAN_BACKUP_JOB_DETAILS and V$RMAN_BACKUP_SUBJOB_DETAILS and their corresponding recovery catalog versions provide details on RMAN backup jobs. For example, the views show how long a backup took, 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. TheSESSION_KEY column is the unique key for the RMAN session in which the backup job occurred.

Note that RMAN backups often write less than they read. Because of RMAN compression, the OUTPUT_BYTES_PER_SEC column cannot be used as measurement of backup speed. The appropriate column to measure backup speed is INPUT_BYTES_PER_SEC. The ratio between read and written data is described in the COMPRESSION_RATIO column.

To query details about past and current RMAN jobs:

  1. Connect SQL*Plus to the database whose backup history you intend to query.
  2. Query the V$RMAN_BACKUP_JOB_DETAILS view for information about the backup type, status, and start and end time.The following query shows the backup job history ordered by session key, which is the primary key for the RMAN session:
    COL STATUS FORMAT a9
    COL hrs    FORMAT 999.99
    SELECT SESSION_KEY, INPUT_TYPE, STATUS,
           TO_CHAR(START_TIME,'mm/dd/yy hh24:mi') start_time,
           TO_CHAR(END_TIME,'mm/dd/yy hh24:mi')   end_time,
           ELAPSED_SECONDS/3600                   hrs
    FROM V$RMAN_BACKUP_JOB_DETAILS
    ORDER BY SESSION_KEY;
    
    

    The following sample output shows the backup job history:

    SESSION_KEY INPUT_TYPE    STATUS    START_TIME     END_TIME           HRS
    ----------- ------------- --------- -------------- -------------- -------
              9 DATAFILE FULL COMPLETED 04/18/07 18:14 04/18/07 18:15     .02
             16 DB FULL       COMPLETED 04/18/07 18:20 04/18/07 18:22     .03
            113 ARCHIVELOG    COMPLETED 04/23/07 16:04 04/23/07 16:05     .01
    
    
  3. Query the V$RMAN_BACKUP_JOB_DETAILS view for the rate of backup jobs in an RMAN session.The following query shows the backup job speed ordered by session key, which the primary key for the RMAN session. The columns in_sec and out_sec columns display the data input and output per second.
    COL in_sec FORMAT a10
    COL out_sec FORMAT a10
    COL TIME_TAKEN_DISPLAY FORMAT a10
    SELECT SESSION_KEY,
           OPTIMIZED,
           COMPRESSION_RATIO,
           INPUT_BYTES_PER_SEC_DISPLAY in_sec,
           OUTPUT_BYTES_PER_SEC_DISPLAY out_sec,
           TIME_TAKEN_DISPLAY
    FROM   V$RMAN_BACKUP_JOB_DETAILS
    ORDER BY SESSION_KEY;
    
    

    The following sample output shows the speed of the backup jobs:

    SESSION_KEY OPT COMPRESSION_RATIO IN_SEC     OUT_SEC    TIME_TAKEN
    ----------- --- ----------------- ---------- ---------- ----------
              9 NO                  1     8.24M      8.24M  00:01:14
             16 NO         1.32732239     6.77M      5.10M  00:01:45
            113 NO                  1     2.99M      2.99M  00:00:44
    
    
  4. Query the V$RMAN_BACKUP_JOB_DETAILS view for the size of the backups in an RMAN session.If you run BACKUP DATABASE, then V$RMAN_BACKUP_JOB_DETAILS.OUTPUT_BYTES shows the total size of backup sets written by the backup job for the database that you are backing up. To view backup set sizes for all registered database, query RC_RMAN_BACKUP_JOB_DETAILS.

    The following query shows the backup job speed ordered by session key, which the primary key for the RMAN session. The columns in_sec and out_sec columns display the data input and output per second.

     COL in_size  FORMAT a10
    COL out_size FORMAT a10
    SELECT SESSION_KEY,
           INPUT_TYPE,
           COMPRESSION_RATIO,
           INPUT_BYTES_DISPLAY in_size,
           OUTPUT_BYTES_DISPLAY out_size
    FROM   V$RMAN_BACKUP_JOB_DETAILS
    ORDER BY SESSION_KEY;
    
    

    The following sample output shows the speed of the backup jobs:

    
    SESSION_KEY INPUT_TYPE    COMPRESSION_RATIO IN_SIZE    OUT_SIZE
    ----------- ------------- ----------------- ---------- ----------
             10 DATAFILE FULL                 1   602.50M    602.58M
             17 DB FULL              1.13736669   634.80M    558.13M
    
    

See Also:

Advertisements

One Comment on “Reporting on RMAN Operations”

  1. Desmond says:

    How do you interpret the value in “Compression_Ratio”?

    Like


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s