10 Reporting on RMAN Operations

Purpose of RMAN Reporting

As part of your backup and recovery strategy, you should periodically run 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.

Basic Concepts of RMAN Reporting

RMAN always stores its RMAN repository of metadata in the control file of each target database on which it performs operations. For example, suppose that you use RMAN to back up the prod1 and prod2 databases. RMAN stores the metadata for backups of prod1 in the control file of prod1, and the metadata for backups of prod2 in the control file of prod2.

Optionally, you can use RMAN with a recovery catalog. In this case, RMAN maintains an additional repository of metadata in a set of tables in a separate recovery catalog database. For example, you could create a recovery catalog in prod3. You can register multiple target databases in this recovery catalog. For example, if you register prod1 and prod2 in the recovery catalog stored in prod3, then RMAN stores metadata about its backups of prod1 and prod2 in the recovery catalog schema.

You can access metadata from the RMAN repository in several different ways:

• The RMAN LIST and REPORT commands provide extensive information on available backups and how they can be used to restore and recover your database.

  The LIST command is described in "Listing Backups and Recovery-Related Objects" and REPORT is described in "Reporting on Backups and Database Schema".

• When the database is open, a number of V$ views provide direct access to RMAN repository records in the control file of each target database.

  Some V$ views such as V$DATAFILE_HEADER, V$PROCESS, and V$SESSION contain information not found in the recovery catalog views. The V$ views are documented in Oracle Database Reference.

• If your database is registered in a recovery catalog, then RC_ views provide direct access to the RMAN repository data stored in the recovery catalog.

  The RC_ views that mostly correspond to the V$ views. The RC_ views are documented in Oracle Database Backup and Recovery Reference.

• The RESTORE ... PREVIEW and RESTORE ... VALIDATE HEADER commands lists the backups that RMAN could restore to the specified time.

  RESTORE ... PREVIEW queries the metadata and does not actually read the backup files. The RESTORE ... VALIDATE HEADER command performs the same work, but in addition to listing the files needed for restore and recovery, the 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. These commands are documented in "Previewing Backups Used in Restore Operations".

As explained in Chapter 11, "Maintaining RMAN Backups and Repository Records," the RMAN repository can sometimes fail to reflect the reality on disk and tape. For example, a user may delete a backup with an operating system utility, so that the RMAN repository incorrectly reports the backup as available.

You can use commands such as CHANGE, CROSSCHECK, and DELETE to update the RMAN repository to reflect the actual state of available backups. Otherwise, the output of the commands and views may be misleading, which means that RMAN may not be able to find the backups to restore and recover your database.

LIST ARCHIVELOG ALL FOR DB_UNIQUE_NAME sfstandby;

About the LIST Command

The primary purpose of the LIST command is to list backup and copies. For example, you can list:

• Backups and proxy copies of a database, tablespace, datafile, archived redo log, or control file

• Backups that have expired

• Backups restricted by time, path name, device type, tag, or recoverability

• Archived redo log files and disk copies

The primary purpose of the LIST command is to list backup and copies. Besides backups and copies, the RMAN can list other types of data. The following table summarizes several useful objects that you can list.

The LIST command supports a number of options that enables you to control how output is displayed. The following table summarizes the most common LIST options.

The LIST objects and options are not exhausted by the contents of the preceding tables. For example, you can list backups restricted by time, path name, device type, tag, or recoverability.

Listing Backups and Copies

Specify the desired objects with the listObjList or recordSpec clause (refer to Oracle Database Backup and Recovery Reference). If you do not specify an object, then RMAN displays copies of all database files and archived redo log files.

By default, RMAN serially lists each backup or proxy copy and then identifies the files included in the backup. You can also list backups by file.

By default, RMAN lists in verbose mode, which means that it provides extensive, multiline information. You can also list backups in a summary mode if the verbose mode generates too much output.

To list backups and copies:

1. Start RMAN and connect to a target database and recovery catalog (if used).

