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;
  


 

4 comments: