RE: Recover from a dropped datafile

From: Hemant K Chitale <hkchital_at_singnet.com.sg>
Date: Tue, 14 Feb 2006 23:26:57 +0800
Message-Id: <6.2.1.2.0.20060214232228.01fc4e10@pop.singnet.com.sg>

The database must be started in NOARCHIVELOG (eg when in MOUNT)
The ALTER DATABASE DATAFILE <filename> OFFLINE DROP does not physically delete the file or remove it from dba_data_files either. The file remains as "part of the database" but requiring recovery.
The normal way to proceed is to export all data from that tablespace, drop the contents of the tablespace, drop the tablespace, re-create the tablespace and reimport. {7.3.4 didn't offer an ALTER TABLE .. MOVE or, not sure of this, ALTER INDEX .. REBUILD}

However, before you attempt any of this, query DBA_EXTENTS to be assured that there ARE NO extents allocated in that datafile. If there are extents allocated,
then that particular index (if it is an index) must be rebuilt with a DROP and CREATE
or that particular table must be carefully exported (metalink notes do show how to get around missing blocks in a table) and rebuilt (ie reimported).

Hemant

At 01:41 PM Tuesday, Jeremiah Wilton wrote:
>Rich,
><deleted>
>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 ...;
>
><deleted>
>--
>Jeremiah Wilton
>ORA-600 Consulting
>
>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?

Hemant K Chitale
http://web.singnet.com.sg/~hkchital

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

Received on Tue Feb 14 2006 - 09:26:57 CST