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;
OR
You can use the RMAN LIST/ADVICE/REPAIR FAILURE. Read the following post to learn more details about RMAN Advisor.

 

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

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

 

About Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress [email protected].-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  [email protected] a mail atabilirsiniz.

Leave a Reply

Your email address will not be published. Required fields are marked *