用oracle的数据恢复顾问（DRA）诊断和恢复数据库

环境介绍：

系统：Linux     版本：centos6.8

数据库：Oracle    版本：11.2.0.1

实验故障：oracle数据库系统文件损坏，或是系统文件所在磁盘损坏。

故障处理：用oracle自带的DRA（Data Recovery Advisor）数据恢复顾问恢复数据。

在恢复之前数据库一定要对数据库做一次全库的完整备份，备份工具RMAN，备份过程不再详细介绍，详见以下rman脚本：

backup device type disk filesperset = 4 tag 'backup%d' database;
backup device type disk filesperset = 4 tag 'backup%d' archivelog all not backed up;
run {
allocate channel oem_backup_disk1 type disk format '/usr/local/oracle/rmanback/%U_%d_%s_%p' maxpiecesize 1000 G;
allocate channel oem_backup_disk2 type disk format '/usr/local/oracle/rmanback/%U_%d_%s_%p' maxpiecesize 1000 G;
backup filesperset = 4 tag 'backup%d' current controlfile;
release channel oem_backup_disk1;
release channel oem_backup_disk2;
}

查看是否已有全库备份的文件rman登录数据库

list backup of database;

如图：

1、  制造故障：将oracle的数据的系统文件删除，删除前先将数据库关闭！如下图：

2、  将oracle数据库的系统文件删除后，启动数据库，可以看到一报错如下图：

 

3、  用rman登录数据库，执行listfailure;这时会反馈相关的错误信息，查看错误信息如下：

[oracle@oracle ~]$ rman target /
Recovery Manager: Release 11.2.0.1.0 - Production on Fri Jul 13 00:37:29 2018
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
connected to target database: ORCL (DBID=1451473727, 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
---------- -------- --------- ------------- -------
1372       CRITICAL OPEN      13-JUL-18     System datafile 1: '/usr/local/oracle/app/oracle/oradata/orcl/system01.dbf' is missing
1202       HIGH     OPEN      13-JUL-18     One or more non-system datafiles are missing
RMAN>

4、  在rman同一窗口执行advise  failure;命令会提示出相关的诊断报告和恢复建议，如下：

RMAN> advise failure;
List of Database Failures
=========================
Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
1372       CRITICAL OPEN      13-JUL-18     System datafile 1: '/usr/local/oracle/app/oracle/oradata/orcl/system01.dbf' is missing
1202       HIGH     OPEN      13-JUL-18     One or more non-system datafiles are missing
analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=10 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=135 device type=DISK
analyzing automatic repair options complete
Mandatory Manual Actions
========================
no manual actions available
Optional Manual Actions
=======================
1. If file /usr/local/oracle/app/oracle/oradata/orcl/system01.dbf was unintentionally renamed or moved, restore it
2. If file /usr/local/oracle/app/oracle/oradata/orcl/sysaux01.dbf was unintentionally renamed or moved, restore it
3. If file /usr/local/oracle/app/oracle/oradata/orcl/undotbs01.dbf was unintentionally renamed or moved, restore it
4. If file /usr/local/oracle/app/oracle/oradata/orcl/undotbs02.dbf was unintentionally renamed or moved, restore it
*************************************************可以看到oracle通过advise failure;命令已经将诊断结果打印出来并给出了相关修复的建议
Automated Repair Options
========================
Option Repair Description
------ ------------------
1      Restore and recover datafile 1; Restore and recover datafile 2; Restore and recover datafile 3; ...
  Strategy: The repair includes complete media recovery with no data loss
  Repair script: /usr/local/oracle/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2397657710.hm

RMAN>

**************************************************相关的诊断建议及修复脚本

/usr/local/oracle/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2397657710.hm

HM修复脚本内容如下：

[root@oracle hm]# less reco_2397657710.hm 
   # restore and recover datafile
   restore datafile 1, 2, 3, 16;
   recover datafile 1, 2, 3, 16;
reco_2397657710.hm (END) 

5、执行修复命令repair  failure;操作步骤如下：

RMAN> repair failure;
Strategy: The repair includes complete media recovery with no data loss
Repair script: /usr/local/oracle/app/oracle/diag/rdbms/orcl/orcl/hm/reco_2397657710.hm
contents of repair script:
   # restore and recover datafile
   restore datafile 1, 2, 3, 16;
   recover datafile 1, 2, 3, 16;
Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script
Starting restore at 13-JUL-18
using channel ORA_DISK_1
using channel ORA_DISK_2
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 00016 to /usr/local/oracle/app/oracle/oradata/orcl/undotbs02.dbf
channel ORA_DISK_1: reading from backup piece /usr/local/oracle/rmanback/1gt7rjhr_1_1
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00001 to /usr/local/oracle/app/oracle/oradata/orcl/system01.dbf
channel ORA_DISK_2: reading from backup piece /usr/local/oracle/rmanback/1ht7rjht_1_1
channel ORA_DISK_1: piece handle=/usr/local/oracle/rmanback/1gt7rjhr_1_1 tag=BACKUP_ORCL_000006_071218094811
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:06
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 00002 to /usr/local/oracle/app/oracle/oradata/orcl/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /usr/local/oracle/rmanback/1it7rjim_1_1
channel ORA_DISK_2: piece handle=/usr/local/oracle/rmanback/1ht7rjht_1_1 tag=BACKUP_ORCL_000006_071218094811
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:01:44
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00003 to /usr/local/oracle/app/oracle/oradata/orcl/undotbs01.dbf
channel ORA_DISK_2: reading from backup piece /usr/local/oracle/rmanback/1jt7rjr7_1_1
channel ORA_DISK_2: piece handle=/usr/local/oracle/rmanback/1jt7rjr7_1_1 tag=BACKUP_ORCL_000006_071218094811
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:00:36
channel ORA_DISK_1: piece handle=/usr/local/oracle/rmanback/1it7rjim_1_1 tag=BACKUP_ORCL_000006_071218094811
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:43
Finished restore at 13-JUL-18
Starting recover at 13-JUL-18
using channel ORA_DISK_1
using channel ORA_DISK_2
starting media recovery
archived log for thread 1 with sequence 148 is already on disk as file /usr/local/oracle/backup/back_%t_%s_%r.dbf1_148_923692993.dbf
archived log for thread 1 with sequence 149 is already on disk as file /usr/local/oracle/backup/back_%t_%s_%r.dbf1_149_923692993.dbf
archived log for thread 1 with sequence 150 is already on disk as file /usr/local/oracle/backup/back_%t_%s_%r.dbf1_150_923692993.dbf
archived log file name=/usr/local/oracle/backup/back_%t_%s_%r.dbf1_148_923692993.dbf thread=1 sequence=148
media recovery complete, elapsed time: 00:00:30
Finished recover at 13-JUL-18
repair failure complete
Do you want to open the database (enter YES or NO)? yes
database opened
RMAN>

6、  查看数据库状态已恢复并且已经打开

SQL> select status from v$instance;

STATUS

------------

OPEN

SQL>

注意：DRA数据恢复顾问只能应用于单实例数据库，在RAC、DataGuard环境下且不能用的。