Re: log manager in ORACLE?

Comments below.

"Y" <newdbms_at_yahoo.com> wrote in message news:3C6881C6.45770644_at_yahoo.com...
> Hi,
> I am using Oracle 8i EE.
> I took a quick view of oracle books. I haven't found a direct answer for
> my questions.
> I just want to know how log manager works in Oracle?
>
> For example, when I issue one update sql request,
>
> 1. Oracle read the datablock from datafiles on disk into buffer. Before
> update data block, oracle first write the before and after image to log
> buffer?
>

Pretty accurate so far. Actually, it takes a lock on the row(s) first, then writes the before and after images into the log buffer.

> 2. When and what information will be written to Rollback Segments?
>

Immediately after that, it writes just the before image of the row or affected columns into the redo block (which it reserved for the purpose somewhere around question 1).

> 3. When the transaction end or the log buffer pool full, log buffer
> pool flush to redo log files?
>

Nope. The log buffer is flushed whenever it is one third full, so it should never get totally full. Ending the transaction with a commit will indeed cause a flush, however. LGWR will also flush if 1Mb of uncommitted redo has been generated, and the buffer is bigger than 3Mb (otherwise the 1/3rd full rule would always kick in earlier). And LGWR will always flush anytime DBWR signals it wants to flush (DBWR is working to its own agenda, and can flush for all sorts of reasons, none of which are transaction specific. But whenever it threatens to flush, it's crucial that LGWR finishes its flush first, since it is the entries in the redo logs that lets us sort out the mixture of committed and uncommitted data we'd find in our datafiles after an unexpected shutdown.)

> 4. The rollback segments will only keep before and after image for open
> transactions. Is that correct? The redo log files will store the before
> and after image of unactive transactions. Is that correct?
>

No. Rollback segments store the before image of transactions whether they are committed or not. We need the before image of committed transactions to generate read consistent images of the data for queries which *started* before transactions were committed. However, it is permissible to overwrite the rollback of transactions which have been committed -but if we do, you'd best pray that no reports need that rollback for read-consistent image purposes, otherwise you'll have an ORA-1555 Snapshot Too Old error message generated.

It's also the case that when LGWR flushes the log buffer, it flushes ALL previously unflushed transactions into the redo logs. That can include committed as well as uncommitted transactions. Therefore, the redo logs store the before and after image of ALL transactions, committed or uncommitted makes no difference (or, in your terms, 'active' or 'inactive').

Regards
HJR
> Thanks for any help?
> Y
>
Received on Tue Feb 12 2002 - 07:52:52 CST