诗檀软件专业数据库修复团队
ORA-01114
oerr ora 1114
01114, 00000, "IO error writing block to file %s (block # %s)"
// *Cause: The device on which the file resides is probably offline. If the
// file is a temporary file, then it is also possible that the device
// has run out of space. This could happen because disk space of
// temporary files is not necessarily allocated at file creation time.
// *Action: Restore access to the device or remove unnecessary files to free
// up space.
适用于：
Oracle Database - Enterprise Edition – 版本 11.2.0.3 及以上
本文信息适用于任何平台。
症状
Application Screen 捕捉以下错误
ORA-01114: IO error writing block to file (block # )
原因
其中一个临时表空间文件的临时文件头损坏。
SELECT * FROM V$TEMP_SPACE_HEADER;
TABLESPACE_NAME FILE_ID BYTES_USED BLOCKS_USED BYTES_FREE BLOCKS_FREE RELATIVE_FNO
TEMP 4 -9,641,656,320 -1,176,960 17,157,849,088 2,094,464 3 <<<<<<<< -ve (Negative) Values in BYTES_USED & BLOCKS_USED indicates Tempfile Header Corruption
TEMP 5 7,516,192,768 917,504 0 0 4
TEMP 1 6,262,095,872 764,416 180,355,072 22,016 1
TEMP 3 6,442,450,944 786,432 0 0 2
解决方案
在V$TEMP_SPACE_HEADER中，一些列的一些负值表明临时文件头损坏且当应用尝试访问执行损坏头时，可能在Application Logs/Screen得到错误 " ORA-01114 error writing block to file" 。
解决行动计划：
请drop/删除该临时文件（在本文示例中即：FILE_ID 4 ）并创建新的临时文件来解决问题。（请检查所有节点的SELECT * FROM V$TEMP_SPACE_HEADER；的输出并且如果任何列有-ve的值，drop或删除并创建新的临时文件）
示例/语法：
04:58:23 SQL> ALTER DATABASE TEMPFILE '/oradata3/oradata/DFCCBPRD/temp04.dbf' DROP INCLUDING DATAFILES;
Database altered.
04:58:35 SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/oradata3/oradata/DFCCBPRD/temp04.dbf' SIZE 7G;
Tablespace altered.
04:58:50 SQL> SELECT * FROM V$TEMP_SPACE_HEADER;
TABLESPACE_NAME FILE_ID BYTES_USED BLOCKS_USED BYTES_FREE
------------------------------ ---------- ---------- ----------- ----------
BLOCKS_FREE RELATIVE_FNO
----------- ------------
TEMP 1 6215958528 758784 226492416
27648 1
TEMP 2 1048576 128 7515144192
917376 3
TEMP 3 6442450944 786432 0
0 2
TEMP 5 7516192768 917504 0
0 4
参考
NOTE:467435.1 - V$Temp_space_header.Bytes_used=-133112528896