Eric Gao's Bloggaoqiang.blog.chinaunix.net

Oracle MySQL PostgreSQL AIX Linux Storage

  • 博客访问: 1826333
  • 博文数量: 196
  • 博客积分: 3409
  • 博客等级: 中校
  • 技术积分: 3858
  • 用 户 组: 普通用户
  • 注册时间: 2010-12-24 10:21
  • 认证徽章:
个人简介

● ITPUB名人堂嘉宾 ● ChinaUnix社区博客专家 ● ChinaUnix社区Oracle板块版主 ● 优酷网认证音乐牛人:EricGuitar ● SDOUG 核心成员 ●E-mail:gaoqiangdba@163.com

文章分类

全部博文(196)

文章存档

2016年(5)

2015年(39)

2014年(27)

2013年(18)

2012年(24)

2011年(63)

2010年(20)

微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题

分类: Oracle

  


概要:

  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







阅读(10818) | 评论(2) | 转发(3) |
给主人留下些什么吧!~~

jackson1985742013-10-15 20:52:38

CU博客助理:嘉宾点评: 以实例的形式详细的介绍了RMAN备份中“查看备份情况”的相关细节.  内容非常详细. 很好的技术参考文档:)(感谢您参与“原创博文评选”获奖结果即将公布)

谢谢您的点评!

回复 | 举报

CU博客助理2013-10-10 11:36:11

嘉宾点评: 以实例的形式详细的介绍了RMAN备份中“查看备份情况”的相关细节.  内容非常详细. 很好的技术参考文档:)(感谢您参与“原创博文评选”获奖结果即将公布)

评论热议
请登录后评论。

登录 注册