2. To view a summary report of all backups and copies, execute the LIST command with the SUMMARY option.

   The following command prints a summary of all RMAN backups:

   LIST BACKUP SUMMARY;
   

   Sample output follows:

   List of Backups
   ===============
   Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
   ------- -- -- - ----------- --------------- ------- ------- ---------- ---
   1       B  A  A SBT_TAPE    21-OCT-07       1       1       NO         TAG20071021T094505
   2       B  F  A SBT_TAPE    21-OCT-07       1       1       NO         TAG20071021T094513
   3       B  A  A SBT_TAPE    21-OCT-07       1       1       NO         TAG20071021T094624
   4       B  F  A SBT_TAPE    21-OCT-07       1       1       NO         TAG20071021T094639
   5       B  F  A DISK        04-NOV-07       1       1       YES        TAG20071104T195949
   

3. To view verbose output for backups and copies, execute the LIST command without the SUMMARY option.

   The following commands list RMAN backups and copies with the default verbose output:

   LIST BACKUP;
   LIST COPY;
   

   Sample output for LIST BACKUP and LIST COPY follows:

   List of Backup Sets
   ===================
   
   BS Key  Size       Device Type Elapsed Time Completion Time
   ------- ---------- ----------- ------------ ---------------
   7       136M       DISK        00:00:20     04-NOV-06      
           BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20071104T200759
           Piece Name: /d2/RDBMS/backupset/2007_11_04/o1_mf_annnn_TAG20071104T200759_ztjxx3k8_.bkp
   
     List of Archived Logs in backup set 7
     Thrd Seq     Low SCN    Low Time  Next SCN   Next Time
     ---- ------- ---------- --------- ---------- ---------
     1    1       173832     21-OCT-06 174750     21-OCT-06
     1    2       174750     21-OCT-06 174755     21-OCT-06
     1    3       174755     21-OCT-06 174758     21-OCT-06
   
   BS Key  Type LV Size       Device Type Elapsed Time Completion Time
   ------- ---- -- ---------- ----------- ------------ ---------------
   8       Full    2M         DISK        00:00:01     04-NOV-06      
           BP Key: 8   Status: AVAILABLE  Compressed: NO  Tag: TAG20071104T200829
           Piece Name: /disk1/oracle/dbs/c-774627068-20071104-01
     Controlfile Included: Ckp SCN: 631510       Ckp time: 04-NOV-06
     SPFILE Included: Modification time: 21-OCT-06
   

   List of Datafile Copies
   =======================
    
   Key     File S Completion Time Ckp SCN    Ckp Time
   ------- ---- - --------------- ---------- ---------------
   1       7    A 11-OCT-06       360072     11-OCT-06
           Name: /work/orcva/RDBMS/datafile/o1_mf_tbs_2_2lv7bf82_.dbf
           Tag: DF7COPY
    
   2       8    A 11-OCT-06       360244     11-OCT-06
           Name: /work/orcva/RDBMS/datafile/o1_mf_tbs_2_2lv7qmcj_.dbf
           Tag: TAG20071011T184835
    
   List of Control File Copies
   ===========================
    
   Key     S Completion Time Ckp SCN    Ckp Time
   ------- - --------------- ---------- ---------------
   3       A 11-OCT-06       360380     11-OCT-06
           Name: /d2/RDBMS/controlfile/o1_mf_TAG20071011T185335_2lv80zqd_.ctl
           Tag: TAG20071011T185335
    
   List of Archived Log Copies for database with db_unique_name RDBMS
   =====================================================================
    
   Key     Thrd Seq     S Low Time
   ------- ---- ------- - ---------
   1       1    1       A 11-OCT-06
           Name: /work/arc_dest/arcr_1_1_603561743.arc
    
   2       1    2       A 11-OCT-06
           Name: /work/arc_dest/arcr_1_2_603561743.arc
    
   3       1    3       A 11-OCT-06
           Name: /work/arc_dest/arcr_1_3_603561743.arc
   

4. To list backups by file, execute LIST with the BY FILE option, specifying the desired objects to list and options. For example, you can enter:

   LIST BACKUP BY FILE;
   

   Sample output follows:

   List of Datafile Backups
   ========================
   
   File Key     TY LV S Ckp SCN    Ckp Time  #Pieces #Copies Compressed Tag
   ---- ------- -  -- - ---------- --------- ------- ------- ---------- ---
   1    5       B  F  A 631092     04-NOV-06 1       1       YES        TAG20071104T195949
        2       B  F  A 175337     21-OCT-06 1       1       NO         TAG20071021T094513
   2    5       B  F  A 631092     04-NOV-06 1       1       YES        TAG20071104T195949
        2       B  F  A 175337     21-OCT-06 1       1       NO         TAG20071021T094513
   
   ... some rows omitted
   
   List of Archived Log Backups
   ============================
   
   Thrd Seq     Low SCN    Low Time  BS Key  S #Pieces #Copies Compressed Tag
   ---- ------- ---------- --------- ------- - ------- ------- ---------- ---
   1    1       173832     21-OCT-06 7       A 1       1       NO         TAG20071104T200759
                                     1       A 1       1       NO         TAG20071021T094505
   1    2       174750     21-OCT-06 7       A 1       1       NO         TAG20071104T200759
                                     1       A 1       1       NO         TAG20071021T094505
   ... some rows omitted
   1    38      575472     03-NOV-06 7       A 1       1       NO         TAG20071104T200759
   1    39      617944     04-NOV-06 7       A 1       1       NO         TAG20071104T200759
   
   
   List of Controlfile Backups
   ===========================
   CF Ckp SCN Ckp Time  BS Key  S #Pieces #Copies Compressed Tag
   ---------- --------- ------- - ------- ------- ---------- ---
   631510     04-NOV-06 8       A 1       1       NO         TAG20071104T200829
   631205     04-NOV-06 6       A 1       1       NO         TAG20071104T200432
   
   List of SPFILE Backups
   ======================
   Modification Time BS Key  S #Pieces #Copies Compressed Tag
   ----------------- ------- - ------- ------- ---------- ---
   21-OCT-06         8       A 1       1       NO         TAG20071104T200829
   21-OCT-06         6       A 1       1       NO         TAG20071104T200432
   

Listing Selected Backups and Copies

You can specify several different conditions to narrow your LIST output.

To list selected backups and copies:

1. Start RMAN and connect to a target database and recovery catalog (if used).

2. Run LIST COPY or LIST BACKUP with the listObjList or recordSpec clause. For example, enter any of the following commands:

   # lists backups of all files in database
   LIST BACKUP OF DATABASE; 
   # lists copy of specified datafile    
   LIST COPY OF DATAFILE 'ora_home/oradata/trgt/system01.dbf'; 
   # lists specified backup set
   LIST BACKUPSET 213; 
   # lists datafile copy
   LIST DATAFILECOPY '/tmp/tools01.dbf';
   

   You can also restrict the search by specifying the maintQualifier or RECOVERABLE clause. For example, enter any of the following commands:

   # specify a backup set by tag
   LIST BACKUPSET TAG 'weekly_full_db_backup';
   # specify a backup or copy by device type
   LIST COPY OF DATAFILE 'ora_home/oradata/trgt/system01.dbf' DEVICE TYPE sbt;
   # specify a backup by directory or path
   LIST BACKUP LIKE '/tmp/%';
   # specify a backup or copy by a range of completion dates
   LIST COPY OF DATAFILE 2 COMPLETED BETWEEN '10-DEC-2002' AND '17-DEC-2002';
   # specify logs backed up at least twice to tape
   LIST ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE sbt;
   

3. Examine the output.

   The output depends upon the options you pass to the LIST command. For example, the following lists copies of datafile 1:

   RMAN> LIST BACKUP OF DATAFILE 1;
   
   List of Backup Sets
   ===================
   
   BS Key  Type LV Size       Device Type Elapsed Time Completion Time
   ------- ---- -- ---------- ----------- ------------ ---------------
   2       Full    230M       SBT_TAPE    00:00:49     21-OCT-06
           BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20071021T094513
           Handle: 02f4eatc_1_1   Media: /smrdir
     List of Datafiles in backup set 2
     File LV Type Ckp SCN    Ckp Time  Name
     ---- -- ---- ---------- --------- ----
     1       Full 175337     21-OCT-06 /oracle/dbs/tbs_01.f
   
   BS Key  Type LV Size       Device Type Elapsed Time Completion Time
   ------- ---- -- ---------- ----------- ------------ ---------------
   5       Full    233M       DISK        00:04:30     04-NOV-06
           BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20071104T195949
           Piece Name: /disk1/2007_11_04/o1_mf_nnndf_TAG20071104T195949_ztjxfvgz_.bkp
     List of Datafiles in backup set 5
     File LV Type Ckp SCN    Ckp Time  Name
     ---- -- ---- ---------- --------- ----
     1       Full 631092     04-NOV-06 /oracle/dbs/tbs_01.f
   

Listing Database Incarnations

Each time an OPEN RESETLOGS operation is performed on a database, this operation creates a new incarnation of the database. Database incarnations and their effect on database recovery are explained in "Database Incarnations".

When performing incremental backups, RMAN can use a backup from a previous incarnation or the current incarnation as a basis for subsequent incremental backups. When performing restore and recovery, RMAN can use backups from a previous incarnation in restore and recovery operations just as it would use backups from the current incarnation, as long as all archived logs are available.

To list database incarnations:

1. Start RMAN and connect to a target database and recovery catalog (if used).

2. Run the LIST INCARNATION command, as shown in the following example:

   LIST INCARNATION;
   

   If you are using a recovery catalog, and if you register multiple target databases in the same catalog, then you can distinguish them by using the OF DATABASE option:

   LIST INCARNATION OF DATABASE prod3;
   

   Refer to Oracle Database Backup and Recovery Reference for an explanation of the various column headings in the LIST output). Sample output follows:

   RMAN> LIST INCARNATION OF DATABASE;
   
   List of Database Incarnations
   DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
   ------- ------- -------- ---------------- ------  ---------- ----------
   1       1       RDBMS    774627068        PARENT  1          21-OCT-06
   2       2       RDBMS    774627068        CURRENT 173832     21-OCT-06
   

   The preceding output indicates that a RESETLOGS was performed on database trgt at SCN 164378, resulting in a new incarnation. The incarnation is distinguished by incarnation key (represented in the Inc Key column).

Listing Restore Points

You can use the LIST command to list either a specific restore point or all restore points known to the RMAN repository. The variations of the command are as follows:

LIST RESTORE POINT restore_point_name;
LIST RESTORE POINT ALL;

RMAN indicates the SCN and time of the restore point, the type of restore point, and the name of the restore point. The following example shows sample output:

RMAN> LIST RESTORE POINT ALL;
 
using target database control file instead of recovery catalog
SCN              RSP Time  Type       Time      Name
---------------- --------- ---------- --------- ----
341859           28-JUL-06            28-JUL-06 NORMAL_RS
343690           28-JUL-06 GUARANTEED 28-JUL-06 GUARANTEED_RS

You can also see a list of the currently defined restore points by querying the V$RESTORE_POINT view as follows:

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
       GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE
FROM   V$RESTORE_POINT;

You can view the name of each restore point, the SCN, wall-clock time and database incarnation number at which the restore points were created, whether each restore point is a guaranteed restore point, and how much space in the recovery area is being used for data needed for Flashback Database operations to that restore point.

You can also use the following query to view only the guaranteed restore points:

SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,
       GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE
FROM   V$RESTORE_POINT
WHERE  GUARANTEE_FLASHBACK_DATABASE='YES';

For normal restore points, STORAGE_SIZE is zero. For guaranteed restore points, STORAGE_SIZE indicates the amount of disk space in the fast recovery area used to retain logs required to guarantee FLASHBACK DATABASE to that restore point.

