How to remove a missing datafile record from controlfile

Hi All,
A very good morning. When I query  dba_data_files, I find a filename to be 
listed as missing. The same file is listed in v$recover_file. But I can 
shutdown the database and startup the database without any problems. This is a 
development database and we do not have backups to recover from.

The following are my questions:
1)       How is it possible for Oracle to start with a missing datafile.
2)      What could be the scenario for this to happen? I know I should be the 
one to tell you the scenario but I do not know the history of this database 
just as you.
3)      How do I remove this record from the control file?
4)      My idea was to export the index tablespace and drop tablespace 
including contents and datafiles and recreate the index tablespace and import 
the contents back again. This could be true for permanent tablespaces but since 
this is an index tablespace I get ORA-02429: cannot drop index used for 
enforcement of unique/primary key
5)      Now I am going to export the user that is using the tablespace and drop 
the user and drop the tablespaces(both permanent and index) and recreate the 
tablespaces and import it.


select TABLESPACE_NAME, FILE_NAME,file_id from dba_data_files order by 
TABLESPACE_NAME

TABLESPACE_NAME            FILE_NAME                                            
      FILE_ID

.
.
INDEX3L                       /opt/oracle/products/102/dbs/MISSING00059    59
INDEX3L                       /oracle/gss/oradata/INDEX3L.dbf                   
     24
.
.

select * from v$recover_file

FILE# ONLINE           ONLINE_STATUS       ERROR                          
CHANGE #       TIME
59        OFFLINE          OFFLINE                      FILE MISSING            
0                      NULL

I did try alter database datafile '/opt/oracle/products/102/dbs/MISSING00059' 
offline drop; but it dint remove the record from control file.

SYS@wssd11 SQL> drop tablespace INDEX3L including contents and datafiles;
drop tablespace COREINDEX3L including contents and datafiles
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key

Is there any other way to make this simpler. Many Thanks for your help. If you 
need more info, please do let me know.

--Sundar

Other related posts: