4
Generating Lists and Reports with Recovery Manager

This chapter describes how to use Recovery Manager to make useful lists and reports of your backups and image copies, and includes the following topics:

Using Lists and Reports in Your Backup and Recovery Strategy

Use the report and list commands to determine what you have backed up or copied as well as what you need to back up or copy. The information, which is available to you whether or not you use a recovery catalog, is extremely helpful in developing an effective backup strategy. Refer to Chapter 3, "Managing the Recovery Manager Repository" to learn how to keep the RMAN repository current.

The list command displays all RMAN backups (both backup sets and proxy copies) and image copies, while the report command performs more complex analysis. For example, generate a report on which datafiles need a backup and which backup pieces are obsolete. Oracle writes the output from the report and list commands to either the screen or a log file.

See Also:
"list" for list command syntax, and "report"for report command syntax.

Generating Lists

The list command queries the recovery catalog or control file and produces a listing of its contents. The primary purpose of the list command is to determine which backups or copies are available. For example, list:

All backups (backup sets and proxy copies) or image copies recorded in the RMAN repository.
Backups or image copies of a specified database, tablespace, datafile, archived redo log, or control file.
Backups and image copies restricted by time, pathname, device name, tag, or recoverability.
Incarnations of a specified database or of all databases known to the recovery catalog.

Use the RMAN repository to determine what you need to back up. In particular, ensure that:

The STATUS columns of the output tables list all backups and image copies as AVAILABLE.
All datafiles, archived redo logs, and control files that you want backed up are included in the output.
The backups and copies are recent.

To generate a list of image copies and backups:

Start RMAN and connect to the target database and, optionally, the recovery catalog database. For example, enter:
```
% rman target / catalog rman/rman@rcat
```
If you want to write the output to a log file, specify the file at startup. For example, enter:
```
% rman target / catalog rman/rman@rcat log '/oracle/log/mlog.f' 
```

Execute list copy and list backup commands. If you do not specify the of listObjList clause, list defaults to of database:

list copy of database archivelog all;  # lists datafiles and archived redo logs
list backup;  # lists backup sets, backup pieces, and proxy copies

Examine the output. See "list" for an explanation of the various column headings in the list output. Following is sample output:

list copy of database archivelog all;

List of Datafile Copies
Key     File S Completion time Ckp SCN    Ckp time    Name
------- ---- - --------------- ---------- ----------  ------
1262    1    A 18-AUG-98       219859     14-AUG-98   /oracle/dbs/copy/tbs_01.f
 
List of Archived Log Copies
Key     Thrd Seq     S Completion time Name
------- ---- ------- - --------------- ------------------------------------
789     1    1       A 14-JUL-98       /oracle/work/arc_dest/arcr_1_1.arc
790     1    2       A 11-AUG-98       /oracle/work/arc_dest/arcr_1_2.arc
791     1    3       A 12-AUG-98       /oracle/work/arc_dest/arcr_1_3.arc

list backup;

List of Backup Sets
Key     Recid      Stamp      LV Set Stamp  Set Count  Completion Time
------- ---------- ---------- -- ---------- ---------- ----------------------
1174    12         341344528  0  341344502  16         14-AUG-98

     List of Backup Pieces
     Key     Pc# Cp# Status     Completion Time    Piece Name
     ------- --- --- ---------- ------------------ -----------------------------
     1176    1   1   AVAILABLE  14-AUG-98          /oracle/dbs/0ga5h07m_1_1

     Controlfile Included
     Ckp SCN    Ckp time
     ---------- ---------------
     219857     14-AUG-98
 
     List of Datafiles Included
     File Name                                  LV Type Ckp SCN    Ckp Time
     ---- ------------------------------------- -- ---- ---------- -------------
     1    /oracle/dbs/tbs_01.f             0  Full 199943     14-AUG-98
     2    /oracle/dbs/tbs_02.f             0  Full 199943     14-AUG-98

To generate a list of copies and backups restricted by object or other conditions:

Start RMAN and connect to the target database and, optionally, the recovery catalog database. For example, enter:
```
% rman target / catalog rman/rman@rcat
```

To restrict by object, use list copy or list backup with the of listObjList condition. For example, enter:

list backup of database;     # lists backups of all files in database
list copy of datafile '/oracle/dbs/tbs_1.f'; # lists copy of specified datafile
list backup of tablespace SYSTEM; # lists all backups of SYSTEM tablespace
list copy of archivelog all;  # lists all archived redo logs and copies of logs
list backup of controlfile; # lists all control file backups

