RE: Recover from a dropped datafile
- From: "Jeremiah Wilton" <jeremiah@xxxxxxxxxxx>
- To: "'Hemant K Chitale'" <hkchital@xxxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 14 Feb 2006 09:21:52 -0800
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@xxxxxxxxxxxxxx]
1. The database must be started in NOARCHIVELOG (eg when in MOUNT)
2. 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.
3. 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).
--
//www.freelists.org/webpage/oracle-l
Other related posts:
