RMAN备份、恢复实验室 之 备份篇 【查看备份情况】

jackson198574

原文地址（更清晰、直观的彩色文字注释）：http://blog.chinaunix.net/uid-20802110-id-3849567.html

概要：

查看备份集
查看SCHEMA统计信息
查看表空间的备份情况
查看数据文件的备份情况
查看控制文件的备份情况
查看不同类型设备上的备份情况
查看归档日志及其备份情况
查看系统中不存在（或者说是不在指定路径下）的备份
查看过期的备份
查看几天前的schema状态
查看需要备份的文件
查看违反规则的备份




操作命令：


查看备份集：

RMAN>list backupset;


List of Backup Sets
===================


BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    9.36M      DISK        00:00:03     02-AUG-13     
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20130802T014453
        Piece Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/02og8fcm_1_1
  SPFILE Included: Modification time: 02-AUG-13
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 1181185      Ckp time: 02-AUG-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    9.36M      DISK        00:00:01     02-AUG-13     
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20130802T014548
        Piece Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/04og8fed_1_1
  SPFILE Included: Modification time: 02-AUG-13
  SPFILE db_unique_name: ORCL                                ----------------数据库名
  Control File Included: Ckp SCN: 1181217      Ckp time: 02-AUG-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    1.01G      DISK        00:02:24     02-AUG-13     
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20130802T015052
        Piece Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/05og8fns_1_1
  List of Datafiles in backup set 3
  File LV Type Ckp SCN    Ckp Time  Name      -----------level type 跟增量备份有关
  ---- -- ---- ---------- --------- ----
  1       Full 1181392    02-AUG-13 /oracle/app/oradata/ORCL/datafile/o1_mf_system_8xm4839m_.dbf
  2       Full 1181392    02-AUG-13 /oracle/app/oradata/ORCL/datafile/o1_mf_sysaux_8xm483f7_.dbf
  3       Full 1181392    02-AUG-13 /oracle/app/oradata/ORCL/datafile/o1_mf_undotbs1_8xm483gd_.dbf
  4       Full 1181392    02-AUG-13 /oracle/app/oradata/ORCL/datafile/o1_mf_users_8xm483jk_.dbf
  5       Full 1181392    02-AUG-13 /oracle/app/oradata/ORCL/datafile/o1_mf_example_8xm4gzck_.dbf   -------------------数据文件信息，包括路径、文件名、SCN、增量备份的LEVEL、文件号等

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    9.36M      DISK        00:00:04     02-AUG-13     
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20130802T015052
        Piece Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/06og8fsf_1_1
  SPFILE Included: Modification time: 02-AUG-13
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 1181392      Ckp time: 02-AUG-13



查看SCHEMA统计信息：
RMAN>  report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    690      SYSTEM               ***     /oracle/app/oradata/ORCL/datafile/o1_mf_system_8xm4839m_.dbf
2    490      SYSAUX               ***     /oracle/app/oradata/ORCL/datafile/o1_mf_sysaux_8xm483f7_.dbf
3    85       UNDOTBS1             ***     /oracle/app/oradata/ORCL/datafile/o1_mf_undotbs1_8xm483gd_.dbf
4    8        USERS                ***     /oracle/app/oradata/ORCL/datafile/o1_mf_users_8xm483jk_.dbf
5    100      EXAMPLE              ***     /oracle/app/oradata/ORCL/datafile/o1_mf_example_8xm4gzck_.dbf
                                     ---------------------可以看到文件号、文件大小、所属表空间名、文件路径、文件名等。

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    29       TEMP                 32767       /oracle/app/oradata/ORCL/datafile/o1_mf_temp_8xm4gq4q_.tmp

