UNDO表空间使用率99%的处理办法
巡检发现运营商某数据库的UNDOTBS1表空间使用率高达99%，用户对此比较担心，经过对UNDO表空间实际使用情况进行分析，认为这是Oracle 10gR2以后Automatic Undo Management(AUM)的机制引起的，属于正常的行为。Oracle 10gR2以后AUM的申请空间顺序如下：
1. 从当前事务的undo extent剩余空间申请。
2. 如果附近的undo extent已经expired，wrap到这个extent。
3. 如果附近的undo extent是unexpired，从undo表空间已分配的空间里申请。
4. 回收已经提交了且undo block标记为expired空间。
5. 如果表空间可以自动增长的，数据文件进行扩展。
6. 回收事务已经提交但是状态为unexpired的undo block，这个操作叫STEAL。
7. 回收还没有提交事务事务的undo block，这个操作容易导致ORA-1555错误。
8. 如果经过上述操作空间依然不能满足，报错UNDO表空间无法扩展。
尽管数据库设置了undo_retention参数，但是Oracle 10gR2以后这个参数只是一个最低值，实际保留多久由数据库动态调整。对于表空间是否自动增长，有两种不同的计算方法：
(1) 如果表空间是固定大小的，则根据剩余空间和当前每秒产生的undo block来计算，最大可能的保证不出ORA-1555错误，导致调整后的undo_retention远大于参数设定值。
(2) 如果表空间是自动增长的，则根据max(maxquerylength+300, undo_retetion)来计算保留时间
XXX数据库的UNDO表空间是不可自动增长的，undo_retention设定为1800秒。
select file_name,autoextensible,bytes,maxbytes from dba_data_files where tablespace_name='UNDOTBS1';
FILE_NAME AU BYTES MAXBYTES
------------------------------------------------- -- ---------- --------
+DG_DBFILE/scp11g/datafile/undotbs1.264.692904117 NO 6442450944 0
通过查询v$undostat发现，undo的实际保留时间是360多万秒，远远大于初始设定值，且没有发生steal from expired(第6步)和unexpired(第7步)，最大事务长度也不超过设定的1800s。
SELECT to_char(begin_time,'yyyy-mm-dd hh24:mi') begin_time,
to_char(end_time,'yyyy-mm-dd hh24:mi') end_time,
UNXPSTEALCNT,EXPSTEALCNT,UNXPBLKRELCNT,EXPBLKREUCNT,TUNED_UNDORETENTION
FROM v$undostat
order by begin_time;
因此可以说明目前undo表空间是足够大的，使用率100%完全是AUM的机制导致的，不会出现因UNDO表空间不足导致业务失败的问题。
解决方案:
当然表空间始终很高给监控带来困扰，为了让UNDO表空间较真实的反映实际使用情况，我们可以采用如下三种办法：
1. 修改UNDO表空间的数据文件为自动增长，并且设定maxsize为一个可以接受的值。
例如:
alter database datafile ‘+DG_DBFILE/scp11g/datafile/undotbs1.264.692904117’ autoextend on maxsize 8g;
如果只想给UNDO表空间6g，和现在的大小一样，也可以指定maxsize 6g，实际上根本无法扩展，但是数据文件属性是autoextend on，算法还是按自动增长的来。
2. 通过隐含参数改变数据库的调整undo_retention的策略
初始化参数文件中增加：
_smu_debug_mode=33554432
实际上方案1和2的效果是一样，就是根据max(maxquerylength+300, undo_retention)来计算实际undo_retention。
3. 取消10gR2的auto UNDO retention tuning
初始化参数文件中增加：
_undo_autotune = false
这样修改后undo_retention就固定为参数设定值(1800s)，如果参数设的不够大，有可能会导致ORA-1555错误。
综合比较这三种方案，由于方案1可以在线执行，完全不影响业务的运行，而且效果和方案2是一样的，因此最推荐。方案3如果出现事务运行时间太长超过设定的undo_retention，可能导致ORA-1555错误，不是很建议。
good post 加精！ 学到很多知识！！ 学习了，非常感谢！ 好厉害的感觉啊。 7. 回收还没有提交事务事务的undo block，这个操作容易导致ORA-1555错误。
还没有提交的事物undo block可以被回收么？这个真的有点疑惑
应该是没有超过undo_retention的undo block吧
页:
[1]