袁菲演过爱情公寓:系统表空间IO错误 数据损坏处理一则

同事最近遇到一个数据库问题，说是系统表空间出现坏块，警告日志文件中不断出现如下错误：

[oracle@gdmstest bdump]$ tail -20 alert_mydb.log
Linux Error: 4: Interrupted system call
Additional information: 23710
Wed Oct 25 16:47:44 2006
Errors in file /opt/oracle/admin/mydb/bdump/mydb_smon_19646.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01115: IO error reading block from file 1 (block # 23712)
ORA-01110: data file 1: '/opt/oracle/oradata/mydb/system01.dbf'
ORA-27091: skgfqio: unable to queue I/O
ORA-27072: skgfdisp: I/O error
Linux Error: 4: Interrupted system call
Additional information: 23710
Wed Oct 25 16:47:59 2006
Errors in file /opt/oracle/admin/mydb/bdump/mydb_smon_19646.trc:
ORA-00604: error occurred at recursive SQL level 1
ORA-01115: IO error reading block from file 1 (block # 23712)
ORA-01110: data file 1: '/opt/oracle/oradata/mydb/system01.dbf'
ORA-27091: skgfqio: unable to queue I/O
ORA-27072: skgfdisp: I/O error
Linux Error: 4: Interrupted system call
Additional information: 23710

而通过dbv检查又没有报数据块损坏：

[oracle@gdmstest mydb]$ dbv file=system01.dbf blocksize=8192

 

DBVERIFY: Release 9.2.0.4.0 - Production on Thu Oct 26 11:36:42 2006

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

DBVERIFY - Verification starting : FILE = system01.dbf

DBVERIFY - Verification complete

Total Pages Examined : 23709
Total Pages Processed (Data) : 13000
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 2090
Total Pages Failing (Index): 0
Total Pages Processed (Other): 1377
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 7242
Total Pages Marked Corrupt : 0
Total Pages Influx : 0

我们一起来看看这个问题，首先从错误日志来看，其实这并不是一个数据块损坏的问题：
ORA-01115: IO error reading block from file 1 (block # 23712)

这是个IO错误，数据块不能读取。

而DBV的提示也只是说检查了23709个数据块，这些数据块没有问题，而我们真正报错的数据块是23712号数据块，也就是说DBV检查到这个块附近，无法继续读取，进而退出。

而系统表空间远远大于 23709 * 8k / 1024 = 185M。

此时检查系统日志，dmesg日志中有大量的寻址错误，也就是说硬件出现了故障：

[maintain@gdmstest bdump]$ dmesg
: error=0x40 { UncorrectableError }, LBAsect=58847319, high=3, low=8515671, sector=14266880
end_request: I/O error, dev 03:06 (hda), sector 14266880
hda: dma_intr: status=0x51 { DriveReady SeekComplete Error }
hda: dma_intr: error=0x40 { UncorrectableError }, LBAsect=58847319, high=3, low=8515671, sector=14266880
end_request: I/O error, dev 03:06 (hda), sector 14266880
hda: dma_intr: status=0x51 { DriveReady SeekComplete Error }
hda: dma_intr: error=0x40 { UncorrectableError }, LBAsect=58847319, high=3, low=8515671, sector=14266880
end_request: I/O error, dev 03:06 (hda), sector 14266880
hda: dma_intr: status=0x51 { DriveReady SeekComplete Error }

至此问题被定位。

如果我们尝试cp系统表空间文件，同样会收到硬件的错误提示信息:

[oracle@gdmstest mydb]$ cp system01.dbf system01.dbf.bk
cp: 正在读入‘system01.dbf’: 输入/输出错误
[oracle@gdmstest mydb]$ ll
总用量 2173060
....
-rw-r----- 1 oracle dba 524296192 10月 25 16:49 system01.dbf
-rw-r----- 1 oracle dba 194236416 10月 25 17:00 system01.dbf.bk
...............

只能复制194236416 Bytes，也就是 194236416 / 8192 = 23710.5，同样是读到23709个数据块左右，硬件的损坏就要通过系统的其它手段去解决了。