oracle undo表空间爆满处理

mwjhw216

　　undo 表空间使用案例分析（UNDOTBS1使用率100%，enq：US-contention,释放undo）
　　转载 2013年02月20日 16:04:29 12007
　　问题描述：
　　近几天发现Oracle10.2.0.4数据库undo tablespace表空间使用率很高（最高时达到100%），报警系统频繁报障undo tablespace表空间使用率过高。
　　TABLESPACE_NAME         TOTAL     USED        FREE       PUSED      PFREE
　　UNDOTBS1             6141 % %
　　查询所有表空间使用情况（今天发现自己创建的表空间在使用完的情况，这个查询语句或者其它查询语句查不出已经使用完的表空间，只能自己删除掉一些使用该表空间的用户，以便释放自己创建的表空间）
　　

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",　　

　　D.TOT_GROOTTE_MB "表空间大小(M)",
　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
　　TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB 100,2),'990.99') || '%' "使用比",
　　F.TOTAL_BYTES "空闲空间(M)",
　　F.MAX_BYTES "最大块(M)"
　　FROM (SELECT TABLESPACE_NAME,
　　ROUND(SUM(BYTES) / (1024  1024), 2) TOTAL_BYTES,
　　ROUND(MAX(BYTES) / (1024 1024), 2) MAX_BYTES
　　FROM SYS.DBA_FREE_SPACE
　　GROUP BY TABLESPACE_NAME) F,
　　(SELECT DD.TABLESPACE_NAME,
　　ROUND(SUM(DD.BYTES) / (1024  1024), 2) TOT_GROOTTE_MB
　　FROM SYS.DBA_DATA_FILES DD
　　GROUP BY DD.TABLESPACE_NAME) D
　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
　　ORDER BY 1;
　　问题分析
　　2.1 业务系统影响
　　undo tablespace表空间使用率达到100%时，没有业务系统用户反映系统出现表空间不能扩展的报错（ORA-30036），但曾经出现过快照过旧报错（ORA-01555）。
　　2.2 数据库日志

　　查看alert_instdb.log报警文件，发现从Tue May 18 10:51:27 2010到Sat May 22 14:55:59 2010，共出现了15次与undo tablespace有关的ORA-01555报错，这些报错都是由同一条SQL语句引起的（SQL>　　Tue May 18 10:51:27 2010

