when log switch occurs.... - Page 3
DBAsupport.com Forums - Powered by vBulletin
Page 3 of 4 FirstFirst 1234 LastLast
Results 21 to 30 of 39

Thread: when log switch occurs....

  1. #21
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Quote Originally Posted by Bore
    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.
    ---------------

  2. #22
    Join Date
    Nov 2006
    Location
    Sofia
    Posts
    630
    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

    Regards
    Boris

  3. #23
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Boris,

    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.
    ---------------

  4. #24
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,136
    Bore and Reydp,

    In my opinion, both of you are good.

    I always welcome healthy criticism, comments and views. I may also commit mistake because Oracle RDBMS is becoming big and big after every release.

    Some time we may have to unlearn what we have learned earlier.

    Thanks.

  5. #25
    Join Date
    Feb 2007
    Posts
    212
    maybe an information overload dear

  6. #26
    Join Date
    Dec 2005
    Posts
    195
    I read the whole thread and it is very interesting. I learned the slow checkpointing from this thread. Thank you all for exchaing useful information.

    I have one question for Tamil. You are saying about INIT parameter DB_CHECKPOINT_BATCH_SIZE. I tried to set this parameter. But it is not taking.

    Here is the oracle version i am using.

    Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
    PL/SQL Release 9.2.0.1.0 - Production
    CORE 9.2.0.1.0 Production
    TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
    NLSRTL Version 9.2.0.1.0 - Production

  7. #27
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    its not a parameter, thats why

  8. #28
    Join Date
    Feb 2007
    Posts
    20

    QUOTE=tamilselvan---one doubt--urgent

    Quote 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?????????

  9. #29
    Join Date
    Jun 2006
    Posts
    259
    Great discussion !!!


    Quote Originally Posted by janki_mehta
    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).


    HTH

  10. #30
    Join Date
    Sep 2001
    Location
    Makati, Philippines
    Posts
    857
    Quote Originally Posted by janki_mehta
    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#1 - a newly opened db without transaction - ONLINE REDO LOG info
    SQL> l
    1 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#
    SQL> /

    GROUP# MEMBER SEQUENCE# BYTES STATUS
    ---------- ------------------------------------ ---------- ---------- ----------------
    1 C:\ORACLE\ORADATA\HRISESM\REDO01.LOG 152 1048576 INACTIVE
    2 C:\ORACLE\ORADATA\HRISESM\REDO02.LOG 153 1048576 INACTIVE
    3 C:\ORACLE\ORADATA\HRISESM\REDO03.LOG 154 1048576 CURRENT

    --- here let's an INSERTs happens to a table--- uncommitted-----

    item#2 - a db with transaction(uncommited) before SHUTDOWN ABORT - ONLINE REDO LOG info
    SQL> /

    GROUP# MEMBER SEQUENCE# BYTES STATUS
    ---------- ------------------------------------ ---------- ---------- ----------------
    1 C:\ORACLE\ORADATA\HRISESM\REDO01.LOG 155 1048576 INACTIVE
    2 C:\ORACLE\ORADATA\HRISESM\REDO02.LOG 156 1048576 CURRENT
    3 C:\ORACLE\ORADATA\HRISESM\REDO03.LOG 154 1048576 INACTIVE


    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;

    CLASS COUNT
    ------------------ ----------
    data block 6

    ----------shutdown abort was issued --------

    ----------the db was restarted---------------

    item#4 - an alertSID.log shows that during instance recovery
    - a transaction recovery(roll forward) occured before database was completely open.
    - See Thread recovery: finish rolling forward thread 1
    - Thread recovery: 67 data blocks read, 65 data blocks written, 1821 redo blocks read
    - As you can see it reads both from the data blocks(datafile) and the redo blocks(redo log files) during instance recovery.
    ALTER DATABASE OPEN
    Beginning crash recovery of 1 threads
    Mon Apr 02 12:38:02 2007
    Thread recovery: start rolling forward thread 1
    Recovery of Online Redo Log: Thread 1 Group 2 Seq 156 Reading mem 0
    Mem# 0 errs 0: C:\ORACLE\ORADATA\HRISESM\REDO02.LOG
    Mon Apr 02 12:38:02 2007
    Thread recovery: finish rolling forward thread 1
    Thread recovery: 67 data blocks read, 65 data blocks written, 1821 redo blocks read
    Crash recovery completed successfully

    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 /

    GROUP# MEMBER SEQUENCE# BYTES STATUS
    ---------- ------------------------------------ ---------- ---------- ----------------
    1 C:\ORACLE\ORADATA\HRISESM\REDO01.LOG 155 1048576 INACTIVE
    2 C:\ORACLE\ORADATA\HRISESM\REDO02.LOG 156 1048576 INACTIVE
    3 C:\ORACLE\ORADATA\HRISESM\REDO03.LOG 157 1048576 CURRENT
    ---------------

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width