If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

I am really amaized that such fundamental mechanism can cause such heated discussions.

Regards
Boris

Hi Boris,
First, this is not a heated discussion, here it is normal and healthy to exchange ideas sometimes with passion. But at the end of the day, everybody listen and express ideas always with a positive outcome and that's the continued process of learning regardless of the knowledge level each one have.

Sometimes, one can make mistake just to emphasize one point, and others may take opportunity to attack that mistake. And it happens here most of the time. If you were a member of this forum 4 or 5 years ago, then you will be amazed how the brilliants showcasing there knowledge into different topics and Tamil is just one of them.

Rey,
First, I absolutely support that such discussions are healty and lead to knolwedge for all of us, which is actually the purpose of the forum
2) My great respect to Tamil and his knowledge about paralellism, query performance, partitioning and many other topics. If this post somehow makes an impression in the opposite, my appoligies.
3) What I am surorised of is, that I did not got any support in what I am saying form any of the really good professionals wriring here (except ixion) nor got I an argumented post against what I say ( in wihch case I would accept that as I have done before, when I have posted wrong answers)
4) I really regret that I did not knew abou this forum 5 years ago, because I enjoy my stay here and the discussions with you

I'm not saying that you are consistently right all along. For all I know Tamil might be explaining it correctly, it could be just your/mine/other interpretation that leads to conclusion.

---------------
What I am surorised of is, that I did not got any support in what I am saying form any of the really good professionals wriring here (except ixion) nor got I an argumented post against what I say ( in wihch case I would accept that as I have done before, when I have posted wrong answers)
------
I really regret that I did not knew abou this forum 5 years ago, because I enjoy my stay here and the discussions with you
---------------

You are on the right way of waking the interest of this guys. Discuss topics that are really interesting. And that also goes for all the other members of this forum.

Be prepair first before raising questions, exhaust all effort first before asking questions. And not just rely on an instant answer, and please appreciate and recognize when somebody helps you. It is really deterring when somebody ask an answer to a problem, and can't even say "thank you" when problem was resolved.

I mean, when this guys started to talk, I become just one of the audience.
I haven't seen lately this type of atmosphere here, but I am hoping that it will come back.

QUOTE=tamilselvan---one doubt--urgent

Originally Posted by tamilselvan

Yes, you are partially right. In general, after a checkpoint, the redo in the redo log files is no longer needed for crash/instance recovery. However, the redo logs may be needed for instance recovery if ta single transaction's redo size is greater than the redo log file size.
For example, You update millions of rows in table and then before commit/rollback, the system crashed even though several log switches occurred during the update process.
Another example is when you clone the database, the recovery process may ask you to enter redo log files which are older than the current redo log.

The only way I can see is, do manual checkpoint, and switch log files.

You can experiment with a test case.
Update millions of rows.
Do log switch.
Shutdown abort
Start up the instance. Note down the recovery time.

Do the same exercise with "alter sytem checkpoint", and you see the vast difference in recovery time.

Does this mean that there is no situation when DBWn writes uncommitted data to disk..coz if during checkpoint, if only bufferes related to redo log files are return to disk...then since redo log files always hold committed data(LGWR writes data from redo log buffer to redo log file on commit)-- so there is no case of writing uncommitted data to disk or what?????????

Does this mean that there is no situation when DBWn writes uncommitted data to disk..coz if during checkpoint, if only bufferes related to redo log files are return to disk...then since redo log files always hold committed data(LGWR writes data from redo log buffer to redo log file on commit)-- so there is no case of writing uncommitted data to disk or what?????????

I don't think that is what he is saying at all;

DBWn will write to disk dirty blocks during a checkpoint. A dirty block is one that has been modified in memory and is not currently on disk. This includes uncommitted data.

Redo Log files hold change vectors for the modified blocks. They do not particularly distinguish between committed/uncommitted data. Only Changes to blocks this includes not only your table data blocks but also Undo blocks. (thats how it can rebuild the undo tablespace and perform transaction rollback).

The commit command guarantees that what your transaction has posted to the log buffers is on disk. The responsiblity for the write operation is LGWR. The commit command will not return until LGWR has flushed the log buffer to disk.

LGWR at the time of your commit will also write any other changes that are in the log buffers. These changes can be from other users and may or may not be completed transactions (ie committed).

Does this mean that there is no situation when DBWn writes uncommitted data to disk..coz if during checkpoint, if only bufferes related to redo log files are return to disk...then since redo log files always hold committed data(LGWR writes data from redo log buffer to redo log file on commit)-- so there is no case of writing uncommitted data to disk or what?????????

-------------------------------------------------

Does this mean that there is no situation
when DBWn writes uncommitted data to disk..
****wrong assumption
coz if during checkpoint, if only bufferes related
to redo log files are return to disk...
*****wrong assumption
then since redo log files always hold committed data
*****wrong assumption
(LGWR writes data from redo log buffer to redo log file on commit)
-- so there is no case of writing uncommitted data to disk or what?????????
*****wrong conclusion
-----------------------------------------------

First, Nice explaination ixion,

Janki_mehta,
Just to clarify your understanding about redo logs switch, checkpoints, DBWn, LGWR. I am open to any opinion, ideas.

I think Tamil also miss 1 point about the reason why a REDO LOG SWITCH OCCURS.
And that is when an ONLINE REDO LOG is full and it has to advance to the next LOG sequence.
When this happens a SLOW CHECKPOINT(others call it SOFT CHECKPOINT) happens implicitly.
The other type of SLOW CHECKPOINT happens as what TAMIL have explained earlier,
which I believe will only happened if you have not configured your db buffer cache properly,
or you have a very huge REDO LOG size that a SLOW CHECKPOINT must happen even before a REDO LOG becomes full.
So at the normal operation where in you have a good db buffer cache configuration and an adequate REDO LOG size,
the SLOW CHECKPOINT that Tamil have discussed is less likely to happen.

I'll show you why a DBWn also writes uncommitted data to disk/datafile.
And it also follows that during checkpoint not only buffers related
to redo log files are written to disk/redo log files but also the dirty blocks are also be disk/datafile.
I'll show you also why uncommitted data are also written to redo logs.

Example.
In a DB where in it is newly opened and no other users used, we are going to do a series of inserts into a single table.
Leaves it as uncommited, then issue a shutdown abort to the database. And let's see what will happen when we start the database.
Walkthrough yourself for explanation through item# INFO.

item#3 - a buffer busy waits - just to confirm that there was at least
- a wait events happened when the uncommitted transaction was made.
SQL> select class, count
2 from v$waitstat
3 where count > 0
4 order by count desc;

item#5 - After the DB was successfully reopen, it shows that redo log Group 2 SEQUENCE# 156
- was also archived immediately instead of putting it in ACTIVE status
- because it was suppose to be ARCHIVE if the DB was shutdown properly.
SQL> SELECT A.GROUP#, A.MEMBER, B.SEQUENCE#, B.BYTES, B.STATUS
2 FROM V$LOGFILE A, V$LOG B
3 WHERE A.GROUP# = B.GROUP#
4 /