Re: HELP-need gurus-Oracle Support conflicting answers

You are right, it is in recover mode.. however, I can't shrink anything since the file
is gone.. What I noticed, is that an exact copy of this database which is renamed
with a controlfile set name command, choked this morning. When I removed the
2 references to the file in question, one as a datafile and another as an alter datafile
offline drop, the database came up normally. Is this an option in the original database?

• Dc.

Joe Maloney wrote:

> 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.
Received on Tue Mar 28 2000 - 00:00:00 CST