查看表空间的备份情况：
RMAN> list backup of tablespace users;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    1.01G      DISK        00:02:24     02-AUG-13     
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20130802T015052
        Piece Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/05og8fns_1_1   -----备份集存放路径
  List of Datafiles in backup set 3
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  4       Full 1181392    02-AUG-13 /oracle/app/oradata/ORCL/datafile/o1_mf_users_8xm483jk_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Full    6.30M      DISK        00:00:00     02-AUG-13     
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20130802T021009
        Piece Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/07og8gs2_1_1
  List of Datafiles in backup set 5
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  4       Full 1181392    02-AUG-13 /oracle/app/oradata/ORCL/datafile/o1_mf_users_8xm483jk_.dbf
查看数据文件的备份情况：

RMAN> list backup of datafile 1;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
3       Full    1.01G      DISK        00:02:24     02-AUG-13     
        BP Key: 3   Status: AVAILABLE  Compressed: NO  Tag: TAG20130802T015052
        Piece Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/05og8fns_1_1
  List of Datafiles in backup set 3
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1181392    02-AUG-13 /oracle/app/oradata/ORCL/datafile/o1_mf_system_8xm4839m_.dbf

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
6       Full    597.20M    DISK        00:00:44     02-AUG-13     
        BP Key: 6   Status: AVAILABLE  Compressed: NO  Tag: TAG20130802T021232
        Piece Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/08og8h0g_1_1
  List of Datafiles in backup set 6
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1       Full 1181392    02-AUG-13 /oracle/app/oradata/ORCL/datafile/o1_mf_system_8xm4839m_.dbf

-------------因为有多次备份，多个备份集中都有该数据文件，所以罗列的备份集是只要包含该文件的所有备份集。另外，对于备份策略，如果以冗余为准，而不是以时间为准的话，执行crosscheck的话，有些备份集可能会出现obselete，这一点有待实验验证，有精力的同学可以做以下实验，来这里探讨一下。





查看控制文件的备份情况：
RMAN> list backup of controlfile;
List of Backup Sets
===================
BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
1       Full    9.36M      DISK        00:00:03     02-AUG-13     
        BP Key: 1   Status: AVAILABLE  Compressed: NO  Tag: TAG20130802T014453
        Piece Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/02og8fcm_1_1
  Control File Included: Ckp SCN: 1181185      Ckp time: 02-AUG-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
2       Full    9.36M      DISK        00:00:01     02-AUG-13     
        BP Key: 2   Status: AVAILABLE  Compressed: NO  Tag: TAG20130802T014548
        Piece Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/04og8fed_1_1
  Control File Included: Ckp SCN: 1181217      Ckp time: 02-AUG-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Full    9.36M      DISK        00:00:04     02-AUG-13     
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20130802T015052
        Piece Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/06og8fsf_1_1
  Control File Included: Ckp SCN: 1181392      Ckp time: 02-AUG-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
7       Full    9.36M      DISK        00:00:04     02-AUG-13     
        BP Key: 7   Status: AVAILABLE  Compressed: NO  Tag: TAG20130802T021232
        Piece Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/09og8h1t_1_1
  Control File Included: Ckp SCN: 1181392      Ckp time: 02-AUG-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
9       Full    9.36M      DISK        00:00:04     02-AUG-13     
        BP Key: 9   Status: AVAILABLE  Compressed: NO  Tag: TAG20130802T021853
        Piece Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/0bog8hdh_1_1
  Control File Included: Ckp SCN: 1181392      Ckp time: 02-AUG-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
10      Full    9.33M      DISK        00:00:01     02-AUG-13     
        BP Key: 10   Status: AVAILABLE  Compressed: NO  Tag: TAG20130802T022227
        Piece Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/0cog8hj3_1_1
  Control File Included: Ckp SCN: 1181392      Ckp time: 02-AUG-13

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
12      Full    9.36M      DISK        00:00:01     02-AUG-13     
        BP Key: 12   Status: AVAILABLE  Compressed: NO  Tag: TAG20130802T022254
        Piece Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/0eog8hma_1_1
  Control File Included: Ckp SCN: 1181392      Ckp time: 02-AUG-13

-------------同上，罗列的是所有包含控制文件的备份集。



