Re: backup of offline dropped datafile

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.

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