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

"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.

> 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...). 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), 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. 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.

> "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." :)

Regards,
Sean M Received on Sat Jun 29 2002 - 01:29:39 CDT