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 COPY command 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 constitute 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 of 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. The SESSION_KEY column is the unique key for the RMAN session in which the backup job occurred.

RMAN backups often write less than they read. Because of RMAN compression, the OUTPUT_BYTES_PER_SEC column cannot be used as the 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 is the primary key for the RMAN session. The columns IN_SEC and OUT_SEC 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 databases, query V$RMAN_BACKUP_JOB_DETAILS.

The following query shows the backup job size and throughput ordered by session key, which is the primary key for the RMAN session. The columns IN_SIZE and OUT_SIZE 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 size 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