recover from controlfile
- From: David Pintor <painterman@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 10 Feb 2009 10:21:31 +0000
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
Other related posts:
