系统表空间错误数据损坏处理一则物业

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

[oracle@gdmstest bdump]$ tail -20 alert_g

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_c:

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/f\'

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_c:

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/f\'

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=f 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 = f

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=, high=3, low=, sector=

end_request: I/O error, dev 03:06 (hda), sector

hda: dma_intr: status=0x51 { DriveReady SeekComplete Error }

hda: dma_intr: error=0x40 { UncorrectableError }, LBAsect=, high=3, low=, sector=

end_request: I/O error, dev 03:06 (hda), sector

hda: dma_intr: status=0x51 { DriveReady SeekComplete Error }

hda: dma_intr: error=0x40 { UncorrectableError }, LBAsect=, high=3, low=, sector=

end_request: I/O error, dev 03:06 (hda), sector

hda: dma_intr: status=0x51 { DriveReady SeekComplete Error }至此问题被定位。

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

[oracle@gdmstest mydb]$ cp f

cp: 正在读入‘f’: 输入/输出错误

[oracle@gdmstest mydb]$ ll

总用量

....

-rw-r----- 1 oracle dba 10月 25 16:49 f

-rw-r----- 1 oracle dba 10月 25 17:00

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

查看本文来源