recover from controlfile

From: David Pintor <painterman_at_gmail.com>
Date: Tue, 10 Feb 2009 10:21:31 +0000
Message-ID: <f7321f200902100221o30ec09f5ka3a24c669a4daebf_at_mail.gmail.com>

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

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Feb 10 2009 - 04:21:31 CST