Re: DBWR and LGWR processes, theoretical question

In article <3DC132C2.2030905_at_web.de>, mig-sm_at_web.de says...
> 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. 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).
>
> I think that the committed transaction cannot be recovered by Oracle. Am
> i right?
>
> What if i use two redo log file groups. Now i have at least one
> corrupted redo log file. Does Oracle check if a redo log file is
> correct? How is it done?
>
>

That's why you should always multiplex your redo logs (i.e. more than one group). Besides, you're talking about two catastrophies occurring simultaneously (server crash / redo log file corrupt). The one doesn't necessarily cause the other. There's a different recovery method for each case (see Backup and Recovery docs).

• Tom