Re: Oracle 8i (8.1.7.0.1) + Redhat Linux 7.2 = Cannot create tablespace file > 2 gb

"Sean M" <smckeown_at_earthlink.net> wrote in message news:3D1D5377.12056C13_at_earthlink.net...
> "Howard J. Rogers" wrote:
> >
> > Not so sure I would want to delete the online redo logs, are you? You
meant
> > that repeating the recovery, followed by a resetlogs, will recreate
them.
> > Fair enough. But what happens if the user error you are trying to avoid
> > repeating was in the online redo logs? So you want to roll forward
partway
> > through (what was) the current log, and stop short of the end.
> >
> > You've just proposed deleting them. Guess I can't roll forward through
what
> > they contained then.
> >
> > Who said? An incomplete recovery means stop short of the end of redo log
>
> Fair enough.
>
> > >So the online redo part of your argument doesn't stand up.
> >
> > Oh yes it does.
> >
> > > As for the controlfile, just recreate it like I said in my last post.
> >
> > Yeah, just re-create it. 'Xcept your onlines aren't as disposable as you
> > might like. And recreating the controlfile using the 'backup to trace'
> > script means that Oracle scans the datafiles for the highest SCN and
forces
> > the new controlfile to agree to that SCN. Which means that there is no
> > incompatibility between the datafiles and the controlfile. Which means
that
>
> Not sure I follow... after I recreate a controlfile I can certainly roll
> forward past the highest SCN in the datafiles. I can roll forward
> through every single redo log in my possesion with a recreated
> controlfile.
>
> > Yup. Who cares about online redo logs anyway. They only contain
committed
> > transactions I'd like to re-perform!!
> >
> > Bollocks. Incomplete recoveries do not mean "don't open the current
log".
> > They mean "don't replay all redo". Quite a different matter.
> >
> > Oh, ah. So now we realise that the onlines are actually quite
worthwhile,
> > because we're trying to recover to a "recent" point in time. Might
surprise
> > you to know that an online redo log can contain anywhere up to 24
> > hours-worth of transactions in databases I'm associated with (I dislike
log
> > switches because of the checkpoints and performance degradation they
cause).
> > Even when it's not that extreme, you aren't (I hope) postulating a law
that
> > says 'recoveries shall not be to 15 minutes ago'.
>
> No, just accustomed to databases with much more frequent switching.

Poor you.

>
> > Ah hah! He's got it. By George he's got it. (Stop me doing the whole My
Fair
> > Lady bit).
>
> Bravo Prof. Higgins. ;) G.B. Shaw would be proud.
>

 > >But I was pretty
> > > sure we were talking about rolling back in time a few
hours/days/weeks,
> >
> > Why?
>
> Because that's the time period we've been using in examples througout
> this thread (backup Monday, restore Tuesday, etc...).

Yeah, but if you restore Tuesday, doesn't mean you have to *recover* all of it.

>And that's why I
> added a big "UNLESS" to my post - to be sure you didn't have a different
> example in mind.
>
> > > where we only apply *archived* redo to catch up. But in general, for
an
> > > incomplete recovery, Oracle never even reads the online redo logs, so
it
> > > will never complain that they're from a different time or incarnation.
> >
> > How in God's name can you make that statement? "In general"... what does
> > that mean? "Excuse me, Mr DBA, I just dropped a table". "When did you do
> > it?" "Er, 15 minutes ago". "Too recent, sod off".
> >
> > I don't think so.
>
> In practice, most incomplete recoveries of production databases roll
> back to a point in time earlier than the data contained in the online
> redos. That's been my experience, and I bet most others' that do this
> on real production systems. Why? Because most real production systems
> switch logs rather frequently (say, a few times per hour),

I hope not. But perhaps things are different south of the equator. Most DBAs I know of here would switch every hour or so. And I disapprove (since they are only protecting themselves against Instance failure by doing so, and I happen to think that hardware and most O/Ses, not to mention UPSes have gotten to the point where I hope we can start to stop worrying about instance failures).

>and it
> usually takes longer than that to 1) discover you need to roll back and
> 2) convince management this is important enough to stop production
> activity and 3) shutdown the database. I'd submit that your example of
> databases with one log switch a day is the exception, not the rule.

I agree (but I'm working to change opinion!).

> That's why I made the "in general" statement. But I'll concede that,
> for an absolute rule you can apply to *all* incomplete recoveries, you
> should backup your online redos before you begin.
>

Great. As ever, we agree. Eventually.

> > "In general", an incomplete recovery is not repeatable without a backup
of
> > the controlfiles and online redo logs.
>
> I disagree that you need backup of the crontrolfile since you can
> recreate it. But ok, "in general." :)

OK, I'll fess up. The reason I don't like the idea of not backing up the controlfile is not that you can't recreate it as you state, but that to do so requires either (a) enormous skill at knocking up a 'create controlfile' statement on the fly (doubtful) or (b) using the 'backup to trace' script. And the trouble with (b) is that that script (c) can be out of date unless you backup to trace routinely (which I always recommend, but we can't always be perfect), requiring manual editing; or (d) proposes to perform a complete recovery for you, so that you either (e) have to edit it to remove or alter the 'recover database' line it contains and add in a reference to 'resetlogs' or (f) accept that you get a failed recovery.

It is *sooooo* much easier just to restore from a precautionary backup, and take control of the exercise.

But again, we'll agree that it is possible to recreate the controlfile in the event that you don't mind waltzing through the alphabet.

;-)
HJR
>
> Regards,
> Sean M
Received on Sat Jun 29 2002 - 01:40:46 CDT