一则数据文件故障处理（ORA-1113）
故障环境：
AIX5.3 ORACLE10.2.0.3 RAC HA
故障现象：
一、启动RAC单节点异常
$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....0A.lsnr application ONLINE OFFLINE
ora.p670a.gsd application ONLINE OFFLINE
ora.p670a.ons application ONLINE OFFLINE
ora.p670a.vip application ONLINE ONLINE p670b
ora....0B.lsnr application ONLINE ONLINE p670b
ora.p670b.gsd application ONLINE ONLINE p670b
ora.p670b.ons application ONLINE ONLINE p670b
ora.p670b.vip application ONLINE ONLINE p670b
ora.zhjport.db application ONLINE OFFLINE
ora....port.cs application ONLINE OFFLINE
ora....rt1.srv application ONLINE OFFLINE
ora....rt2.srv application ONLINE OFFLINE
ora....t1.inst application ONLINE OFFLINE
ora....t2.inst application ONLINE OFFLINE
二、观察实例的ALERT日志，发现存在ORA-1113错误
ALTER DATABASE MOUNT
Wed Dec 6 00:29:55 2010
This instance was first to mount
Setting recovery target incarnation to 2
Wed Dec 6 00:29:59 2010
Successful mount of redo thread 2, with mount id 3375273859
Wed Dec 6 00:29:59 2010
Database mounted in Shared Mode (CLUSTER_DATABASE=TRUE)
Completed: ALTER DATABASE MOUNT
Wed Dec 6 00:29:59 2010
ALTER DATABASE OPEN
This instance was first to open
ORA-1113 signalled during: ALTER DATABASE OPEN...
Wed Dec 6 00:30:00 2010
Shutting down instance (abort)
License high water mark = 1
Instance terminated by USER, pid = 1482880
根据ORA-1113初步判断是OPEN过程中发现，数据文件错误
三、为了精确定位，通过SQLPLUS命令行OPEN库
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 73 needs media recovery
ORA-01110: data file 73: '/home/oracle/database/DEV_DATA'
很显然，数据文件DEV_DATA存在问题
处理过程：
1、确认/home/oracle/database下物理文件存在；
2、确认权限无问题；
3、确认属主无问题；
4、无奈只有OFFLINE该数据文件；
SQL> alter database datafile '/home/oracle/database/DEV_DATA' offline drop;
Database altered.
SQL> alter database open;
Database altered.
SQL>
四、确认
1、通过$ crs_stat -t确认状态正常；
$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora....0A.lsnr application ONLINE OFFLINE
ora.p670a.gsd application ONLINE OFFLINE
ora.p670a.ons application ONLINE OFFLINE
ora.p670a.vip application ONLINE ONLINE p670b
ora....0B.lsnr application ONLINE ONLINE p670b
ora.p670b.gsd application ONLINE ONLINE p670b
ora.p670b.ons application ONLINE ONLINE p670b
ora.p670b.vip application ONLINE ONLINE p670b
ora.zhjport.db application ONLINE ONLINE p670b
ora....port.cs application ONLINE ONLINE p670b
ora....rt1.srv application ONLINE OFFLINE
ora....rt2.srv application ONLINE ONLINE p670b
ora....t1.inst application ONLINE OFFLINE
ora....t2.inst application ONLINE ONLINE p670b
2、通过PLSQL客户端访问正常；
五、后续分析
问题的根源是用户把数据文件建在了本地，而非ORADATAVG上，其他人破坏了本地的文件。
为什么offline参数中加了drop，因为数据库是非归档模式，如果是归档模式，则处理如下：
1.先mount数据库
startup mount
2.脱机坏的数据文件
alter database datafile '/home/oracle/database/DEV_DATA' offline;
如果数据库不是归档模式，则以上操作会报错
3.打开数据库
alter database open;
4.恢复数据文件
recover datafile '/home/oracle/database/DEV_DATA';
5.联机被脱机的数据文件
alter database datafile '/home/oracle/database/DEV_DATA' online;
本文转自zylhsy 51CTO博客，原文链接：http://blog.51cto.com/yunlongzheng/448391，如需转载请自行联系原作者