查看不同类型设备上的备份：
查看设备类型为“Disk”的备份：（查看磁带上的备份可以使用“list device type sbt backup;”命令）
RMAN> list device type disk backup;
List of Backup Sets
===================
BS Key  Type LV Size
------- ---- -- ----------
1       Full    9.36M
  SPFILE Included: Modification time: 02-AUG-13
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 1181185      Ckp time: 02-AUG-13

  Backup Set Copy #1 of backup set 1
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:03     02-AUG-13       NO         TAG20130802T014453

    List of Backup Pieces for backup set 1 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    1       1   AVAILABLE   /oracle/app/oracle/product/11.2.0/db_1/dbs/02og8fcm_1_1

  Backup Set Copy #2 of backup set 1
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:03     02-AUG-13       NO         TAG20130802T014453

    List of Backup Pieces for backup set 1 Copy #2
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    21      1   AVAILABLE   /oracle/app/oracle/product/11.2.0/db_1/dbs/02og8fcm_1_2

BS Key  Type LV Size
------- ---- -- ----------
2       Full    9.36M
  SPFILE Included: Modification time: 02-AUG-13
  SPFILE db_unique_name: ORCL
  Control File Included: Ckp SCN: 1181217      Ckp time: 02-AUG-13

  Backup Set Copy #1 of backup set 2
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:01     02-AUG-13       NO         TAG20130802T014548

    List of Backup Pieces for backup set 2 Copy #1
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    2       1   AVAILABLE   /oracle/app/oracle/product/11.2.0/db_1/dbs/04og8fed_1_1

  Backup Set Copy #2 of backup set 2
  Device Type Elapsed Time Completion Time Compressed Tag
  ----------- ------------ --------------- ---------- ---
  DISK        00:00:01     02-AUG-13       NO         TAG20130802T014548

    List of Backup Pieces for backup set 2 Copy #2
    BP Key  Pc# Status      Piece Name
    ------- --- ----------- ----------
    22      1   AVAILABLE   /oracle/app/oracle/product/11.2.0/db_1/dbs/04og8fed_1_2


查看归档日志及其备份情况：
注：查看归档日志的命令为：list archivelog all;  查看未备份的备份集；
    查看归档日志备份集的命令为：list backup of archivelog all； 查看已备份的备份集；
RMAN> list archivelog all;

List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
1       1    12      A 02-AUG-13
        Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_12_820195525.dbf

2       1    13      A 02-AUG-13
        Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_13_820195525.dbf

3       1    14      A 02-AUG-13
        Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_14_820195525.dbf

4       1    15      A 02-AUG-13
        Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_15_820195525.dbf

6       1    16      A 02-AUG-13
        Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_16_820195525.dbf

5       1    17      A 02-AUG-13
        Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_17_820195525.dbf

7       1    18      A 02-AUG-13
        Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_18_820195525.dbf

8       1    19      A 02-AUG-13
        Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_19_820195525.dbf

9       1    20      A 02-AUG-13
        Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_20_820195525.dbf

10      1    21      A 02-AUG-13
        Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_21_820195525.dbf

11      1    22      A 02-AUG-13
        Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_22_820195525.dbf

12      1    23      A 02-AUG-13
        Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_23_820195525.dbf

13      1    24      A 02-AUG-13
        Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_24_820195525.dbf

14      1    25      A 02-AUG-13
        Name: /oracle/app/oracle/product/11.2.0/db_1/dbs/arch1_25_820195525.dbf

查看系统中不存在（或者说是不在指定路径下）的备份：
RMAN> list expired backup;

specification does not match any backup in the repository
注：1.以上命令查看的是不存在的备份，如备份集用操作系统命令删除之后，crosscheck之后该备份集就为expired（X）状态。
2.跟obsolete概念区分，该参数是针对不满足retention policy条件(可以按照存放份数，也可以按照存放日期)的备份。


