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 ofREPORT
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:
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:
TheREPORT
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.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 usingCONFIGURE
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.You can specify different criteria for REPORT
NEED
BACKUP
, using one of the following forms of the command:
REPORT
NEED
BACKUP
RECOVERY
WINDOW
OF
n
DAYS
Displays objects requiring backup to satisfy a recovery window-based retention policy.
REPORT
NEED
BACKUP
REDUNDANCY
n
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.
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
See Also:
Oracle Database Backup and Recovery Reference for all possible options forREPORT
NEED
BACKUP
and an explanation of the various column headings in the outputYou 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;
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
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;
See Also:
"Configuring the Backup Retention Policy" for a conceptual overview of RMAN backup retention policy
"Deleting Expired RMAN Backups after CROSSCHECK" for information on deleting RMAN backups and deleting records of RMAN backups from the RMAN repository
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 theatClause
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