Try using the alter database command to remove the datafile in question from the database and then perform recovery. Once recovery is done drop the tablespace including contents. -- Mark D Powell -- Phone (313) 592-5148 ________________________________ From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of David Pintor Sent: Tuesday, February 10, 2009 5:22 AM To: oracle-l@xxxxxxxxxxxxx Subject: recover from controlfile Hi there, Version: Oracle 10g This is just a testing situation. I simulate the lost of the three control files. I restore the control files from a binary copy and try to recover by mounting the database and typing: SQL> RECOVER DATABASE USING BACKUP CONTROLFILE; Then I get: ORA-00283: recovery session canceled due to errors ORA-01111: name for data file 5 is unknown - rename to correct file ORA-01110: data file 5: '/opt/oracle/product/10.1.0/db_1/dbs/MISSING00005' ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01111: name for data file 5 is unknown - rename to correct file ORA-01110: data file 5: '/opt/oracle/product/10.1.0/db_1/dbs/MISSING00005' When I look at the last the dbwr process trace I see: .... .... .... *** SERVICE NAME:() 2009-01-29 14:58:20.687 *** SESSION ID:(168.1) 2009-01-29 14:58:20.687 ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: '/u01/oradata/INFRA/index01.dbf' ORA-27037: unable to obtain file status SVR4 Error: 2: No such file or directory Additional information: 3 *** 2009-01-29 15:01:09.385 ORA-01157: cannot identify/lock data file 5 - see DBWR trace file ORA-01110: data file 5: '/u01/oradata/INFRA/index01.dbf' ORA-27037: unable to obtain file status SVR4 Error: 2: No such file or directory Additional information: 3 .... .... So then, I remembered that I had created a INDEXES tablespace on that date, but the datafile is missing now (as I removed it then, but not the tablespace - my mistake), so the tablespace is still there... SQL> select * from v$tablespace; TS# NAME INC BIG FLA ---------- -------------------------------------------------- --- --- --- 0 SYSTEM YES NO YES 1 UNDOTBS1 YES NO YES 2 SYSAUX YES NO YES 4 QA_TBSP YES NO YES 5 USERS YES NO YES 7 INFRA YES NO YES 3 TEMP YES NO YES 11 INDEXES YES NO YES ...but not the datafile... SQL> select * from v$datafile; FILE# NAME ---------- -------------------------------------------------- 1 /u01/oradata/INFRA/system01.dbf 2 /usr/oradata/INFRA/undotbs01.dbf 3 /u01/oradata/INFRA/sysaux01.dbf 4 /u01/oradata/INFRA/qatbsp01.dbf 5 /opt/oracle/product/10.1.0/db_1/dbs/MISSING00005 6 /u01/oradata/INFRA/users01.dbf 7 /u01/oradata/INFRA/infra03.dbf 8 /u01/oradata/INFRA/infra01.dbf 10 /u01/oradata/INFRA/infra02.dbf I don't really need this tablespace anymore, so I want to remove it, but obviously I can't because I'm not able to moun't the database... Is there any way to solve this or the only solution left is restoring from last backup? Thanks for your help! David