RMAN: How to Query the RMAN Recovery Catalog
PURPOSE
——-
This document describes how to query the RMAN recovery catalog through
data dictionary views, the list command, and the report command.
SCOPE & APPLICATION
——————-
This document is intended for users who are or want to be familiar
with the recovery catalog of Recovery Manager(RMAN). It goes into
moderate detail on some of the ways you can gather information about
the recover catalog but should not be used as the ultimate source of
information on the recovery catalog since it only covers some of the
more popular data dictionary views.
HOW TO QUERY THE RMAN RECOVERY CATALOG
=======================================
RMAN Data Dictionary Views
————————–
When the “catrman.sql” script is run, several views are created in the recovery
catalog owner schema. Here are some of the more important views.
RC_DATABASE
This view gives information about the databases registered in the
recovery catalog.
COLUMN DESCRIPTION
DB_KEY The primary key for the database.
DBINC_KEY The primary key for the current incarnation.
DBID Unique identifier for the database.
NAME The DB_NAME for the current incarnation.
RESETLOGS_CHANGE# The SCN of the most recent RESETLOGS operation.
RESETLOGS_TIME The timestamp of the most recent RESETLOGS
operation.
RC_TABLESPACE
This view lists information about all tablespaces registered in the
recovery catalog, all dropped tablespaces, and tablespaces that
belong to old database incarnations. It corresponds to the
V$TABLESPACE dynamic performance view. The current value is shown
for tablespace attributes.
COLUMN DESCRIPTION
DB_KEY The primary key for the target database.
DBINC_KEY The primary key for the incarnation of the target
database.
DB_NAME The DB_NAME of the database incarnation this
record belongs to.
TS# The tablespace identifier in the target database.
NAME The tablespace name.
CREATION_CHANGE# The creation SCN (from the first datafile).
CREATION_TIME The creation time of the tablespace.
DROP_CHANGE# The SCN recorded when the tablespace was dropped.
DROP_TIME The date when the tablespace was dropped.
RC_DATAFILE
This view lists information about all datafiles registered in the
recovery catalog. It corresponds to the V$DATAFILE dynamic
performance view. A datafile is shown as dropped if its tablespace
was dropped.
COLUMN DESCRIPTION
DB_KEY The primary key for the target database.
DBINC_KEY The primary key for the incarnation of the target
database.
DB_NAME The DB_NAME of the database incarnation this
record belongs to.
TS# The tablespace identifier in the target database.
TABLESPACE_NAME The tablespace name.
FILE# The absolute file number of the datafile.
CREATION_CHANGE# The SCN at datafile creation.
CREATION_TIME The time of datafile creation.
DROP_CHANGE# The SCN recorded when the datafile was dropped.
DROP_TIME The time when the datafile was dropped.
BYTES The size of the datafile in bytes.
BLOCKS The number of blocks in the datafile.
BLOCK_SIZE The size of the data blocks.
NAME The datafile filename.
STOP_CHANGE# SCN for datafile if offline normal or read-only.
READ_ONLY 1 if STOP_CHANGE# is read-only; otherwise 0.
RC_STORED_SCRIPT
This view lists information about scripts stored in the recovery
catalog. The view contains one row for each stored script.
COLUMN DESCRIPTION
DB_KEY The primary key for the database that owns this
script.
DB_NAME The DB_NAME of the database incarnation this
record belongs to.
SCRIPT_NAME The name of the script.
RC_STORED_SCRIPT_LINE
This view lists information about lines of the scripts stored in the
recovery catalog. The view contains one row for each line of each
stored script.
COLUMN DESCRIPTION
DB_KEY The primary key for the database that owns this
script.
SCRIPT_NAME The name of the stored script.
LINE The number of the line in the script.
TEXT The text of the line of the script.
To determine which databases are currently registered in the recovery catalog:
SQL> SELECT * FROM RC_DATABASE;
DB_KEY DBINC_KEY DBID NAME CHANGE# RESETLOGS
——– ———– ———— —— ——— ———–
1 2 1943591421 DB00 1 20-OCT-97
To determine which tablespaces are currently stored in the recovery catalog for the target database:
SQL> SELECT DB_KEY, DBINC_KEY, DB_NAME, TS#, NAME,
CREATION_CHANGE#, CHANGE#, CREATION_TIME, CRE_DATE
FROM RC_TABLESPACE;
DB_KEY DBINC_KEY DB_NAME TS# NAME CHANGE# CRE_DATE
——– ———– ——— —– ——– ——— ———-
1 2 DB00 3 DATA01 9611 20-OCT-97
1 2 DB00 1 RBS 9599 20-OCT-97
1 2 DB00 4 RMAN_TS 14023 29-OCT-97
1 2 DB00 0 SYSTEM 3 20-OCT-97
1 2 DB00 2 TEMP 9605 20-OCT-97
To determine which scripts are currently stored in the recovery catalog for the target database:
SQL> SELECT * FROM RC_STORED_SCRIPT;
DB_KEY DB_NAME SCRIPT_NAME
——– ——— —————
1 DB00 nightlybackup
1 DB00 archivebackup
RMAN DATA DICTIONARY VIEWS used to query the control file
==========================================================
If you are not using a recovery catalog, RMAN information is stored in the
target database’s control file .
V$ARCHIVED_LOG
This view displays archived log information from the controlfile
including archive log names. An archive log record is inserted after
the online redo log is successfully archived or cleared (name column
is NULL if the log was cleared). If the log is archived twice, there
will be two archived log records with the same THREAD#, SEQUENCE#,
and FIRST_CHANGE#, but with a different name. An archive log record
is also inserted when an archive log is restored from a backup set
or a copy.
COLUMN DESCRIPTION
RECID Archived log record ID
STAMP Archived log record stamp
NAME Archived log file name
THREAD# Redo thread number
SEQUENCE# Redo log sequence number
RESETLOGS_CHANGE# Resetlogs change# of database when written
RESETLOGS_TIME Resetlogs time of database when written
FIRST_CHANGE# First change# in the archived log
FIRST_TIME Timestamp of the first change
NEXT_CHANGE# First change in the next log
NEXT_TIME Timestamp of the next change
BLOCKS Size of the archived log in blocks
BLOCK_SIZE Redo log block size
COMPLETION_TIME Time when the archiving completed
DELETED YES/NO
V$BACKUP_CORRUPTION
This view displays information about corruptions in datafile backups
from the controlfile. Note that corruptions are not tolerated in the
controlfile and archived log backups.
COLUMN DESCRIPTION
RECID Backup corruption record ID
STAMP Backup corruption record stamp
SET_STAMP Backup set stamp
SET_COUNT Backup set count
PIECE# Backup piece number
FILE# Datafile number
BLOCK# First block of the corrupted range
BLOCKS Number of contiguous blocks in corrupted range
CORRUPTION_CHANGE# Change# where logical corruption was detected.
MARKED_CORRUPT YES/NO. If YES the blocks were not marked
corrupted in datafile, but were detected and
marked while making backup
V$COPY_CORRUPTION
This view displays information about datafile copy corruptions from
the controlfile.
COLUMN DESCRIPTION
RECID Copy corruption record ID
STAMP Copy corruption record stamp
COPY_RECID Datafile copy record ID
COPY_STAMP Datafile copy record stamp
FILE# Datafile number
BLOCK# First block of the corrupted range
BLOCKS Number of contiguous blocks in corrupted range
CORRUPTION_CHANGE# Change# where logical corruption was detected.
MARKED_CORRUPT YES/NO. If YES the blocks were not marked
corrupted in datafile, but were detected and
marked while making the datafile copy
V$BACKUP_DATAFILE
Useful for creating equal sized backup sets by determining the
number of blocks in each datafile. Can also find the number of
corrupt blocks for the datafile.
COLUMN DESCRIPTION
RECID Backup datafile record ID
STAMP Backup datafile record stamp
SET_STAMP Backup set stamp
SET_COUNT Backup set count
FILE# Datafile number. Set to 0 for controlfile
CREATION_CHANGE# Creation change of the datafile
CREATION_TIME Creation timestamp of the datafile
RESETLOGS_CHANGE# Resetlogs change# of datafile when backed up
RESETLOGS_TIME Resetlogs timestamp of datafile when backed up
INCREMENTAL_LEVEL (0-4) incremental backup level
INCREMENTAL_CHANGE# All blocks changed after incremental change# is
included in this backup.
CHECKPOINT_CHANGE# All changes up to checkpoint change# are included
in this backup
CHECKPOINT_TIME Timestamp of the checkpoint
ABSOLUTE_FUZZY_CHANGE# Highest change# in this backup
MARKED_CORRUPT Number of blocks marked corrupt
MEDIA_CORRUPT Number of blocks media corrupt
LOGICALLY_CORRUPT Number of blocks logically corrupt
DATAFILE_BLOCKS Size of the datafile in blocks at backup time.
BLOCKS Size of the backup datafile in blocks.
BLOCK_SIZE Block size
OLDEST_OFFLINE_RANGE The RECID of the oldest offline range record in
this backup controlfile.
COMPLETION_TIME The time completed.
V$BACKUP_REDOLOG
This view displays information about archived logs in backup sets
from the controlfile. Note that online redo logs cannot be backed up
directly; they must be archived first to disk and then backed up. An
archive log backup set can contain one or more archived logs.
COLUMN DESCRIPTION
RECID Record ID for this row.
STAMP Timestamp used with RECID to identify this row
SET_STAMP One foreign key for the row of the V$BACKUP_SET
table that identifies backup set
SET_COUNT One foreign key for the row of the V$BACKUP_SET
table that identifies this backup set
THREAD# Thread number for the log
SEQUENCE# Log sequence number
RESETLOGS_CHANGE# Change number of the last resetlogs
RESETLOGS_TIME Change time of the last resetlogs
FIRST_CHANGE# SCN when the log was switched into.
FIRST_TIME Time allocated when the log was switched into
NEXT_CHANGE# SCN when the log was switched out of.
NEXT_TIME Time allocated when the log was switched out of
BLOCKS Size of the log in logical blocks
BLOCK_SIZE Size of the log blocks in bytes
V$BACKUP_SET
This view displays backup set information from the controlfile. A
backup set record is inserted after the backup set is successfully
completed.
COLUMN DESCRIPTION
RECID Backup set record ID
STAMP Backup set record timestamp
SET_STAMP Backup set stamp.
SET_COUNT Backup set count. The backup set count is
incremented by one every time a new backup set
is started
BACKUP_TYPE Type of files that are in this backup.
archived redo logs = \QL
datafile full backup = \QD
incremental backup = \QI
CONTROLFILE_INCLUDED YES/NO
INCREMENTAL_LEVEL Location where this backup set fits into the
database’s backup strategy.
0 = full datafile backups
non-zero = incremental datafile backups
NULL = archivelog backups
PIECES Number of distinct backup pieces in backup set
COMPLETION_TIME Time when the backup completes successfully
ELAPSED_SECONDS The number of elapsed seconds.
BLOCK_SIZE Block size of the backup set
V$BACKUP_PIECE
This view displays information about backup pieces from the
controlfile. Each backup set consist of one or more backup pieces.
COLUMN DESCRIPTION
RECID Backup piece record ID
STAMP Backup piece record stamp
SET_STAMP Backup set stamp
SET_COUNT Backup set count
PIECE# Backup piece number (1-N)
DEVICE_TYPE Type of device where backup piece resides.
HANDLE Identifies the backup piece on restore
COMMENTS Comment returned by OS or storage subsystem.
MEDIA Name of the media where backup piece resides.
CONCUR YES/NO, Whether piece on media can be accessed
concurrently
TAG Backup piece tag. The tag is specified at backup
set level, but stored at piece level
DELETED If set to YES indicates the piece is deleted
START_TIME The starting time.
COMPLETION_TIME The completion time.
ELAPSED_SECONDS The number of elapsed seconds.
Querying the Recovery Catalog Through RMAN
———————————————
While inside RMAN you can use the list command to query the contents of
the recovery catalog, or the target database control file if no
recovery catalog is used.
Examples:
To list all backups of files in tablespace tbs_1 that were made since November
first:
RMAN> list until time ‘Nov 1 1996 00:00:00’ backupset of tablespace tbs_1;
To list all backups on device type ‘sbt_tape’:
RMAN> list device type ‘sbt_tape’ backupset of database;
To list all copies of a datafile, qualified by tag and directory:
RMAN> list tag foo like ‘/somedir/%’ copy of datafile 21;
To list all database incarnations registered in the recovery catalog:
RMAN> list incarnation of database;
You can also use the list command to determine which copies and backups can be
deleted. For example, if a full backup of the database was created on November
2, and it will not be necessary to recover the database to an earlier
point-in-time, then the backup sets listed in the following report can be
deleted:
RMAN> list until time ‘Nov 1 1996 00:00:00’ backupset of database;
9i New List Functionality
————————-
This command will allow you to list be either backup or file. The BY BACKUP
output shows backup sets and the contents of each backup set. The BY FILE
shows the file name, backup sets where the file appears and backup copies of
this file. A SUMMARY option is available with the BY BACKUP option which gives
a one-line summary for each file or backup set. Also note that SUMMARY and
VERBOSE options only apply to the LIST BACKUP, not to LIST COPY. The defaults
are BY BACKUP, VERBOSE.
RMAN> list backup .. [listoptions];
listoptions: [BY report unrecoverable database;
To report on all datafiles which need a new backup because 3 or more
incremental backups have been taken since the last full backup.
RMAN> report need backup incremental 3 database;
To report on all datafiles in tablespace tbs_1 which need a new backup
because the last full or incremental backup was taken more than 5 days
ago.
RMAN> report need backup days 5 database;
9i New Report Functionality
—————————-
This command shows which backups are no longer needed according to the
retention policy. The RECOVERY WINDOW has been added to specify a window
of time during which the database must be recoverable.
RMAN> report obsolete ;
This command will delete files that would be reported by REPORT OBSOLETE.
RMAN> delete obsolete;
NEW 9i SHOW COMMAND
——————–
There is a new command similar to the SHOW PARAMETER command in svrmgrl which
allows you to display the current values for various CONFIGURE commands.
RMAN> show show_operand [,show_operand …];
show_operand: RETENTION POLICY |
EXCLUDE |
BACKUP COPIES |
CHANNEL |
DEFAULT DEVICE TYPE |
SNAPSHOT CONTROLFILE |
AUXNAME |
MAXSETSIZE |
BACKUP OPTIMIZATION |
ALL
NOTE: for additional table and views please refer to the 9i and 10g documentation as a reference
扫一扫
1151
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
