oracle数据库数据文件无法读取的处理方法

案例所属系统

山东联通服务开通系统

案例现象

业务人员反映查询数据是报错如下：

ORA-00376: file 91 cannot be read at this time

ORA-01110: data file 91: '/dev/rlv_fwkt_015'

insert into tin_autohlr_hw_his

案例平台环境

AIX 6100-03，ORACLE 10gRAC，故障发生前2小时，服务开通数据库备用实例因NTP 服务问题被强行重启，并导致存储发生锁盘现象，但已处理完毕。

主机结构

IBM P570主机2台，主机刚刚重启，运行状态正常

应用部署

数据库服务器

案例分析过程

检查数据库状态

SQL> select instance_name,status,startup_time from v$instance;

INSTANCE_NAME STATUS STARTUP_TIME

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

fwkt1 OPEN 2014/03/01 00:03:07

检查报错数据文件所在的表空间

SQL> select file_name,file_id,status,online_status from dba_data_files where file_name='/dev/rlv_fwkt_015';

FILE_NAME FILE_ID STATUS ONLINE_

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

/dev/rlv_fwkt_015 91 A V AILABLE RECOVER

SQL> select * from v$recover_file;

FILE# ONLINE ONLINE_ ERROR CHANGE# TIME

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

91 OFFLINE OFFLINE 1.3723E+13 2014/02/28 21:38:25

可以看出该数据文件需要恢复，但到目前为止仍无法判断该文件错误是磁盘物理损坏还是逻辑损坏。

下面是确认该数据文件中所包含的segment有哪些，检查如下：

SQL> select segment_name,segment_type from dba_segments where header_file=91;;

SEGMENT_NAME SEGMENT_TYPE

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

BELL_HIS_NUM_P INDEX PARTITION

NX_HIS_ORDER_P INDEX PARTITION

NX_HIS_ORDER_P INDEX PARTITION

NX_HIS_ORDER_P INDEX PARTITION

NX_HIS_ORDER_P INDEX PARTITION

NX_HIS_ORDER_P INDEX PARTITION

NX_HIS_STATE_P INDEX PARTITION

NX_HIS_STATE_P INDEX PARTITION

8 rows selected.

可以看出全部都是索引，结合业务反馈的情况，不加where条件时，查询正常，一旦加上条件就报错，到这里基本可以判定问题所在了。

剩下的就是确定问题的处理方案了。

经现场维护人员与原厂工程师讨论，确定了两种方法：

方法一：因故障刚发生不久，且数据库归档日志仍旧存在，可以通过直接recover数据文件的方法来恢复报错的数据文件，命令如下：

Recover datafile 91;

方法二：重建报错的数据文件上的索引分区到其它表空间下，命令如下：

ALTER INDEX indexname REBUILD PARTITION partition_name TABLESPACE newtablespacename;

处理过程：

对数据文件进行恢复

SQL> recover datafile 91;

ORA-00279: change 13722992083393 generated at 02/28/2014 21:38:25 needed for thread 1 ORA-00289: suggestion : /archlog/fwkt_1_61347_740409054.arc

ORA-00280: change 13722992083393 for thread 1 is in sequence #61347

Specify log: {=suggested | filename | AUTO | CANCEL}

auto

ORA-00279: change 13722992083393 generated at 02/28/2014 20:42:58 needed for thread 2 ORA-00289: suggestion : /archlog/fwkt_2_25771_740409054.arc

ORA-00280: change 13722992083393 for thread 2 is in sequence #25771

ORA-00279: change 13722992084998 generated at 02/28/2014 21:38:27 needed for thread 2 ORA-00289: suggestion : /archlog/fwkt_2_25772_740409054.arc

ORA-00280: change 13722992084998 for thread 2 is in sequence #25772

ORA-00278: log file '/archlog/fwkt_2_25771_740409054.arc' no longer needed for this recovery

ORA-00279: change 13722992486561 generated at 02/28/2014 21:56:06 needed for thread 1 ORA-00289: suggestion : /archlog/fwkt_1_61348_740409054.arc

ORA-00280: change 13722992486561 for thread 1 is in sequence #61348

ORA-00278: log file '/archlog/fwkt_1_61347_740409054.arc' no longer needed for this recovery

ORA-00279: change 13722992877535 generated at 03/01/2014 00:03:40 needed for thread 1 ORA-00289: suggestion : /archlog/fwkt_1_61349_740409054.arc

ORA-00280: change 13722992877535 for thread 1 is in sequence #61349

ORA-00278: log file '/archlog/fwkt_1_61348_740409054.arc' no longer needed for this recovery

Log applied.

Media recovery complete.

对数据文件进行online操作

SQL> alter database datafile 91 online;

再次查询数据文件状态

SQL> select FILE_NAME,FILE_ID,TABLESPACE_NAME,STA TUS,ONLINE_STATUS from dba_data_files;

FILE_NAME FILE_ID TABLESPACE_NAME STATUS ONLINE_

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

/dev/rlv_fwkt_015 91 DA TAIDXHIS A V AILABLE online

SQL> select * from v$recover_file;

no rows selected

对数据文件/dev/rlv_fwkt_017上处理的坏块进行处理：

查询出现的坏块所在的segment以及分区号

SQL> SELECT /*+ rule */ segment_name, segment_type, relative_fno,partition_name FROM dba_extents WHERE file_id = &file_id AND &block BETWEEN block_id AND block_id + blocks - 1;

Enter value for file_id: 93

Enter value for block: 1136391

old 1: SELECT /*+ rule */ segment_name, segment_type, relative_fno,partition_name FROM dba_extents WHERE file_id = &file_id AND &block BETWEEN block_id AND block_id + blocks - 1

new 1: SELECT /*+ rule */ segment_name, segment_type, relative_fno,partition_name FROM dba_extents WHERE file_id = 93 AND 1136391 BETWEEN block_id AND block_id + blocks - 1

SEGMENT_NAME SEGMENT_TYPE RELATIVE_FNO PARTITION_NAME

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

HW_HIS_NUM_P INDEX PARTITION 93 P9

坏块所在的segment为索引，对该索引的分区进行重建

SQL> alter index "INTERFACE"."HW_HIS_NUM_P" rebuild partition "P9" online nologging ; 应用重新测试后问题解决

至此，故障解决。