ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > Oracle > 【RMAN】Data Recovery Advisor 小测试

【RMAN】Data Recovery Advisor 小测试

原创 Oracle 作者：xysoul_云龙 时间：2017-07-25 14:40:02 0 删除 编辑

执行以下语句报错：

点击(此处)折叠或打开

  1. SQL> create table t_a as select * from all_objects;
  2. create table t_a as select * from all_objects
  3.                                   *
  4. ERROR at line 1:
  5. ORA-01116: error in opening database file 4
  6. ORA-01110: data file 4: '/oracle/app/oracle/oradata/mydb/users01.dbf'
  7. ORA-27041: unable to open file
  8. Linux-x86_64 Error: 2: No such file or directory
  9. Additional information: 3

提示找不到该数据文件，那么我们怎么恢复呢，大多是通过rman查看备份，执行restore,recover等相关命令，那么有没有更简单的方式呢？

下面我们来看下：

在 Oracle Database 11g 中，新增的 Data Recovery Advisor 极大地简化了该操作。

执行下面命令

点击(此处)折叠或打开

  1. [oracle@mytest ~]$ rman target /

  3. Recovery Manager: Release 11.2.0.4.0 - Production on Tue Jul 25 13:54:35 2017

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

  7. connected to target database: MYDB (DBID=2861835083)

  9. RMAN> list failure;   --列出失败的信息

  11. using target database control file instead of recovery catalog
  12. List of Database Failures
  13. =========================

  15. Failure ID Priority Status Time Detected Summary
  16. ---------- -------- --------- ------------- -------
  17. 502 HIGH OPEN 25-JUL-17 One or more non-system datafiles are missing   --数据文件丢失

  19. RMAN> list failure 502 detail;   --关于某项失败的详细信息

  21. List of Database Failures
  22. =========================

  24. Failure ID Priority Status Time Detected Summary
  25. ---------- -------- --------- ------------- -------
  26. 502 HIGH OPEN 25-JUL-17 One or more non-system datafiles are missing
  27.   Impact: See impact for individual child failures
  28.   List of child failures for parent failure ID 502
  29.   Failure ID Priority Status Time Detected Summary
  30.   ---------- -------- --------- ------------- -------
  31.   505 HIGH OPEN 25-JUL-17 Datafile 4: '/oracle/app/oracle/oradata/mydb/users01.dbf' is missing   --具体哪个文件丢失
  32.     Impact: Some objects in tablespace USERS might be unavailable

怎么修复呢？


点击(此处)折叠或打开

  1. RMAN> advise failure;  --执行该命令，查看推荐恢复方式

  3. List of Database Failures
  4. =========================

  6. Failure ID Priority Status Time Detected Summary
  7. ---------- -------- --------- ------------- -------
  8. 502 HIGH OPEN 25-JUL-17 One or more non-system datafiles are missing
  9.   Impact: See impact for individual child failures
  10.   List of child failures for parent failure ID 502
  11.   Failure ID Priority Status Time Detected Summary
  12.   ---------- -------- --------- ------------- -------
  13.   505 HIGH OPEN 25-JUL-17 Datafile 4: '/oracle/app/oracle/oradata/mydb/users01.dbf' is missing
  14.     Impact: Some objects in tablespace USERS might be unavailable

  16. analyzing automatic repair options; this may take some time
  17. allocated channel: ORA_DISK_1
  18. channel ORA_DISK_1: SID=125 device type=DISK
  19. analyzing automatic repair options complete

  21. Mandatory Manual Actions
  22. ========================
  23. no manual actions available

  25. Optional Manual Actions
  26. =======================
  27. 1. If file /oracle/app/oracle/oradata/mydb/users01.dbf was unintentionally renamed or moved, restore it

  29. Automated Repair Options
  30. ========================
  31. Option Repair Description
  32. ------ ------------------
  33. 1 Restore and recover datafile 4
  34.   Strategy: The repair includes complete media recovery with no data loss
  35.   Repair script: /oracle/app/oracle/diag/rdbms/mydb/mydb/hm/reco_2119774466.hm

也可参考文件：动态性能视图 V$IR_MANUAL_CHECKLIST 

开始恢复：

