Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: backup of offline dropped datafile

Re: backup of offline dropped datafile

From: zhu chao <>
Date: 2005-12-25 12:21:24

If it is locally managed tablespace, then oracle will not try to allocate new extents to the offline dropped datafile.

If it is dictionary managed tablespace, then oracle will do that wrong thing. And it found no file to write, then there will be no segment data , but some empty extent in the datafile.

Seems the original poster's DB is in noarchivelog mode. So the best solution would be export the damanged tablespace and then drop and recreate it, import it back. Or create a new tablespace and then move the tables one by one during outage time.

On 12/24/05, Tanel Põder wrote:
> Hi,
> > From the scenario that Koen described, it doesn't look that storage been
> > allocated for any objects in the dropped data file, so queries against
> > objects
> > shoudn't fail.
> If there is any table/index segment which had it's segment header block in
> the offline datafile, most queries against it will definitely fail
> (exceptions would be accessing table using ROWID access path or a cluster
> access, which doesn't have to read segment header).
> Index root blocks are always immediately next to segment header block, so
> indexe scans would fail.
> I don't have access to an Oracle DB right now, but you could try to
> allocate
> extents to a table in your USERS tablespace to see whether it will hit a
> "file inaccessible" error or not. It used to, but could be changed in
> later
> versions (although I don't really why Oracle would do that, because an
> offline drop renders tablespace physically invalid and it's only mean is
> getting rid of a corrupt/lost datafile, followed by drop tablespace).
> > Even if you recreate controlfiles/resetlogs (from backup or from trace),
> > offline datafile will still apear in v$datafile - there is nothing you
> can
> > do
> > about it - but from my understanding it should not cause any issue.
> See above, offline drop renders tablespace invalid, everything done in the
> tablespace after it is hacking.
> Tanel.
> --

Zhu Chao
Received on Sun Dec 25 2005 - 12:21:24 CST

Original text of this message