ORA-01114: IO error writing block to file

[enrique_busi3]

Hi Experts, I have a big problem in a BW system.

When run querys or background process occurs the following error:

Database error 1114 at FET

> ORA-01114: IO error writing block to file 257 (block #

> 582853)#ORA-27063: number of bytes read/written is

> incorrect#IBM AIX RISC System/6000 Error: 28: No space left

> on device#Additional information: -1#Additional information:

> 122880#ORA-01114: IO error writing block to file 257 (block

> 582853)#ORA-27063: number of bytes read/written is

> incorrect#IBM AIX RISC System/6000 Error: 28: No space left

> on device#Additional information: -1#Additional information:

> 122880

Please help me.

Regards.

Enrique.

[former_member204746 · Accepted solution]

disk might be full. check that at OS level.

search SAP note with keyword ORA-01114.

you will find only 6 notes, read them carefully.

583861 UNIX: Errors due to Oracle executable

546006 Problems with Oracle due to operating system errors

633914 Error when executing DBMS_STATS

892294 Enhanced support for non-ABAP database in BRCONNECT 7.00

659946 FAQ: Temporary tablespaces

483856 Description of the alerts for Oracle database monitoring

[enrique_busi3 · Accepted solution]

Hi Eric, the disk is not full, I will watch the notes.

Thank you.

Enrique.

[stefan_koehler · Accepted solution]

Hello Enrique,

as you mentioned this error happens if you execute queries it might be a problem with temporary files (also the df output maybe not correct, because of sparse files).

Please execute the following queries / commands and post the output here

shell> df 
shell> sqlplus / as sysdba
SQL> set linesize 255
SQL> SELECT FILE_NAME, BYTES, AUTOEXTENSIBLE, MAXBYTES, INCREMENT_BY 
FROM DBA_TEMP_FILES WHERE FILE_ID = 257;

SQL> SELECT FILE_NAME, BYTES, AUTOEXTENSIBLE, MAXBYTES, INCREMENT_BY 
FROM DBA_DATA_FILES WHERE FILE_ID = 257;

Regards

Stefan

[enrique_busi3 · Accepted solution]

Hi Stefan, I execute querys but in both the result is empty for FILE_ID = 257;

All files with AUTOEXTENSIBLE = ON have free space.

Regards.

Enrique.

[stefan_koehler · Accepted solution]

Hello Enrique,

> All files with AUTOEXTENSIBLE = ON have free space.

That's not the point i tried to focus on.

> I execute querys but in both the result is empty for FILE_ID = 257;

What? Then i think you have some DDIC inconsistency in your database. Maybe oracle thinks that a file with the ID 257 should exist, but it doesn't - so the write fails.

I would suggest to open a SAP call for that, so the SAP database team can check this strange behavior directly on your system. I could also try to request some control file dumps, etc. but then we would go beyond the scope of the forum.

Regards

Stefan

[enrique_busi3 · Accepted solution]

Hello Stefan. I put a message in SAP about this problem.

Thank you for response.

Best Regards.

Enrique.

[Former Member · Accepted solution]

Hi,

Are all the data files having status 'Available'?

DB02->tablespaces current sizes->data files/tempfiles

Manoj

[enrique_busi3 · Accepted solution]

Hi Manoj, this is detail of tablespace PSAPTEMP:

Tablespace name PSAPTEMP

Block size(KB) 8

Init. extent(MB) 1,000

Next extent_(MB) 1,000

Min. extents 1

Min. Extent length(MB) 1,000

Status ONLINE

Contents TEMPORARY

Logging NOLOGGING

Forced logging NO

Extent management LOCAL

Allocation type UNIFORM

Plugged in NO

SSM MANUAL

Def. table compression DISABLED

In the SO I see: PSAPTEMP size = 10.240,00 MB

[enrique_busi3 · Accepted solution]

Hello Manoj, sorry I want say.

In db02 the tablespace detail for PSAPTEMP show:

TABLESPACE SIZE(MB) FREE(MB) BLOCK SIZE INIT.EXTEND(MB) NEXT EXTEND(MB)

PSAPTEMP 0,00 0,00 8 1,000 1,000

And really have 10.000 MB in files for this tablespace.

Regards.

Enrique.

[former_member220071]

Hi ,

Try this --

Create a new temp tablespace, make this as the default temp tablespace and drop the old one.

Abhijeet

[enrique_busi3]

Hellow, thank you. The problem was resolved.

The error is in a file in the tablespace. The issue is described in note 548221.

Regards.

Enrique.

[stefan_koehler]

Hello Enrique,

just for your information - this is no error - this is works as designed. I mentioned this in my first reply from Mar 19, 2009 5:45 PM.

Regards

Stefan

[Former Member]

Hi,

It seems that the problems with Oracle is due to operating system errors.

check SAP note 546006.

Regards,

Sachin Rane.