Thursday, January 29, 2015

ORA-01114 Not Recorded Correctly in Alert Log

A user reported an ORA-01114 and an ORA-27069 in a 3rd party application running against an Oracle 11.1 database:

ERROR 28/01/2015 10:32:37  INF2 LKPDP_10:READER_1_1  RR_4035    SQL Error [
ORA-01114: IO error writing block to file 201 (block # 524367)
ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 524367
Additional information: 7
Additional information: 524289
ORA-01114: IO error writing block to file 201 (block # 524367)
ORA-27069: attempt to do I/O beyond the range of the file
Additional information: 524367
Additional information: 7
Additional information: 524289

I searched the database’s alert log but could not find either of the errors. I asked the user when this had happened and looked at the corresponding place in the alert log. Then I noticed that the ORA-01114 had been recorded without the leading zero:

Wed Jan 28 10:32:51 2015
ORA-1114 : opiodr aborting process unknown ospid (23531_1)
 
… but there was still no sign of the ORA-27069.

3 comments:

Anonymous said...

Sound like a temp file was being written to that was on a filesystem and is created in oracle as a SPARSE file. What is file_id 201 actually called?

Andrew Reid said...

Yes, it was a temporary file. You have obviously seen something similar before but for those who haven't, Oracle starts numbering temporary files from the value of the db_files initialisation parameter + 1.

Anonymous said...

Check the size of filesystem vs. the size of all the temp files on it. Shrink the tempfiles down to the real size of the filesystem. Oracle gets this stuff right on ASM not on filesystems?!?