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

女爷i 2022-06-13 03:44 174阅读 0赞

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

## 一、数据恢复顾问特性 ##

特性描述

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

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

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

## 二、数据故障的情形 ##

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

## 三、数据恢复顾问流程 ##

如下图：  
![这里写图片描述][SouthEast]

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

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

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

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

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

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

### 1、RMAN命令 ###

*  LIST FAILURE (列出之前执行的故障评估 )

*  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

[SouthEast]: /images/20220613/45084a23ce8141d3ac393054387bcbaa.png