Oracle数据恢复顾问(Data Recovery Advisor)

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了Oracle数据恢复顾问(Data Recovery Advisor)，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

Oracle数据恢复顾问用于当数据发生错误或故障时，进行自动收集数据故障信息，并生成恢复脚本，用于完成数据恢复。数据恢复顾问也可以主动检查故障。 在这种模式下，它可以在数据库进程发现数据损坏并发出错误之前进行潜在的检测并分析数据故障。数据故障可能非常严重。 例如，如果您当前的日志文件丢失，则无法启动你的数据库。 一些数据故障（如数据文件中的块损坏）不是灾难性的他们不会将数据库关闭或阻止您启动Oracle实例。 数据恢复顾问处理这两种情况：当您无法启动数据库时（因为某些情况）所需的数据库文件丢失，不一致或损坏）以及文件损坏时的数据库文件在运行时发现。

一、数据恢复顾问特性

特性描述

   - 快速检测，分析和修复故障
   - 最大限度地减少用户的中断
   - 减少停机和运行时故障

支持的用户接口
   - EM GUI界面
   - RMAN命令行

支持的数据库配置：
   - 单实例
   - 不是RAC
   - 支持故障转移到待机状态，但不能分析和修复备用数据库

二、数据故障的情形

不可访问的组件，例如：
   - 在操作系统级丢失数据文件
   - 访问权限不正确
   - 离线表空间等等
物理损坏，如块校验故障或无效的块头字段值
逻辑损坏，如不一致的字典，损坏的行，损坏的索引条目或失败事务
不一致，如控制文件过旧或过新于数据文件和联机重做日志
I/O 失败，例如对打开的文件数量的限制超出，通道无法访问，网络或I / O错误

三、数据恢复顾问流程

如下图：

Oracle Database 11g中的自动诊断工作流程如下。 使用数据恢复顾问，您只需要启动一个建议和恢复。
1、健康监视器会自动执行检查并记录失败及其症状，将其作为一个“findings”，存放到自动诊断存储库（ADR）。

2、数据恢复顾问将调查结果整合到失败中。 并列出之前执行故障严重性评估级别

3、当您要求维修建议失败时，数据恢复顾问将失败映射到自动和手动修复选项，检查基本可行性，并提供修复建议。

4、您可以选择手动执行修复或请求Data Recovery Advisor进行修复(OEM)

5、对于数据监测，首选的为“反应性”健康检查及数据恢复恢复顾问，Oracle也建议使用VALIDATE命令作为“主动”检查。

四、数据恢复顾问RMAN接口命令及相关视图