About Reports of RMAN Backups

You can use the REPORT command to answer important questions, such as:

• Which files need a backup?

• Which files have had unrecoverable operations performed on them?

• Which backups are obsolete and can be deleted?

• What was the physical schema of the target database or a database in the Data Guard environment at some previous time?

• Which files have not been backed up recently?

Reports enable you to confirm that your backup and recovery strategy is in fact meeting your requirements for database recoverability. The two major forms of REPORT used to determine whether your database is recoverable are:

• REPORT NEED BACKUP

  Reports which database files need to be backed up to meet a configured or specified retention policy

• REPORT UNRECOVERABLE

  Reports which database files require backup because they have been affected by some NOLOGGING operation such as a direct-path INSERT

The RMAN repository contains other information that you can access with the REPORT command. Table 10-3 summarizes the REPORT options.

Reporting on Files Needing a Backup Under a Retention Policy

Use the REPORT NEED BACKUP command to determine which database files need backup under a specific retention policy.

With no arguments, REPORT NEED BACKUP reports which objects need backup under the currently configured retention policy. The output for a configured retention policy of REDUNDANCY 1 is similar to this example:

RMAN> REPORT NEED BACKUP;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
Report of files with less than 1 redundant backups
File #bkps Name
---- ----- -----------------------------------------------------
2    0     /oracle/oradata/trgt/undotbs01.dbf

REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE SKIP TABLESPACE TBS_2;
REPORT NEED BACKUP REDUNDANCY 2 DATAFILE 1;
REPORT NEED BACKUP TABLESPACE TBS_3; # uses configured retention policy
REPORT NEED BACKUP INCREMENTAL 2; # checks entire database

REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE DEVICE TYPE sbt;
REPORT NEED BACKUP DEVICE TYPE DISK;
REPORT NEED BACKUP TABLESPACE TBS_3 DEVICE TYPE sbt; 

Reporting on Datafiles Affected by Unrecoverable Operations

When a datafile has been changed by an unrecoverable operation, such as a direct load 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.

To identify datafiles affected by an unrecoverable operation:

1. Start RMAN and connect to a target database and recovery catalog (if used).

2. Execute the REPORT UNRECOVERABLE command.

   The following example includes sample output:

   RMAN> REPORT UNRECOVERABLE;
   
   Report of files that need backup due to unrecoverable operations
   File Type of Backup Required Name
   ---- ----------------------- -----------------------------------
   1    full                    /oracle/oradata/trgt/system01.dbf
   

Reporting on Obsolete Backups

You can report backup sets, backup pieces and datafile copies that are obsolete, that is, not needed to meet a specified retention policy, by specifying the OBSOLETE keyword.

To report obsolete backups:

1. Start RMAN and connect to a target database and recovery catalog (if used).

2. Execute the CROSSCHECK command to update the status of backups in the repository compared to their status on disk.

   In the simplest case, you could crosscheck all backups on disk, tape or both, using any one of the following commands:

   CROSSCHECK BACKUP DEVICE TYPE DISK;
   CROSSCHECK BACKUP DEVICE TYPE sbt;
   CROSSCHECK BACKUP; # crosshecks all backups on all devices 
   

   See Chapter 11, "Maintaining RMAN Backups and Repository Records" for more details on how to update the RMAN repository record to contain the actual set of available backups.

3. Run REPORT OBSOLETE to identify which backups are obsolete because they are no longer needed for recovery.

   If you do not specify any other options, then REPORT OBSOLETE displays the backups that are obsolete according to the current retention policy, as shown in the following example:

   RMAN> REPORT OBSOLETE;
   
   Datafile Copy        44     08-FEB-06          /backup/ora_df549738566_s70_s1
   Datafile Copy        45     08-FEB-06          /backup/ora_df549738567_s71_s1
   Datafile Copy        46     08-FEB-06          /backup/ora_df549738568_s72_s1
   Backup Set           26     08-FEB-06
     Backup Piece       26     08-FEB-06          /backup/ora_df549738682_s76_s1
   .
   .
   .
   

   You can also check which backups are obsolete under different recovery window-based or redundancy-based retention policies, by using REPORT OBSOLETE with RECOVERY WINDOW and REDUNDANCY options, as shown in these examples:

   REPORT OBSOLETE RECOVERY WINDOW OF 3 DAYS;
   REPORT OBSOLETE REDUNDANCY 1;
   

Reporting on the Database Schema

The REPORT SCHEMA command lists and displays information about the database files, tablespaces, and so on. See Oracle Database Backup and Recovery Reference for a description of the REPORT SCHEMA output.

If you do not specify FOR DB_UNIQUE_NAME with REPORT SCHEMA, then a recovery catalog connection is optional, but a target database connection is required. In a Data Guard environment, you can specify REPORT SCHEMA FOR DB_UNIQUE_NAME to report the schema for a database in the environment. In this case, an RMAN connection to a target database is not required. You can connect RMAN to the recovery catalog and set the DBID instead.

To report on the database schema:

1. Start RMAN and connect to the desired databases.

2. If you did not connect RMAN to a target database in the previous step, and you intend to specify the FOR DB_UNIQUE_NAME clause on REPORT SCHEMA, then set the database DBID. For example, enter the following command:

   RMAN> SET DBID 28014364;
   

3. Run the REPORT SCHEMA command as shown in the following example:

   RMAN> REPORT SCHEMA;
    
   Report of database schema for database with db_unique_name DGRDBMS
    
   List of Permanent Datafiles
   ===========================
   File Size(MB) Tablespace           RB segs Datafile Name
   ---- -------- -------------------- ------- ------------------------
   1    450      SYSTEM               YES     /disk1/oracle/dbs/t_db1.f
   2    141      SYSAUX               NO      /disk1/oracle/dbs/t_ax1.f
   3    50       UD1                  YES     /disk1/oracle/dbs/t_undo1.f
   4    50       TBS_11               NO      /disk1/oracle/dbs/tbs_111.f
   5    50       TBS_11               NO      /disk1/oracle/dbs/tbs_112.f
    
   List of Temporary Files
   =======================
   File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
   ---- -------- -------------------- ----------- --------------------
   1    40       TEMP                 32767       /disk1/oracle/dbs/t_tmp1.f
   

   If you use a recovery catalog, then you can use the atClause to specify a past time, SCN, or log sequence number, as shown in these examples of the command:

   RMAN> REPORT SCHEMA AT TIME 'SYSDATE-14';     # schema 14 days ago
   RMAN> REPORT SCHEMA AT SCN 1000;              # schema at scn 1000
   RMAN> REPORT SCHEMA AT SEQUENCE 100 THREAD 1; # schema at sequence 100
   RMAN> REPORT SCHEMA FOR DB_UNIQUE_NAME standby1; # schema for database standby1
   

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 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. 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 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 RC_RMAN_BACKUP_JOB_DETAILS.

   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_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
   

Determining the Encryption Status of Backup Pieces

The ENCRYPTED column of V$BACKUP_PIECE and RC_BACKUP_PIECE indicates whether a backup piece is encrypted (YES) or unencrypted (NO). For example, you can run the following query in SQL*Plus to determine which backup pieces are encrypted:

COL BS_REC    FORMAT 99999
COL BP_REC    FORMAT 99999
COL MB        FORMAT 9999999
COL ENCRYPTED FORMAT A7
COL TAG       FORMAT A25

SELECT S.RECID AS "BS_REC", P.RECID AS "BP_REC", P.ENCRYPTED, 
       P.TAG, P.HANDLE AS "MEDIA_HANDLE"
FROM   V$BACKUP_PIECE P, V$BACKUP_SET S
WHERE  P.SET_STAMP = S.SET_STAMP
AND    P.SET_COUNT = S.SET_COUNT;

The following sample output shows that the backups are encrypted:

