undo表空间的bug

转自：阿里巴巴的文章

Oracle10g的自动undo retention调整特性

March 12th, 2008

Oracle10g中引入了一个新的自动调整undo retention的特性，本意是为了尽量避免ora-01555错误，但是自动的东西，有时候会不可避免的聪明过头，这个特性容易导致undo表空间过度使用无法回收。

在Oracle10gR2中只要使用了自动undo表空间管理，不管设置undo_retention为多少，自动undo retention特性都会启用。这时MMON进程每隔30秒会根据maxquerylen计算出一个tuned undo retention，然后将系统的undo retention设置为该值。如果undo tablespace的datafile是不能自动扩展的话，可能触发bug 5387030，tuned undo retenttion会变得非常大，导致undo表空间长时间无法回收空间。

通过以下查询可以查看tuned undo retention的值：

select tuned_undoretention, maxquerylen, maxqueryid from v$undostat;

在我们的一个案例中这个值最大达到了345600也就是96小时，使得undo表空间在事务比较频繁的情况下很快达到了100%的使用率，导致监控短信频繁响起。

知道了原因，解决方案也就有了：

1. 10.2.0.2/10.2.0.3有相应的patch，这个bug在10.2.0.4中已经修复，建议找时间停机打patch
2. 设置隐含参数_smu_debug_mode=33554432，将tuned_undoretention取值算法修正为max(maxquerylen secs + 300,undo_retention )，不建议使用
3. 设置隐含参数_undo_autotune=false，关闭自动undo retention调整特性，不建议使用

参考：
Note:461480.1 FAQ – Automatic Undo Management (AUM) / System Managed Undo (SMU)
Note:240746.1 10g NEW FEATURE on AUTOMATIC UNDO RETENTION
Bug 5387030 – Automatic tuning of undo_retention causes unusual extra space allocation

根据上述问题，和后面的人的讨论。

“这个bug我也遇到过,可以采用把datafile设置为自动扩展,然后设置个maxsize,这样就能暂时避免这个bug了,undo表空间也会自动收缩了”  来自于汪磊的表述。

我也就做了如下的尝试。

ALTER TABLESPACE UNDOTBS1 ADD DATAFILE '/data/ora11g/oradata/oracle9i/undotbs04.dbf'SIZE 8192M

AUTOEXTEND ON

NEXT 5M

MAXSIZE 65535M;

由于我之前使用的是固定表空间，所以它无法释放。现在改成了自动扩展的，所以表空间，第二天释放了。

有图有真相：

昨天的undo表空间的情况：

TABLESPACE_NAME                 TATOLSIZE   USEDSIZE   FREESIZE USEDPERCENT FREEPERCENT
------------------------------ ---------- ---------- ---------- ----------- -----------
UNDOTBS1                            49152  45180.375    3971.63       91.92        8.08

今天的undo表空间的情况：

TABLESPACE_NAME                 TATOLSIZE   USEDSIZE   FREESIZE USEDPERCENT FREEPERCENT
------------------------------ ---------- ---------- ---------- ----------- -----------
UNDOTBS1                            57344       2680      54664        4.67       95.33

从只剩下8.03%到马上就有了95.33%，都证明了上述的轮调。说明oracle的bug一直都是存在的。哎。只要是固定的话，undo表空间就是不好使。oracle的版本是11.2.0