RE: recover from controlfile

From: Powell, Mark D <mark.powell_at_eds.com>
Date: Tue, 10 Feb 2009 13:31:40 -0500
Message-ID: <D1DC33E67722D54A93F05F702C99E2A90373A839_at_usahm208.amer.corp.eds.com>

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
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of David Pintor

	Sent: Tuesday, February 10, 2009 5:22 AM
	To: oracle-l_at_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         

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

Received on Tue Feb 10 2009 - 12:31:40 CST