Re: HELP-need gurus-Oracle Support conflicting answers

It has been my experience that both your Support people were right.

As long as Oracle does not try to access the datafile, it doesn't care. If you look in V$DATAFILE, it is probably marked in RECOVER mode.

If Oracle tries to access the file, it can cause problems. The corruption issue might be platform specific.

Where I have run into this (on NT, HP-UX and Solaris), the answer that sometimes works is to shrink the datafile down to smaller than the allowable minimum extent size for the objects in the database. Even though it shows up as free space, it will never be allocated because it is too small.

You might look into the 'alter database create datafile' command. I am not sure when it became valid (I have used in on 8.0.5, I don't know about 7.3), but it might help.

In article <38E05906.AC7D9809_at_us.ibm.com>, Douglas Cowles <dcowles_at_us.ibm.com> wrote:
> Make a long story short -
> Oracle 7.3.4.4 on AIX 4.3.1 archive log mode
> I added a datafile to a 12G tablespace to allow for growth..
> I then realized I had named it wrong.
> I did an alter database datafile 'wrongfilename' offline drop.
> I then removed the datafile from the os.
> I then noticed it still showed up in the dictionary as available at
> 100MB.
> This scared me. ( I thought it would leave the dictionary)
> I stopped the backup from running for fear the database would not come
> up
> without the datafile.
> My first call to Oracle support indicated that this was an issue that
> could cause
> corruption and that the only way to remove it was to drop and re-
 create
> the
> tablespace. This was/is not an option. It would have to go to tape
> somewhere and would take a lot of involement to accomplish.
> My second call to Oracle support got a different analyst who said it
> wouldn't matter
> at all. The database would know the file was empty and not to worry
> about it.
> He told me to shutdown and startup to prove it. He was right. Although
> the file
> shows up in a backup controlfile to trace, the database starts up fine
> without the file
> there. He again said that it would hang out in the dictionary until
> some future re-org
> but would not cause any harm. These answers directly conflict each
> other.
> Which representative was right? Clearly the second one was right about
> the database not being bothered by it.. but getting 2 different
 answers
> makes me queesy.
> I have a few fears -
> 1) Since the dba_data_files view still shows this file as available
 and
> 100MB even
> though it is not there, will the tablespace attempt to extend into it
 in
> the future and
> cause a nasty error.
> 2) Although the database appears to be fine for the time being, a copy
> of the database with the "SET NAME" create controlfile command is used
> every morning
> on a different box. Will this work? I will know within 10 hours.
>
> This also leaves me with a few questions -
> 1) Is there no way to remove a datafile safely without re-creating the
> tablespace?
> Oracle was pretty stubborn about this, and said yes, essentially, when
> you create
> a datafile you are pretty much stuck with it, but I get the impression
> from the Sever
> SQL reference manual that the behavior of offline drop has something
 to
> do with
> whether the database is in archive log mode or not. I will do some
> experiments on a
> test database tomorrow, but would appreciate any feedback anyhow..
> 2) Is there some sort of clever trick that can be done - like a)
 taking
> the database
> out of archive log mode long enough to get rid of this file. b)
> resizing it to 0 c)
> adding it back d) anything at all !
>
> Also - I find it hard to believe that Oracle support, who can walk you
> through re-creating binary control files has absolutely no tricks up
> their sleeve for getting
> a datafile out of the dictionary.. I mean.. they must have clever
 little
> hacks sitting all
> over their office...
>
> I'll be honest.. my experience with Oracle Support is like flipping a
> coin. Occassionally, you get someone very sharp, and often you don't.
> I find often the
> best approach is to walk in with as much information as possible,
> sometimes more
> than they have on the tip of their brain, and then they generally put
> you on hold and
> get someone more clever.. in light of this.. I am curious if anyone
> knows of a nasty
> way to get rid of the file.. Dollar views, etc., I don't know.. I
 would
> NEVER do it
> without a blessing since this is an important database.. but I am
> curious.. maybe
> I can nudge support into going for it..
>
> Thanks,
> Dc.
>
>

--
Joseph R.P. Maloney, CCP,CSP,CDP
MPiR, Inc.
502-451-7404
some witty phrase goes here, I think.


Sent via Deja.com http://www.deja.com/
Before you buy.