Re: _allow_resetlogs_corruption question ...

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 Mon Oct 04 2004 - 09:46:53 CDT