1 2 Previous Next 20 Replies Latest reply: Feb 23, 2013 10:13 AM by Lonion RSS

    uncommit change and redo record ?

    Lonion
      SQL> update t set a = 1 where b = 2;        ----> must have redo record
      2 rows updated.
      
      SQL> rollback;
      the above redo record that uncommit changed must be written from redo buffer to the online redo logfile.

      Question:
      why Oracle write the redo record that uncommit changed to the online redo logfile ? when it will be used ?

      Thanks advance.
        • 1. Re: uncommit change and redo record ?
          Mihael
          all changes should be recorded in order to database consistency during recovery
          • 2. Re: uncommit change and redo record ?
            Fran
            You are wrong.

            Sometimes LGWR writes redo log entries before a transaction is committed, not always. These entries become permanent only if the transaction is later committed. when you make the upgrade (and no commit yet), the original information are in undo tablespace not in online redo logs.

            LGWR writes into online redo logs when a commit is done, every 3 seconds, when redo log buffer is one-third full or if it's necessary when DBWn process writes modified buffers to disk.

            check:
            redo:
            http://docs.oracle.com/cd/B28359_01/server.111/b28310/onlineredo001.htm
            undo:
            http://docs.oracle.com/cd/B28359_01/server.111/b28310/undo001.htm
            • 3. Re: uncommit change and redo record ?
              JohnWatson
              Perhaps it will become clear if you consider that Oracle does not, in the true sense of the term, do rollback. Yes, there is a ROLLBACK command - but what it does is construct another set of DML commands that reverse the effect of the first set of DML commands. Then it COMMITs the entire set of statements as one transaction.
              So all the change vectors are needed for recovery.
              --
              John Watson
              Oracle Certified Master DBA
              http://skillbuilders.com
              • 4. Re: uncommit change and redo record ?
                Nicolas.Gasparotto
                Lonion wrote:
                ...
                Question:
                why Oracle write the redo record that uncommit changed to the online redo logfile ?
                Because oracle is optimistic and prefer to assume you'll commit which you usually do in most of cases.

                Nicolas.
                • 5. Re: uncommit change and redo record ?
                  JohnWatson
                  Fran wrote:
                  Sometimes LGWR writes redo log entries before a transaction is committed, not always. These entries become permanent only if the transaction is later committed. when you make the upgrade (and no commit yet), the original information are in undo tablespace not in online redo logs.
                  Well, not quite. The changes to the undo blocks are also protected by redo. So both the new values and the old values are present in the redo logfiles.
                  • 6. Re: uncommit change and redo record ?
                    Lonion
                    I know this . but when it(the redo record that uncommit change) can be used ? rollforward ? rollback ? Maybe it's rollforward ,look at the following:

                    From Here : http://docs.oracle.com/cd/B10500_01/server.920/a96533/instreco.htm#442821

                    Cache Recovery (Rolling Forward)
                    During the cache recovery step, Oracle 【applies all committed and uncommitted changes in the redo log files】 to the affected data blocks. The work required for cache recovery processing is proportional to the rate of change to the database (update transactions each second) and the time between checkpoints.

                    Transaction Recovery (Rolling Back)
                    To make the database consistent, the changes that were not committed at the time of the crash must be undone (in other words, rolled back). During the transaction recovery step, Oracle applies the rollback segments to undo the uncommitted changes. The work required to do transaction recovery is proportional to the number and size of uncommitted transactions when the system fault occurred.

                    From "Cache Recovery (Rolling Forward)" =>【applies all committed and uncommitted changes in the redo log files】
                    Question: why Rolling Forward applies uncommitted changes ?
                    • 7. Re: uncommit change and redo record ?
                      sonidba
                      Each and every change that is made , is written to the logfile regardless of whether you are going to commit or rollback it in the future. Even when you issue rollback, related information is written to the logfile. See,
                      When you issue update statement , al least two datafile will get updated in some near future. one datafile is related to undo tablespace( data is written to undo tablespace so that rollback can be performed. There are other reasons as well.) and other datafile is where the the data block actually resides.



                      Datafile is updated by DBWR duirng checkpoints. DBWR writes dirty buffers from the database buffer cache to the datafiles. These dirty buffers may contain data blocks(meaning, blocks from the datafiles), or undo blocks (meaning , block to be written to the datafile related to undo tablespace). Before writing these dirty buffers, change information is logged in the logfiles, and that change may be related to any datafile(including undo).
                      • 8. Re: uncommit change and redo record ?
                        JohnWatson
                        Lonion wrote:
                        I know this . but when it(the redo record that uncommit change) can be used ? rollforward ? rollback ? Maybe it's rollforward ,look at the following:
                        From "Cache Recovery (Rolling Forward)" =>【applies all committed and uncommitted changes in the redo log files】
                        Question: why Rolling Forward applies uncommitted changes ?
                        Forward recovery is done in mount mode (assuming single instance) and the recovery process has no idea of whether a change to a data segment or an undo segment has been committed: it is just a change. Rollback occurs after the database has been opened, when a lot more information is available.
                        --
                        John Watson
                        Oracle Certified Master DBA
                        http://skillbuilders.com
                        • 9. Re: uncommit change and redo record ?
                          Aman....
                          Lonion wrote:
                          I know this . but when it(the redo record that uncommit change) can be used ? rollforward ? rollback ? Maybe it's rollforward ,look at the following:
                          It;s for roll forward only . For roll backward oracle would ask for the images stored in the Undo Blocks.
                          From Here : http://docs.oracle.com/cd/B10500_01/server.920/a96533/instreco.htm#442821

                          Cache Recovery (Rolling Forward)
                          During the cache recovery step, Oracle 【applies all committed and uncommitted changes in the redo log files】 to the affected data blocks. The work required for cache recovery processing is proportional to the rate of change to the database (update transactions each second) and the time between checkpoints.

                          Transaction Recovery (Rolling Back)
                          To make the database consistent, the changes that were not committed at the time of the crash must be undone (in other words, rolled back). During the transaction recovery step, Oracle applies the rollback segments to undo the uncommitted changes. The work required to do transaction recovery is proportional to the number and size of uncommitted transactions when the system fault occurred.

                          From "Cache Recovery (Rolling Forward)" =>【applies all committed and uncommitted changes in the redo log files】
                          Question: why Rolling Forward applies uncommitted changes ?
                          Actually, Roll Forward doesn't care about that at all. The reason for doing the roll forward is to bridge the gap that exists between the data files and the control files because either there was an instance crash or there is a datafile restored from the backup . The roll forward would apply all the data for the missing transactions . The next step, roll backward , would start rolling back the transactions for which there is no Commit found in the transcation table , found in the Undo segment, pointer of which is going to be kept in the block itself. So once the roll forward and roll backward are complete, Oracle ensures that only the consistent data is ther ein the datafile (means which is committed) since roll forward makes the inconsistent data also come into the data file.

                          HTH
                          Aman....
                          • 10. Re: uncommit change and redo record ?
                            Mihael
                            Question: why Rolling Forward applies uncommitted changes ?
                            uncommitted changes are also written to datafiles during checkpoint
                            to achieve consistency, oracle should apply all changes during recovery
                            • 11. Re: uncommit change and redo record ?
                              sonidba
                              Now, I have nothing to write when Aman says.

                              Great explanation.
                              • 12. Re: uncommit change and redo record ?
                                Aman....
                                sonidba wrote:
                                Now, I have nothing to write when Aman says.

                                Great explanation.
                                Thanks for the kind words but please correct wherever is needed. I would sincerely appreciate that!

                                Aman....
                                • 13. Re: uncommit change and redo record ?
                                  Fran
                                  Well, not quite. The changes to the undo blocks are also protected by redo. So both the new values and the old values are present in the redo logfiles.
                                  Please explain it, in documentation tells that LGWR "can", for example when activity is high.
                                  Tell me if I am wrong but, I never read in any document that LGWR always writes redo to protect undo blocks.

                                  Always is nice learn something new :)
                                  • 14. Re: uncommit change and redo record ?
                                    Aman....
                                    Fran wrote:
                                    Well, not quite. The changes to the undo blocks are also protected by redo. So both the new values and the old values are present in the redo logfiles.
                                    Please explain it, in documentation tells that LGWR "can", for example when activity is high.
                                    Tell me if I am wrong but, I never read in any document that LGWR always writes redo to protect undo blocks.
                                    Actually, the redo data for the Undo is also just like the normal data block change. Undo blocks are nothing special than the normal data blocks except that they are not updated by the users directly. So any change done to them is going to create change vectors just like any change done by the normal user's dml's to the normal blocks.

                                    HTH
                                    Aman....
                                    1 2 Previous Next