点击(此处)折叠或打开

  1. RMAN> repair failure preview;   --修复预演

  3. Strategy: The repair includes complete media recovery with no data loss
  4. Repair script: /oracle/app/oracle/diag/rdbms/mydb/mydb/hm/reco_2119774466.hm

  6. contents of repair script:   --具体相关命令
  7.    # restore and recover datafile
  8.    sql 'alter database datafile 4 offline';
  9.    restore datafile 4;
  10.    recover datafile 4;
  11.    sql 'alter database datafile 4 online';

  13. RMAN> repair failure;   --开始修复

  15. Strategy: The repair includes complete media recovery with no data loss
  16. Repair script: /oracle/app/oracle/diag/rdbms/mydb/mydb/hm/reco_2119774466.hm

  18. contents of repair script:
  19.    # restore and recover datafile
  20.    sql 'alter database datafile 4 offline';
  21.    restore datafile 4;
  22.    recover datafile 4;
  23.    sql 'alter database datafile 4 online';

  25. Do you really want to execute the above repair (enter YES or NO)? yes    -- 确认输入yes，如果不想输入，可执行repair failure noprompt 
  26. executing repair script

  28. sql statement: alter database datafile 4 offline

  30. Starting restore at 25-JUL-17
  31. using channel ORA_DISK_1

  33. channel ORA_DISK_1: starting datafile backup set restore
  34. channel ORA_DISK_1: specifying datafile(s) to restore from backup set
  35. channel ORA_DISK_1: restoring datafile 00004 to /oracle/app/oracle/oradata/mydb/users01.dbf
  36. channel ORA_DISK_1: reading from backup piece /home/oracle/full_03sa84pg_1_1_3.dat
  37. channel ORA_DISK_1: piece handle=/home/oracle/full_03sa84pg_1_1_3.dat tag=TAG20170725T134816
  38. channel ORA_DISK_1: restored backup piece 1
  39. channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
  40. Finished restore at 25-JUL-17

  42. Starting recover at 25-JUL-17
  43. using channel ORA_DISK_1

  45. starting media recovery
  46. media recovery complete, elapsed time: 00:00:00

  48. Finished recover at 25-JUL-17

  50. sql statement: alter database datafile 4 online
  51. repair failure complete

至此修复完成

运行状况检查：
在 Oracle Database 11g 中，RMAN 中有个新命令 VALIDATE DATABASE 通过检查数据库块中的物理损坏极大地简化了该操作。如果检测到损坏，将记录到自动诊断信息库中。然后，RMAN 将生成输出，下面显示了部分输出：


点击(此处)折叠或打开

  1. RMAN> validate database;

  3. Starting validate at 25-JUL-17
  4. using channel ORA_DISK_1
  5. channel ORA_DISK_1: starting validation of datafile
  6. channel ORA_DISK_1: specifying datafile(s) for validation
  7. input datafile file number=00006 name=/oracle/app/oracle/oradata/mydb/test01.dbf
  8. input datafile file number=00007 name=/backup/test02.dbf
  9. input datafile file number=00008 name=/backup/mydb/undo01.dbf
  10. input datafile file number=00001 name=/oracle/app/oracle/oradata/mydb/system01.dbf
  11. channel ORA_DISK_1: validation complete, elapsed time: 00:00:25
  12. List of Datafiles
  13. =================
  14. File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
  15. ---- ------ -------------- ------------ --------------- ----------
  16. 1 OK 0 15597 97351 2880261
  17.   File Name: /oracle/app/oracle/oradata/mydb/system01.dbf
  18.   Block Type Blocks Failing Blocks Processed
  19.   ---------- -------------- ----------------
  20.   Data 0 64154
  21.   Index 0 13405
  22.   Other 0 4124

  24. File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
  25. ---- ------ -------------- ------------ --------------- ----------
  26. 6 OK 0 13874 262144 2737477
  27.   File Name: /oracle/app/oracle/oradata/mydb/test01.dbf
  28.   Block Type Blocks Failing Blocks Processed
  29.   ---------- -------------- ----------------
  30.   Data 0 150350
  31.   Index 0 95666
  32.   Other 0 2254

  34. File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
  35. ---- ------ -------------- ------------ --------------- ----------
  36. 7 OK 0 17575 131072 2790581
  37.   File Name: /backup/test02.dbf
  38.   Block Type Blocks Failing Blocks Processed
  39.   ---------- -------------- ----------------
  40.   Data 0 17101
  41.   Index 0 95654
  42.   Other 0 742

  44. File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
  45. ---- ------ -------------- ------------ --------------- ----------
  46. 8 OK 0 34161 38400 2880261
  47.   File Name: /backup/mydb/undo01.dbf
  48.   Block Type Blocks Failing Blocks Processed
  49.   ---------- -------------- ----------------
  50.   Data 0 0
  51.   Index 0 0
  52.   Other 0 4239

  54. channel ORA_DISK_1: starting validation of datafile
  55. channel ORA_DISK_1: specifying datafile(s) for validation
  56. input datafile file number=00002 name=/oracle/app/oracle/oradata/mydb/sysaux01.dbf
  57. input datafile file number=00004 name=/oracle/app/oracle/oradata/mydb/users01.dbf
  58. channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
  59. List of Datafiles
  60. =================
  61. File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
  62. ---- ------ -------------- ------------ --------------- ----------
  63. 2 OK 0 21651 78129 2880049
  64.   File Name: /oracle/app/oracle/oradata/mydb/sysaux01.dbf
  65.   Block Type Blocks Failing Blocks Processed
  66.   ---------- -------------- ----------------
  67.   Data 0 15713
  68.   Index 0 11381
  69.   Other 0 29335

  71. File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
  72. ---- ------ -------------- ------------ --------------- ----------
  73. 4 OK 0 154 9948 2734400
  74.   File Name: /oracle/app/oracle/oradata/mydb/users01.dbf
  75.   Block Type Blocks Failing Blocks Processed
  76.   ---------- -------------- ----------------
  77.   Data 0 3930
  78.   Index 0 635
  79.   Other 0 5201

  81. channel ORA_DISK_1: starting validation of datafile
  82. channel ORA_DISK_1: specifying datafile(s) for validation
  83. input datafile file number=00005 name=/oracle/app/oracle/oradata/mydb/example01.dbf
  84. channel ORA_DISK_1: validation complete, elapsed time: 00:00:03
  85. List of Datafiles
  86. =================
  87. File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
  88. ---- ------ -------------- ------------ --------------- ----------
  89. 5 OK 0 32632 47040 2744132
  90.   File Name: /oracle/app/oracle/oradata/mydb/example01.dbf
  91.   Block Type Blocks Failing Blocks Processed
  92.   ---------- -------------- ----------------
  93.   Data 0 9606
  94.   Index 0 1733
  95.   Other 0 3069

  97. channel ORA_DISK_1: starting validation of datafile
  98. channel ORA_DISK_1: specifying datafile(s) for validation
  99. including current control file for validation
  100. including current SPFILE in backup set
  101. channel ORA_DISK_1: validation complete, elapsed time: 00:00:00
  102. List of Control File and SPFILE
  103. ===============================
  104. File Type Status Blocks Failing Blocks Examined
  105. ------------ ------ -------------- ---------------
  106. SPFILE OK 0 2
  107. Control File OK 0 636
  108. Finished validate at 25-JUL-17

当然也可以执行相关其他命令：

validate tablespace users;
validate datafile 1;
 validate datafile 4 block 56;
当然，还可以验证 spfile、controlfilecopy、恢复文件、快速恢复区，等等。


关于Oracle11.2 Data Recovery Advisor 小测试 先到这里。

参考文档：

Oracle Database 11g：
面向 DBA 和开发人员的重要特性

作者：Arup Nanda Oracle ACE 总监







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

0
0
上一篇： 【LISTENER】一个数据库配置两个监听端口号
下一篇： 【RMAN】Oracle11g备份恢复新特性
请登录后发表评论 登录
全部评论
xysoul_云龙
专家姓名：杨云龙
主要从事Oracle数据库 DBA相关工作，其他操作系统、中间件等也有涉及，热衷分享。岁月老将至，如有幸在江湖中留点踪迹，也是欣慰。 【文盲筱烨】好读书爱运动的IT技术爱好者 微博：文盲筱烨 微信公众号：筱烨视点

注册时间：2014-02-15

  • 博文量
    412
  • 访问量
    1104608

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593