Re: _allow_resetlogs_corruption question ...

Howard,

Makes sense -- my mistake was I forgot to pull out an old copy of one of the datafiles -- the strange thing that was that v$datafile and v$recover_file showed that the SCNs were consistent. That threw me off track. You were absolutely correct in advising me to ignore that.

Every once in a while I practice these crazy recovery scenarios, with and without a recovery catalog, so that I'm prepared to handle the worst case. It works now, although you have to turn flashback off while in mount state.

Domenic.

"Howard J. Rogers" <howardjr_at_dizwell.com> wrote in message news:<4162b488$0$23894$afc38c87_at_news.optusnet.com.au>...
> Domenic wrote:
>
> > Howard,
> >
> > This wan't a real scenario -- I was playing around to see what would
> > happen if I lost all my control files and all my online redos. Relax,
> > it's a toy database!
>
> At the risk of sounding like a grumpy old man, could you maybe remember that
> just as the undo that your transaction generates is used by consistent
> readers elsewhere on the database, so questions and scenarios you post here
> are read by inconsistent newbies?
>
> I jumped on your statement of deleting the online redo logs simply because I
> would hate any newbie with a tenuous grasp of the subtleties of the English
> language to read what you wrote and think something meaningful about
> recovery techniques was being proposed.
>
> So it's not a question of me relaxing, so much (perhaps) of posters being a
> little more clear in what they post here.
>
> > The end result was that I could not open
> > resetlogs without the _allow_resetlogs_corruption parameter. The
> > restored datafiles were SCN consistent but the controlfile SCN was
> > before that.
>
> I see in another post that you eventually realised that actually the
> statement about the SCNs being consistent was not quite true. It turned out
> to be one file left ahead of the pack, if I read your other post correctly.
> It's quite a common question here (or seems to be): as I originally said,
> the message about File 1 being the one needing extra recovery is almost
> always wrong. The problem almost inevitably lies elsewhere, just as you now
> recognise.
>
> > I just wanted to understand how Oracle rolls back uncommitted changes
> > in the datafiles without the last online log.
>
> OK, Log 8 and Log 9. You start a transaction whilst the database is using
> Log 8. The transaction is still running, uncommitted, when LGWR switches to
> Log 9. Now you have a crash, and Log 9 is lost.
>
> Thing is, all sorts of dirty buffers were written to disk at that log
> switch... including the UNDO blocks that your transaction had been busily
> dirtying just as much as it was dirtying the EMP table's blocks. And of
> course dirtying undo blocks generates just as much redo as dirtying any
> other block. The redo for the EMP table *and* the undo segment is therefore
> still safe and sound in Log 8.
>
> So recovery can replay through Log 8, re-dirtying those blocks. When the end
> of Log 8 is reached, Oracle realises that the transaction must fail and be
> rolled back -and by replaying Log 8, you have also re-constructed the undo
> blocks needed to accomplish that roll back.
>
> You didn't replay Log 9, of course. So you don't need to roll that one back.
> That Log 9 is missing is therefore of no consequence to the recovery
> mechanism. If you can't re-perform the missing redo, what need is there to
> roll it back afterwards?
>
> Hopefully some of that makes sense when you read it!
>
> Regards
> HJR
Received on Tue Oct 05 2004 - 21:07:10 CDT