10 Replies Latest reply: Mar 20, 2014 10:01 AM by 1889767 RSS

    Long running transaction

    1889767

      Please answer my question I am really very confused.

       

      Two node RAC Database 11.2.0.3 

      By mistake on huge table delete was executed.After six hours we killed the session and then rollback started and degraded the performance.

       

      My question is below mentioned Action plan can stop the rollback.

       

      1.Delete commands running for about 6 hours from node 1.

      2. We  killed the session.

      3.Long rollback started and degrading the performance of node 1.

      4. Try to drop the object on which rollback is running but failed ( busy aquire lock error occur) as expected. (Note: Table is not important for us)

      4.We  aborted the node 1.

      5.We dropped the table from node2.(Successfully dropped)

      6 Start the node 1 instance.

       

      My question is simple when we start the instance rollback will happened? If happened then where? because table is already dropped from node 2.


      Thanks

      Usman

        • 1. Re: Long running transaction
          BPeaslandDBA

          If you killed the session that was running the long operation, Oracle has no choice but to roll back the work. As a general rule of thumb, I keep in mind that the rollback will take as long as the operation was running. So your 6 hour operation that was killed may take six hours to rollback. That is just a rule of thumb and is often wrong as the rollback can complete faster. But it won't be done in 1 minute. It will still take some time.

           

          A RAC database acts as if it were one database. It doesn't matter if you dropped from one node and then tried to drop from the other node. Oracle's transaction mechanisms work cross instances for a RAC database and make sure that all instances are producing a transactionally-consistent result. You will have to wait for the rollback to complete.

           

          HTH,

          Brian

          • 2. Re: Long running transaction
            1889767

            Thanks Brian


            Please clarify.

            when we tried dropping the table from node 1 error occured but after aborting instance 1 where rollback is running and then we drop the table from node 2 its successfully dropped

            When instance 1 start again how can rollback process rollback the remaining transaction when object is already dropped?

             

            Thanks

            • 3. Re: Long running transaction
              BPeaslandDBA

              An Oracle database needs to maintain transactional consistency. The fact that this is a RAC, multi-instance database does not change it. If a transaction is in the process of rolling back on node 1, and the instance on node 1 is terminated, then any other instance in the cluster can perform the work. This is because the Undo tablespace is on shared storage. The online redo logs are on shared storage. While each instance has its own undo tablespace and its own thread of redo, all instances can use them to perform recovery for a failed instance.

               

              > When instance 1 start again how can rollback process rollback the remaining transaction when object is already dropped?

               

              Probably because another instance is actively rolling back the process still.

               

              Cheers,

              Brian

              • 4. Re: Long running transaction
                Simo Kemppinen

                It is also good to remember that when you are running (big) transaction which makes changes (like delete).

                Database usually needs to resize your UNDO and TEMP tablespaces with autoextents (if they are not big enough already) and this will slow down transaction.

                So it is advisable to check and manually increase size (a lot bigger) of these tablespaces if they are getting full continuously. This way transaction will finish faster.

                 

                And if you only need to remove data from one big table then truncate table is something you might try. It does not generate undo/redo so you cannot rollback it but it is efficient and quick way to clean table.

                • 5. Re: Long running transaction
                  Jyoti Verma -Oracle

                  Were you able to successfully drop the table in question from the node 2 instance? If yes, was there any performance issue still seen after the table drop happened?

                  • 6. Re: Long running transaction
                    1889767

                    Thanks to All,

                     

                    Brian,

                    As you said that "after aborting the instance rollback will run from 2 instance "

                    when we tried to drop the object from node 1 (where rollback was running) error occured and I am not able to drop the table after aborting the node 1 table successfully dropped from node 2?If your statement is right then why we are able to drop the object from node 2 without error.

                     

                    Jyoti Verma,

                     

                    Yes we were drop the table successfully without error and then no wait shown in AWR report "Wait for undo" and node 1 started without and wait.

                    • 7. Re: Long running transaction
                      1889767

                      Brian,

                       

                      One more question when the one instance aborted redo will use the second instance but are you sure we can use aborting node undo tablespace also?

                      • 8. Re: Long running transaction
                        BPeaslandDBA

                        when we tried to drop the object from node 1 (where rollback was running) error occured and I am not able to drop the table after aborting the node 1 table successfully dropped from node 2?If your statement is right then why we are able to drop the object from node 2 without error.

                         

                         

                        It could be a timing issue with respect to when locks were released. At this point, I cannot say for sure as the rollback is completed and the locks have been released.


                        Cheers,
                        Brian

                        • 9. Re: Long running transaction
                          BPeaslandDBA

                          One more question when the one instance aborted redo will use the second instance but are you sure we can use aborting node undo tablespace also?

                           

                          All instances can use all undo tablespaces if needed.

                           

                          Cheers,
                          Brian

                          • 10. Re: Long running transaction
                            1889767

                            Thanks Brian

                             

                            I am preparing the environment and trying to reproduce the issue.