ORA-01114: IO error writing block to file – returns wrong file number – can’t find file

  • Published on Jul 26 2012
  • # 6,421
  • oracle

I was notifed by developer team that they get a whole lot of ORA-01114 errors.

********_ora_17587.trc:ORA-01114: IO error writing block to file 201 (block # 96000)
********_ora_17587.trc:ORA-01114: IO error writing block to file 201 (block # 96000)
********_ora_17587.trc:2012-07-26 <span id="more-776"></span>19:02:32.484306*:9C82F5B9:sql_mon_query:keswx.c@3633:keswxWriteEndInfoToStream(): done writing error info: code=1114 fac=ORA msg=ORA-01114: IO error writing block to file 201 (block # 96000)
********_ora_17587.trc:ORA-01114: IO error writing block to file 201 (block # 96000)
********_ora_17587.trc:ORA-01114: IO error writing block to file 201 (block # 96000)
********_ora_17587.trc:ORA-01114: IO error writing block to file 201 (block # 96000)
********_ora_17587.trc:2012-07-26 

Of course the first thing to do is find the file `data | temp _file` involved but oddly I get ‘no rows returned’ when querying dba_data_files & dba_temp_files ;

SQL> select file_id, file_name from dba_data_files where file_id=201
  2  union 
  3  select file_id, file_name from dba_temp_files where file_id=201
  4  /

no rows selected

¿ qué ?

What’s that – no file with file_id 201 ??? Then why is Oracle error referring to file 201?

Luckily I found this excellent article which in my own words says that:

Whenever the file mentioned in errors like ‘ORA-01114’ is a tempfile the file id is `file_id` + `the value of the DB_FILES init parameter`.

Or;

IF file_id > DB_FILE THEN
    file_type := 'tempfile';
    file_id := DB_FILES - file_id ;

In our database, with DB_FILES set to 200, I need to substract 200 from the value in the ORA error message to get the correct file_id…
Makes no sense to me. But works.

My init parameter db_files is set to 200 … Hence the file I need to query is a temp file with file_id 1;

SQL> select file_id, file_name, bytes, maxbytes 
  2  from dba_temp_files 
  3  where file_id = 1
  4  /
 FILE_ID FILE_NAME                                          BYTES   MAXBYTES
-------- --------------------------------------------- ---------- ----------
       1 /m002/oradata/predcdw1/predcdw1temp01.dbf      922746880 1551892480

Further analysys turned out it’s ‘just’ a filled up mountpoint:

(********):/ora/diag/rdbms/********/********/trace>ls -lart /m002/oradata/********/********temp01.dbf
-rw-r----- 1 oracle dba 922755072 Jul 27 09:47 /m002/oradata/********/********temp01.dbf
(********):/ora/diag/rdbms/********/********/trace>df -h /m002/oradata/********/********temp01.dbf
Filesystem            Size  Used Avail Use% Mounted on

/dev/mapper/ahl010vg-lvol9
                      2.0G  1.9G     0 100% /m002/oradata/********
(********):/ora/diag/rdbms/********/********/trace>


Leave a Reply

Helpful? - leave your note below so I can brag

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>