Re: backup of offline dropped datafile
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 <firstname.lastname@example.org> wrote:
> 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
> 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
> 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.