interaction between LGWR and DBWR
Umar FArooq
Can someone please tell me how LGWR and DBWR interact...do they flush
their respective buffers simultaneously....what happens if DBWR has
flushed the data and the system crashes before LGWR could flush the redo
buffer...is such a scenario possible?
Thx.
Umar Farooq Ch.
umar....@cressoft.com.pk
Jared Hecker
No, LGWR flushes much more frequently. DBWR only flushes when a certain
amount oof it's buffers are dirty, or it needs
more-than--clean-buffers-available to satsify a query.
hth
jh
Umar FArooq (umar....@cressoft.com.pk) wrote:
: Can someone please tell me how LGWR and DBWR interact...do they flush
: Thx.
: Umar Farooq Ch.
: umar....@cressoft.com.pk
--
Jared Hecker | HWA Inc. - Oracle architecture and Administration
ja...@hwai.com | ** serving NYC and New Jersey **
Tanya Injac
Hi, Umar.
1.
LGWR and DBWR will write data simultaneously just in the case of a
checkpoint. Then, LGWR sends DBWR list of modified buffers that have to be
written on the disks.
2.
Yes, the scenario where some data are written on the disks, but instances
crashed before LGWR flushed log buffers to redo log files is possible. Lets
say that happened for an uncommitted transaction X.
During the instance recovery, smon will roll forward all data up to the
consistent SCN for all transactions. That latest SCN is the SCN from the
redo log files. After the "rolling forward", the old rollback data (with
the changes from transaction X) will still be in the database. During the
"rolling backwards", oracle looks at the outstanding transactions from
rollback segments (one of them is transaction X) and rolls back them. So,
your data from transaction X will be lost.
Regards,
Tanya
--
Tanya Injac
Oracle Developer/DBA
Unisys NZ
Umar FArooq <umar....@cressoft.com.pk> wrote in article
<355ADCE8...@cressoft.com.pk>...
Tanya Injac
The fact is the instance recovery is not clearly documented, at least not
in the manuals I have.
The other possible solution might be: no dirty buffers are written to the
disk without log buffer being previously flushed to the file.
But, that contradicts with the fact that DBWR can write dirty buffers if
the threshold is reached for the dirty buffers or there are no free buffers
or time-out happens ( found in Oracle Server Concept manual 7.3 (chapter
9-7)). It does not say anywhere that writing of dirty buffers on the disk
causes the flushing of the log buffers.
I would like to hear opinions of the other people.
Umar FArooq
Tanya Injac wrote:
> During the
> "rolling backwards", oracle looks at the outstanding transactions from
>
> rollback segments (one of them is transaction X) and rolls back them.
> So,
> your data from transaction X will be lost.
>
ok..so the rollback segments are not flushed until a corresponding redo
log entry is made..am i right?
Umar Farooq Chaudhri.
Software Engineer
Crescent Software Products (Pvt.) Ltd.
Lahore, Pakistan.
umar....@cressoft.com.pk
Tanya Injac
Umar,
I got e-mail from one DBA. I'll be free enough to put his answer here:
"There is a description in one of the manuals, but don't ask me which,
which covers this exactly as you have
surmised.
In fact, when DBWR has to write blocks for which the log is not on disc
DBWR calls LGWR to write first so that LGWR is always ahead of DBWR.
If memory serves correctly the statistics 'redo log synch writes'
is the one which tells you how many times this has happened. "
------------------------------------
Manual (for Oracle 7.3) says that "rollback segments which contain DEAD
transactions will be put as PARTIALLY UNABLE". So, I thought those
rollback segments would not be flushed and "rolling forward" would use
the other rollback segments.
I contacted Oracle help desk about this question because at the end I
was not sure what's really going on. They told me: DBWR will never
write down dirty buffers without initiating LGWR to write log buffers.
So, according to them, situation where DBWR writes down dirty buffers
before LGWR writes down redo entries is impossible.
I trust them, but I would like to see that written down in the manuals.
That is important fact and I just don't know how they could have missed
it ?!
Regards,
Tanya
----------------------------------
The text sent by Oracle help desk:
Rolling Forward and Rolling Back
Database buffers in the SGA are written to disk only when necessary,
using a least-recently-used algorithm. Because of the way that DBWR uses
this algorithm to write database buffers to datafiles, datafiles might
contain some data blocks modified by uncommitted transactions and some
data blocks missing changes from committed transactions.
Two potential problems can result if an instance failure occurs:
Data blocks modified by a transaction might not be written to the
datafiles at commit time and might only appear in the redo
log.Therefore, the redo log contains changes that must be reapplied to
the database during
recovery.
Since the redo log might have also contained data that was not
committed, the uncommitted transaction changes applied by the redo log
(as well as any uncommitted changes applied by earlier redo logs) must
be erased from the database.
To solve this dilemma, two separate steps are generally used by Oracle
for a successful recovery of a system failure: rolling forward with the
redo log and rolling back with the rollback segments.
The Redo Log and Rolling Forward
The redo log is a set of operating system files that record all changes
made to any database buffer, including data, index, and rollback
segments, whether the changes are committed or uncommitted. The redo log
protects changes made to database buffers in memory that have not been
written to the datafiles.
The first step of recovery from an instance or disk failure is to roll
forward, or reapply all of the changes recorded in the redo log to the
datafiles. Because rollback data is also recorded in the redo log,
rolling forward also regenerates the corresponding rollback segments.
Rolling forward proceeds through as many redo log files as necessary to
bring the database forward in time. Rolling forward usually includes
online redo log files and may include archived redo log files.
After roll forward, the data blocks contain all committed changes as
well as any uncommitted changes that were recorded in the redo log.
Rollback Segments and Rolling Back
Rollback segments record database actions that should be undone during
certain database operations. In database recovery, rollback segments
undo the effects of uncommitted transactions previously applied by the
rolling forward phase.
After the roll forward, any changes that were not committed must be
undone. After redo log files have reapplied all changes made to the
database, then the corresponding rollback segments are used. Rollback
segments are used to identify and undo transactions that were never
committed, yet were recorded in the redo log and applied to the database
during roll forward. This process is called rolling back.
Oracle can roll back multiple transactions simultaneously as needed. All
transactions system-wide that were active at the time of failure are
marked as DEAD. Instead of waiting for SMON to roll back dead
transactions, new transactions can recover blocking transactions
themselves to get the row locks they need.
This feature is called fast transaction rollback.
--------------------------
Raoul De Kezel
In article <01bd7fb4$00f2e0c0$6762...@nz6220.nz.unisys.com>,
tanya...@unn.unisys.com says...
> The other possible solution might be: no dirty buffers are written to the
> disk without log buffer being previously flushed to the file.
> But, that contradicts with the fact that DBWR can write dirty buffers if
> the threshold is reached for the dirty buffers or there are no free buffers
> or time-out happens ( found in Oracle Server Concept manual 7.3 (chapter
> 9-7)).
I don't see the contradiction. Could you explain it in more details ?
> It does not say anywhere that writing of dirty buffers on the disk
> causes the flushing of the log buffers.
Well, I read in Oracle Server Concept manual 7.3, page 9-9:
"LGWR writes ... redo buffers when the DBWR process writes modified
buffers to disk", which looks to me quite similar to your statement,
isn't it ?
Best regards
--- Raoul