RE: Recover from a dropped datafile

Rich,

You didn't mention how the customer dropped the datafile (in the O/S or Oracle?), or which command was used. Also you didn't mention if the database is currently up or down. I guess it must be down because it sounds like you can't get it open.

As long as there are no extents in the missing file, you can startup the database in mount mode, then ALTER DATABASE DATAFILE ... OFFLINE DROP; for the missing datafile (make sure you don't drop the wrong one!), then alter database open. You can then create a new tablespace and move the tables from the messed up tablespace into the new one using CREATE TABLE ... AS SELECT * FROM ...; I suppose you may also be able to leave the tablespace where it is with the offline dropped datafile with no ill effects. You should just be able to continue adding datafiles as needed. I am embarrassed that I did not test this out on a real database before suggesting it, and I can't recall frop when I have tried it what ill effects there may be down the road with the leave-it-alone approach.

Make sure you take a look at these commands in the reference manual for precise syntax before you use them.

http://download-west.oracle.com/docs/cd/A57673_01/DOC/server/doc/SQL73/ch4a. htm

--

Jeremiah Wilton
ORA-600 Consulting
Recoveries - Seminars - Hiring
http://www.ora-600.net

-----Original Message-----

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Ignizio, Richard

I have a situation where a customer has "accidentally" dropped a datafile from a tablespace. The good news is that the datafile was empty since she just added it. The bad news is that there is no backup and it is a 7.3.4 database.

Is it possible to offline the datafile and open the database and some how move the data to a new tablespace?

--

http://www.freelists.org/webpage/oracle-l Received on Mon Feb 13 2006 - 23:41:17 CST