凌晨3点多,接到客户电话,说是昨天晚上数据库杀了一个会话(会话已经报错),然后这个会话产生的UNDO数据一直在回滚,从晚上九点开始,占用大量UNDO表空间,新的会话上去执行SQL的时候会报无法分配UNDO表空间的错误。
凌晨3点多,接到客户电话,说是昨天晚上数据库杀了一个会话(会话已经报错),然后这个会话产生的UNDO数据一直在回滚,从晚上九点开始,占用大量UNDO表空间,新的会话上去执行SQL的时候会报无法分配UNDO表空间的错误。我们知道,诸如Insert,Delete,Update等操作都会在UNDO表空间其中的一个回滚段里面分配相应的空间以便生成UNDO回滚信息。当我们Rollback或者使用版本查询、闪回表的时候都要用到UNDO信息,这里就有一个矛盾,如果UNDO信息保存的时间越长,那么这些特性被支持的力度也就越大,这个是我们很愿意看到的,但是凡事都有两面性,在支持这些特性的同时,也会有相应的消耗,而且如果保持太长时间的信息的话,成本将成倍的增加。
这也就是我们要知道一个合适的平衡点儿,既可以最大限度的满足我们对这些特性的需求,同时也减少存储的需求。
另外一方面,如果这个时间设置的过小,那么当我们的一个比较长的SQL执行时间超过这个参数的时候,我们就会获得一个ORA-01555快照过旧的错误,更何况我们的系统是一个OLAP系统。
其实最好的办法是新建一个UNDO表空间,然后将新的UNDO表空间指定到当前实例上,让老的UNDO表空间慢慢回滚,或者在原来的UNDO表空间里面增加数据文件。
但是这两种方法都需要新的存储空间支持。并且第二种在新增到原有UNDO表空间中以后想要缩小UNDO就很困难了。
对于UNDO表空间,好像Oracle并不想提供太多的管理手段给DBA,也行这也是为了保证数据的完整性,而做出的妥协吧。
在现场,我们试了将参数 UNDO_retention从原来的20800缩小到8000
SQL> alter system set UNDO_RETENTION=8000 sid='dw2' scope=both;
但是经过观察,效果并不明显,后来知道从Oracle 10g开始,有一个_undo_autotune的参数,根据undo表空间使用情况自动控制undo_retention的值,也就是在UNDO表空间自动扩展的时候,保证undo_retention设置的值为最低阀值,然后根据需要扩展UNDO表空间,如果UNDO表空间AutoExtend为OFF,那么就根据UNDO STATUS的信息来动态的设置undo_retention的值,那么问题就来了,我们系统中 _undo_autotune的值是TURE,也就是说undo_retention的值是由系统来决定的,我们所做的修改根本就没有作用。
根据查询系统的视图得知,该回滚大概有220万个blocks需要回滚,每小时大概20万个,也就是说需要11到12个小时才能回滚完。
在不增加UNDO表空间或者不切换UNDO表空间的前提下,自动管理UNDO的模式下,实在是没有什么好办法可以做到在快速释放UNDO空间(Oracle要保证回滚完成,以保证数据完整性,如果会话在则有会话进程来完成回滚,否则由SMON进程来完成)
我们做到只能是等待回滚完成,及时关闭节点,在重启以后数据库依然要完成回滚才可以的。如果大家有什么好办法,欢迎一起来讨论
我的建议是修改_undo_autotune参数为False,然后可以适当的缩小undo_retention的值,如果达不到预期的话,可以通过新建UNDO表空间并且替换之的办法来解决问题.
SQL> create undo tablespace UNDO005 datafile '.......' size 20G autoextend off;
SQL> alter system set undo_tablespace=UNDO005;
等待到回滚完成以后再切换回来原来的UNDO表空间.
SQL> alter system set undo_tablespace=UNDO002;
建议:这种类型的大型数据库,因为每个SQL执行时间都比较长,数据量比较大,有些一个语句都会参数几十个G的UNDO数据,所以建议在回滚开始的时候,切换一个备用的UNDO表空间,让其慢慢的回滚,待回滚完毕之后,再切换回来,当然,如果回滚段比较大,不影响使用的情况下也可以让之慢慢回滚,实在是想不出什么好的办法,如果大家有好的办法,不妨分享一下.
原文: http://blog.itpub.net/469356/viewspace-758711