Oracle8i Recovery Manager User's Guide and Reference Release 2 (8.1.6) A76990-01 |
|
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:
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.
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 repository to determine what you need to back up. In particular, ensure that:
% 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'
list copy of database archivelog all; # lists datafiles and archived redo logs list backup; # lists backup sets, backup pieces, and proxy copies
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
% rman target / catalog rman/rman@rcat
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
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. |
To gain more detailed information from the RMAN repository, generate a report. Use the report command to answer questions such as the following:
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".
Note:
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:
% 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'
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;
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';
% 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'
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;
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;
report obsolete orphan;
For an explanation of orphaned backups, see "Reporting on Orphaned Backups".
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
% 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'
report unrecoverable database; # examines all datafiles
You must use a recovery catalog for reporting on database schema at a past time, SCN, or log sequence number.
% 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'
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;
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.
Following are some examples of list and report generation:
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/%';
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';
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';
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';
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;
In this scenario, assume that you want to delete the following:
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
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;
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
.
$ORACLE_HOME/rdbms/demo
directory and run the following shell script:
% rman1.sh
deleted.log
to see the command output.
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;
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. |
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:
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
|
![]() Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|