环境介绍:
系统: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环境下且不能用的。