问题描述: 近几天发现Oracle10.2.0.4数据库undo tablespace表空间使用率很高(最高时达到100%),报警系统频繁报障undo tablespace表空间使用率过高。 TABLESPACE_NAME TOTAL USED FREE PUSED PFREE
----------------- ---------- ---------- ---------- ---------- ----------
UNDOTBS1 6141 % %
查询所有表空间使用情况(今天发现自己创建的表空间在使用完的情况,这个查询语句或者其它查询语句查不出已经使用完的表空间,只能自己删除掉一些使用该表空间的用户,以便释放自己创建的表空间) SELECT UPPER(F.TABLESPACE_NAME) "表空间名", 问题分析 2.3 Undo参数 2.4 Undo tablespace空间使用情况 select tablespace_name,status, sum(bytes)/1024/1024 MB, count(*) from dba_undo_extents UNDOTBS1表空间总空间是6141MB,已分配空间达到6140.8125MB,其中UNEXPIRED EXTENT空间占4344.75MB。 2.5 V$UNDOSTAT和DBA_HIST_UNDOSTAT
总结及建议 Bytes 那么一个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。可以同时使用以下解决方法: 1. 设置event让SMON不自动OFFLINE回滚段。 alter system set events '10511 trace name context forever, level 1'; 2. 设置参数_rollback_segment_count :表示有多少rollback segment要处于online的状态;可以将该数值设置为数据库最繁忙的时候的回滚段数目。 alter system set "_rollback_segment_count"=; 这里以‘_’开头的为隐藏参数,通过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%'; 3. undo autotune bug多多。最好disable。 alter system set "_undo_autotune"= false; 这种方法就是关闭了UNDO的自动调整功能,同事也能解决掉UNDO表空间会在很长时间都一直保持着使用率是接近100%的问题。 4. 有一个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=big_table_%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' 强行终止了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 SQL>select tablespace_name,status,sum(bytes/1024/1024) from dba_undo_extents group by tablespace_name,status; 在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' size 11518m;
尝试通过重新启动数据库,来释放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' size 11518m extent management local;
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 |
|
来自: 姑苏慕容凡 > 《Oracle 管理》