1 2 3 Previous Next 36 Replies Latest reply on Dec 23, 2010 12:44 PM by PAB Go to original post
      • 15. Re: In Memory Undo
        Hi Dan,
        damorgan wrote:
        No I mean in any context no matter how you define it.

        In Oracle the redo stream ONLY contains committed transactions.

        We do not use Streams or Data Guard to replicate uncommitted transactions.
        We do not use Redo Log Files and Archived Redo Log Files to recover uncommitted transactions.
        Not in Oracle.
        Sorry, Dan, but the above is just plain wrong. Uncommitted data can and will get written to the online redo log files. If that uncommitted data is later rolled back, Oracle will rollback the changes by referring to undo.

        In the event of an instance crash, during instance recovery, Oracle will read the file headers, determine which files need to be recovered to what SCN, then read the redo, apply all changes in redo, up to the point in time of the instance crash, then read undo headers, find all uncommitted transactions, and roll them back.

        Uncommitted data can and will be written to redo log files, but, it's ok. In the future, that uncommitted data will either get committed or get rolled back, and the state of the database will be preserved.

        For an Oracle Documentation reference, see here:
        Scroll down to "Redo Log Contents", and see the fourth paragraph.
        Start reading here:

        Continue here:
        I checked those links briefly, skimmed over them, and I didn't see anything that explicitly supports your statement. While it's certainly true that committed data must be written to redo, (in fact, the commit isn't complete till LGWR signals the user's server process that the redo write is complete), there's nothing that says uncommitted data will not be written to redo. It can and will happen.
        How much redo do you think you generate if you update 1,000,000 rows and follow it with ROLLBACK rather than COMMIT?
        In the event of an update, I'd expect at least as much redo generated for rollback as commit. In the event of an insert, I'd expect rollback to be less than insert, and for a delete, I'd expect rollback to be more than insert.

        • 16. Re: In Memory Undo
          I stand corrected. I forgot about changes to undo segments.
          • 17. Re: In Memory Undo
            Kamran Agayev A.
            Agree with Bobak

            I've asked this question from Tom Kyte, please read his answer below:


            - - - - - - - - - - - - - - - - - - - - -
            [Kamran Agayev A. |http://kamranagayev.wordpress.com] (OCP 9i/10g)
            Author of "Oracle Backup & Recovery: Expert secrets for using RMAN and Data Pump"
            • 18. Re: In Memory Undo
              Maran Viswarayar
              In Oracle the redo stream ONLY contains committed transactions.
              It cant be

              Assume i update a millions of records and the log buffer is 5MB ...and the update takes 15 minutes..

              Do you think nothing will be written in the redolg...

              The logwrites writes the contents of redolog buffer every 3 seconds along with another 3 events which triggers the logwriter...

              It writes all the change vectors into the redolog file
              • 19. Re: In Memory Undo
                Why in Oracle its so difficult to retain the basics?
                or Why in Oracle its so easy to forget the basics?
                Earlier I remember in one of the threads i read Sybrand pointed out , when datafile is in begin backup mode,it is not written??
                • 20. Re: In Memory Undo
                  Pavan Kumar

                  Now, the topic diverts to Fractured blocks and it' concepts.

                  - Pavan Kumar N
                  Oracle 9i/10g - OCP
                  • 21. Re: In Memory Undo
                    Kamran Agayev A.
                    Earlier I remember in one of the threads i read Sybrand pointed out , when datafile is in begin backup mode,it is not written??
                    It's incorrect. They are written. See the following discussion of Tom Kyte

                    - - - - - - - - - - - - - - - - - - - - -
                    [Kamran Agayev A. |http://kamranagayev.wordpress.com] (OCP 9i/10g)
                    Author of *"Oracle Backup & Recovery: Expert secrets for using RMAN and Data Pump"*
                    • 22. Re: In Memory Undo
                      Maran Viswarayar
                      This link will provide all the necessary information

                      • 23. Re: In Memory Undo
                        I know what happens in begin backup mode.I know the necesary basics.
                        Whqat i wanted to say was as damorgan said redo streams contain only committed data.Likewise Sybrand once said, a datafile is not written in backup mode. All these are senior people with far more knowledge than us but they too can forget the basics not only us.So thats why I said ...
                        • 24. Re: In Memory Undo
                          Maran Viswarayar
                          Anyone can forget anything at anytime...Its just that simple...We are human... :)
                          • 25. Re: In Memory Undo
                            Nah!! You cannot forget the basics. Its not permissible :)
                            • 26. Re: In Memory Undo
                              Permissible, but only when you are ready to come to my classes after forgetting it ;-) .

                              • 27. Re: In Memory Undo
                                Are you an instructor in india?
                                • 28. Re: In Memory Undo
                                  Jonathan Lewis

                                  I think Craig has described in-memory undo incorrectly.

                                  Essentially+ the content of the undo blocks and the content of the redo log file are the same whether or not you use in-memory undo (and private redo threads) or traditional redo logging. The main difference is one of timing and ordering; there is also a reduction in redo record+ count with in-memory undo, though the number of redo change vectors+ remains the same.

                                  With a traditional small transaction the steps are as follows:
                                  you change a table or index block - this requires you to generate a redo change vector
                                  at the same time you have to record enough information to rollback the change - this is an undo record
                                  but an undo record is stored in an undo block, so you generate a redo change vector to describe the change you make to the undo block
                                  Oracle combines the two change vectors into a single redo record - which is reported in the session statistic as an increment to "redo entries"
                                  to make the change Oracle uses the redo allocation and redo copy latches to acquire space and copy the record into the redo buffer.

                                  Say you inserted 10 rows, one at a time, into a table with 4 indexes - this would generate 50 redo records and 50 undo records, and hit the redo latches 50 times. (That's 5 records per row, one for the table row, one each for each of the indexes - times 10).

                                  With In-memory undo enabled - and since my example is a small enough piece of work:
                                  As you change the first table row Oracle allocates one private redo buffer (called a redo strand) and one private "undo" buffer from the shared pool.
                                  The "in memory undo" buffer is actually a buffer for the redo that describes the changes to the undo.
                                  As you make your changes to the table and indexes, each change vector is written into the private redo buffer
                                  At the same time, each change vector that describes the corresponding undo record is written into the "in memory undo" buffer.
                                  The total number of change vectors, and the content of those change vectors is exactly the same as in the traditional change vectors
                                  When you commit, Oracle concatenates the two buffers into a single large redo record and writes it into the redo buffer
                                  At the same time all 100 change vectors are applied to the table, index, and undo blocks (and all the other stuff relating to commits also happens).
                                  Consequently the final number of "db block changes" is the same in either case
                                  The most significant difference in volume of redo is that the traditional method results in 50 redo record headers (600 bytes) whereas the in-memory undo produces only one redo record header (12 bytes).

                                  There are lots of little details and variations around the edges - start and end of transaction, for example, and what happens if one of the two buffers fills (because they're only 64KB or 128KB in size) - but the description above covers the most significant differences in the strategy.

                                  Jonathan Lewis

                                  To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                                  {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                                  fixed format
                                  "Science is more than a body of knowledge; it is a way of thinking" 
                                  Carl Sagan                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                  • 29. Re: In Memory Undo
                                    Thanks so much for the clarification sir.

                                    Best regards