ORA-01157 ORA-01110 ORA-27037 ASM file Addition Issues

This blog is intended to make DBA’s aware of what havoc it will create if we misplace a command of datafile addition in ASM disk group especially in RAC.

Last night I missed the ‘+’ sign of DG01 while adding a tempfile in new prod RAC DB resulted in below error & file creation in default local/non-shared location of $ORACLE_HOME/dbs

Which is not readable by other instance, being a temp file it had less impact on DB but such incident can happen while adding data DB files which will lead to db outage

My Mistake: 

I missed ‘+’ sign of DG01

SQL>alter tablespace TEMP add tempfile 'DG01' size 20G;

Impact: Any Running session using this file will receive & log error in alert log

SQL> select * from dba_temp_free_space;

ORA-01157: cannot identify/lock data file 1010 - see DBWR trace file

ORA-01110: data file 1010: '/u01/app/ora11g/product/11.2.0/db_1/dbs/DG01'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Workaround: No workaround to resolve this issue online , although i have attempted below

To recover below issue I have tried asmcm cp command which has copied local $ORACLE_HOME/dbs/DG01 to ASM location but need to make tablespace offline to rename this tempfile & point to ASM location

But it is not possible to offline TEMP tablespace.

cp /u01/app/ora11g/product/11.2.0/dbhome_1/dbs/DG01 +DG01/remdev/tempfile/temp1.bak

Resolution: To recreate TEMP tablespace , make sure default tablespace for the database is set

Fix:

To avoid this issue in future, we have to make use of below commands while adding temp/datafile in ASM environment.

Being OMF, ASM has predefined template for each type of tablespace & it is not required to pass disk group location or absolute path while adding a tablespace or data/tempfile in it.

SQL>alter tablespace TEST add datafile size 10M;

SQL>alter tablespace TEMP add tempfile size 10M;