You can also restrict your search by specifying a combination of tag, device type, filename pattern, or time options. For example, enter:

list backup tag 'weekly_full_db_backup';    # by tag 
list copy of datafile '/oracle/dbs/tbs_1.f' type 'sbt_tape';   # by type 
list backup like '/oracle/backup/tbs_4%';    # by filename pattern 
list backup of archivelog until time 'SYSDATE-30';   # by time 
list copy of datafile 2 completed between '10-DEC-1999' and '17-DEC-1999'; # by time

Examine the output. For example, following is sample output for a list of copies of datafile 1:

RMAN> list copy of datafile 1;

RMAN-03022: compiling command: list

List of Datafile Copies
Key     File S Completion time Ckp SCN    Ckp time        Name
------- ---- - --------------- ---------- --------------- ------
3       1    A 18-DEC-98       114148     18-DEC-98       /oracle/dbs/df1.bak

See Also:
"listObjList" for listObjList syntax, and "List Output" for an explanation of the various columns in the list output.

Generating Reports

To gain more detailed information from the RMAN repository, generate a report. Use the report command to answer questions such as the following:

Which files need a backup?
Which files have not been backed up recently?
Which files are listed as unrecoverable?
Which backups or copies are obsolete and can be deleted?
What was the physical schema of the database at some previous time?

Which backups and copies are on disk and which are on tape?

Note:
For the report to be accurate, the RMAN repository must be current and you must have used the change, uncatalog, and crosscheck commands appropriately to update the status of all backups and copies is correct. To learn how to maintain the RMAN repository see "Maintaining the RMAN Repository".

The information that you glean from reports can be extremely important for your backup and recovery strategy. In particular, use the report need backup and report unrecoverable commands regularly to ensure that:

The necessary backups are available to perform recovery.
Recovery can be performed within a reasonable length of time, that is, that the mean time to recovery (MTTR) is minimized.

To report on objects that need a backup:

Start RMAN and connect to the target database and, optionally, the recovery catalog database. For example, enter:
```
% rman target / catalog rman/rman@rcat
```
To write the output to a log file, specify a file at startup:
```
% rman target / catalog rman/rman@rcat log "/oracle/log/mlog.f'
```

If necessary, issue crosscheck commands to update the status of backups and change ... crosscheck commands to update the status of image copies (if you want to specify image copies by primary key, issue a list command to obtain the keys).

Following is a possible crosscheck session:

# must allocate maintenance channel for crosscheck 
allocate channel for maintenance type disk;
crosscheck backup;  # crosschecks all backups
change datafile copy 100,101,102,103,104,105,106,107 crosscheck; # specified by key
change archivelog copy 50,51,52,53,54 crosscheck;  # specified by key
release channel;

Use the need backup option to identify which datafiles need a new backup, restricting the report by a threshold number of days or incremental backups. RMAN considers any backups older than the days parameter value as needing a new backup because backups require days worth of archived redo logs for recovery.

For example, enter:
```
report need backup days = 7 database;  # needs at least 7 days of logs to recover
report need backup days = 30 tablespace system;
report need backup days = 14 datafile '/oracle/dbs/tbs_5.f';
```
You can also specify the incremental parameter. If complete recovery of a datafile requires more than the specified number of incremental backups, then RMAN considers it in need of a new backup. For example, enter:
```
report need backup incremental = 1 database; 
report need backup incremental = 3 tablespace system;
report need backup incremental = 5 datafile '/oracle/dbs/tbs_5.f';
```
Examine the report and back up those datafiles requiring a new backup.

See Also:
"Report Output" for an explanation of the various column headings in the report output.

To report on backups that are obsolete:

Start RMAN and connect to the target database and, optionally, the recovery catalog database. For example, enter:
```
% rman target / catalog rman/rman@rcat
```
If you want to write the output to a message log file, specify the file at startup:
```
% rman target / catalog rman/rman@rcat log '/oracle/log/mlog.f' 
```
If necessary, issue crosscheck commands to update the status of backups and change ... crosscheck commands to update the status of image copies (if you want to specify copies by primary key, issue a list command to obtain them):
```
allocate channel for maintenance type disk;
crosscheck backup;
change datafile copy 100,101,102,103,104,105,106,107 crosscheck;
change archivelog copy 50,51,52,53,54 crosscheck;
release channel;
```
Use the obsolete option to identify which backups are obsolete because they are no longer needed for recovery. The redundancy parameter specifies the minimum level of redundancy considered necessary for a backup or copy to be obsolete. If you do not specify the parameter, redundancy defaults to 1.

