Re: DBWR and LGWR processes, theoretical question

"Michael Gast" <mig-sm_at_web.de> wrote in message news:3DC132C2.2030905_at_web.de...
> Hi,
>
> i'm currently 'upgrading' my knowledge of Oracle from version 7.x to
> 9.2. Reading through the Database Concepts manual, i found that Oracle
> uses write ahead logging. If i understand it correctly, that means that
> there could be a time gap between writing committed data changes to the
> tablespace files by the DBWx process and writing them to the redo log
> files by the LGWR process (as far as i can see now, there is no change
> since Oracle 7.x on this point).
>
> Now my question:
> Take a system that modifies data heavily. To make it simple, let us use
> only one transaction changing a lot of data (Example: In my last project
> i had the requirement to insert 650 MB of raw data in up to 160000 rows
> within a single transaction).
>
> So, what is happening?
> - LGWR writes the committed statement to the redo log file. Due to the
> write-ahead logging it finishes faster than the DBW0 - DBWx processes.
> - DBW0 - DBWx are processing their batch to write the changes to the
> tablespaces (which may take a little bit of time due to the data
> volume).
>
> Now we have the state, that a committed transaction only is stored in
> the redo log file but not necessarily is stored completely in the
> tablespace.

This is perfectly standard, and hasn't changed in years. A 'commit' causes LGWR to flush, not DBWR. So it is possible that as you commit, absolutely nothing of your data is written down to the data files. During normal database operation, data files contain data which is both committed, uncommitted, and somewhere in between.

However, Oracle promises not to tell you that your commit has succeeded until LGWR can confirm the write of the REDO to the redo logs has been completed successfully. Which means that if you ever see a message 'commit processed', you can be sure the recipe for how to repeat your transaction is safe and sound in the redo logs... which in turn means that in the event of a failure of the type you're contemplating, we can re-create the data and put it back to the state it should be as a result of your transaction.

Likewise, if a datafile contains uncommitted data (which it will do), and then you have a crash, we can use the redo logs to work out what was committed and what was uncommitted at the time of the crash, and roll the uncommitted stuff back.

>As far as i understand the checkpoint is not reached (i
> assume, a usual state in such an environment).
>
> At this point of time the server crashes and let us assume the last redo
> log file is corrupt (simple example, no redo log file groups).
>

Now that's a big (and nasty) assumption. Since the only thing that definitely contains the details of your committed transactions is the redo log, and since the only thing that can distinguish between committed and uncommitted transactions is the redo log, having a corrupt redo log is a recipe for disaster. If that log has a status of active or current, you've just lost data.

> I think that the committed transaction cannot be recovered by Oracle. Am
> i right?
>
> What if i use two redo log file groups.

Two *groups* doesn't get you anywhere. I think you meant to say two MEMBERS in each group. And that's called 'multiplexing', and yes: it protects a single member from being corrupted, because once detected, Oracle will silently failover to reading from the remaining good member. Meaning that the stream of redo needed to recover your transactions is still unbroken, and can be used successfully.

>Now i have at least one
> corrupted redo log file. Does Oracle check if a redo log file is
> correct? How is it done?
>

Quite frequently, it only detects a corruption when it's trying to *read* some redo -and redo is only ever read after a failure of some kind, when recovery is initiated. By then, of course, it's a bit late to find out... hence the practical necessity of having at least one, preferably two, additional members in each log group (ie, three members per group). Multiplexing your redo should be considered compulsory, and I eternally regret Oracle not actually making it so.

That aside, if your redo logs are safe, and looked after, then you will NEVER, ever lose a committed transaction in Oracle.

Regards
HJR
> --
> All emails sent to this address are never read and never will be
> answered. Sorry, but until someone cleans up the spam mess, that's the
> way it has to be.
>
> E-Mails, die direkt an diese Adresse geschickt werden, lese und
> beantworte ich nicht. Ich bedauere diesen Umstand sehr, kenne derzeit
> aber keine bessere Möglichkeit, um die Spam-Flut abzustellen.
>
> Mit freundlichen Grüßen / Best Regards
> Michael Gast
> SEPP MED GmbH
>
Received on Thu Oct 31 2002 - 14:08:37 CST