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 (列出之前执行的故障评估 )
LIST FAILURE
[ ALL | CRITICAL | HIGH | LOW | CLOSED |
failnum[,failnum,…] ]
[ EXCLUDE FAILURE failnum[,failnum,…] ]
[ DETAIL ]
  • ADVISE FAILURE (显示推荐的恢复选项)
ADVISE FAILURE
[ ALL | CRITICAL | HIGH | LOW | failnum[,failnum,…] ]
[ EXCLUDE FAILURE failnum [,failnum,…] ]
  • REPAIR FAILURE (修复和关闭故障，在同一个RMAN会话中的ADVISE之后）
REPAIR FAILURE
[USING ADVISE OPTION integer]
[ { {NOPROMPT | PREVIEW}}...]
  • CHANGE FAILURE (更改或关闭一个或多个故障)
CHANGE FAILURE
{ ALL | CRITICAL | HIGH | LOW | failnum[,failnum,…] }
[ EXCLUDE FAILURE failnum[,failnum,…] ]
{ PRIORITY {CRITICAL | HIGH | LOW} |
CLOSE } – change status of the failure(s) to closed
[ 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、数据文件丢失恢复

SQL> select * from v$version where rownum=1;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

SQL> select open_mode,log_mode from v$database;

OPEN_MODE            LOG_MODE
-------------------- ------------
READ WRITE           ARCHIVELOG

SQL> select 'Leshami' Author,'http://blog.csdn.net/leshami' Blog,
  2  '645746311' QQ from dual;

AUTHOR  BLOG                         QQ
------- ---------------------------- ---------
Leshami http://blog.csdn.net/leshami 645746311

RMAN> backup database plus archivelog;             

List of Backups
===============
Key     TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
9       B  A  A DISK        26-JUN-17       1       1       NO         TAG20170626T112044
10      B  F  A DISK        26-JUN-17       1       1       NO         TAG20170626T112048
11      B  A  A DISK        26-JUN-17       1       1       NO         TAG20170626T112245
12      B  F  A DISK        26-JUN-17       1       1       NO         TAG20170626T112247

SQL> conn scott/tiger;     

SQL> create table tb_obj as select * from all_objects;

SQL> select table_name,tablespace_name from user_tables where table_name='TB_OBJ';

TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------
TB_OBJ                         USERS

SQL> select count(*) from tb_obj;

  COUNT(*)
----------
     72907

SQL> ho rm -rf /app/oracle/ora11g/oradata/ora11g/users01.dbf

$ tail -fn 50 /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/trace/alert_ora11g.log 

Mon Jun 26 11:34:44 2017
ALTER SYSTEM: Flushing buffer cache
Mon Jun 26 11:36:58 2017
Errors in file /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_21094.trc:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: '/app/oracle/ora11g/oradata/ora11g/users01.dbf'
ORA-27041: unable to open file
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号，以及优先级别，具体信息等

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
107442     HIGH     OPEN      26-JUN-17     One or more non-system datafiles are missing

RMAN> advise failure;  --该命令执行后会针对list failure后的故障生成恢复脚本

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
107442     HIGH     OPEN      26-JUN-17     One or more non-system datafiles are missing

analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

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

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 4
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/hm/reco_1718882889.hm

RMAN> repair failure;     --执行恢复，即执行上一步骤生成的脚本

Strategy: The repair includes complete media recovery with no data loss
Repair script: /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/hm/reco_1718882889.hm

contents of repair script:
   # restore and recover datafile
   sql 'alter database datafile 4 offline';    #此处是先将数据文件执行offline，然后还原数据文件，恢复数据文件
   restore datafile 4;
   recover datafile 4;
   sql 'alter database datafile 4 online';

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

sql statement: alter database datafile 4 offline

Starting restore at 26-JUN-17
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /app/oracle/ora11g/oradata/ora11g/users01.dbf
channel ORA_DISK_1: reading from backup piece /app/oracle/ora11g/fast_recovery_area/ORA11G
       /backupset/2017_06_26/o1_mf_nnndf_TAG20170626T112048_do0zdnkj_.bkp
channel ORA_DISK_1: piece handle=/app/oracle/ora11g/fast_recovery_area/ORA11G
       /backupset/2017_06_26/o1_mf_nnndf_TAG20170626T112048_do0zdnkj_.bkp tag=TAG20170626T112048
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 26-JUN-17

Starting recover at 26-JUN-17
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:05

Finished recover at 26-JUN-17

sql statement: alter database datafile 4 online
repair failure complete

SQL> select count(*) from tb_obj;   --验证成功性

  COUNT(*)
----------
     72907

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

SQL> delete from tb_obj where owner='SCOTT';

SQL> commit;

SQL> select count(*) from tb_obj;

  COUNT(*)
----------
     72899

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/app/oracle/ora11g/oradata/ora11g/redo03.log
/app/oracle/ora11g/oradata/ora11g/redo02.log
/app/oracle/ora11g/oradata/ora11g/redo01.log

SQL> ho rm -rf /app/oracle/ora11g/oradata/ora11g/redo*   --删除全部联机日志

SQL> conn / as sysdba
Connected.
SQL> shutdown immediate;
SQL> startup          --启动后报错，实例被强制终止
ORACLE instance started.

Total System Global Area  730714112 bytes
Fixed Size                  2231952 bytes
Variable Size             532676976 bytes
Database Buffers          188743680 bytes
Redo Buffers                7061504 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 21655
Session ID: 96 Serial number: 3

--查看alert的告警日志
Mon Jun 26 11:57:09 2017
ARC3 started with pid=23, OS id=21663
ARC1: Archival started
ARC2: Archival started
ARC1: Becoming the 'no FAL' ARCH
ARC1: Becoming the 'no SRL' ARCH
ARC2: Becoming the heartbeat ARCH
Errors in file /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/trace/ora11g_lgwr_21606.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/app/oracle/ora11g/oradata/ora11g/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/trace/ora11g_lgwr_21606.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/app/oracle/ora11g/oradata/ora11g/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Errors in file /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/trace/ora11g_ora_21655.trc:
ORA-00313: open failed for members of log group 1 of thread
ORA-00312: online log 1 thread 1: '/app/oracle/ora11g/oradata/ora11g/redo01.log'
USER (ospid: 21655): terminating the instance due to error 313
Mon Jun 26 11:57:09 2017
Errors in file /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/trace/ora11g_m000_21665.trc:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/app/oracle/ora11g/oradata/ora11g/redo01.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
System state dump requested by (instance=1, osid=21655), summary=[abnormal instance termination].
System State dumped to trace file /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/trace/ora11g_diag_21596.trc
Dumping diagnostic data in directory=[cdmp_20170626115709], requested by
        (instance=1, osid=21655), summary=[abnormal instance termination].  --实例异常终止
Instance terminated by USER, pid = 21655

SQL> startup mount;       --将数据库启动到mount状态

$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jun 26 11:59:55 2017

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

connected to target database: ORA11G (DBID=42938845, not open)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
107519     CRITICAL OPEN      26-JUN-17     Redo log group 3 is unavailable
107513     CRITICAL OPEN      26-JUN-17     Redo log group 2 is unavailable
107507     CRITICAL OPEN      26-JUN-17     Redo log group 1 is unavailable
107522     HIGH     OPEN      26-JUN-17     Redo log file /app/oracle/ora11g/oradata/ora11g/redo03.log is missing
107516     HIGH     OPEN      26-JUN-17     Redo log file /app/oracle/ora11g/oradata/ora11g/redo02.log is missing
107510     HIGH     OPEN      26-JUN-17     Redo log file /app/oracle/ora11g/oradata/ora11g/redo01.log is missing

RMAN> advise failure; 

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
107519     CRITICAL OPEN      26-JUN-17     Redo log group 3 is unavailable
107513     CRITICAL OPEN      26-JUN-17     Redo log group 2 is unavailable
107507     CRITICAL OPEN      26-JUN-17     Redo log group 1 is unavailable
107522     HIGH     OPEN      26-JUN-17     Redo log file /app/oracle/ora11g/oradata/ora11g/redo03.log is missing
107516     HIGH     OPEN      26-JUN-17     Redo log file /app/oracle/ora11g/oradata/ora11g/redo02.log is missing
107510     HIGH     OPEN      26-JUN-17     Redo log file /app/oracle/ora11g/oradata/ora11g/redo01.log is missing

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=189 device type=DISK
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If file /app/oracle/ora11g/oradata/ora11g/redo03.log was unintentionally renamed or moved, restore it
2. If file /app/oracle/ora11g/oradata/ora11g/redo02.log was unintentionally renamed or moved, restore it
3. If file /app/oracle/ora11g/oradata/ora11g/redo01.log was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Open resetlogs
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/hm/reco_4276184973.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /app/oracle/ora11g/diag/rdbms/ora11g/ora11g/hm/reco_4276184973.hm

contents of repair script:
   # recover database until cancel and open resetlogs
   sql 'alter database recover database until cancel';  --使用until cancel方式恢复数据库
   alter database open resetlogs;                                --不完全恢复后，只能基于resetlogs方式打开数据库

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

sql statement: alter database recover database until cancel

database opened
repair failure complete

SQL> conn scott/tiger;
Connected.
SQL> select count(*) from tb_obj;

  COUNT(*)
----------
     72899
时间： 2024-04-25 17:39:57

Oracle数据恢复顾问(Data Recovery Advisor)的相关文章

Oracle Data Recovery Advisor（DRA) 数据恢复顾问

Oracle Data Recovery Advisor(DRA) 数据恢复顾问 对DBA而言,数据备份.还原是日常工作的基本功.Oracle发展到今天,自动化.智能化是一个重要的发展方向.数据库可以自动的诊断问题故障,并且解决修复错误,可能离我们并不遥远.     数据备份和还原,在Oracle DBA学习过程中,占到了很大的篇幅.从冷备份到热备份,从完全恢复到非完全恢复,这个过程中涉及了很多的技术细节和知识点.一些DBA初始接触这些概念和操作,容易被弄乱.Oracle 11g推出的Data

Oracle data recover Advisor自动恢复的建议

从oracle 11gR1版本开始,oracle提供了数据库数据文件修复的建议方案,从而更好的让用户去选择恢复方法 这就显的越来越智能,只需要我们按照修复建议向导去做即可. 环境 Oracle Linux AS 5.5+oracle 11.2 (归档模式) 1.备份前的情况   SQL> conn fmismain/fmismain Connected. SQL> select count(*) from xtdw; COUNT(*) ----------   29 SQL> selec

Oracle DG（Data Guard）支持异构平台说明

Oracle DG(Data Guard)支持异构平台说明  以下转自:http://blog.csdn.net/tianlesoftware/article/details/7241488 一.说明 OracleData Guard 最简单的配置是主备库的环境都一样,但是在有些情况下需要异构的配置,比如在迁移时为了减少停机时间或者零停机,可能就需要使用异构的DG 配置. 关于Oralce DataGuard 异构平台的搭建,MOS上有2篇文章专门来说明: Data Guard Support

Oracle 10G的Data Pump (Part I)

oracle Oracle 10G的Data Pump (Part I) 作者: Fenng出处: Http://www.DBAnotes.net Oracle 10G的Data Pump技术能够在不同数据库间高速的移动数据库和元数据. 这个技术的基础是两个数据移动工具:Data Pump Export和Data Pump Import. Oracle的Data Pump是通过一个PL/SQL包来实现的:DBMS_DataPump(也叫Data Pump API).Data Pump使用直接路径

VMware Vsphere Data Recovery 完整性检查错误修复

今天上班的时候收到VMware Vsphere Data Recovery 的报告状态邮件,发现备份未成功,完整性检查失败的错误. 解决完整性检查失败的原因是,找出在备份过程中损坏的备份文件然后标记删除,在手动执行完整性检查,通过之后就可以成功进行下次备份了.

Oracle 12C Active Data Guard Far Sync 配置

Active Data Guard Far Sync是Oracle 12c的新功能(也称为Far Sync Standby),Far Sync功能的实现是通过在距离主库(Primary Database)相对较近的地点配置Far Sync实例,主库(Primary Database) 同步(synchronous)传输redo到Far Sync实例,然后Far Sync实例再将redo异步(asynchronous)传输到终端备库(Standby Database).这样既可以保证零数据丢失又可

Oracle数据泵(Data Dump)错误汇集

Oracle数据泵(Data Dump)使用过程当中经常会遇到一些奇奇怪怪的错误案例,下面总结一些自己使用数据泵(Data Dump)过程当中遇到的问题以及解决方法.都是在使用过程中遇到的问题,以后陆续遇到数据泵(Data Dump)的错误案例,都会补充在此篇. 错误案例1: ORA-39065: DISPATCH 中出现意外的主进程异常错误:ORA-44002: 对象名无效 Windows 平台错误提示: Linux平台错误提示 解决方法: [oracle@DB-Server admin]$

Oracle数据泵(Data Dump)使用过程当中经常会遇到一些奇奇怪怪的错误案例_oracle

Oracle数据泵(Data Dump)使用过程当中经常会遇到一些奇奇怪怪的错误案例,下面总结一些自己使用数据泵(Data Dump)过程当中遇到的问题以及解决方法.都是在使用过程中遇到的问题,以后陆续遇到数据泵(Data Dump)的错误案例,都会补充在此篇. 错误案例1: ORA-39065: DISPATCH 中出现意外的主进程异常错误:ORA-44002: 对象名无效 Windows 平台错误提示: Linux平台错误提示 解决方法: [oracle@DB-Server admin]$

Oracle数据恢复: 50TB ASM crash例子

某客户50 TB的ASM发生故障,经过合力拯救,恢复正常,在此简单记录一下!实际上最后发现比我想象中的简单的多.如下是关于该故障的详细描述情况. –db alert log信息 Mon Jul  6 15:17:43 2015 Errors in file /oraclehome/admin/xxxx/udump/xxxx_ora_262606.trc: ORA-27063: number of bytes read/written is incorrect IBM AIX RISC Syste