A datafile copy is obsolete if at least integer more recent backups of this file exist; a datafile backup set is obsolete if at least integer more recent backups or image copies of each file contained in the backup set exist. For example, enter:
```
# lists backups or copies that have at least 2 more recent backups or copies
report obsolete redundancy = 2; 
```
Use the untilClause to use make the redundancy check for backups sets or copies that are more recent, but not later than the specified time, SCN, or log sequence number:
```
# obsolete if there are at least 2 copies/backups that are no more than 2 weeks old  
report obsolete redundancy = 2 until time 'SYSDATE-14';  
report obsolete until scn 1000;
report obsolete redundancy = 3 until logseq = 121 thread = 1; 
```
Use the orphan option to list which backups and copies are unusable because they belong to a incarnation that is not a direct predecessor of the current incarnation:
```
report obsolete orphan;
```
For an explanation of orphaned backups, see "Reporting on Orphaned Backups".

Examine the report and delete those backups that are obsolete.

RMAN> report obsolete;

 

RMAN-03022: compiling command: report
Report of obsolete backups and copies
Type                 Recid  Stamp     Filename
-------------------- ------ --------- --------------------------
Backup Set           4      345390311
Backup Piece         4      345390310 /oracle/dbs/04a9cf76_1_1

RMAN> allocate channel for delete type disk;
 
RMAN-03022: compiling command: allocate
RMAN-03023: executing command: allocate
RMAN-08030: allocated channel: delete
RMAN-08500: channel delete: sid=11 devtype=DISK
 
RMAN> change backuppiece '/oracle/dbs/04a9cf76_1_1' delete;
 
RMAN-03022: compiling command: change
RMAN-03023: executing command: change
RMAN-08073: deleted backup piece
RMAN-08517: backup piece handle=/oracle/dbs/04a9cf76_1_1 recid=4 stamp=345390310
RMAN-03023: executing command: partial resync
RMAN-08003: starting partial resync of recovery catalog
RMAN-08005: partial resync complete

To report on backups that are unrecoverable:

Start RMAN and connect to the target database and, optionally, the recovery catalog database. For example, enter:
```
% rman target / catalog rman/rman@rcat
```
If you want to write the output to a message log file, specify the file at startup:
```
% rman target / catalog rman/rman@rcat log "/oracle/log/mlog.f' 
```
Use the unrecoverable option of the report command to determine which datafiles have had an unrecoverable operation performed against an object residing in the datafile since its last backup.
```
report unrecoverable database; # examines all datafiles
```

To report the database schema at a specified point in time:

You must use a recovery catalog for reporting on database schema at a past time, SCN, or log sequence number.

Start RMAN and connect to the target database and the recovery catalog database. For example, enter:
```
% rman target / catalog rman/rman@rcat
```
If you want to write the output to a message log file, specify the file at startup:
```
% rman target / catalog rman/rman@rcat log "/oracle/log/mlog.f' 
```
Issue report schema for a list of all the datafiles and tablespaces in the target database at the current time:
```
report schema;
```
Use the untilClause to specify a past time, SCN, or log sequence number:
```
report schema at time 'SYSDATE-14'; 
report schema at scn 1000;
report schema at logseq 100; 
```

Examine the report. For example, here is sample output:

RMAN> report schema at scn 1000;
RMAN-03022: compiling command: report



Report of database schema
File K-bytes    Tablespace           RB segs Name
---- ---------- -------------------- ------- -------------------
1         35840 SYSTEM               YES     /oracle/dbs/tbs_01.f
2           978 SYSTEM               YES     /oracle/dbs/tbs_02.f
3           978 TBS_1                NO      /oracle/dbs/tbs_11.f
4           978 TBS_1                NO      /oracle/dbs/tbs_12.f
5           978 TBS_2                NO      /oracle/dbs/tbs_21.f
6           978 TBS_2                NO      /oracle/dbs/tbs_22.f

This type of information is useful for incomplete recovery because you can determine the schema of the database for the time that you want to recover to.

See Also:
"report" for report command syntax, and "list" for list command syntax.

List and Report Scenarios

Following are some examples of list and report generation:

Makings Lists of Backups and Copies

Use the list command to query the contents of the recovery catalog or the target database control file if no recovery catalog is used. You can use several different parameters to qualify your lists.

The following example lists all backups of datafiles in tablespace TBS_1 that were made after November 1, 1999:

list backup of tablespace tbs_1 completed before 'Nov 1 1999 00:00:00';

The following example lists all backup sets or proxy copies on media management devices:

list backup of database device type 'sbt_tape';

The following example lists all copies of datafile 2 using the tag weekly_df2__copy that are in the copy sub-directory:

list copy of datafile 2 tag weekly_df2_copy like '/copy/%';

Using Lists to Determine Obsolete Backups and Copies

Use the list command to determine which copies and backups can be deleted. For example, if you created a full backup of the database on November 2, and you know you will not need to recover the database to an earlier date, then the backups and image copies listed in the following report can be deleted:

list backup of database completed before 'Nov 1 1999 00:00:00';
list copy completed before 'Nov 1 1999 00:00:00';

Reporting Datafiles Needing Backups

The following command reports all datafiles in the database that require the application of three or more incremental backups to be recovered to their current state:

report need backup incremental 3 database;

The following command reports all datafiles from tablespace SYSTEM that have not had a full or incremental backup in five or more days:

report need backup days 5 tablespace system;

The following command reports which of datafiles 1 - 5 need backups because they do not have two or more backups or copies stored on tape:

report need backup redundancy 2 datafile 1,2,3,4,5 device type 'sbt_tape';

Reporting Unrecoverable Datafiles

The following example reports on all datafiles on tape that need a new backup because they contain unlogged changes that were made after the last full or incremental backup.

report unrecoverable database device type 'sbt_tape';

Reporting Obsolete Backups and Copies

The following command reports all backups and copies on disk that are obsolete because three more recent backups or copies are already available:

report obsolete redundancy 3 device type disk;

The following command reports all backups on tape that are obsolete because at least two backups already exist that were made no more than one week ago:

report obsolete redundancy 2 until time 'SYSDATE-7' device type 'sbt_tape';

The following command reports which datafiles are obsolete because they belong to a database incarnation that is not a direct predecessor of the current incarnation:

report obsolete orphan;

Manually Deleting Obsolete Backups and Copies

In this scenario, assume that you want to delete the following:

Datafile copies for which there are at least two more recent copies.
Datafile backups for which there are at least two more recent backups or image copies of each datafile contained in the backup set.

Generate a report with redundancy set to 2:

report obsolete redundancy 2;



RMAN-03022: compiling command: report
Report of obsolete backups and copies
Type                 Recid  Stamp     Filename
-------------------- ------ --------- --------------------------
Datafile Copy        23     345392880 /oracle/dbs/tbs_01.copy
Datafile Copy        22     345392456 /oracle/dbs/tbs_01_copy.f
Backup Set           31     345552065
Backup Piece         31     345552061 /oracle/dbs/0va9hd5o_1_1
Backup Set           23     345399397
Backup Piece         23     345399391 /oracle/dbs/0ma9co2p_1_1
Backup Set           20     345397468
Backup Piece         20     345397464 /oracle/dbs/0ka9cm6l_1_1

Issue change ... delete commands for the copies and backups to delete them and remove their repository records. Use the filenames or issue a list command to obtain the primary keys:

allocate channel for delete type disk;
change backuppiece '/oracle/dbs/0va9hd5o_1_1', '/oracle/dbs/0ma9co2p_1_1',
   '/oracle/dbs/0ka9cm6l_1_1' delete;
change datafilecopy '/oracle/dbs/tbs_01.copy', '/oracle/dbs/tbs_01_copy.f' 
   delete;
release channel;

Deleting Obsolete Backups and Copies Using a UNIX Shell Script

Oracle provides the $ORACLE_HOME/rdbms/demo/rman1.sh UNIX script to automate deletion of obsolete backups and copies. The script uses sed and grep commands to process the output of the RMAN report obsolete command and constructs an RMAN command file containing the necessary change ... delete commands. This script does not require the use of a recovery catalog.

You can edit the report obsolete commands in the script as desired: the script uses the default value for report obsolete for both disk and tape devices. The output of the commands are stored in deleted.log.

