Oracle 数据库崩溃ORA-63999，ORA-01114，ORA-01110，ORA-27063

你好，

我的数据库崩溃了，因为过度分配临时文件大小 (稀疏文件)。在挂载点，分配了10gb，在数据库级别，临时表空间有2个文件，总大小为10.8gb。

数据库被崩溃，看到错误，报告到下面的警报日志...

Mon Jun 19 15:07:50 2017
KCF: read, write or open error, block=0x8a609 online=1
        file=2 '/dfdr0prod/fdr/o02fdrtemp/temp02.O02PRD2'
        error=27063 txt: 'SVR4 Error: 28: No space left on device
Additional information: -1
Additional information: 16384'
Errors in file /dfdr0prod/fdr/oracle/diag/rdbms/o02fdrtemp/O02PRD2/trace/O02PRD2_dbw1_12685.trc:
Errors in file /dfdr0prod/fdr/oracle/diag/rdbms/o02fdrtemp/O02PRD2/trace/O02PRD2_dbw1_12685.trc:
ORA-63999: data file suffered media failure
ORA-01114: IO error writing block to file 302 (block # 566793)
ORA-01110: data file 302: '/dfdr0prod/fdr/o02fdrtemp/temp02.O02PRD2'
ORA-27063: number of bytes read/written is incorrect
SVR4 Error: 28: No space left on device
Additional information: -1
Additional information: 16384
DBW1 (ospid: 12685): terminating the instance due to error 63999
Mon Jun 19 15:07:50 2017

是否稀疏文件导致终止实例？或者这是 _datafile_write_errors_crash_instance = TRUE (默认) 参数的影响。

谢谢，
拉利

是的，从11.2.0.2开始，所有数据文件写入错误都会导致实例终止。正如您所怀疑的，这是由默认为true的隐藏参数 _ datafile_write_errors_crash_instance控制的。

如MOS注释7691270.8中所述:

This fix introduces a notable change in behaviour in that 
from 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 



With this fix:
 If _datafile_write_errors_crash_instance = TRUE (default) then
  any write to a datafile which fails due to an IO error causes 
  an instance crash. 

 If _datafile_write_errors_crash_instance = FALSE then the behaviour
  reverts to the previous behaviour (before this fix) such that
  a write error to a datafile offlines the file (provided the DB is
  in archivelog mode and the file is not in SYSTEM tablespace in 
  which case the instance is aborted)