just for the info sake.
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 31

Thread: just for the info sake.

  1. #1
    Join Date
    Aug 2001
    Location
    chennai,bangalore
    Posts
    840
    Hi friends,
    When does the commited transaction in the redo log file be written to the data file.

    cheese
    anandkl

    anandkl

  2. #2
    Join Date
    Mar 2002
    Posts
    171
    Committed transactions are not written from Redo Log files to datafiles.

    It is written from database-blocks of the "database buffer" in the SGA to the datafiles by the DBWR background process.

    This happens during timeout or whenever "dirty buffers" get filled in the "Dirty Buffer List."

  3. #3
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,591
    Committing Transactions
    Committing a transaction means making permanent the changes performed by the SQL statements within the transaction.

    Before a transaction that modifies data is committed, the following has occurred:

    Oracle has generated rollback segment records in rollback segment buffers of the system global area (SGA). The rollback information contains the old data values changed by the SQL statements of the transaction.

    Oracle has generated redo log entries in the redo log buffer of the SGA. The redo log record contains the change to the data block and the change to the rollback block. These changes may go to disk before a transaction is committed.

    The changes have been made to the database buffers of the SGA. These changes may go to disk before a transaction actually is committed.

    ***Note: ****
    The data changes for a committed transaction, stored in the database buffers of the SGA, are not necessarily written immediately to the datafiles by the database writer (DBWn) background process. This writing takes place when it is most efficient for the database to do so. It may happen before the transaction commits or, alternatively, it may happen some time after the transaction commits.

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


    /* AND ALSO TO YOUR PREVIOUS Q ABOUT THE TRANSACTION DEFFERING,ALTHOUGH IT IS CNSTR DEFERRING.......... */



    Discrete Transaction Management
    Application developers can improve the performance of short, nondistributed transactions by using the BEGIN_DISCRETE_TRANSACTION procedure. This procedure streamlines transaction processing so that short transactions can execute more rapidly.

    During a discrete transaction, all changes made to any data are deferred until the transaction commits. Of course, other concurrent transactions are unable to see the uncommitted changes of a transaction whether the transaction is discrete or not.

    The following events occur during a discrete transaction:

    Oracle generates redo information, but stores it in a separate location in memory.

    When the transaction issues a commit request, Oracle writes the redo information to the redo log file along with other group commits.

    Oracle applies the changes to the database block directly to the block.

    Oracle returns control to the application after the commit completes.

    This transaction design eliminates the need to generate undo information, since the block is not modified until the transaction is committed, and the redo information is stored in the redo log buffers.

    There is no interaction between discrete transactions, which always generate redo, and the NOLOGGING mode, which applies only to direct path operations. Discrete transactions may therefore be issued against tables that have the NOLOGGING attribute set.


    COURTESY......
    http://otn.oracle.com/docs/products/...5trans.htm#222

    IT IS INETERESTING!
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  4. #4
    Join Date
    Mar 2002
    Posts
    171
    Tarry: You said "The data changes for a committed transaction, stored in the database buffers of the SGA, are not necessarily written immediately to the datafiles by the database writer (DBWn) background process. This writing takes place when it is most efficient for the database to do so. It may happen before the transaction commits or, alternatively, it may happen some time after the transaction commits"


    Does this mean that -- those transactions that went to the disk (datafiles) before getting committed are still available say after a Power Failure? Can you kindly substantiate this?

  5. #5
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,591
    Where would u find the info of these transactions which were written before being committed?

    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  6. #6
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,591
    Or more specific asnwer to anand's Q about oracle's write ahead logging logic.

    The database writer writes modified blocks from the database buffer cache to the datafiles. Since Oracle uses write-ahead logging, DBWn does not need to write blocks when a transaction commits. Instead, DBWn is designed to perform batched writes with high efficiency. In the most common case, DBWn writes only when more data needs to be read into the system global area and too few database buffers are free. The least recently used data is written to the datafiles first.



    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  7. #7
    Join Date
    Mar 2002
    Posts
    171
    Tarry: Since your post mentioned that even UNCOMMITTED data gets inside datafiles --

    Lets say for instance you INSERTED 10 Rows into EMP Table and they were NOT COMMITTED. But lets assume these records went to the datafiles (as you mentioned).

    Assume a power failure at this juncture. Next time when you start the database and select from EMP, do you think those rows would be there?

    This question arised basically because you mentioned that even UNCOMMITTED rows go to datafiles, besides COMMITTED.

    Hope I've made the question clear.

  8. #8
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,448
    when checkpoint occurs

    there are uncommited data on datafile and in case of a power failure smon will do instance recovery which includes applying redo logs, rebuilds RBS and rolls back uncommited transactions

  9. #9
    Join Date
    Jan 2002
    Location
    Netherlands
    Posts
    1,591
    They won't be SEEN. but the instance recovery initiated by the SMON will do the following.................

    Due to the way in which DBWn writes database buffers to datafiles(TARRY SAYS: here i would assume that the write ahead logging forced by sga flooded with subsequent queries,and limited space in the db buffer,will prompt the DBWn to start writing modifictions the last firsts ...how don't ask me this is what oracle says, maybe the experienced oracle guys can explain why that happens) being read at any given time a datafile may contain some tentative modifications by uncommitted transactions and may not contain some modifications by committed transactions. Therefore, two potential situations can result after a failure:

    Data blocks containing committed modifications were not written to the datafiles, so the changes may only appear in the redo log. Therefore, the redo log contains committed data that must be applied to the datafiles.

    Since the redo log may have contained data that was not committed, uncommitted transaction changes applied by the redo log during recovery must be erased from the datafiles.

    To solve this situation, two separate steps are always used by Oracle during recovery from an instance or media failure: rolling forward and rolling back.

    Rolling Forward
    The first step of recovery is to roll forward, that is, reapply to the datafiles all of the changes recorded in the redo log. Rolling forward proceeds through as many redo log files as necessary to bring the datafiles forward to the required time.

    If all necessary redo information is online, Oracle rolls forward automatically when the database starts. After roll forward, the datafiles contain all committed changes as well as any uncommitted changes that were recorded in the redo log.

    Rolling Back
    The roll forward is only half of recovery. After the roll forward, any changes that were not committed must be undone. After the redo log files have been applied, then the rollback segments are used to identify and undo transactions that were never committed, yet were recorded in the redo log. This process is called rolling back. Oracle completes this step automatically.


    Hope that explains it.
    Tarry Singh
    I'm a JOLE(JavaOracleLinuxEnthusiast)
    TarryBlogging
    --- Everything was meant to be---

  10. #10
    Join Date
    Mar 2002
    Posts
    171
    Wonderful Tarry. Amazing explaination. Thanks for the same.

    However, this leaves another Question for me: If redo log contained committed and uncommitted data -- Why did they design such that Oracle Rolls-back after Roll Forward? Why not retain even the uncommitted?? Wouldn't this have been a big advantage during catastrophic instances? Tarry, I would really appreciate if you could shed some light over this.

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