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

  


概要：

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

操作命令：



查看备份集：

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=1347976258)

 

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