Change into the $ORACLE_HOME/rdbms/demo directory and run the following shell script:
```
% rman1.sh
```
If desired, check deleted.log to see the command output.
If you use a recovery catalog, you can query the catalog to check that the records were updated correctly. For example, run the SQL*Plus script described in "Reporting Deleted Backups and Copies" against the recovery catalog database to issue a report of all deleted backups and copies.

Generating Historical Reports of Database Schema

The following commands reports the database schema in the present, a week ago, two weeks ago, and a month ago:

report schema;
report schema at time 'SYSDATE-7';
report schema at time "TO_DATE('12/20/98','MM/DD/YY')";

The following command reports on the database schema at SCN 953:

report schema at scn 953;

The following command reports on the database schema at log sequence number 12 of thread 2:

report schema at logseq 12 thread 2;

Listing Database Incarnations

Every time that you perform a RESETLOGS operation on a database, you create a new incarnation. This example lists all database incarnation of PROD1 registered in the recovery catalog:

list incarnation of database prod1;

List of Database Incarnations
DB Key  Inc Key   DB Name   DB ID       CUR    Reset SCN    Reset Time
------- -------   -------   ------      ---    ----------   ----------
1       2         PROD1     1224038686  NO     1            02-JUL-98
1       582       PROD1     1224038686  YES    59727        10-JUL-98

See Also:
"UNKNOWN Database Name Appears in Recovery Catalog" for information about UNKNOWN database names in the list output.

Reporting Deleted Backups and Copies

The RMAN report command does not show deleted backups and copies. If compatibility is set to 8.1.6 or higher, then change ... delete commands automatically purge records from the RMAN repository. If compatibility is not set to 8.1.6, and records have not been purged using prgrmanc.sql, then you can perform a query against the V$ or recovery catalog tables to list these records.

If you use a recovery catalog, you can execute the following SQL script to display deleted backups and copies:

Note:
If you do not use a recovery catalog, you can substitute the corresponding V$ view for the recovery catalog view and run the script against the target database. For example, substitute V$DATAFILE_COPY for RC_DATAFILE_COPY.

col bp_key format 999999
col bs_key format 999999
col backup_type format a4 heading "TYPE"
col piece# format 999999
col copy# format 99
col status format a6
col name format a40
col tag format a20
col thread# format 9999 heading "THRD#"
col sequence# format 9999 heading "SEQ#"
col handle format a30

ttitle 'Deleted Backup Pieces'
SELECT bp_key, bs_key, handle, backup_type, piece#, copy#
FROM rman.rc_backup_piece
WHERE status = 'D';

ttitle 'Deleted Datafile Copies'
SELECT cdf_key, name, tag
FROM rman.rc_datafile_copy
WHERE status = 'D';

ttitle 'Deleted Archived Redo Logs'
SELECT al_key, thread#, sequence#, name
FROM rman.rc_archived_log
WHERE status = 'D';

ttitle 'Deleted Control File Copies'
SELECT ccf_key, name, tag
FROM rman.rc_controlfile_copy
WHERE status = 'D';
ttitle off

The following shows sample output for the script:

Fri Jul 16                                                             page    1
                             Deleted Backup Pieces

 BP_KEY  BS_KEY HANDLE                         TYPE  PIECE# COPY#
------- ------- ------------------------------ ---- ------- -----
   1037    1035 /oracle/dbs/02b1h3ah_1_1  D          1     1
   1044    1042 /oracle/dbs/03b1h3b3_1_1  D          1     1
   1051    1049 /oracle/dbs/04b1h3bf_1_1  D          1     1
   1058    1056 /oracle/dbs/05b1h3bl_1_1  D          1     1


Fri Jul 16                                                             page    1
                            Deleted Datafile Copies

   CDF_KEY NAME                                     TAG
---------- ---------------------------------------- --------------------
      1069 /oracle/work/df1.f
      1073 /oracle/work/df2.f

Fri Jul 16                                                             page    1
                           Deleted Archived Redo Logs

    AL_KEY THRD#  SEQ# NAME
---------- ----- ----- ----------------------------------------
       972     1   947 /oracle/work/arc_dest/arcr_1_947.arc
       973     1   948 /oracle/work/arc_dest/arcr_1_948.arc
       974     1   949 /oracle/work/arc_dest/arcr_1_949.arc

Fri Jul 16                                                             page    1
                          Deleted Control File Copies

   CCF_KEY NAME                                     TAG
---------- ---------------------------------------- --------------------
      1066 /oracle/work/cf1.f

4 Generating Lists and Reports with Recovery Manager