Re: oracle redo log transaction

"orauser" <test_at_test.fr> wrote:
> hi,
> i've read the guides from oracle but don't understand fully how redo logs
> works exactly.
> i'm coming from sqlserver world and i know i can't make a comparaison but
> ...
>
> I know that when a redolog is full, the LGWR do a log switch but what
> occrurs when a log are switched ?
> LGWR call DBWR to applies change from the current redolog (which will be
> switched) to the datafiles or/and the rollback segment ?

Yes. It is called a checkpoint.

> then DBWR notifes LGWR that it can resume ?

Well, kind of. LGWR tells DBWR to start a checkpoint for the log being switched out of, but LGWR doesn't wait for that checkpoint to complete. It waits for the completion of the checkpoint that was started a while ago, back when the log that is now being switched *into* was being switched out of.

>
> Now after the second log switch the first redo log will be rewritten and
> if i issue a rollback, from
> where the data are taken to rollback? from the rollback segment ?

Yes, of course.

> what process write to the rollback segment ?

Any process that does anything that might need to be rolled back.

> And if the DB crash, since the statement is not finished there will be a
> rollback ?

Yes.

>
> why from a performance view, no one recommend a important redolog size ?
> > 50 Mo and onl 3 to 5 files ?
>
> Let's take a update statement on a big table (10 millions rows), if i
> have only 2 redo logs about 10 Mo each.
> How oracle does not return an error that redolog is full (like in mssql)
> ?

Once the checkpoint is complete, the data protected by the redo log (Both regular data and rollback data) is already stored on disk the ordinary and rollback segments, so the redo is no longer needed for instance recovery, and can be overwritten. (Of course, it is still necessary for media recovery, which is why it needs to be archived before being overwritten).

> is this the size of the rollback segment who actually limit the size of
> the transaction ?

Yes.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB