Undo Management

Undo What is that

Configuring Undo

Sizing Undo Tablespaces

Undo Advisor

3. .

5. Undo, why undo ?

7. We require undo for following

9. Read Consistency : Read consistency gives your query the output

10. as it was at that point in time

12. Example : Imagine you issued select query which takes 4-5 min to

13. execute, and on the second minute someone modified the data by

14. issuing update command, inspite of this the first query's output is

15. unaffected. That is because the before modified image is stored

16. in the undo

18. Rollback Transaction : To undo any modified data Example suppose

19. you have modified any data and then you change mind not to modify

20. the data, then you could undo that.

21. When a rollback statement is issued, undo records are used to undo changes that were made to the

22. database by uncommited transaction.

24. Recover Database : During recovery undo records are used to undo

25. any uncommited changes applied from the redo logs to the datafiles.

27. Flashback Query : Here you could get the image of the database as

28. it was before some time. say 15-20 minutes if undo is configured properly

32. Configuring Undo

35. We have three imp undo parameter for configuration

37. undo_management = auto

38. undo_tablespace = <undo tablespace name>

39. undo_retention = <time in seconds>

41. Undo Management : Setting it to auto lets oracle manage the undo

42. Undo Tablespace : Undo tablespace which will be active.

43. Undo retention : Time in seconds til which oracle will store the undo

44. information in tablespace.

51. Sizing Undo Tablespaces

53. Oracle provides tool v$undostat, it stores undo block generation data,

54. which are taken at 10 minutes interval. In the v$undostat table you

55. would find column undoblks which is in bytes.

57. Two parameter vital for preventing errors related to undo are

58. 1) undo retention

59. 2) Undo tablespace size

60. SQL>select max(maxquerylen) from v$undostat;

61. above query gives us the undo retention, now we have to calculate the

62. size of undo

63. For getting the undo size we again query the v$undostat, which

64. store the undo blocks generated in 10 minutes interval

65. Note : v$undostat keeps 4 days of information

66. Now,

67. undo size = transaction rate * block size * undo retention

68. select max(undoblks) from v$undostat;

69. transaction rate = [select max(undoblks) from v$undostat;] / 600

70. Now undo size = transaction rate * block size * undo retention

71. Monitor Undo status

73. select status , sum(blocks)*8192/1024/1024/1024 GB from dba_undo_extents group by status;

75. Additional Details

77. Each transaction is assigned to only one undo segment,however one undo segment can be

78. assigned to multiple transaction at a given point of time, to check which transaction have

79. which undo segments assigned query the v$transaction view.

81. Undo segments expand and contract automatically as needed and act as circular storage

82. buffer for transactions

84. Transaction fills extents in its undo segment until a transaction is completed or all space is

85. consumed.

87. When an extend fills up and more space is needed, the transaction will acquire more space

88. from next extent in the segment.

90. When all extent in the undo segment are consumed, the transaction overwrites undo data

91. which is no longer needed. Undo tablespace contain undo segments, an instance can

92. have only one active undo tablespace at a time

94. Undo segments are owned by SYS and they have segment type "TYPE 2 UNDO"

95. Undo tablespace are specialized, permanent tablespace with automatic

96. extent allocation.

98. There are only two this one needs to know to monitor undo

99. 1) Undo space

100. 2) Snapshot to old error

105. UNDO Space : when huge transaction takes place in the database, this requires huge space

106. in the undo tablespace to store the original data

108. insert operation requires very little undo space

109. delete operation requies huge undo space

110. when undo tablespace runs out of space, the oracle server raises ORA-01650

111. unable to extend rollback segment

112. Snapshot to old error : Here when a query tries to access data that no longer exists in the

113. undo tablespace, this happens when a query takes long time to execute, here we get ORA-1555

114. snapshot too old error

115. Undo management.

117. Two Types Of Undo management

119. Automatic : Here the original data is stored in undo segment

120. Oracle server determines the time period for which the undo

121. data is retained in undo segments, data is stored in the undo

122. segments even after the transaction is complete (called unexpired data)

124. Undo retention : Amount of time for which the commited undo data should be retained in the

125. undo tablespace, after which it will expire or will be overwritten. In Automatic undo management

126. undo is retained till it expires, however if an active transaction required more space the

127. commited but unexpired data is overwritten. If undo retention is set to 0, automatic undo retention

128. tuning is enabled, this retains undo information as required to support longest running query

130. Manual : The segment where the original data is stored is called as rollback segment

131. Undo data falls in three categories

132. 1) Uncommited undo info for active transaction, used for rollback or recovering failed transaction,

133. this is never overwritten

134. 2) Commited undo : These is retained to meed the retention period

135. 3) Expired undo : these is not required to support an active transaction and is overwritten when

136. an active transaction needs space and no free is available When space is required by active

137. transaction and there is no free space Commited undo data is overwritten instead of failing

138. the transaction. However if the retention_guarantee is set the, these would not happen and lets

139. transaction to fail for lack of undo space.