FAQ
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_at_freelists.org
On Behalf Of David Pintor

Sent: Tuesday, February 10, 2009 5:22 AM
To: oracle-l@freelists.org
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

Search Discussions

Discussion Posts

Previous

Related Discussions

Discussion Navigation
viewthread | post
posts ‹ prev | 2 of 2 | next ›
Discussion Overview
grouporacle-l @
categoriesoracle
postedFeb 10, '09 at 10:21a
activeFeb 10, '09 at 6:31p
posts2
users2
websiteoracle.com

2 users in discussion

Powell, Mark D: 1 post David Pintor: 1 post

People

Translate

site design / logo © 2022 Grokbase