ORA-01115: IO error reading block from file

I got ” ORA-01115: IO error reading block from file ”  error in Oracle database.

ORA-01115: IO error reading block from file

Details of error are as follows.

ORA-01115: IO error reading block from file string (block # string)

Cause: Device on which the file resides is probably offline

Action: Restore access to the device

When trying to use a non-dba group user to connect to database or to query all_tables, then session would fail with the following errors.  



<user>$ id -a
uid=500(test) gid=1(bin) groups=1(bin)

<user>$ sqlplus <user>
SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 1 15:10:36 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter password:
ERROR:
ORA-00604: error occurred at recursive SQL level 1
ORA-01115: IO error reading block from file (block # )
ORA-01110: data file 1: '+DATA/testdb/datafile/system.269.765749853'
ORA-15081: failed to submit an I/O operation to a disk
ORA-00604: error occurred at recursive SQL level 1
ORA-01115: IO error reading block from file (block # )
ORA-01110: data file 1: '+DATA/testdb/datafile/system.269.765749853'
ORA-15081: failed to submit an I/O operation to a disk

Enter user-name:


OR user was able to connect but querying all_tables would fail.



SQL> connect <user>
Enter password:
Connected.
SQL> select table_name from all_tables;
select table_name from all_tables
*
ERROR at line 1:
ORA-01115: IO error reading block from file (block # )
ORA-01110: data file 1: '+DATA/testdb/datafile/system.269.765749853'
ORA-15081: failed to submit an I/O operation to a disk



Everything was ok with the user and group configurations for ASM and DB instances. However, the issue persists.

IO error reading block from file string (block # string)

This ORA-01115 error is related with the Device on which the file resides is probably offline.

The non-dba user is not a member of any DB related groups, e.g. oinstall, asmadmin, asmdba, dba, oper…etc. Thus, connecting to DB should be done using remote connections with a connect string(db link).

When trying to connect locally without a dblink, it requires OS authentication. However, non-dba users do NOT have OS privilege to get proper OS authentication. Thus, the session would fail with IO errors reported in this note.

The solution is using remote connection (connecting with a defined connect string[dblink] in tnsnames.ora file) for non-dba users.

When doing so, there may be another issue that could happen:
By default, the tnsnames.ora created by DBCA or NETCA has restricted privilege for other group.

For example:

-rw-r----- 1 oracle oinstall 337 Oct 28 20:22 tnsnames.ora

This second issue would cause another error:

$ sqlplus <user>@<db1>

SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 1 15:04:05 2011

Copyright (c) 1982, 2010, Oracle. All rights reserved.

Enter password:
ERROR:
ORA-12154: TNS:could not resolve the connect identifier specified

Enter user-name:

After the changing the privilege of tnsnames.ora file, this second issue would be gone. Then the original issue may occur.

Please note that IOs are cached in memory, so when trying to reproduce the issues, please flush the buffer cache with the dba user before each non-dba user connection test.

SQL> alter system flush buffer_cache;

Do you want to learn Oracle Database for Beginners, then read the following articles.

Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )