ORA-01110: data file 201

#########################################
#
# ORA-01110: data file 201
#
##########################################

-- Problem
DDE: Problem Key 'ORA 1110' was flood controlled (0x1) (no incident)
ORA-01110: data file 201: '+DATA01'
ORA-01186: file 201 failed verification tests
ORA-01157: cannot identify/lock data file 201 - see DBWR trace file

-- Gather status of files
col name format a76
col MEMBER format a76
select file#,status,name from v$datafile;
select file#,status,name from v$tempfile;
select GROUP# , MEMBER from v$logfile
where member like '%DATA01%';

-- TEMP datafile showed a problem

     FILE# STATUS  NAME
---------- ------- ----------------------------------------------------------------------------
         1 ONLINE  +DATA01

       
-- The example below shoed that the ASM space was exhausted due to old files being resident
SQL> alter tablespace TEMP add tempfile size 10G;
alter tablespace TEMP add tempfile size 10G
*
ERROR at line 1:
ORA-01119: error in creating database file '+DATA01'
ORA-17502: ksfdcre:4 Failed to create file +DATA01
ORA-15041: diskgroup "DATA01" space exhausted

-- Workaround
create temporary tablespace temp_sed ;
alter database default temporary tablespace temp_sed;
drop tablespace temp;
select file#,status,name from v$tempfile;
create temporary tablespace TEMP;
alter database default temporary tablespace temp;
drop tablespace temp_sed;
select file#,status,name from v$tempfile;

-- Post check
select file#,status,name from v$tempfile;

FILE# STATUS  NAME
----- ------- ----------------------------------------------------------------------------
         1 ONLINE  +DATA01/qt01dda_tss2l592/tempfile/temp.265.852292851

-- Thanks to the following for advice.

Sameer Shaik

http://www.shaiksameer.com/2011/07/ora-01157-cannot-identifylock-data-file.html