由Bug引起的ORA-01114&ORA-27069错误

.[@more@]

Fri Apr 11 08:48:35 2008
ARC1: Beginning to archive log 3 thread 1 sequence 1044
Creating archive destination LOG_ARCHIVE_DEST_1: 'D:ORACLEORADATAHREMPLOYARCHIVE1_1044.DBF'
Fri Apr 11 08:48:48 2008
ARC1: Completed archiving log 3 thread 1 sequence 1044
Fri Apr 11 09:00:30 2008
ALTER DATABASE DATAFILE 'D:ORACLEORADATAHREMPLOYHR.DBF' RESIZE 4096M
Fri Apr 11 09:00:59 2008
Completed: ALTER DATABASE DATAFILE 'D:ORACLEORADATAHREMPLO
Fri Apr 11 09:01:00 2008
KCF: write/open error block=0x5832d online=1
file=11 D:ORACLEORADATAHREMPLOYHR.DBF
error=27069 txt: 'OSD-04026: Invalid parameter passed. (OS 361261)'
Automatic datafile offline due to write error on
file 11: D:ORACLEORADATAHREMPLOYHR.DBF
Fri Apr 11 09:01:00 2008
Errors in file d:oracleadminhremployudumphremploy_j001_2568.trc:

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

查找到原因是一个bug 。 参考：

最近在执行一个大数据量查询时报如下错误：

ORA-01114: IO error writing block to file 201 (block # 523913)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
OSD-04026: Invalid parameter passed. (OS 523927)
ORA-01114: IO error writing block to file 201 (block # 523913)

经查询file 201是临时表空间的第一个文件，该文件初始大小为2GB，自动扩展，每次扩展640K，当到达4G时出现以上错误。后来又测试了一下，发现在跨越8G时也会出现以上错误：

SQL> select * from v_offtake_profit_inv_national;
ERROR:
ORA-01114: IO error writing block to file 201 (block # 1048178)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
OSD-04026: Invalid parameter passed. (OS 1048192)
ORA-01114: IO error writing block to file 201 (block # 1048178)
ORA-27069: skgfdisp: attempt to do I/O beyond the range of the file
OSD-04026: Invalid parameter passed. (OS 1048192)

但是如果把每次扩展改成10M，则不会出现这个错误。

数据库版本：Oracle 9.2.0.1 Enterprise Edtion

OS：Windows 2003 Enterprise SP1

文件系统：NTFS

在metalink上查找相关资料，发现是Oracle的一个BUG，参考Notes:317174.1，内容如下：

———————————————————————————————————————

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

Applies to:
Oracle Server - Enterprise Edition - Version: 9.2.0.1.0
Microsoft Windows (32-bit)

SymptomsWhen 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=0×32002 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)

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

SolutionThis 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

ReferencesBug 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 }

————————————————————————————————-

解决方法：

1。升级至9203以上

2。临时文件不设置自动扩展，把每个临时文件大小设置成<4GB，通过添加多个临时文件来扩展空间

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

转载于:http://blog.itpub.net/35489/viewspace-1002319/