　　ORA-01555 caused by SQL statement below (SQL>　　Tue May 18 10:51:27 2010
　　select decode(temp_kp.mc,null,temp_hdhs.mc,temp_kp.mc) as mc,
　　decode(temp_kp.jzyje,null, 0,temp_kp.jzyje) as jzyje,
　　decode(temp_kp.jzyfs,null, 0,temp_kp.jzyfs) as jzyfs,
　　decode(temp_kp.bdcje,null, 0,temp_kp.bdcje) as bdcje,
　　decode(temp_kp.bdcfs,null, 0,temp_kp.bdcfs) as bdcfs,
　　decode(temp_kp.ptje,null, 0,temp_kp.ptje) as ptje,
　　decode(temp_kp.ptfs,null, 0,temp_kp.ptfs) as ptfs,
　　decode(temp_kp.dkje,null, 0,temp_kp.dkje) as dkje,
　　decode(temp_kp.dkfs,null, 0,temp_kp.dkfs) as dkfs,
　　decode(temp_kp.bgdlyje,null, 0,temp_kp.bgdlyje) as bgdlyje,
　　decode(temp_kp.bgdlyfs,null, 0,temp_kp.bgdlyfs) as bgdlyfs,
　　decode(temp_kp.gjhyyje,null, 0,temp_kp.gjhyyje) as gjhyyje,
　　decode(temp_kp.gjhyyfs,null, 0,temp_kp.gjhyyfs) as gjhyyfs,
　　decode(temp_kp.gjhyycpdlje,null, 0,temp_kp.gjhyycpdlje) as gjhyycpdlje,
　　decode(temp_kp.gjhyycpdlfs,null, 0,temp_kp.gjhyycpdlfs) as gjhyycpdlfs,
　　decode(temp_kp.gjhwysje,null, 0,temp_kp.gjhwysje) as
　　2.3 Undo参数
　　SQL> show parameter undo
　　NAME TYPE VALUE
　　undo_management string AUTO
　　undo_retention integer 900
　　undo_tablespace string UNDOTBS1
　　2.4 Undo tablespace空间使用情况
　　select file_name,autoextensible,bytes/1048576 MB,increment_by,maxbytes/1048576 Max_MB
　　from dba_data_files
　　where tablespace_name = 'UNDOTBS1';
　　FILE_NAME AUTOEXTENSIBLE MB INCREMENT_BY MAX_MB
　　/dev/rinstdbUNDO_lv NO 1023 0 0
　　/dev/rinstdbUNDO2_lv NO 3071 0 0
　　/dev/rinstdbUNDO3_lv NO 2047 0 0
　　select tablespace_name,status, sum(bytes)/1024/1024 MB, count() from dba_undo_extents
　　group by tablespace_name,status
　　TABLESPACE_NAME STATUS MB COUNT()
　　TS_UNDO EXPIRED 2.375 38
　　UNDOTBS1 UNEXPIRED 4344.75 2687
　　UNDOTBS1 EXPIRED 1796.0625 3146
　　UNDOTBS1表空间总空间是6141MB，已分配空间达到6140.8125MB，其中UNEXPIRED EXTENT空间占4344.75MB。
　　2.5 V$UNDOSTAT和DBA_HIST_UNDOSTAT
　　查询V$UNDOSTAT视图和DBA_HIST_UNDOSTAT数据字典可以看到：

• 从2010-05-19 PM 10:53:38到2010-5-22 04:53:38 PM，MAXQUERYLEN值都保持在20万秒以上，MAXQUERYLEN最大值是285073s（2010-5-22 04:53:38 PM），
  
• TUNED_UNDORETENTION保持在20万秒以上，TUNED_UNDORETENTION最大值是345742s （2010-5-26 09:13:38 AM），TUNED_UNDORETENTION当前值是255484（2010-5-27 02:43:38 PM）
  
• SSOLDERRCNT累计值大于0
  
• NOSPACEERRCNT一直都是0
  

　　从以上数据可以了解到，从2010-05-19 PM 10:53:38到2010-5-22 04:53:38 PM期间出现了多次异常超长时间查询，这些长时间查询导致TUNED_UNDORETENTION参数值变大，这些查询曾经导致ORA-01555报错，但没有导致DML语句出错。
　　注：从oracle10g开始，oracle提供了Automatic UNDO Retention特性，即oracle会根据实际情况自动调整undo_retention值，以尽可能避免ORA-01555的报错。v$undostat视图中几个主要列的解释：

　　MAXQUERYLEN>
　　SSOLDERRCNT>
　　NOSPACEERRCNT>　　TUNED_UNDORETENTIONSystem tuned value indicating the period for which undo is being retained
　　总结及建议
　　3.1 Undo tablespace使用率100%原因
　　从oracle10g开始，oracle提供了Automatic UNDO Retention特性，即oracle会根据实际情况自动调整undo_retention值，从2010.5.18至2010.5.22期间产生了多次超长时间查询，这段期间MAXQUERYLEN值保持在20万秒以上，为了尽可能避免ORA-01555的报错，尽可能保留更多的UNEXPIRED EXTENT，ORACLE会首先选择在undo tablespace中扩充rollback segment，所以undo tablespace使用率会不断上升，最终导致了undo tablespace空间使用率达到100%。
　　3.2 Undo tablespace空间监控方法
　　undo tablespace空间使用率达到100%，只是意味着所有的空间都已经分配给rollback segment，但并不一定会影响到业务的select和insert、delete、update操作，因为rollback segment中的空间是可以重用的。在oracle10g，不能通过监控dba_free_space数据库字典的方法去获得undo tablespace的使用情况，可以考虑通过以下方法进行监控：

• 监控v$undostat视图，主要包括MAXQUERYLEN、SSOLDERRCNT、NOSPACEERRCNT列值，如：MAXQUERYLEN大于36000秒则报警、SSOLDERRCNT大于0次（或一定次数）则报警、NOSPACEERRCNT大于0次则严重警告等；
  
• 　　监控平均每一秒钟所需的最少回滚表空间容量，如果超过一定范围则报警
  　　SELECT (UR (UPS DBS)) + (DBS 24) AS "Bytes"
  　　FROM (SELECT value AS UR FROM v$parameter WHERE name = 'undo_retention'),
  　　(SELECT (SUM(undoblks)/SUM(((end_time - begin_time)86400))) AS UPS FROM v$undostat),
  　　(select block_size as DBS from dba_tablespaces where tablespace_name=
  　　(select value from v$parameter where name = 'undo_tablespace'));
  
  Bytes
  　　413215736
  
  

　　那么一个undo表空间的理论大小为：413215736*900
　　对于oracle10g版本，使用如下命令强制undo保存时间
　　alter tablespace undotbs1 retention guarantee;
　　实例二：
　　这几天遇到一个错误，我也不知道算不算错误吧，因为没有报错，只是在那突然的短短2分钟内表的操作突然降低了，导致了该软件重新启动。查看alert日志没有报错，而是在ASH里找到了TOP SQL框有一个这样的错误，使我百思不得其解。查看该SQL语句只是简单的一个更新，并不需要优化。最后再百度、google的帮助下终于找到了错误原因，原来与UNDO的设置有关。首先来介绍下undo_retention参数，该参数是撤销段的最短保留时间，而在默认情况下Oracle将根据表空间的大小和历史使用情况，自动调整undo信息保存时间，同时忽略 undo_retention的值,除非undo_retention的guarantee 特性被启用.也就是执行以下命令：
　　ALTER TABLESPACE UNDOTBS RETENTION GUARANTEE;
　　在自动调整启用的情况下，实际的撤销信息最短保留时间可以通过查询V$UNDOSTAT视图上的TUNED_UNDORETENTION列获得。往往最短保存时间远远大于设定的UNDO_RETENTION。UNDO自动优化功能能够最大限度的使用undo表空间,满足大部分的sql执行,但是也带来一个问题：很多事务执行完毕之后，发现UNDO表空间会在很长时间都一直保持着使用率是接近100%的状态，active 状态的很少。这种接近状态还无法手工的收缩，甚至于重启数据库实例也无法缓解，而此时常常会收到undo表空间的监控报警。
　　再来说说enq: US - contention问题
　　这是oracle10g中开始出现的bug(在11.1.0.7中仍有这个BUG)，当因为系统activity增加或者降低的时候，oracle SMON进程会自动ONLINE或者OFFLINE rollback segments。这样导致某些与undo segments相关的latch或者enqueue被hold住太长时间，导致系统很多活跃session都开始等待enq: US - contention。可以同时使用以下解决方法:

• 设置event让SMON不自动OFFLINE回滚段。
  

　　alter system set events '10511 trace name context forever, level 1';

• 设置参数_rollback_segment_count ：表示有多少rollback segment要处于online的状态；可以将该数值设置为数据库最繁忙的时候的回滚段数目。
  

　　alter system set "_rollback_segmentcount"=;
　　这里以‘’开头的为隐藏参数，通过show parameter 是看不到的，可以通过以下语句：
　　select a.ksppinm name, b.ksppstvl value, a.ksppdesc description
　　from x$ksppi a, x$ksppcv b
　　where a.indx = b.indx
　　and a.ksppinm like '%_rollback_segment_count%';

• 　　undo autotune bug多多。最好disable。
  　　alter system set "_undo_autotune"= false;
  　　这种方法就是关闭了UNDO的自动调整功能，同事也能解决掉UNDO表空间会在很长时间都一直保持着使用率是接近100%的问题。
  
  
• 有一个patch: A fix to bug 7291739 is to set a new hidden parameter, _highthreshold_undoretention to set a high threshold for undo retention completely distinct from maxquerylen.
  

　　alter system set "_highthreshold_undoretention"=;
　　案例三：
　　一.概述:
　　使用IMPDP工具导入大表(166G)数据时,报undo表空间不能扩展,导入工作失败.手工停止了impdp后,undo表空间存在无法自动释放的故障.本文主要描述如何通过重建undo表空间来手工释放undo表空间.
　　数据库环境的描述:
　　OS: AIX 6.1+HACMP 5.3
　　DB: ORACLE 10.2.0.5 RAC
　　二.问题的描述
　　impdp 导入数据时,报ora-30036错误
　　$impdp user/passwd directory=imp_dir dumpfile=bigtable%U.dmp parallel=10 logfile=imp_big_table.log
　　ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
　　检查数据库的归档日志文件,也发现了这个报警
　　more /oracle/admin/*/bdump/alert_{SID}.log|grep undo
　　ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
　　ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
　　ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTBS1'
　　强行终止了impdp的操作,impdp的进程在操作中已不见

ps -ef|grep impdp
　　root 8650752 8061396 0 15:02:08 pts/1 0:00 grep impdp
　　卸载数据文件所在的文件系统时,发现无法下载
　　SQL>select * from dba_directories
　　OWNER DIRECTORY_NAME DIRECTORY_PATH
　　SYS imp_dir /imp_data
　　#umount /imp_data
　　umount: 0506-349 Cannot unmount /dev/imp_data: The requested resource is busy.
　　检查UNDO 表空间的使用情况,如下,存在一个两个比较大的EXPIRED的undo segment.
　　SQL> select owner,segment_name,sum(bytes/1024/1024) from dba_undo_extents group by owner,segment_name order by 3
　　OWN SEGMENT_NAME SUM(BYTES/1024/1024)
　　.........
　　SYS _SYSSMU12$ 26.125
　　SYS _SYSSMU9$ 27.125
　　SYS _SYSSMU7$ 45.125
　　SYS _SYSSMU18$ 72.125
　　SYS _SYSSMU20$ 72.125
　　SYS _SYSSMU10$ 72.125
　　SYS _SYSSMU1$ 83.125
　　SYS _SYSSMU6$ 3563.1875
　　SYS _SYSSMU8$ 9524.4375
　　SQL>select tablespace_name,status,sum(bytes/1024/1024) from dba_undo_extents group by tablespace_name,status;
　　TABLESPACE_NAME STATUS SUM(BYTES/1024/1024)
　　UNDOTBS1 ACTIVE 47.0625
　　UNDOTBS2 EXPIRED 291.25
　　UNDOTBS1 UNEXPIRED 285.875
　　UNDOTBS1 EXPIRED 13063.6875
　　在metalink上,查找相关信息,看到有一个bug,说的是：undo表空间不足的时候，不会overwrite expired的undo segment。这个bug是10.2.0.3，9.2.0.8版本上发生，当前数据库的版本为10.2.0.5，不应该是这个bug引起的。
　　三.问题的分析
　　数据库的环境是10.2.0.5 RAC，UNDO表空间不足发生在node1的undotbs1上，为了不影响数据库的运行，首先为该表空间增加了空间。

　　SQL>alter tablespace undotbs1 add datafile '/dev/rora_data_03'>　　尝试通过重新启动数据库，来释放undo表空间上的内容，结果失败，undotbs1仍然存在大量的expried的segment不能释放（这个数据库的undo 是自动管理的）
　　因为umount 导入文件系统也失败，考虑是不是因为手工强行停止了impdp的操作，导致了操作系统中还有相关进程没有完全停掉。所以采用了重新启动数据库服务器的方式来释放相关的文件系统的锁。
　　四.问题的解决
　　重新启动数据库服务器后，尝试umount 文件系统(/imp_data),结果成功.
　　现在剩下的问题是,如何shrink undo segment的问题,总不能让那么多的undo表空间就象太空垃圾一样,存在在数据库中.
　　在metalink上找到相关的文档:How to Shrink the datafile of Undo Tablespace [ID 268870.1]
　　按照文档介绍的方法,进行了下面的操作

　　SQL>create undo tablespace undotbs3 datafile '/dev/rora_data_02'>　　SQL>alter system set undo_tablespace='UNDOTBS3' scope=both sid='JLZDH1';
　　SQL>drop tablespace undotbs1 including contents;
　　ORA-30013 : undo tablespace undotbs1 is currently in use
　　在删除undo表空间undotbs1时,报表空间在使用,无法删除的错误.
　　重新启动数据库,再次查看undo表空间的情况
　　SQL> show parameter undo
　　NAME TYPE VALUE
　　undo_management string AUTO
　　undo_retention integer 450
　　undo_tablespace string UNDOTBS3
　　SQL> select tablespace_name,status,sum(bytes/1024/1024) from dba_undo_extents group by tablespace_name,status;
　　TABLESPACE_NAME STATUS SUM(BYTES/1024/1024)
　　UNDOTBS3 UNEXPIRED 6.25
　　UNDOTBS2 EXPIRED 291.25
　　UNDOTBS1 EXPIRED 7152.1875
　　UNDOTBS3 EXPIRED 13.0625
　　再次尝试删除undotbs1,成功了.
　　SQL> drop tablespace undotbs1 including contents;
　　Tablespace dropped.
　　SQL> select tablespace_name,status,sum(bytes/1024/1024) from dba_undo_extents group by tablespace_name,status;
　　TABLESPACE_NAME STATUS SUM(BYTES/1024/1024)
　　UNDOTBS3 UNEXPIRED 2.3125
　　UNDOTBS2 EXPIRED 291.25
　　UNDOTBS3 EXPIRED 17
　　五.总结
　　impdp/expdp是oracle提供的数据库数据导入/导出的工具,速度比旧工具imp/exp要快很多,但也存在很多的bug,由于加入了job方式的管理,异常出现的机会比较多.曾经遇到过,手工终止impdp操作,导致了system表空间被大量占用的问题.本次又遇到undo表空间不能释放的问题.虽然数据泵有很多问题,但使用了parallel并行处理后,速度提升不是一般的多,所以日常工作中,还是首选数据泵工具.
　　undo表空间不能释放时,最好的解决办法就是:
　　1.重新建立一个新的undo表空间.
　　2.设置数据库的undo表空间为新的undo表空间
　　3.删除旧的undo表空间及其内容
　　文章转自：http://blog.csdn.net/changyanmanman/article/details/8313205