BS_REC BP_REC ENCRYPT TAG
------ ------ ------- -------------------------
MEDIA_HANDLE
--------------------------------------------------------------------------------
     1      1 YES     TAG20070711T140124
/disk1/c-39525561-20070711-00
 
     2      2 YES     TAG20070711T140128
/disk1/c-39525561-20070711-01
 
     3      3 YES     TAG20070711T140130
/disk1/c-39525561-20070711-02

About Recovery Catalog Views

RMAN obtains backup and recovery metadata from a target database control file and stores it in the tables of the recovery catalog. The recovery catalog views are derived from these tables. The recovery catalog views are not normalized or optimized for user queries.

In general, the recovery catalog views are not as user-friendly as the RMAN reporting commands. For example, when you start RMAN and connect to a target database, you obtain the information for this target database only when you issue LIST, REPORT, and SHOW commands. If you have ten different target databases registered in the same recovery catalog, then any query of the catalog views show the metadata for all incarnations of all ten databases. You often have to perform complex selects and joins among the views to extract usable information about a database incarnation.

Most of the catalog views have a corresponding V$ view in the database. For example, RC_BACKUP_PIECE corresponds to V$BACKUP_PIECE. The primary difference between the recovery catalog view and corresponding V$ view is that each recovery catalog view contains metadata about all the target databases registered in the recovery catalog. The V$ view contains information only about itself.

Querying Catalog Views for the Target DB_KEY or DBID Values

The DB_KEY value, which is the primary key for a registered database, is used only in the recovery catalog. The easiest way is to obtain the DB_KEY is to use the DBID of the target database, which is displayed whenever you connect RMAN to a database as TARGET. The DBID distinguishes databases registered in the RMAN recovery catalog.

Assume that you want to obtain information about one of the databases registered in the recovery catalog.

To query the catalog for information about the current incarnation of a database:

1. Determine the DBID for the database whose records you want to view.

   You can obtain the DBID by looking at the output displayed when RMAN connects to the database, querying V$RMAN_OUTPUT, or querying a V$DATABASE view. The following example connects SQL*Plus to the desired database and queries the DBID:

   SQL> CONNECT / AS SYSDBA
   SQL> SELECT DBID 
     2  FROM   V$DATABASE;
   
   DBID
   ---------
   598368217
   

2. Start SQL*Plus and connect to the recovery catalog database as the owner of the recovery catalog.

3. Obtain the database key for the database whose DBID you obtained in step 1.

   You can then obtain the DB_KEY for a database by running the following query, where dbid_of_target is the DBID obtained in step 1:

   SELECT DB_KEY 
   FROM   RC_DATABASE 
   WHERE  DBID = dbid_of_target;
   

4. Query the records for the current incarnation of the database whose DBID you obtained in step 1.

   To obtain information about the current incarnation of a target database, specify the target database DB_KEY value and perform a join with RC_DATABASE_INCARNATION. Use a WHERE condition to specify that the CURRENT_INCARNATION column value is set to YES. For example, to obtain information about backup sets in the current incarnation of a target database with the DB_KEY value of 1, query as follows:

   SELECT BS_KEY, BACKUP_TYPE, COMPLETION_TIME
   FROM   RC_DATABASE_INCARNATION i, RC_BACKUP_SET b
   WHERE  i.DB_KEY = 1
   AND    i.DB_KEY = b.DB_KEY
   AND    i.CURRENT_INCARNATION = 'YES';
   

Querying RC_BACKUP_FILES

You can query the view RC_BACKUP_FILES for information about all backups of any database registered in the recovery catalog. Before querying RC_BACKUP_FILES, however, you must call DBMS_RCVMAN.SETDATABASE. Specify the DBID of one of the databases registered in the recovery catalog, as shown in the following example:

SQL> CALL DBMS_RCVMAN.SETDATABASE(null,null,null,2283997583);

The fourth parameter must be the DBID of a database registered in the recovery catalog. The other parameters must all be NULL.