Re: Deletion of datafile == loss of online backup ability ???

"Simon Gottesman" <sgotte_at_prodigy-nospamplease.net> wrote in message news:e9Gd7.8819$5l4.2168162471_at_newssvr15.news.prodigy.com...
> Using 8.1.7 on Win2k Pro.
> I used "alter database datafile <path> offline drop" to drop a datafile,
 and
> when the db was shut down, deleted the file, as specified in <path>, from
> the OS. I have thus lost the ability to do online backups of the
 database.
> (The db is in archive mode and automatic archival is enabled).
> When I try to put the tablespace that contained that datafile in backup
 mode
> with "alter tablespace mydbspace begin backup", I get this:
> ORA-01128: cannot start online backup - file 3 is offline
> ORA-01110: data file 3: 'C:\MYDB\MYDB_DTFILE2.ORA'
> Why is this? Even a little help is much appreciated.
>

You cannot remove files from a tablespace once it's been added. The best you can do is to shrink it down to some negligble size.

The 'offline drop' command is usually used when you are recovering a database after media failure and, for one reason or another, you do not wish to attempt recovery of a particular datafile (for example, the one associated with the TEMP tablespace). By 'offlining' a datafile, you are instructing Oracle to ignore the fact that it is out-of-synch with the rest of the database (useful for getting the rest of the database open around the problem). An 'offline drop' means 'ignore it's out-of-synch, and by the way, I have no intention of ever attempting recovery on it, so please forget all about it.

It's pretty serious stuff: basically, you've ballsed up the entire tablespace, and as far as Oracle is concerned, it's effectively not there any more.

Recovery is going to be messy. If you have a binary backup version of your controlfile, you could use that to attempt a complete recovery (ie, that binary version still contains all the pointers to the relevant files). You'll need a backup of the datafile that you deleted, too, of course.

If you manage to pull off that recovery, then fine... just live with the presence of the datafile you don't want anymore. There's nothing you can do about it, except for an 'alter database datafile 3 resize 10K;'... shrinking it down to a totally insignificant size. Alternatively, export everything in that tablespace, drop the tablespace, delete all the files associated with it, recreate it with the files you DO want, and then import the lot.

As to why the begin backup command fails: you've told Oracle that you don't want to work with that file anymore (but that doesn't mean it forgets all about it). The begin backup command requires all files associated with a tablespace to be checkpointed before the headers of all files are frozen... unfortunately, it's a bit difficult to checkpoint a file which doesn't physically exist, and which in anycase you've already told Oracle that you don't wish to use again.

Regards
HJR
>
>
Received on Sun Aug 12 2001 - 21:23:30 CDT