11 Reporting on RMAN Operations

As part of your backup and recovery strategy, you should periodically run reports that indicate what you have backed up. You can determine which data files need backups or which files were not backed up recently. Also, you can preview which backups RMAN must restore if a problem occurs.

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

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 about 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, several 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 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 list the backups that RMAN can restore to the specified time.

  RESTORE ... PREVIEW queries the metadata but does not 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 operations, 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 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.

You can view reports on the metadata related to a multitenant container database (CDB), the root only, or one or more pluggable databases (PDBs). The information in this chapter about reporting pertains to CDBs and PDBs, with the differences described in the following sections.

This section contains the following topics:

• Reporting in CDBs

• Reporting in PDBs

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, data file, 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

Besides backups and copies, RMAN can list other types of data. Table 11-1 summarizes several useful objects that you can list.

The LIST command supports options that control how output is displayed. Table 11-2 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.

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 COPY LIKE '/tmp/%';
   # specify a backup or copy by a range of completion dates
   LIST COPY OF DATAFILE 2 COMPLETED BETWEEN '10-DEC-2012' AND '17-DEC-2012';
   # specify logs backed up at least twice to tape
   LIST ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE sbt;
   

3. Examine the output.

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-13
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20131021T094513
        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-13 /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-13
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20131104T195949
        Piece Name: /disk1/2013_11_04/o1_mf_nnndf_TAG20131104T195949_ztjxfvgz_.bkp
  List of Datafiles in backup set 5
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 631092     04-NOV-13 /oracle/dbs/tbs_01.f

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;
   

   See Also:

   Oracle Database Backup and Recovery Reference for an explanation of the various column headings in the LIST output

   Following is a sample output of listing the incarnation of a particular database:

   RMAN> LIST INCARNATION OF DATABASE rdbms;
   
   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-13
   2       2       RDBMS    774627068        CURRENT 173832     21-OCT-13
   

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

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 must 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 11-3 summarizes the REPORT options.

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

   See Also:

   "Making Database Connections with RMAN"

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
   

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

   See Also:

   "Making Database Connections with RMAN"

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; # crosschecks all backups on all devices 
   

   See 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;
   
   RMAN retention policy will be applied to the command
   RMAN retention policy is set to redundancy 1
   Report of obsolete backups and copies
   Type                 Key    Completion Time    Filename/Handle
   -------------------- ------ ------------------ --------------------
   Datafile Copy        44     08-FEB-13          /backup/ora_df549738566_s70_s1
   Datafile Copy        45     08-FEB-13          /backup/ora_df549738567_s71_s1
   Datafile Copy        46     08-FEB-13          /backup/ora_df549738568_s72_s1
   Backup Set           26     08-FEB-13
     Backup Piece       26     08-FEB-13          /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;
   

1. Start RMAN and connect to the desired databases.

   See Also:

   "Making Database Connections with RMAN"

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
   

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
   

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 10 different target databases registered in the same recovery catalog, then any query of the catalog views shows the metadata for all incarnations of all 10 databases. You often must 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.

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

   To obtain the DB_KEY for a database run 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 the RC_DATABASE_INCARNATION view. 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';