查看过期的备份：
RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1   ------检测条件是按照份数来的，多余的备份会被标记为obsolete，也可以设定是n Days。
Report of obsolete backups and copies
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Backup Set           1      02-AUG-13        
  Backup Piece       1      02-AUG-13          /oracle/app/oracle/product/11.2.0/db_1/dbs/02og8fcm_1_1
Backup Set           1      02-AUG-13        
  Backup Piece       21     02-AUG-13          /oracle/app/oracle/product/11.2.0/db_1/dbs/02og8fcm_1_2
Backup Set           2      02-AUG-13        
  Backup Piece       22     02-AUG-13          /oracle/app/oracle/product/11.2.0/db_1/dbs/04og8fed_1_2
Backup Set           2      02-AUG-13         
  Backup Piece       2      02-AUG-13          /oracle/app/oracle/product/11.2.0/db_1/dbs/04og8fed_1_1
Backup Set           3      02-AUG-13        
  Backup Piece       23     02-AUG-13          /oracle/app/oracle/product/11.2.0/db_1/dbs/05og8fns_1_2
Backup Set           3      02-AUG-13        
  Backup Piece       3      02-AUG-13          /oracle/app/oracle/product/11.2.0/db_1/dbs/05og8fns_1_1
Backup Set           4      02-AUG-13        
  Backup Piece       24     02-AUG-13          /oracle/app/oracle/product/11.2.0/db_1/dbs/06og8fsf_1_2
Backup Set           4      02-AUG-13        
  Backup Piece       4      02-AUG-13          /oracle/app/oracle/product/11.2.0/db_1/dbs/06og8fsf_1_1
Backup Set           7      02-AUG-13        
  Backup Piece       7      02-AUG-13          /oracle/app/oracle/product/11.2.0/db_1/dbs/09og8h1t_1_1
Backup Set           7      02-AUG-13        
  Backup Piece       25     02-AUG-13          /oracle/app/oracle/product/11.2.0/db_1/dbs/09og8h1t_1_2
Backup Set           9      02-AUG-13        
  Backup Piece       9      02-AUG-13          /oracle/app/oracle/product/11.2.0/db_1/dbs/0bog8hdh_1_1
Backup Set           9      02-AUG-13        
  Backup Piece       26     02-AUG-13          /oracle/app/oracle/product/11.2.0/db_1/dbs/0bog8hdh_1_2
Backup Set           10     02-AUG-13        
  Backup Piece       27     02-AUG-13          /oracle/app/oracle/product/11.2.0/db_1/dbs/0cog8hj3_1_2
Backup Set           10     02-AUG-13        
  Backup Piece       10     02-AUG-13          /oracle/app/oracle/product/11.2.0/db_1/dbs/0cog8hj3_1_1
Backup Set           12     02-AUG-13        
  Backup Piece       29     02-AUG-13          /oracle/app/oracle/product/11.2.0/db_1/dbs/0eog8hma_1_2
Backup Set           12     02-AUG-13        
  Backup Piece       12     02-AUG-13          /oracle/app/oracle/product/11.2.0/db_1/dbs/0eog8hma_1_1
Backup Set           14     02-AUG-13        
  Backup Piece       14     02-AUG-13          /oracle/app/oracle/product/11.2.0/db_1/dbs/c-1347976258-20130802-00
Backup Set           16     02-AUG-13        
  Backup Piece       16     02-AUG-13          /oracle/app/oracle/product/11.2.0/db_1/dbs/c-1347976258-20130802-01



查看几天前的schema状态
[oracle@dbserver dbs]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Fri Aug 2 03:22:07 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=134797625

RMAN> report schema at time 'SYSDATE-1';

using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of report command at 08/02/2013 03:22:23
RMAN-06137: must have recovery catalog for REPORT SCHEMA AT TIME
--------------所以很多时候都建议使用catalog进行备份

查看需要备份的文件：
RMAN> 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
---- ----- -----------------------------------------------------

查看违反规则的备份：
RMAN> report obsolete;

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
no obsolete backups found