4.8 Reporting on Backups and Database Schema

The RMAN REPORT command analyzes the available backups and your database to answer imoprtant 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 database at some previous time?

Which files have not been backed up recently?

Note:

The results of REPORT are only correct if the RMAN repository has an accurate record of available backups and the state of your database. For example, if backups have been deleted from disk or tape outside of RMAN, reports generated by RMAN do not automatically reflect these changes.

If some backups recorded in the RMAN repository have been deleted, or if some are temporarily unavailable due to a storage device being offline or media being unavailable, you can use the CROSSCHECK commands to update the status of all backups, or the CHANGE, CATALOG, UNCATALOG and DELETE commands to directly set the status of individual backups. See Chapter 8, "Recovery Manager Maintenance Tasks" to learn how to update the RMAN repository to reflect your actual available backups.

This section contains the following topics:

About Reports of RMAN Backups
Reporting on Files Needing a Backup Under a Retention Policy
Reporting on Datafiles Affected by Unrecoverable Operations
Reporting Obsolete Backups
Reporting on the Database Schema

4.8.1 About Reports of RMAN Backups

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

Note:

The REPORT command use the information in the RMAN repository as the basis for their output. If backups have been manipulated outside of the control of RMAN (for example, if you believe some disk-based backups have been deleted, or tape backups are temporarily unavailable or permanently lost) then see Chapter 8, "Recovery Manager Maintenance Tasks" for details on how to update the RMAN repository record to contain the actual set of available backups using the CROSSCHECK, CHANGE, CATALOG, UNCATALOG and DELETE commands. Otherwise, the output of REPORT may be misleading.

4.8.2 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:

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

Note:

If you disable the retention policy using CONFIGURE RETENTION POLICY TO NONE, then REPORT NEED BACKUP returns an error message, because without a retention policy, RMAN cannot determine which files need to be backed up.

4.8.2.1 Using RMAN REPORT NEED BACKUP with Different Retention Policies

You can specify different criteria for REPORT NEED BACKUP, using one of the following forms of the command:

REPORT NEED BACKUP RECOVERY WINDOW OFn DAYS

Displays objects requiring backup to satisfy a recovery window-based retention policy.
REPORT NEED BACKUP REDUNDANCYn

Displays objects requiring backup to satisfy a redundancy-based retention policy.
REPORT NEED BACKUP DAYS = n

Displays files that require more than n days' worth of archived redo log files for recovery.
REPORT NEED BACKUP INCREMENTAL n

Displays files that require application of more than n incremental backups for recovery.

4.8.2.2 Using RMAN REPORT NEED BACKUP with Tablespaces and Datafiles

REPORT NEED BACKUP can check the entire database, skip specified tablespaces, or check only specific tablespaces or datafiles against different retention policies, as shown in the following examples:

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

4.8.2.3 Using REPORT NEED BACKUP with Backups onTape or Disk Only

You can limit the backups tested by REPORT NEED BACKUP to disk-based or tape-based backups only, as shown in these examples:

RMAN> REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE DEVICE TYPE SBT;
RMAN> REPORT NEED BACKUP DEVICE TYPE DISK;
RMAN> REPORT NEED BACKUP TABLESPACE TBS_3 DEVICE TYPE SBT;

4.8.3 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 and the type of backup required to ensure the datafile can be restored from backup, use the REPORT UNRECOVERABLE command, as shown in this example:

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

4.8.4 Reporting 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. 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-05          /backup/ora_df549738566_s70_s1
Datafile Copy        45     08-FEB-05          /backup/ora_df549738567_s71_s1
Datafile Copy        46     08-FEB-05          /backup/ora_df549738568_s72_s1
Backup Set           26     08-FEB-05
  Backup Piece       26     08-FEB-05          /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;

You can specify to only consider backups on disk or on tape when checking for obsolete files, by specifying a device type with the REPORT command, as in this example:

REPORT OBSOLETE RECOVERY WINDOW OF 3 DAYS DEVICE TYPE DISK;
REPORT OBSOLETE REDUNDANCY 1;

To report obsolete backups:

Connect to your target database and your recovery catalog (if you are using a recovery catalog.)
To make sure that your RMAN repository has current information about the status of different backups, you may want to issue CROSSCHECK commands to update the status of backups in the repository compared to their status on disk, or use the CHANGE, CATALOG, UNCATALOG and DELETE commands to directly specify the status of individual backups.

In the simplest case, you could crosscheck all backups on disk, tape or both, using any one of the following commands:
```
RMAN> CROSSCHECK BACKUP DEVICE TYPE DISK;
RMAN> CROSSCHECK BACKUP DEVICE TYPE SBT;
RMAN> CROSSCHECK BACKUP; # crosshecks all backups on all devices 
```
See Chapter 8, "Recovery Manager Maintenance Tasks" for more details on how to update the RMAN repository record to contain the actual set of available backups.

Run REPORT OBSOLETE to identify which backups are obsolete because they are no longer needed for recovery. Here are several examples:

# lists backups that not needed to recover the database to within last week
REPORT OBSOLETE RECOVERY WINDOW OF 7 DAYS; 

# lists backups not needed for redundancy-based retention policy, considering only backups stored on tape
REPORT OBSOLETE REDUNDANCY = 2 DEVICE TYPE sbt;

4.8.5 Reporting on the Database Schema

The REPORT SCHEMA command lists and displays information about the database files.

After connecting RMAN to the target database and recovery catalog (if you use one), issue REPORT SCHEMA as shown in this example:

RMAN> REPORT SCHEMA;
List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    450      SYSTEM               ***     /oracle/oradata/tbs_01.f
2    50       SYSAUX               ***     /oracle/oradata/tbs_ax1.f
3    2        SYSTEM               ***     /oracle/oradata/tbs_02.f
4    2        TBS_1                ***     /oracle/oradata/tbs_11.f
.
.
.
21   2        TBS_4                ***     /oracle/oradata/tbs_43.f
22   2        TBS_5                ***     /oracle/oradata/tbs_53.f
 
List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    40       TEMP                 32767       /oracle/oradata/tbs_tmp1.f

Note:

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:

REPORT SCHEMA AT TIME 'SYSDATE-14';     # schema 14 days ago
REPORT SCHEMA AT SCN 1000;              # schema at scn 1000
REPORT SCHEMA AT SEQUENCE 100 THREAD 1; # schema at sequence 100