11gR2 continued it surprises: Have you ever seen that data file I/O error cause Instance crash ? 11.2.0.2 does it…

Uh!!,

Yes should start with that Uh.. only. Let get back to one more post.

One of my databases having I/O error, actually its a VM Linux machine with 11.2.0.2, I have removed the Virtual disk forcibly, (renamed) which is having one datafile. Actually, want to test this new hidden parameter “_datafile_write_errors_crash_instance”, which exactly crashed my instances as expected, so thought of writing this post to make you all aware of it.

See below excerpt of my alert log, after simulating the virtual disk failure

Errors in file /u01/app/oracle/admin/test/bdump/test_dbw0_8888.trc:
ORA-01114: IO error writing block to file 6 (block # 4722)
ORA-01110: data file 6: '/u01/test/oradata/users01.dbf'
ORA-27072: skgfdisp: I/O error
Linux Error: 5: Input/output error
Additional information: 3721
DBW0: terminating instance due to error 1243
Instance terminated by DBW0, pid = 9246

Before to 11.2.0.2 , when any data file got corrupted,or receives

any I/O error the Oracle kernel used to make that OFFLINE, will not crash the instance, of course non system datafiles only and the database in archive log mode.

Wherein, beginning 11.2.0.2 , Oracle gives that control to DBA whether and either you want to control this behavior,  with this parameter _datafile_write_errors_crash_instance set to true (default true), database will crash automatically as contradicts to normal behavior making your datafile online, Uh” serious implications according to my knowledge, not at all suitable to majority of customers or applications.

Does not sound’s to me very reasonable becoz,

• We may have something running/transacting in database leaving that datafile apart.
• We are not keen on that datafile.
• We do not want a crash of entire database, (where multiple applications using the same database)
• Crash means abort, Data integrity or the Business data may stake with this sudden crash

So

be aware ,

as this parameter

_datafile_write_errors_crash_instance

default is true, you may need to keep this item in your post upgrade check list where you may/need want to

make it false. Oracle’s version on this parameter:-

This fix introduces a notable change in behaviour in that rom 11.2.0.2 onwards an I/O write error to a datafile will now crash the instance.

Before this fix I/O errors to datafiles not in the system tablespace offline the respective datafiles when the database is in archivelog mode.  This behavior is not always desirable. Some customers would prefer that the instance crash due to a datafile write error.

This fix introduces a new hidden parameter to control if the instance should crash on a write error or not: _datafile_write_errors_crash_instance

-Thanks

Geek DBA