1、RMAN命令

  • LIST FAILURE (列出之前执行的故障评估 )
  1. LIST FAILURE [ ALL | CRITICAL | HIGH | LOW | CLOSED | failnum[,failnum,…] ] [ EXCLUDE FAILURE failnum[,…] ] [ DETAIL ]
  • ADVISE FAILURE (显示推荐的恢复选项) 
  1. ADVISE FAILURE
  2. [ ALL | CRITICAL | HIGH | LOW | failnum[,…] ]
  3. [ EXCLUDE FAILURE failnum [,…] ]
  • REPAIR FAILURE (修复和关闭故障，在同一个RMAN会话中的ADVISE之后） 
  1. REPAIR FAILURE
  2. [USING ADVISE OPTION integer]
  3. [ { {NOPROMPT | PREVIEW}}...]
  • CHANGE FAILURE (更改或关闭一个或多个故障)
  1. CHANGE FAILURE
  2. { ALL | CRITICAL | HIGH | LOW | failnum[,…] }
  3. [ EXCLUDE FAILURE failnum[,…] ]
  4. { PRIORITY {CRITICAL | HIGH | LOW} |
  5. CLOSE } – change status of the failure(s) to closed
  6. [ NOPROMPT ] – do not ask user for a confirmation

2、相关视图

V$IR_FAILURE：列出所有故障，包括已关闭故障(等同于list failure命令获得的结果)
V$IR_MANUAL_CHECKLIST：列出手动修复的建议(等同于advise failure命令的结果)
V$IR_REPAIR：恢复清单(等同于advise failure命令的结果 )
V$IR_FAILURE_SET：失败的交叉引用建议标识符

五、基于RMAN演示数据恢复顾问

1、数据文件丢失恢复

  1. sql> select * from v$version where rownum=1;
  2.  
  3. BANNER --------------------------------------------------------------------------------
  4. Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
  5.  
  6. sql> select open_mode,log_mode from v$database;
  7.  
  8. OPEN_MODE LOG_MODE
  9. -------------------- ------------
  10. READ WRITE           ARCHIVELOG
  11.  
  12. sql> select 'Leshami' Author,'http://blog.csdn.net/leshami' Blog, 2 '645746311' QQ from dual;
  13.  
  14. AUTHOR  BLOG                         QQ
  15. ------- ---------------------------- ---------
  16. Leshami http://blog.csdn.net/leshami 645746311
  17.  
  18. RMAN> backup database plus archivelog;             
  19.  
  20. List of Backups                                                                          
  21. ===============                                                                          
  22. Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag               
  23. ------- -- -- - ----------- --------------- ------- ------- ---------- ---               
  24. 9       B  A  A DISK        26-JUN-17       1       1       NO         TAG20170626T112044
  25. 10      B  F  A DISK        26-JUN-17       1       1       NO         TAG20170626T112048
  26. 11      B  A  A DISK        26-JUN-17       1       1       NO         TAG20170626T112245
  27. 12      B  F  A DISK        26-JUN-17       1       1       NO         TAG20170626T112247
  28.  
  29. sql> conn scott/tiger;     
  30.  
  31. sql> create table tb_obj as select * from all_objects;
  32.  
  33. sql> select table_name,tablespace_name from user_tables where table_name='TB_OBJ';
  34.  
  35. TABLE_NAME TABLESPACE_NAME
  36. ------------------------------ ------------------------------
  37. TB_OBJ USERS sql> select count(*) from tb_obj;  COUNT(*) ----------
  38.  72907
  39.  
  40. sql> ho rm -rf /app/oracle/ora11g/oradata/ora11g/users01.dbf
  41.  
  42. $ tail -fn 50 /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/trace/alert_ora11g.log Mon Jun 26 11:34:44 2017
  43. ALTER SYSTEM: Flushing buffer cache
  44. Mon Jun 26 11:36:58 2017
  45. Errors in file /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_21094.trc:
  46. ORA-01116: error in opening database file 4
  47. ORA-01110: data file 4: '/app/oracle/ora11g/oradata/ora11g/users01.dbf'
  48. ORA-27041: unable to open file
  49. Linux-x86_64 Error: 2: No such file or directory Additional information: 3 Mon Jun 26 11:37:01 2017 Checker run found 1 new persistent data failures RMAN> list failure;  --该命令执行后列出了failureID号，以及优先级别，具体信息等
  50.  
  51. List of Database Failures =========================
  52.  
  53. Failure ID Priority Status    Time Detected Summary
  54. ---------- -------- --------- ------------- -------
  55. 107442     HIGH     OPEN      26-JUN-17     One or more non-system datafiles are missing
  56.  
  57. RMAN> advise failure;  --该命令执行后会针对list failure后的故障生成恢复脚本
  58.  
  59. List of Database Failures =========================
  60.  
  61. Failure ID Priority Status    Time Detected Summary
  62. ---------- -------- --------- ------------- -------
  63. 107442     HIGH     OPEN      26-JUN-17     One or more non-system datafiles are missing
  64.  
  65. analyzing automatic repair options; this may take some time
  66. using channel ORA_DISK_1
  67. analyzing automatic repair options complete
  68.  
  69. Mandatory Manual Actions ========================
  70. no manual actions available
  71.  
  72. Optional Manual Actions =======================
  73. 1. If file /app/oracle/ora11g/oradata/ora11g/users01.dbf was unintentionally renamed or moved,restore it
  74.  
  75. Automated Repair Options ========================
  76. Option Repair Description
  77. ------ ------------------
  78. 1      Restore and recover datafile 4        
  79.  Strategy: The repair includes complete media recovery with no data loss
  80.  Repair script: /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/hm/reco_1718882889.hm
  81.  
  82. RMAN> repair failure;     --执行恢复，即执行上一步骤生成的脚本
  83.  
  84. Strategy: The repair includes complete media recovery with no data loss
  85. Repair script: /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/hm/reco_1718882889.hm contents of repair script:
  86.  # restore and recover datafile
  87.  sql 'alter database datafile 4 offline'; #此处是先将数据文件执行offline，然后还原数据文件，恢复数据文件
  88.  restore datafile 4;
  89.  recover datafile 4;
  90.  sql 'alter database datafile 4 online';
  91.  
  92. Do you really want to execute the above repair (enter YES or NO)? yes
  93. executing repair script
  94.  
  95. sql statement: alter database datafile 4 offline
  96.  
  97. Starting restore at 26-JUN-17
  98. using channel ORA_DISK_1
  99.  
  100. channel ORA_DISK_1: starting datafile backup set restore
  101. channel ORA_DISK_1: specifying datafile(s) to restore from backup set
  102. channel ORA_DISK_1: restoring datafile 00004 to /app/oracle/ora11g/oradata/ora11g/users01.dbf
  103. channel ORA_DISK_1: reading from backup piece /app/oracle/ora11g/fast_recovery_area/ORA11G
  104.  /backupset/2017_06_26/o1_mf_nnndf_TAG20170626T112048_do0zdnkj_.bkp
  105. channel ORA_DISK_1: piece handle=/app/oracle/ora11g/fast_recovery_area/ORA11G
  106.  /backupset/2017_06_26/o1_mf_nnndf_TAG20170626T112048_do0zdnkj_.bkp tag=TAG20170626T112048
  107. channel ORA_DISK_1: restored backup piece 1
  108. channel ORA_DISK_1: restore complete,elapsed time: 00:00:07
  109. Finished restore at 26-JUN-17
  110.  
  111. Starting recover at 26-JUN-17
  112. using channel ORA_DISK_1
  113.  
  114. starting media recovery
  115. media recovery complete,elapsed time: 00:00:05
  116.  
  117. Finished recover at 26-JUN-17
  118.  
  119. sql statement: alter database datafile 4 online
  120. repair failure complete
  121.  
  122. sql> select count(*) from tb_obj; --验证成功性  COUNT(*) ----------
  123.  72907

2、联机重做日志文件丢失恢复

  1. sql> delete from tb_obj where owner='SCOTT';
  2.  
  3. sql> commit;
  4.  
  5. sql> select count(*) from tb_obj;
  6.  
  7.   COUNT(*)
  8. ----------
  9.      72899
  10.  
  11. sql> select member from v$logfile;
  12.  
  13. MEMBER
  14. --------------------------------------------------------------------------------
  15. /app/oracle/ora11g/oradata/ora11g/redo03.log
  16. /app/oracle/ora11g/oradata/ora11g/redo02.log
  17. /app/oracle/ora11g/oradata/ora11g/redo01.log
  18.  
  19. sql> ho rm -rf /app/oracle/ora11g/oradata/ora11g/redo*   --删除全部联机日志
  20.  
  21. sql> conn / as sysdba
  22. Connected.
  23. sql> shutdown immediate;
  24. sql> startup          --启动后报错，实例被强制终止
  25. ORACLE instance started.
  26.  
  27. Total System Global Area  730714112 bytes
  28. Fixed Size                  2231952 bytes
  29. Variable Size             532676976 bytes
  30. Database Buffers          188743680 bytes
  31. Redo Buffers                7061504 bytes
  32. Database mounted.
  33. ORA-03113: end-of-file on communication channel
  34. Process ID: 21655
  35. Session ID: 96 Serial number: 3
  36.  
  37. --查看alert的告警日志
  38. Mon Jun 26 11:57:09 2017
  39. ARC3 started with pid=23,OS id=21663 
  40. ARC1: Archival started
  41. ARC2: Archival started
  42. ARC1: Becoming the 'no FAL' ARCH
  43. ARC1: Becoming the 'no SRL' ARCH
  44. ARC2: Becoming the heartbeat ARCH
  45. Errors in file /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/trace/ora11g_lgwr_21606.trc:
  46. ORA-00313: open Failed for members of log group 1 of thread 1
  47. ORA-00312: online log 1 thread 1: '/app/oracle/ora11g/oradata/ora11g/redo01.log'
  48. ORA-27037: unable to obtain file status
  49. Linux-x86_64 Error: 2: No such file or directory
  50. Additional information: 3
  51. Errors in file /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/trace/ora11g_lgwr_21606.trc:
  52. ORA-00313: open Failed for members of log group 1 of thread 1
  53. ORA-00312: online log 1 thread 1: '/app/oracle/ora11g/oradata/ora11g/redo01.log'
  54. ORA-27037: unable to obtain file status
  55. Linux-x86_64 Error: 2: No such file or directory
  56. Additional information: 3
  57. Errors in file /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_21655.trc:
  58. ORA-00313: open Failed for members of log group 1 of thread 
  59. ORA-00312: online log 1 thread 1: '/app/oracle/ora11g/oradata/ora11g/redo01.log'
  60. USER (ospid: 21655): terminating the instance due to error 313
  61. Mon Jun 26 11:57:09 2017
  62. Errors in file /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_21665.trc:
  63. ORA-00313: open Failed for members of log group 1 of thread 1
  64. ORA-00312: online log 1 thread 1: '/app/oracle/ora11g/oradata/ora11g/redo01.log'
  65. ORA-27037: unable to obtain file status
  66. Linux-x86_64 Error: 2: No such file or directory
  67. Additional information: 3
  68. System state dump requested by (instance=1,osid=21655),summary=[abnormal instance termination].
  69. System State dumped to trace file /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/trace/ora11g_diag_21596.trc
  70. Dumping diagnostic data in directory=[cdmp_20170626115709],requested by 
  71.         (instance=1,summary=[abnormal instance termination].  --实例异常终止
  72. Instance terminated by USER,pid = 21655
  73.  
  74. sql> startup mount;       --将数据库启动到mount状态
  75.  
  76. $ rman target /
  77.  
  78. Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jun 26 11:59:55 2017
  79.  
  80. Copyright (c) 1982,2011,Oracle and/or its affiliates.  All rights reserved.
  81.  
  82. connected to target database: ORA11G (DBID=42938845,not open)
  83.  
  84. RMAN> list failure;
  85.  
  86. using target database control file instead of recovery catalog
  87. List of Database Failures
  88. =========================
  89.  
  90. Failure ID Priority Status    Time Detected Summary
  91. ---------- -------- --------- ------------- -------
  92. 107519     CRITICAL OPEN      26-JUN-17     Redo log group 3 is unavailable
  93. 107513     CRITICAL OPEN      26-JUN-17     Redo log group 2 is unavailable
  94. 107507     CRITICAL OPEN      26-JUN-17     Redo log group 1 is unavailable
  95. 107522     HIGH     OPEN      26-JUN-17     Redo log file /app/oracle/ora11g/oradata/ora11g/redo03.log is missing
  96. 107516     HIGH     OPEN      26-JUN-17     Redo log file /app/oracle/ora11g/oradata/ora11g/redo02.log is missing
  97. 107510     HIGH     OPEN      26-JUN-17     Redo log file /app/oracle/ora11g/oradata/ora11g/redo01.log is missing
  98.  
  99. RMAN> advise failure; 
  100.  
  101. List of Database Failures
  102. =========================
  103.  
  104. Failure ID Priority Status    Time Detected Summary
  105. ---------- -------- --------- ------------- -------
  106. 107519     CRITICAL OPEN      26-JUN-17     Redo log group 3 is unavailable
  107. 107513     CRITICAL OPEN      26-JUN-17     Redo log group 2 is unavailable
  108. 107507     CRITICAL OPEN      26-JUN-17     Redo log group 1 is unavailable
  109. 107522     HIGH     OPEN      26-JUN-17     Redo log file /app/oracle/ora11g/oradata/ora11g/redo03.log is missing
  110. 107516     HIGH     OPEN      26-JUN-17     Redo log file /app/oracle/ora11g/oradata/ora11g/redo02.log is missing
  111. 107510     HIGH     OPEN      26-JUN-17     Redo log file /app/oracle/ora11g/oradata/ora11g/redo01.log is missing
  112.  
  113. analyzing automatic repair options; this may take some time
  114. allocated channel: ORA_DISK_1
  115. channel ORA_DISK_1: SID=189 device type=DISK
  116. analyzing automatic repair options complete
  117.  
  118. Mandatory Manual Actions
  119. ========================
  120. no manual actions available
  121.  
  122. Optional Manual Actions
  123. =======================
  124. 1. If file /app/oracle/ora11g/oradata/ora11g/redo03.log was unintentionally renamed or moved,restore it
  125. 2. If file /app/oracle/ora11g/oradata/ora11g/redo02.log was unintentionally renamed or moved,restore it
  126. 3. If file /app/oracle/ora11g/oradata/ora11g/redo01.log was unintentionally renamed or moved,restore it
  127.  
  128. Automated Repair Options
  129. ========================
  130. Option Repair Description
  131. ------ ------------------
  132. 1      Open resetlogs  
  133.   Strategy: The repair includes complete media recovery with no data loss
  134.   Repair script: /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/hm/reco_4276184973.hm
  135.  
  136. RMAN> repair failure;
  137.  
  138. Strategy: The repair includes complete media recovery with no data loss
  139. Repair script: /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/hm/reco_4276184973.hm
  140.  
  141. contents of repair script:
  142.    # recover database until cancel and open resetlogs
  143.    sql 'alter database recover database until cancel';  --使用until cancel方式恢复数据库
  144.    alter database open resetlogs;                                --不完全恢复后，只能基于resetlogs方式打开数据库
  145.  
  146. Do you really want to execute the above repair (enter YES or NO)? yes
  147. executing repair script
  148.  
  149. sql statement: alter database recover database until cancel
  150.  
  151. database opened
  152. repair failure complete
  153.  
  154. sql> conn scott/tiger;
  155. Connected.
  156. sql> select count(*) from tb_obj;
  157.  
  158.   COUNT(*)
  159. ----------
  160.      72899

猜你在找的Oracle相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问