Re: HELP-need gurus-Oracle Support conflicting answers

If anyone is still interested in this thread the results were quite interesting. What I had done, was offline drop a live datafile and removed it from the OS without a cold backup. My first f-up as a DBA. Anyway, this was quite eaily recoverable with an alter database create datafile 'the crappy file', and then a recover datafile 'crappy file', which requested some redo logs only between the time of the files creation and a couple hours after it's drop. Then a standard rename process fixed the problem for good. My nervous system took a beating over this one, special thanks to Joe for his earlier suggestion in this thread, which was what the final answer was. Oracle support was quite confused. When they hear you don't have a cold backup of the file, they tend to give misleading information. One said that was it, when I rolled forward, it would offline drop again, which was not true, because that information is in the system files, which I am not recovering.. I also got the suggestion that the data was merely gone, and I had to re-create the table. (lot of VERY important info in this datafile if you know what I mean.. I'm sure some of you can relate) All untrue.. but a lesson learned for me.. under certain circumstances, and I would be very careful, you can recover a datafile without a cold backup.. Most DBA's I know who I respect have at least one screw-up story. I would hate to think that you earn your stripes by blowing it.. but it seems that way...you certainly learn a great deal and reset the bar for yourself..

Special thanks to Anita in the RDBMS group in Orlando for her help...

• Dc.

On Tue, 28 Mar 2000 11:17:07 -0500, Douglas Cowles <dcowles_at_us.ibm.com> wrote:

>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 Thu Apr 20 2000 - 00:00:00 CDT