RE: Recover from a dropped datafile

From: Jeremiah Wilton <jeremiah_at_ora-600.net>
Date: Tue, 14 Feb 2006 09:21:52 -0800
Message-ID: <006201c6318b$26409660$a902a8c0@flbp7000a>

Hemant,

In 7.3.4, you could ALTER INDEX ... REBUILD. So in such a case, an index could be rebuilt into another tablespace after the database is open.

Also, in case a segment has extents in the problem datafile that are as yet unused and below the HWM, you can ALTER TABLE ... DEALLOCATE UNUSED;

Why do you feel must the database be started in NOARCHIVELOG mode?

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

-----Original Message-----
From: Hemant K Chitale [mailto:hkchital_at_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).

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

Received on Tue Feb 14 2006 - 11:21:52 CST