Release 1 (9.0.1)
Part Number A90135-01
 Home |
 Book List |
 Contents |
 Index |  Master Index |  Feedback |
| Oracle9i Recovery Manager User's Guide Release 1 (9.0.1) Part Number A90135-01 |
|
This chapter describes how to use Recovery Manager to obtain information about RMAN from the repository. This chapter contains these topics:
You can obtain information from the RMAN repository in several different ways. The following table describes the basic options.
The main source of information about RMAN is the REPORT and LIST command output. Use these commands to query the RMAN repository and determine what you have backed up as well as what you need to back up. This information is extremely helpful in developing an effective backup strategy.
The LIST command displays all RMAN backups (both backup sets and proxy copies) and copies, while the REPORT command performs more complex analysis. For example, you can generate a report on which datafiles need a backup and which backup pieces are obsolete with the REPORT command. RMAN writes the output from the REPORT and LIST commands to either standard output or a log file.
The SHOW command displays persistent configuration settings. For example, if you allocate automatic channels with the CONFIGURE command, these settings are revealed in the SHOW output.
|
See Also:
|
The LIST command queries the recovery catalog or control file and produces a listing of the backups, copies, archived redo logs, and database incarnations recorded there. You can specify these files when running the CHANGE, CROSSCHECK, and DELETE commands.
This section contains these topics:
You can control how the output is displayed by using the BY BACKUP and BY FILE options and choosing between the SUMMARY and VERBOSE options.
The primary purpose of the LIST command is to determine which backups or copies are available. Note that only backups and copies that completed successfully are stored in the repository. For example, you can list:
Use the RMAN repository to determine what you need to back up. In particular, ensure that:
STATUS columns of the output tables list all backups and image copies as AVAILABLE
By default, RMAN lists backups by backup, which means that it serially lists each backup set or proxy copy and then identifies the files included in the backup. By default, RMAN lists backups and copies in verbose mode, which means that it provides extensive, multiline information.
To list backups by backup:
LIST BACKUP. Specify the desired objects with the listObjList clause. For example, you can enter:
LIST BACKUP; # lists backup sets, backup pieces, and proxy copies
Optionally, specify the EXPIRED keyword to identify those backups that were not found during a crosscheck:
LIST EXPIRED BACKUP;
LIST output). Sample output follows:
LIST BACKUP; List of Backup Sets =================== BS Key Device Type Elapsed Time Completion Time ------- ----------- ----------- --------------- 387 SBT_TAPE 00:00:03 15-SEP-00 BP Key: 388 Status: AVAILABLE Tag: Piece Name: 12c5erb2_1_1 List of Archived Logs in backup set 387 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 144 51234 15-SEP-00 51318 15-SEP-00 1 145 51318 15-SEP-00 51324 15-SEP-00 1 146 51324 15-SEP-00 51330 15-SEP-00 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ----------- --------------- 396 Full 60M SBT_TAPE 00:00:19 15-SEP-00 BP Key: 397 Status: AVAILABLE Tag: Piece Name: 13c5erba_1_1 List of Datafiles in backup set 396 File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- --------- ---- 1 0 Full 51333 15-SEP-00 /oracle/dbs/tbs_01.f 2 0 Full 51333 15-SEP-00 /oracle/dbs/tbs_02.f 3 0 Full 51333 15-SEP-00 /oracle/dbs/tbs_11.f 4 0 Full 51333 15-SEP-00 /oracle/dbs/tbs_12.f 5 0 Full 51333 15-SEP-00 /oracle/dbs/tbs_21.f BS Key Device Type Elapsed Time Completion Time ------- ----------- ----------- --------------- 423 SBT_TAPE 00:00:01 15-SEP-00 BP Key: 424 Status: AVAILABLE Tag: Piece Name: 14c5erce_1_1 List of Archived Logs in backup set 423 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- --------- ---------- --------- 1 147 51330 15-SEP-00 51336 15-SEP-00 BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ----------- --------------- 427 Full 1M SBT_TAPE 00:00:02 15-SEP-00 BP Key: 428 Status: AVAILABLE Tag: Piece Name: c-674966176-20000915-00 Controlfile Included: Ckp SCN: 51338 Ckp time: 15-SEP-00
You can list copies of datafiles, control files, and archived logs. Specify the desired objects with the listObjList or recordSpec clause (refer to Oracle9i Recovery Manager Reference). If you do not specify an object, then RMAN displays copies of all database files and archived redo logs. By default, RMAN lists backups in verbose mode, which means that it provides extensive, multiline information.
To list backups by file:
LIST BACKUP with the BY FILE option. Specify the desired objects and options. For example, you can enter:
LIST BACKUP BY FILE;
Optionally, specify the EXPIRED keyword to identify those backups that were not found during a crosscheck:
LIST EXPIRED BACKUP BY FILE;
LIST output). Sample output follows:
List of Datafile Backups ======================== File Key TY LV S Ckp SCN Ckp Time #Pieces #Copies Tag ---- ------- - -- - ---------- ------------------- ------- ------- --- 1 797 B 1 A 204376 06/28/2000 16:56:25 1 1 796 B 1 A 204373 06/28/2000 16:55:28 1 2 788 B F A 41469 06/18/2000 01:51:28 1 1 208 P F A 61506 06/22/2000 22:52:25 List of Archived Log Backups ============================ Thrd Seq Low SCN Low Time BS Key S #Pieces #Copies Tag ---- ------- ---------- ------------------- ------- - ------- ------- --- 1 125 39578 06/17/2000 09:49:09 791 A 1 7 790 A 1 1 1 126 40097 06/17/2000 10:49:09 791 A 1 7 790 A 1 1 List of Controlfile Backups =========================== CF Ckp SCN Ckp Time BS Key S #Pieces #Copies Tag ---------- ------------------- ------- - ------- ------- --- 204375 06/28/2000 16:56:25 797 A 1 1 204372 06/28/2000 16:55:28 796 A 1 2 61508 06/23/2000 00:53:50 795 A 1 1
Besides listing backup sets and proxy copies, you can list image copies. Specify the desired objects with the listObjList or recordSpec clause. If you do not specify an object, then LIST COPY displays all database files and archived redo logs. By default, RMAN lists backups in verbose mode which means that it provides extensive, multiline information.
To list image copies:
LIST COPY. Specify the desired objects and options. For example, you can enter:
LIST COPY OF ARCHIVELOG ALL;
Optionally, specify the EXPIRED keyword to identify those copies that were not found during a crosscheck:
LIST EXPIRED COPY;
LIST output). Sample output follows:
LIST COPY OF ARCHIVELOG ALL; List of Archived Log Copies Key Thrd Seq S Low Time Name ------- ---- ------- - --------- ---- 153 1 141 A 15-SEP-00 /oracle/work/arc_dest/arcr_1_141.arc 154 1 142 A 15-SEP-00 /oracle/work/arc_dest/arcr_1_142.arc 155 1 143 A 15-SEP-00 /oracle/work/arc_dest/arcr_1_143.arc 339 1 144 A 15-SEP-00 /oracle/work/arc_dest/arcr_1_144.arc 373 1 145 A 15-SEP-00 /oracle/work/arc_dest/arcr_1_145.arc 385 1 146 A 15-SEP-00 /oracle/work/arc_dest/arcr_1_146.arc 421 1 147 A 15-SEP-00 /oracle/work/arc_dest/arcr_1_147.arc
By default the LIST output is highly detailed, but you can also specify that RMAN display the output in summarized form. Specify the desired objects with the listObjectList or recordSpec clause. If you do not specify an object, then LIST BACKUP displays all backups. By default, RMAN lists backups in verbose mode.
To list backups in summary mode:
LIST BACKUP. Specify the desired objects and options. For example, you can enter:
LIST BACKUP SUMMARY;
Optionally, specify the EXPIRED keyword to identify those copies that were not found during a crosscheck:
LIST EXPIRED BACKUP SUMMARY;
LIST output). Sample output follows:
List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Tag ------- -- -- - ----------- --------------- ------- ------- --- 387 B 0 A SBT_TAPE 15-SEP-00 1 1 396 B 0 A SBT_TAPE 15-SEP-00 1 1 423 B 0 A SBT_TAPE 15-SEP-00 1 1 427 B 0 A SBT_TAPE 15-SEP-00 1 1
You can specify a multitude of conditions to narrow your LIST output.
To generate a list of copies and backups restricted by object or other conditions:
LIST COPY or LIST BACKUP with the listObjList or recordSpec condition. For example, enter:
# lists backups of all files in database LIST BACKUP OF DATABASE; # lists copy of specified datafile LIST COPY OF DATAFILE '/oracle/dbs/tbs_1.f'; # lists specified backup set LIST BACKUPSET 213; # lists datafile copy LIST DATAFILECOPY '/df1.f';
maintQualifier or RECOVERABLE clause. For example, enter:
# specify a backup by tag LIST BACKUP TAG 'weekly_full_db_backup'; # specify a backup or copy by device type LIST COPY OF DATAFILE '/oracle/dbs/tbs_1.f' type sbt; # specify a backup or copy by directory or path LIST BACKUP LIKE '/oracle/backup/%'; # specify a backup or copy by a range of completion dates LIST COPY OF DATAFILE 2 COMPLETED BETWEEN '10-DEC-1999' AND '17-DEC-1999';
datafile 1:
LIST COPY OF DATAFILE 1; List of Datafile Copies Key File S Completion time Ckp SCN Ckp time Name ------- ---- - --------------- ---------- --------------- ------ 3 1 A 18-JUL-00 114148 17-JUL-00 /oracle/dbs/df1.bak
|
See Also:
Oracle9i Recovery Manager Reference for |
Every time you reset the online redo logs of a target database, you create a new incarnation of the database. You can track the incarnations with the INCARNATION option of the LIST command. Note that you must use a recovery catalog when running LIST INCARNATION.
To list database incarnations:
LIST INCARNATION:
LIST INCARNATION;
If you register multiple target databases in the same catalog, you can distinguish them by using the OF DATABASE option:
LIST INCARNATION OF DATABASE prod3;
LIST output). Sample output follows:
LIST INCARNATION OF DATABASE; List of Database Incarnations DB Key Inc Key DB Name DB ID CUR Reset SCN Reset Time ------- ------- -------- ---------------- --- ---------- ---------- 1 2 RDBMS 674966176 YES 1 15-SEP-00
This section contains the following topics:
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 synchronized with the control file and you must have run the
Note:
CHANGE, UNCATALOG, and CROSSCHECK commands recently to update the status of all backups and copies. To learn how to maintain the RMAN repository refer to Chapter 16, "Managing the Recovery Manager Repository".
The information that you obtain from reports can be extremely important for your backup and recovery strategy. In particular, run the REPORT NEED BACKUP and REPORT UNRECOVERABLE commands regularly to ensure the following:
You can report on objects that require a backup by specifying the NEED BACKUP keyword. The REDUNDANCY parameter specifies the minimum number of backups or copies that must exist for a datafile to be considered not in need of a backup. If you do not specify the parameter, REDUNDANCY defaults to 1. The DAYS parameter indicates that recovery must begin by using logs more than integer days old. The INCREMENTAL parameter indicates that more than integer incremental backups are required for complete recovery.
To report on objects that need a backup:
CROSSCHECK commands as needed to update the status of backups and copies. Following is a possible crosscheck session:
# allocate maintenance channel for crosscheck if automatic channels not configured ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE sbt; CROSSCHECK BACKUP; # crosschecks all backups CROSSCHECK COPY; # crosschecks all copies
REPORT NEED BACKUP without any other options to determine which files need backups (sample output follows):
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 ---- ----- ----------------------------------------------------- 1 0 /ade/lashdown_rdbms/oracle/dbs/tbs_01.f 2 0 /ade/lashdown_rdbms/oracle/dbs/tbs_02.f 3 0 /ade/lashdown_rdbms/oracle/dbs/tbs_11.f
REPORT NEED BACKUP DAYS. Any files older than the DAYS parameter value need a new backup because their backups require the specified number of DAYS worth of archived logs for recovery. For example, run:
REPORT NEED BACKUP DAYS = 7 DATABASE; # needs min 7 days of logs to recover REPORT NEED BACKUP DAYS = 30 TABLESPACE SYSTEM; REPORT NEED BACKUP DAYS = 14 DATAFILE '/oracle/dbs/tbs_5.f';
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';
|
See Also:
Oracle9i Recovery Manager Reference for an explanation of the various column headings in the |
You can report on objects that are obsolete, that is, superfluous, by specifying the OBSOLETE keyword. If you do not specify any other options, then REPORT OBSOLETE displays the backups and copies that are marked obsolete by the current retention policy. By default, the retention policy is configured to REDUNDANCY of 1.
The REPORT OBSOLETE command supports the RECOVERY WINDOW and REDUNDANCY options at the command level, which have the same meanings as the options with the same names on the CONFIGURE command.
To report on obsolete backups and copies:
CROSSCHECK commands as needed to update the status of backups and copies. Following is a possible crosscheck session:
# allocate maintenance channel for crosscheck if automatic channels not configured ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE sbt; CROSSCHECK BACKUP; # crosschecks all backups CROSSCHECK COPY; # crosschecks all copies RELEASE CHANNEL;
OBSOLETE option to identify which backups are obsolete because they are no longer needed for recovery. For example, enter:
# lists backups or copies that are superfluous because they are not needed to recover # the database to a random point within the past week REPORT OBSOLETE RECOVERY WINDOW OF 7 DAYS; # lists backups or copies that are superfluous because more than 2 copies of the # files exist on tape REPORT OBSOLETE REDUNDANCY = 2 DEVICE TYPE sbt;
ORPHAN option to list unusable backups and copies belonging to an incarnation that is not a direct predecessor of the current incarnation (refer to "Reports of Orphaned Backups"). For example, enter:
REPORT OBSOLETE ORPHAN;
DELETE OBSOLETE command. For example, you can enter:
# delete obsolete backups and copies displayed when you issue REPORT OBSOLETE DELETE OBSOLETE; # delete obsolete backups and copies according to a specified recovery window DELETE OBSOLETE RECOVERY WINDOW OF 7 DAYS; # delete obsolete backups and copies according to a specified redundancy DELETE OBSOLETE REDUNDANCY = 2;
Note that RMAN prompts you for confirmation before actually deleting the files. To suppress the prompt, specify the NOPROMPT option of the DELETE command.
|
See Also:
"Reports of Obsolete Backups" for a conceptual overview of reports of obsolete backups, and "Backup Retention Policies" for a conceptual overview of retention policies |
Issue the REPORT UNRECOVERABLE command to determine which datafiles have had an unrecoverable operation performed against an object residing in the datafile after its last backup.
Assume that you perform an unrecoverable operation on the table employee by issuing an ALTER TABLE employee ... NOLOGGING statement. If the employee table is located in datafile 3, then the REPORT command can flag backups of this datafile as unrecoverable.
To report on backups and copies that are unrecoverable:
After connecting to the target database and recovery catalog (if you use one), issue REPORT UNRECOVERABLE. For example, enter:
REPORT UNRECOVERABLE DATABASE; # examines all datafiles REPORT UNRECOVERABLE TABLESPACE 'users'; # examines a specific tablespace
You do not have to use V$ or recovery catalog views to identify the database files. Issue REPORT SCHEMA to list the files. If you use a recovery catalog, then you also generate historical reports of the database schema at a past time. You do not need a recovery catalog, however, to report the current schema.
To report the database schema at a specified point in time:
REPORT SCHEMA for a list of all the datafiles and tablespaces in the target database at the current time:
REPORT SCHEMA;
If you use a recovery catalog, then you can use the atClause to specify a past time, SCN, or log sequence number:
REPORT SCHEMA AT TIME 'SYSDATE-14'; # schema as it existed two weeks ago REPORT SCHEMA AT SCN 1000; # schema as it existed at scn 1000 REPORT SCHEMA AT SEQUENCE 100; # schema as it existed at log sequence 100
REPORT SCHEMA AT SCN 1000; 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 to which you want to recover.
Run the SHOW command to display persistent configuration settings specified with the CONFIGURE command. These settings are persistent in the sense of being configured for use with any RMAN session.
By using the SHOW command, you can perform the queries discussed in the following sections:
Oracle9i Recovery Manager Reference for
See Also:
CONFIGURE command syntax, and Oracle9i Recovery Manager Reference for SHOW command syntax
You can use the CONFIGURE command to specify a variety of persistent settings for the RMAN environment. The SHOW ALL command displays both the CONFIGURE commands that you have issued as well as RMAN's default configurations. Note that you can return any CONFIGURE command to its default setting by running CONFIGURE ... CLEAR.
To show all RMAN configuration settings:
After connecting to the target database and recovery catalog (if you use one), run the SHOW ALL command. For example, enter the following:
SHOW ALL; # shows all CONFIGURE settings, both user-entered and default
Sample output for SHOW ALL follows:
RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default CONFIGURE BACKUP OPTIMIZATION ON; CONFIGURE DEFAULT DEVICE TYPE TO 'SBT'; CONFIGURE DEVICE TYPE 'SBT' PARALLELISM 1; CONFIGURE DEVICE TYPE DISK PARALLELISM 2; CONFIGURE DATAFILE BACKUP COPIES FOR DISK TO 2; CONFIGURE DATAFILE BACKUP COPIES FOR SBT TO 1; #default CONFIGURE ARCHIVELOG BACKUP COPIES FOR SBT TO 1; # default CONFIGURE ARCHIVELOG BACKUP COPIES FOR DISK TO 1; # default CONFIGURE MAXSETSIZE TO 3072K; CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/dbs/cf_snap.f'; CONFIGURE EXCLUDE FOR TABLESPACE 'TBS_5';
Note that the output is displayed so that you can paste it into a script and run it as an RMAN command file; hence, you can easily change your entire configuration. You can even run the script on a different target database.
You can use the CONFIGURE RETENTION POLICY command to specify either the number of days in the recovery window or the level of redundancy. By default, the retention policy is set to REDUNDANCY = 1.
To show the configuration policy:
After connecting to the target database and recovery catalog (if you use one), run the SHOW RETENTION POLICY command. For example, enter:
SHOW RETENTION POLICY; # shows the CONFIGURE setting for the retention policy
Sample output for SHOW RETENTION POLICY follows:
RMAN configuration parameters are: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
You can use the CONFIGURE command to set the following:
Issue the SHOW CHANNEL command to display the settings for all automatically allocated channels.
To show the automatic channel settings:
After connecting to the target database and recovery catalog (if you use one), issue the SHOW CHANNEL command. For example, enter:
SHOW CHANNEL; # shows the CONFIGURE setting for the automatic channels
Sample output for SHOW CHANNEL follows:
RMAN configuration parameters are: CONFIGURE CHANNEL DEVICE TYPE 'SBT' RATE 1500K;
Issue the SHOW DEVICE TYPE command to display the configured devices and their parallelism settings. The DISK device type is preconfigured.
To show the default device type for automatic channels:
After connecting to the target database and recovery catalog (if you use one), run the SHOW DEVICE TYPE command. For example, enter:
SHOW DEVICE TYPE; # shows the CONFIGURE DEVICE TYPE ... PARALLELISM settings
Sample output for SHOW DEVICE TYPE follows:
RMAN configuration parameters are: CONFIGURE DEVICE TYPE 'SBT' PARALLELISM 1; CONFIGURE DEVICE TYPE DISK PARALLELISM 2;
Issue the SHOW DEFAULT DEVICE TYPE command to display the settings for the default device type used by the automatic channels. When you issue the BACKUP command, RMAN allocates only default channels of the type set by the CONFIGURE DEFAULT DEVICE TYPE command. This default device type setting is not in effect when you use commands other than BACKUP. Note that you cannot disable the default device type: it is always either DISK (default setting) or sbt.
To show the default device type for automatic channels:
After connecting to the target database and recovery catalog (if you use one), run the SHOW DEFAULT DEVICE TYPE command. For example, enter:
SHOW DEFAULT DEVICE TYPE; # shows the CONFIGURE DEFAULT DEVICE TYPE setting
Sample output for SHOW DEFAULT DEVICE TYPE follows:
RMAN configuration parameters are: CONFIGURE DEFAULT DEVICE TYPE TO 'SBT';
You can use the CONFIGURE command to set the following behavior for the BACKUP command:
CONFIGURE EXCLUDE)
CONFIGURE ... BACKUP COPIES)
CONFIGURE MAXSETSIZE)
CONFIGURE BACKUP OPTIMIZATION)
You can use the CONFIGURE EXCLUDE command to exclude tablespaces from whole database backups.
To show the tablespaces excluded from whole database backups:
After connecting to the target database and recovery catalog (if you use one), run the SHOW EXCLUDE command. For example, enter:
SHOW EXCLUDE; # shows the CONFIGURE EXCLUDE setting
Sample output for SHOW EXCLUDE follows:
RMAN configuration parameters are: CONFIGURE EXCLUDE FOR TABLESPACE 'OLD_ACCOUNTS';
Use the CONFIGURE ... BACKUP COPIES command to set the number of identical copies that RMAN makes of each backup. For example, if the value is 3, RMAN produces a total of three identical copies of each backup piece in a backup set.
To show the number of identical copies of each backup:
After connecting to the target database and recovery catalog (if you use one), run the SHOW ARCHIVELOG BACKUP COPIES or SHOW DATAFILE BACKUP COPIES commands. For example, enter:
SHOW DATAFILE BACKUP COPIES; # shows the CONFIGURE DATAFILE BACKUP COPIES setting
Sample output for SHOW DATAFILE BACKUP COPIES follows:
RMAN configuration parameters are: CONFIGURE DATAFILE BACKUP COPIES FOR DISK TO 2; CONFIGURE DATAFILE BACKUP COPIES FOR SBT TO 1; #default
You can run the CONFIGURE MAXSETSIZE command to set the maximum sizes for RMAN backup sets. The size of a backup set is measured in the total bytes of the included backup pieces.
To show the maximum sizes for RMAN backup sets:
After connecting to the target database and recovery catalog (if you use one), issue the SHOW MAXSETSIZE command. For example, enter:
SHOW MAXSETSIZE; # shows the CONFIGURE MAXSETSIZE settings
Sample output for SHOW MAXSETSIZE follows:
RMAN configuration parameters are: CONFIGURE MAXSETSIZE TO 3072K;
You can use the CONFIGURE BACKUP OPTIMIZATION command to enable and disable backup optimization.
To show the status of backup optimization:
After connecting to the target database and recovery catalog (if you use one), issue the SHOW BACKUP OPTIMIZATION command. For example, enter:
SHOW BACKUP OPTIMIZATION;
Sample output for SHOW BACKUP OPTIMIZATION follows:
RMAN configuration parameters are: CONFIGURE BACKUP OPTIMIZATION ON;
You can use the CONFIGURE SNAPSHOT CONTROLFILE command to set the default value for the snapshot control file. Issue the SHOW SNAPSHOT CONTROLFILE command to display this value.
To show the snapshot control file filename:
After connecting to the target database and recovery catalog (if you use one), run the SHOW SNAPSHOT CONTROLFILE command. For example, enter:
SHOW SNAPSHOT CONTROLFILE NAME; # shows the CONFIGURE SNAPSHOT CONTROLFILE setting
Sample output for SHOW SNAPSHOT CONTROLFILE follows:
RMAN configuration parameters are: CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/oracle/dbs/cf_snap.f';
You can use the CONFIGURE AUXNAME command to set persistent filenames for auxiliary channels. For example, you can give new filenames for duplicate or standby datafiles, or datafiles in a TSPITR operation. Issue the SHOW AUXNAME command to display these filenames.
To show persistent settings for auxiliary filenames:
After connecting to the target database and recovery catalog (if you use one), issue the SHOW AUXNAME command. For example, enter:
SHOW AUXNAME; # shows the CONFIGURE AUXNAME setting
Sample output for SHOW AUXNAME follows:
RMAN configuration parameters are: CONFIGURE AUXNAME FOR DATAFILE '/oracle/dbs/tbs_02.f' TO '/dupdb/df2.dbf';
To print the text of a specified stored script, either run the PRINT SCRIPT command or query the RC_STORED_SCRIPT_LINE catalog view. To display a list of RMAN stored scripts, query the RC_STORED_SCRIPT catalog view.
This section contains these topics:
Use the PRINT SCRIPT command to display the text of a stored script. If desired, you can save the output to an RMAN log file.
To print a stored script to a message log:
LOG argument if you want to print to a message log. For example, enter the following to specify rman_log:
% rman TARGET / CATALOG rman/rman@rcat LOG = rman_log
PRINT SCRIPT command to write the script to the log:
PRINT SCRIPT b_whole;
RMAN> EXIT % vi rman_log
The RC_STORED_SCRIPT_LINE view contains the text of all stored scripts for all incarnations of the target databases registered in the catalog.
To list the text for a specific script:
% sqlplus rman/rman@rcat
database_key with the numerical primary key of the target database and script_name with the name of the script:
SELECT TEXT FROM RC_DATABASE_INCARNATION i, RC_STORED_SCRIPT_LINE l WHERE i.DB_KEY = database_key AND SCRIPT_NAME = script_name AND i.DB_KEY = s.DB_KEY AND i.CURRENT_INCARNATION = 'YES' /
Sample output follows:
TEXT -------------------------------------------------------------------------------- { backup database plus archivelog;}
The RC_STORED_SCRIPT view contains information about all stored scripts for all incarnations of the target databases registered in the catalog.
To list the scripts for the current incarnation of a target database:
% sqlplus rman/rman@rcat
database_key with the numerical primary key of the target database:
SELECT SCRIPT_NAME FROM RC_DATABASE_INCARNATION i, RC_STORED_SCRIPT s WHERE i.DB_KEY = database_key AND i.DB_KEY = s.DB_KEY AND i.CURRENT_INCARNATION = 'YES' /
Sample output follows:
SCRIPT_NAME -------------------------------------------------------------------------------- backup_db backup_system
In general, you can use LIST, REPORT, and SHOW to obtain most RMAN information: backups, copies, database incarnations, configuration settings, and so forth. Nevertheless, you may at times want to use V$ views for information that these commands do not display.
This section contains these topics:
Sometimes it is useful to identify what a server session performing a backup or copy operation is doing. You have access to several views that can assist in monitoring the progress of or obtaining information about RMAN jobs, as described in the following table.
You can use RMAN to perform the checks discussed in the following sections:
To identify which server sessions correspond to which RMAN channels, use the SET command with the COMMAND ID parameter. The COMMAND ID parameter enters the specified string into the CLIENT_INFO column of the V$SESSION dynamic performance view. Join V$SESSION with V$PROCESS to correlate the server session with the channel.
The CLIENT_INFO column of V$SESSION contains information for each Recovery Manager server session. The data appears in one of the following formats:
ID=string
This form appears for the first connection to the target database established by RMAN.
ID=string, CH=channel_id
This form appears for all allocated channels.
The SPID column of V$PROCESS identifies the operating system process number.
To correlate a process with a channel during a backup:
COMMAND ID parameter after allocating the channels and then back up the desired object. For example, enter:
RUN { ALLOCATE CHANNEL c1 TYPE sbt; SET COMMAND ID TO 'rman'; # optionally, issue a HOST command to access the operating system prompt HOST; BACKUP INCREMENTAL LEVEL 0 FILESPERSET 5 TABLESPACE SYSTEM;SQL 'ALTER SYSTEM ARCHIVE LOG ALL';}
V$SESSION and V$PROCESS views while the RMAN job is executing. V$PROCESS.SPID indicates the operating system process ID, and V$SESSION.SID indicates the server session ID. For example, enter:
SELECT SID, SPID, CLIENT_INFO FROM V$PROCESS p, V$SESSION s WHERE p.ADDR = s.PADDR AND CLIENT_INFO LIKE '%id=rman%'; SID SPID CLIENT_INFO ---------- --------- ---------------------------------------------------------------- 8 21973 id=rman 16 22057 id=rman 17 22068 id=rman,ch=t1 18 22070 id=rman,ch=t2
|
See Also:
Oracle9i Recovery Manager Reference for |
Monitor the progress of backups, copies, and restores by querying the view V$SESSION_LONGOPS.
Each server session performing a backup, restore, or copy reports its progress compared to the total amount of work required for that particular part of the restore. For example, if you perform a restore that uses two channels, and each channel has two backup sets to restore (a total of four sets), then each server session reports its progress through a single set. When that set is completely restored, RMAN starts reporting progress on the next set to restore.
To monitor job progress:
RESTORE DATABASE;
SELECT SID, SERIAL#, CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "% COMPLETE" FROM V$SESSION_LONGOPS WHERE OPNAME LIKE 'RMAN%' AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK /
If you repeat the query while the restore progresses, then you see output such as the following:
SQL> @longops SID SERIAL# CONTEXT SOFAR TOTALWORK % COMPLETE ---------- ---------- ---------- ---------- ---------- ---------- 8 19 1 10377 36617 28.34 SQL> @longops SID SERIAL# CONTEXT SOFAR TOTALWORK % COMPLETE ---------- ---------- ---------- ---------- ---------- ---------- 8 19 1 21513 36617 58.75 SQL> @longops SID SERIAL# CONTEXT SOFAR TOTALWORK % COMPLETE ---------- ---------- ---------- ---------- ---------- ---------- 8 19 1 29641 36617 80.95 SQL> @longops SID SERIAL# CONTEXT SOFAR TOTALWORK % COMPLETE ---------- ---------- ---------- ---------- ---------- ---------- 8 19 1 35849 36617 97.9 SQL> @longops no rows selected
% COMPLETE column does not increase, then RMAN is encountering a problem. Query V$SESSION_WAIT to determine which events are being waited for. For example, enter:
SELECT SID, SECONDS_IN_WAIT AS SEC_WAIT, EVENT FROM V$SESSION_WAIT WHERE WAIT_TIME = 0 ORDER BY SID; SID SEC_WAIT EVENT ---------- ---------- ----------------------------------------------- 1 368383335 pmon timer 2 1097 rdbms ipc message 3 387928 rdbms ipc message 4 0 rdbms ipc message 5 1408 smon timer 6 386114 rdbms ipc message 7 387626 rdbms ipc message 8 1060 SQL*Net message from client 9 1060 SQL*Net message from client 12 1060 SQL*Net message from client 13 2366 SQL*Net message from client 14 2757 SQL*Net message from client 12 rows selected.
|
See Also:
|
Monitor backup and restore performance by querying V$BACKUP_SYNC_IO and V$BACKUP_ASYNC_IO.
|
See Also:
Oracle9i Database Reference for more information on these |
You can often use the dynamic performance view V$RECOVER_FILE to determine which files need to be recovered and why they need to be recovered.
The following query displays the file ID numbers of datafiles that require media recovery, as well as the reason for recovery (if known) and the SCN and time when recovery needs to begin:
SQL> SELECT * FROM V$RECOVER_FILE; FILE# ONLINE ERROR CHANGE# TIME ---------- ------- ------------------ ---------- --------- 14 ONLINE 0 15 ONLINE FILE NOT FOUND 0 21 OFFLINE OFFLINE NORMAL 0
Query V$DATAFILE and V$TABLESPACE to obtain filenames and tablespace names for datafiles requiring recovery. For example, enter:
SELECT d.NAME, t.NAME FROM V$DATAFILE d, V$TABLESPACE t WHERE t.TS# = d.TS# AND d.FILE# IN (14,15,21); # use values obtained from V$RECOVER_FILE query NAME TABLESPACE_NAME ---------------------------------- ---------------- /oracle/dbs/tbs_14.f TBS_1 /oracle/dbs/tbs_15.f TBS_2 /oracle/dbs/tbs_21.f TBS_3
You can combine these queries in the following SQL*Plus script (sample output follows):
COL df# FORMAT 999 COL df_name FORMAT a20 COL tbsp_name FORMAT a10 COL status FORMAT a7 COL error FORMAT a10 SELECT r.FILE# AS df#, d.NAME AS df_name, t.NAME AS tbsp_name, d.STATUS, r.ERROR, r.CHANGE#, r.TIME FROM V$RECOVER_FILE r, V$DATAFILE d, V$TABLESPACE t WHERE t.TS# = d.TS# AND d.FILE# = r.FILE# / SQL> @script DF# DF_NAME TBSP_NAME STATUS ERROR CHANGE# TIME ---- -------------------- ---------- ------- ---------- ----------- ---------- 12 /oracle/dbs/tbs_41.f TBS_4 OFFLINE OFFLINE 0 NORMAL 13 /oracle/dbs/tbs_42.f TBS_4 OFFLINE OFFLINE 0 NORMAL 20 /oracle/dbs/tbs_43.f TBS_4 OFFLINE OFFLINE 0 NORMAL
The LIST, REPORT, and SHOW commands should provide you with all the repository information that you require. Nevertheless, you can sometimes also obtain useful information from the recovery catalog views, which are views in the catalog schema prefixed with RC_.
This section contains these topics:
Oracle9i Recovery Manager Reference for reference information about the recovery catalog views
See Also:
The recovery catalog views are not normalized, but are optimized for RMAN usage rather than user queries. RMAN obtains backup and recovery information from the target database control file and stores it in the catalog tables.
In general, the recovery catalog views are not as user-friendly as the RMAN reporting commands. For example, when you start RMAN and connect to a target database, you obtain the information for this target database only when you issue LIST, REPORT, and SHOW commands. If you have 10 different target databases registered in the same recovery catalog, then the catalog views show the information for all incarnations of all databases registered in the catalog. You often have to perform joins among the views to distinguish the specific incarnation of the target database that you are interested in.
Most of the catalog views have a corresponding dynamic performance view in the database server. For example, RC_BACKUP_PIECE corresponds to V$BACKUP_PIECE. The primary difference between the catalog and server views is that each catalog view contains information about all the databases registered in the catalog, whereas the server view contains information only about itself. The two types of views often use different primary keys to uniquely identify rows.
Most of the catalog views contain the columns DB_KEY, DBINC_KEY, and DB_NAME. Each target database is uniquely identified by a primary key, which is the DB_KEY column value, as well as the DBID, which is the 32-bit database identifier. Each incarnation of each target database is distinguished by the DBINC_KEY primary key. When querying information about a specific incarnation of a target database, you should use these columns to specify the database. Then, you can perform joins with most of the other catalog views to obtain the desired information.
An important difference between catalog and V$ views is that a different system of unique identifiers is used for backup and recovery objects. For example, many V$ views such as V$ARCHIVED_LOG use the RECID and STAMP columns to form a concatenated primary key. The corresponding catalog view uses a derived value as its primary keys and stores this value in a single column. For example, the primary key in RC_ARCHIVED_LOG is the AL_KEY column. The AL_KEY column value is the primary key that RMAN displays in the LIST command output.
The DB_KEY value is used only in the catalog, so you may question how to obtain it. The easiest way is to use the DBID of the target database, which is displayed whenever you connect RMAN to the target database. The DBID, which is a unique system-defined number given to every Oracle database, is what distinguishes one target database from another target database in the RMAN metadata.
Assume that you want to obtain information about one of the target databases registered in the recovery catalog. You can easily determine the DBID from this database either by looking at the output displayed when RMAN connects to the database, or querying a V$ view as in the following:
SQL> SELECT DBID FROM V$DATABASE; DBID --------- 598368217
You can then obtain the DB_KEY for a target database by running the following query, where dbid_of_target is the DBID that you previously obtained:
SQL> SELECT DB_KEY FROM RC_DATABASE WHERE DBID = dbid_of_target;
To obtain information about the current incarnation of a target database, specify the target database DB_KEY value and perform a join with RC_DATABASE_INCARNATION by using a WHERE condition to specify that the CURRENT_INCARNATION column value is set to YES. For example, to obtain information about backup sets in the current incarnation of a target database with the DB_KEY value of 1, you can execute this script:
SELECT BS_KEY, BACKUP_TYPE, COMPLETION_TIME FROM RC_DATABASE_INCARNATION i, RC_BACKUP_SET b WHERE i.DB_KEY = 1 AND i.DB_KEY = b.DB_KEY AND i.CURRENT_INCARNATION = 'YES' /
Note that you should use the DB_NAME column to specify a database only if you do not have more than one database registered with the same DB_NAME. RMAN permits you to register more than one database with the same database name, but requires that the DBID values be different. For example, you can have 10 databases with the DB_NAME value of prod1, each with a different DBID. Because the DBID is the unique identifier for every database in the metadata, use this value to obtain the DB_KEY and then use DB_KEY to uniquely identify the database.
This section contains these topics:
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 lists.
The following example lists all backups of datafiles in tablespace tbs_1 that were made after June 11, 2000:
LIST BACKUP OF TABLESPACE tbs_1 BY FILE COMPLETED BEFORE 'JUN 11 2000 00:00:00';
The following example lists backups on media management devices:
LIST BACKUP OF DATABASE SUMMARY DEVICE TYPE sbt;
The following example lists all copies of datafile 2 with the tag weekly_df2__copy that are in the /copy directory:
LIST COPY OF DATAFILE 2 TAG weekly_df2_copy LIKE '/copy/%';
Use the REPORT command to determine which copies and backups are superfluous and so can be deleted. For example, if you only need to be able to recover the database to a point within the last two weeks, then issue this command:
REPORT OBSOLETE RECOVERY WINDOW OF 14 DAYS;
You can then delete these obsolete backups and copies by issuing this command:
DELETE OBSOLETE RECOVERY WINDOW OF 14 DAYS;
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;
The following commands reports the database schema in the present, a week ago, and on September 20, 2000:
REPORT SCHEMA; REPORT SCHEMA AT TIME 'SYSDATE-7'; REPORT SCHEMA AT TIME "TO_DATE('09/20/00','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 SEQUENCE 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-00 1 582 PROD1 1224038686 YES 59727 10-JUL-00
|
See Also:
"UNKNOWN Database Name Appears in Recovery Catalog: Scenario" for information about |
|
 Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|