ITPub博客

首页 > Linux操作系统 > Linux操作系统 > RMAN: How to Query the RMAN Recovery Catalog [ID 98342.1]

RMAN: How to Query the RMAN Recovery Catalog [ID 98342.1]

原创 Linux操作系统 作者:spider0283 时间:2011-09-03 11:10:07 0 删除 编辑

修改时间 19-OCT-2010     类型 BULLETIN     状态 PUBLISHED 

"Checked for relevance on 21-Dec-2008"

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

显示相关信息 相关的


产品
  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Standard Edition
  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Personal Edition
  • Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition
关键字
RECOVERY MANAGER; DATABASE RECOVERY

返回页首返回页首


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/38267/viewspace-706561/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2011-03-29

  • 博文量
    194
  • 访问量
    628247