7
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 6, "Managing Recovery Manager Metadata" to learn how to keep the RMAN metadata 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: For list command syntax, see "list". For report command syntax, see "report".

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 metadata.
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 metadata 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 parameter, 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   /vobs/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       /vobs/oracle/work/arc_dest/arcr_1_1.arc
790     1    2       A 11-AUG-98       /vobs/oracle/work/arc_dest/arcr_1_2.arc
791     1    3       A 12-AUG-98       /vobs/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          /vobs/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    /vobs/oracle/dbs/tbs_01.f             0  Full 199843     14-AUG-98
     2    /vobs/oracle/dbs/tbs_02.f             0  Full 199843     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@rcvcat
    

         
```

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-1998' and '17-DEC-1998'; # 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       /vobs/oracle/dbs/df1.bak

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

Generating Reports

To gain more detailed information from the RMAN metadata, 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 metadata 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 metadata, see "Maintaining RMAN Metadata".

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, i.e., 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: For an explanation of the various column headings in the report output, see "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/backupss 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     /vobs/oracle/dbs/tbs_01.f
2           978 SYSTEM               YES     /vobs/oracle/dbs/tbs_02.f
3           978 TBS_1                NO      /vobs/oracle/dbs/tbs_11.f
4           978 TBS_1                NO      /vobs/oracle/dbs/tbs_12.f
5           978 TBS_2                NO      /vobs/oracle/dbs/tbs_21.f
6           978 TBS_2                NO      /vobs/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: For report command syntax, see "report". For list command syntax, see "list".

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 since November 1, 1998:

list backup of tablespace tbs_1 completed before 'Nov 1 1998 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/%';

This example lists all database incarnations registered in the recovery catalog:

list incarnation of database;

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 1998 00:00:00';
list copy completed before 'Nov 1 1998 00:00:00';

Reporting Datafiles Needing Backups

The following command reports all datafiles in the database that would 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 backup (full or incremental) 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;

For example, if a parent incarnation has two children, incarnation A and incarnation 
B, and B is the current incarnation, the children of A will be orphans

Deleting Obsolete Backups

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 /vobs/oracle/dbs/tbs_01.copy
Datafile Copy        22     345392456 /vobs/oracle/dbs/tbs_01_copy.f
Backup Set           31     345552065
Backup Piece         31     345552061 /vobs/oracle/dbs/0va9hd5o_1_1
Backup Set           23     345399397
Backup Piece         23     345399391 /vobs/oracle/dbs/0ma9co2p_1_1
Backup Set           20     345397468
Backup Piece         20     345397464 /vobs/oracle/dbs/0ka9cm6l_1_1

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

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

Optionally, start a SQL*Plus session and query the recovery catalog views to check that the status of the copies and backup pieces has been changed to deleted (the list output does not display files with deleted status):
```
SQL> SELECT handle FROM rc_backup_piece WHERE status = 'D';
SQL> SELECT name FROM rc_datafile_copy WHERE status = 'D';
```

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;

7 Generating Lists and Reports with Recovery Manager