Database crashed due to 100% usage of datfile mountpoint & Recovery steps

Today one of my database was crashed due to 100% usage of datafile mountpoint. We are getting below error in alert logs.


Linux-x86_64 Error: 28: No space left on device
ORA-63999: data file suffered media failure
ORA-01114: IO error writing block to file 14 (block # 2039938)
ORA-01110: data file 14: '/db05/oradata/JC2INT/vino_data05.dbf'


This is due to hidden parameter _datafile_write_errors_crash_instance  .  Due to this  ANY write error to a datafile will cause the instance to abort.

We need to increase the storage.As a temporary solution, I thought to move tempfiles to another mountpoint and tried to start the database.


In our case DB is already down.Then we need to move the temp files from /db05  to /backup mountpoint to get some freespace.

cd /db05/oradata/JC2INT
mv temp05.dbf   /backup/JC_TMP


SQL> startup mount;
ORACLE instance started.

Total System Global Area 2137886720 bytes
Fixed Size                  2254952 bytes
Variable Size             637536152 bytes
Database Buffers         1493172224 bytes
Redo Buffers                4923392 bytes
Database mounted.
SQL> SELECT name FROM v$tempfile;

NAME
--------------------------------------------------------------------------------
/db05/oradata/JC2INT/temp05.dbf
/db05/oradata/JC2INT/temp04.dbf
/db05/oradata/JC2INT/temp03.dbf
/db05/oradata/JC2INT/temp02.dbf
/db05/oradata/JC2INT/TEMP01.dbf

SQL> ALTER DATABASE RENAME FILE '/db05/oradata/JC2INT/temp05.dbf' TO '/backup/JC_TMP/temp05.dbf';

Database altered.

SQL> SELECT name FROM v$tempfile;

NAME
--------------------------------------------------------------------------------
/backup/JC_TMP/temp05.dbf
/db05/oradata/JC2INT/temp04.dbf
/db05/oradata/JC2INT/temp03.dbf
/db05/oradata/JC2INT/temp02.dbf
/db05/oradata/JC2INT/TEMP01.dbf

SQL> alter database open;

Database altered.