Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Oracle 8i (8.1.7.0.1) + Redhat Linux 7.2 = Cannot create tablespace file > 2 gb

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

From: Howard J. Rogers <dba_at_hjrdba.com>
Date: Sat, 29 Jun 2002 15:51:38 +1000
Message-ID: <afjhv4$nqr$1@lust.ihug.co.nz>

"Sean M" <smckeown_at_earthlink.net> wrote in message news:3D1D46E3.CC2FECEC_at_earthlink.net...
> "Howard J. Rogers" wrote:
> >
> > We were. Recover until time 10.00am, when you were told Scott dropped
the
> > SALES table. Open resetlogs. Bugger me.... SALES is still missing.
> >
> > Now repeat the incomplete recovery.
> >
> > You can restore the datafiles, for sure. But your control file now
thinks
> > its time 0. Your online redo logs think likewise.
> >
> > IE, before the recovery, you had:
> >
> > Control: time M
> > Datafiles: time M
> > Online Redo : time M
> >
> > Restore the datafiles:
> >
> > Control: M
> > Datafiles: F
> > Online Redo : M
> >
> > Perform Recovery:
> >
> > Control : M
> > Datafiles : L
> > Online Redo : M
> >
> > Open resetlogs:
> >
> > Control : A
> > Datafiles : A
> > Online Redo: A
> >
> > Now repeatr the recovery....
> >
> > Control A
> > Datafiles F
> > Online redo A.
> >
> > Your control file and redo logs are from a time *before* the datafiles.
> > Rather worse, they are from a completely different incarnation of the
> > database. You won't be able to repeat the recovery.
>
> Sure you can... just recreate the controlfile and delete the online redo
> logs. Re-restore your datafiles, and you're off and running again.

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.

>
> > However, if you've backed up the original control files and redo logs
first,
> > then given:
> >
> > Control: A
> > Datafiles: A
> > Online Redo : A
> >
> > You can restore the precautionary backup, to give this:
> >
> > Control : M
> > Datafiles: A
> > Online Redo : M
> >
> > Now re-restore the datafiles:
> >
> > Control : M
> > Datafiles: F
> > Online Redo : M
> >
> > And "M-F-M" is the state we were in to perform the initial recovery. So
now
> > you can recover until time "K".
> >
> > Without being able to restore the initial control files or online redo
logs,
> > you cannot repeat an incomplete recovery if it proves that the first
attempt
> > sailed straight past the dodgy redo.
>
> That's not correct. First of all you can just throw away your online
> redo logs if you're doing an incomplete recovery - they're worthless
> since they contain information newer than the point to which you're
> recovering.

Who said? An incomplete recovery means stop short of the end of redo log marker. It says nothing about "I don't want the current log AT ALL".

>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 trying a repeat incomplete recovery gets, er, shall we say "interesting".

>
> > > Sure there is! Just re-restore your backup, and start again. Might
> > > cost you some time if the DB is big, but certainly possible.
> >
> > You can't do that.
>
> Sure I can, and I have more than once!
>
> > If you restore the datafiles from the "proper" backup,
> > you are stuck with a control file and online logs from a new
incarnation.
>
> So what? Blow 'em away, recreate the controlfile, and I'm fine.

Yup. Who cares about online redo logs anyway. They only contain committed transactions I'd like to re-perform!!

>
> > If
> > you restore *everything* from the previous backup, you've possibly lost
> > transactions in the current log you wanted to re-perform.
>
> Transactions in the current log? We're talking incomplete recovery here
> - we never get to the online redos. Why do we need them? Delete them,
> and let the next open resetlogs re-recreate them.
>

Bollocks. Incomplete recoveries do not mean "don't open the current log". They mean "don't replay all redo". Quite a different matter.

> > 'Fraid the manuals are unambiguous on the subject, as be me: if you
don't
> > take a precautionary backup of your control files and online logs before
> > beginning an incomplete recovery, you cannot repeat said recovery when
you
> > find that it didn't bring back what you wanted it to.
>
> I totally disagree - UNLESS you're talking about an incomplete recovery
> to an extremely recent point in time,

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

>so recent that there is still data
> in the online redos that you're rolling forward onto.

Ah hah! He's got it. By George he's got it. (Stop me doing the whole My Fair Lady bit).

>But I was pretty
> sure we were talking about rolling back in time a few hours/days/weeks,

Why?

> 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 general", an incomplete recovery is not repeatable without a backup of the controlfiles and online redo logs.

Regards
HJR
>
> Regards,
> Sean
Received on Sat Jun 29 2002 - 00:51:38 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US