ORA-63999: data file suffered media failure — Oracle shut down by itself

This was a quite interesting problem — Oracle terminated itself. After checking the alert log, it turned out the disk holding the temporary tablespace data files was full.

ORA-63999: data file suffered media failure
ORA-01114: IO error writing block to file 2050 (block # 322176)
ORA-01110: data file 2050: '/dbase/temp/PROD/temp2.dbf'
ORA-27072: File I/O error
Linux-x86_64 Error: 28: No space left on device
Additional information: 4
Additional information: 322176
Additional information: 4294967295
Errors in file /opt/oracle/diag/rdbms/PROD/trace/PROD_dbw1_21131.trc  (incident=96161):
ORA-63999 [] [] [] [] [] [] [] [] [] [] [] []
.
.
.
2021-07-06T10:00:32.414218-04:00
CKPT (ospid: 21172): terminating the instance due to ORA error 63999
2021-07-06T10:00:32.414570-04:00
Cause - 'Instance is being terminated due to fatal process DBW1 is terminating with error 63999'
2021-07-06T10:00:32.416172-04:00
System state dump requested by (instance=1, osid=21172 (CKPT)), summary=[abnormal instance termination].
2021-07-06T10:00:32.420002-04:00
opiodr aborting process unknown ospid (24115) as a result of ORA-1092
2021-07-06T10:00:32.420865-04:00
Errors in file /opt/oracle/diag/rdbms/PROD/trace/PROD_ora_24115.trc:
ORA-01092: ORACLE instance terminated. Disconnection forced

The temp data file was created with a max size 32G with auto extensiable and the temporary tablespace is the default temp tablespace for the database.

SQL> select * from DATABASE_PROPERTIES WHERE PROPERTY_NAME = 'DEFAULT_TEMP_TABLESPACE';

PROPERTY_NAME                 PROPERTY_VALUE      DESCRIPTION
----------------------------  ------------------ ------------------------------------------
DEFAULT_TEMP_TABLESPACE       TEMP                Name of default temporary tablespace

However, even with autoextend off, as Oracle states, “On some operating systems, the database does not allocate space for the temp file until the temp file blocks are actually accessed. This delay in space allocation results in faster creation and resizing of temp files, but it requires that sufficient disk space is available when the temp files are later used.”

As the database activities for sorting increased, the actual size of the temp data files on OS also increased and caused the disk full. Oracle had to terminate itself because of this.

This behaviour — an I/O write error to a datafile will crash the instance, has been introduced since 11gR2. (see Bug 7691270 – Crash the DB in case of write errors (rather than just offline files — Doc ID 7691270.8). It is controlled by a hidden parameter – _datafile_write_errors_crash_instance with the default value TRUE — meaning database will shutdown itself when running into this disk full issue.

col description format a60
col name format a30
col type format a10
col value format a10
select a.ksppinm name, b.ksppstvl value, b.ksppstdf default_value,
   decode  (a.ksppity, 1,  'boolean', 2,'string', 3,'number', 4,'file', a.ksppity) type,
   a.ksppdesc description
from sys.x$ksppi a, sys.x$ksppcv b
where a.indx = b.indx
and a.ksppinm like '%datafile_write%'
order by name;

NAME                                     VALUE      DEFAULT_V TYPE       DESCRIPTION
---------------------------------------- ---------- --------- ---------- --------------------------------------------
_datafile_write_errors_crash_instance    TRUE       TRUE      boolean    datafile write errors crash instance

Note the behavior applies to datafiles and tempfiles.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s