Oracle8i Backup and Recovery Guide
Release 8.1.5

A67773-01

Library

Product

Contents

Index

Prev Next

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:

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

To generate a list of image copies and backups:

  1. 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' 
        
    
    
    
  2. 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
        
    
    
    
  3. 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:

  1. Start RMAN and connect to the target database and, optionally, the recovery catalog database. For example, enter:

    % rman target / catalog rman/rman@rcvcat
        
    
    
    
  2. 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   
        
    
    
    
  3. 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:

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:

To report on objects that need a backup:

  1. 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'
        
    
    
    
  2. 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;
        
    
    
    
  3. 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';
        
    
    
    
  4. 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:

  1. 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' 
        
    
    
    
  2. 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;
        
    
    
    
  3. 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; 
        
    
    
    
  4. 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".

  5. 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:

  1. 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' 
        
    
    
    
  2. 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.

  1. 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' 
        
    
    
    
  2. 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; 
        
    
    
    
  3. 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:

  1. 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
    
    
  2. 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;
        
    
    
    
  3. 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;




Prev

Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index