一次异常处理

2007/3/7 14点时发现公司wms db出现异常, 异常现象如下:

[@more@]

2007/3/7 14点时发现公司wms db出现异常, 异常现象如下:

1.有一个数据文件users06.dbf无法读取，报ora-00376错误。如下：

ORA-12096: "WMS"."SYS_PERSON" 上的实体化视图日志中存在错误

ORA-00376: 此时无法读取档 11

ORA-01110: 数据文件 11: 'I:ORADATAWMSDBUSERS06.DBF

2.从v$datafile查看该档状态，一直处于recover，而从dba_data_files查看，bytes等几个字段都为空.

该档案大小为4G,应该没有超过所谓的os对文件大小的限值.用dbv检验datafile,ok,没有坏块.

处理过程:

1. 直接recover datafile, 报如下错误：

SQL> recover datafile 11;

ORA-00283: 恢复会话因错误而取消

ORA-01115: 从档 11 读取块时出现 IO 错误 (块 # 452896)

ORA-01110: 数据文件 11: 'I:ORADATAWMSDBUSERS06.DBF'

ORA-27069: skgfdisp: 尝试在档范围外执行 I/O

OSD-04026: 传递的参数无效. (OS 452896)。

关闭后启动到mount 状态，再recover，还是报同样错误！

但是DB可以正常关闭和启动，不报任何错误！

2.在大约16点半的时候,尝试使用RMAN备份进行restore及recover,无法完成.最后的报错信息如下:

RMAN-03002: failure of recover command at 03/07/2007 17:11:31

ORA-00283: recovery session canceled due to errors

RMAN-11003: failure during parse/execution of SQL statement: alter database reco

ver logfile 'H:ORACLEORADATAWMSDBARCH1_4564.DBF'

ORA-00283: 恢复会话因错误而取消

ORA-01115: 从档 11 读取块时出现 IO 错误 (块 # 517897)

ORA-01110: 数据文件 11: 'I:ORADATAWMSDBUSERS06.DBF'

ORA-27069: skgfdisp: 尝试在档范围外执行 I/O

OSD-04026:传递的参数无效.

3.对archivelog档案进行recover验证,没有问题.

4.这时已经将近18点,电话联系,寻求支持.

5.19点左右与公司张XX联系上,检查了一下,找不到原因, 临时调用一台server(10.192.29.180),安装oracle,建立备用DB.

6.20点的时候,王XX也赶到现场.请他帮忙将3/6日的一次备份imp到临时server上.

7.22点左右,这时在正式DB上新建一个instance test,复制datafile到一个临时目录(i:datafile_bak)下,新建控制文件及pfile,使用隐含参数_allow_resetlogs_corruption=true,

打开新建instance(alter database open resetlogs).看datafile是否可用.输入指令后,一直没有反应,用另外的session查看DB状态,为Open.

查看alert log,也是停留在open的状态.然后select,update有用到USERS06.DBF的某个table,可以正常读取.查看datafile状态,均为online,说明没有问题.当时心里还一阵高兴.

准备将资料exp出来再imp回去.却发现一直处于exp第一个table状态,不再往下走. 这时已经又过去几个钟头.

8.到凌晨4点多,仍旧无法exp出来.没有办法,只好将3/7日 18点exp出来的dmp档往备用DB(10.192.29.180)上导入, 目前资料3/6日下午6点以前的已经ok.

之后的还在导入,由于重复资料很多,到目前还没有导完,可能有一小小部分的丢失,还得请熊诚帮忙检查一下.

现请熊诚将AP连结此DB(或请公司那边将2者IP互换),原DB(29.32)用作进一步检查.待查明问题后再迁回去.初步估计是硬件故障,文件虽然没有损坏,但是不能读取.也不能恢复.

最后发现是遇到oracle的bug，metalink上描述如下：

Subject: DB <9.2.0.3 Cannot AUTOEXTEND to 4Gb boundary in NT

Doc ID:

Note:317174.1 Type: PROBLEM

Last Revision Date: 02-AUG-2005 Status: MODERATED

In this Document

Symptoms

Cause

Solution

References

________________________________________

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1.0

Microsoft Windows (32-bit)

Symptoms

When a datafile has been configured such that it will autoextend onto a 4GB boundary, any attempt to autoextend the file causes the file the database to crash (when in noarchive log mode). If the database was in archive log mode,will be marked offline recover and access to the database continues.

The following error appears in the alert.log :

.

KCF: write/open error block=0x32002 online=1

file=11 'E:ORACLEORA92BORGMAXIMO.ORA'

error=27069 txt: 'OSD-4026: Invalid parameter passed. (OS-204802)'

Automatic datafile offline due to write error on

file 11: 'E:ORACLEORA92BORGMAXIMO.ORA'

.

Any attempt to online / recover this file results in :

.

ORA-283: recovery session canceled due to errors

ORA-1115: IO error reading block from file 11 (block # 204802)

ORA-1110: data file 11: 'E:ORACLEORA92BORGMAXIMO.ORA'

ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file

OSD-4026: Invalid parameter passed. (OS-204802)

Cause

Its a Bug specific to NT platform only. Cannot AUTOEXTEND to 4Gb boundary

Solution

This bug is fixed in 9203. Upgrading the database to the latest relase followed by a recovery will solve this problem.

Here are the steps:

1.Do a clean shutdown of the database

2. Upgrade the database to the latest version ( greater than 9.2.0.3) as this bug is fixed in 9203.

If the database version is below 9i Rel 2(9.2.0.1) then first upgrade to 9iRel2 and then apply the latest available patchset.

Kindly check metalink for the latest patchset available

3.Mount the database

4.Do a COMPLTE recovery of the dataabse

5.Then start the database in migrate mode and upgrade the database. Refer Note 159657.1: Complete Upgrade Checklist for Manual Upgrades from 8.X / 9.0.1 to Oracle9i

6.Shutdown the database and then restart in normal mode.

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP

References

Bug 1668488 - Unable To Recover Datafile That Has Auto Extended Onto A 4gb Boundary

Errors

ORA-1115 IO error reading block from file block #

ORA-1110 datafile :

ORA-283 recovery session canceled due to errors

OSD-4026 Invalid parameter passed.

ORA-27069 { Message text depends on version }

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/7567263/viewspace-998605/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/7567263/viewspace-998605/