Oracle 10g Undo表空间过大导致磁盘空间不足的解决

twinking 贡献于2011-12-28

作者 对ORACLE的外部表的简单介绍 10大城市2万个热门IT职位 注册有奖  创建于2011-12-13 08:33:00   修改者对ORACLE的外部表的简单介绍 10大城市2万个热门IT职位 注册有奖  修改于2011-12-13 08:37:00字数6564

文档摘要:在Oracle 10g数据库的应用中,出现了UNDO表空间过大导致磁盘空间不足而崩溃的现象。对此问题进行分析后,总结了出现该问题的原因主要有以下两点:<br> 1. 有较大的事务量让Oracle Undo自动扩展,产生过度占用磁盘空间的情况;<br> 2. 有较大事务没有收缩或者没有提交所导制;
关键词:

Oracle 10g UNDO表空间过大导致磁盘空间不足的解决 在Oracle 10g数据库的应用中,出现了UNDO表空间过大导致磁盘空间不足而崩溃的现象。对此问题进行分析后,总结了出现该问题的原因主要有以下两点: 1. 有较大的事务量让Oracle Undo自动扩展,产生过度占用磁盘空间的情况; 2. 有较大事务没有收缩或者没有提交所导制; 说明:本问题在Oracle系统管理中属于比较正常的一现象,日常维护多注意对磁盘空间的监控。 Oracle 10g 有自动Automatic Undo Retention Tuning 这个特性。设置的 undo_retention 参数只是一个指导值,缺省值900秒,,Oracle 会自动调整 Undo (会跨过 undo_retention 设定的时间) 来保证不会出现 Ora-1555 错误.。通过查询V$UNDOSTAT(该视图记录4天以内的UNDO表空间使用情况,超过4天可以查询DBA_HIST_UNDOSTAT视图)的tuned_undoretention (该字段在10G版本才有,9I是没有的)字段可以得到Oracle 根据事务量(如果是文件不可扩展,则会考虑剩余空间)采样后的自动计算出最佳的 retenton 时间.。 1)查询retention值 show parameter undo_retention 查询自动计算出最佳的retenton 时间 select tuned_undoretention, maxquerylen, maxqueryid from v$undostat; 2)更改retention值 ALTER SYSTEM SET undo_retention=10800 SCOPE=BOTH; 这样对于一个事务量分布不均匀的数据库来说,,就会引发潜在的问题--在批处理的时候可能 Undo 会用光, 而且这个状态将一直持续, 不会释放。 如何取消10g的auto UNDO Retention Tuning,有如下三种方法: (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 ),不建议使用SQL> Alter system set "_smu_debug_mode" = 33554432; (3)设置隐含参数_undo_autotune=false,关闭自动undo retention调整特性,不建议使用SQL> Alter system set "_undo_autotune" = false;from metalink 420525.1: Automatic Tuning of Undo_retention Causes Space Problems. 解决步骤: 1. 启动SQLPLUS,并用sys登陆到数据库。 1. #su - oracle   2. $>sqlplus / as sysdba  2. 查找数据库的UNDO表空间名,确定当前例程正在使用的UNDO表空间:Show parameter undo_tablespace。 3. 确认UNDO表空间; 1. SQL> select name from v$tablespace;   2. NAME   3. ------------------------------   4. .......   5. UNDOTBS1  4. 检查数据库UNDO表空间占用空间情况以及数据文件存放位置; SQL>select file_name,bytes/1024/1024 from dba_data_files where tablespace_name like 'UNDOTBS%'; 5. 查看回滚段的使用情况,哪个用户正在使用回滚段的资源,如果有用户最好更换时间(特别是生产环境)。 1. SQL> select s.username, u.name from v$transaction t,v$rollstat r, v$rollname u,v$session s   2. where s.taddr=t.addr and t.xidusn=r.usn and r.usn=u.usn order by s.username;  6. 检查UNDO Segment状态; 1. SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize;  7. 创建新的UNDO表空间,并设置自动扩展参数; 1. SQL> create undo tablespace undotbs2 datafile '/opt/oracle/oradata/ge01/UNDOTBS2.dbf' size 100m reuse autoextend on next 50m maxsize 5000m;  2. Tablespace created. 8. 动态更改spfile配置文件; 1. SQL> alter system set undo_tablespace=undotbs2 scope=both;   2. System altered.  9. 等待原UNDO表空间所有UNDO SEGMENT OFFLINE; 1. select usn,xacts,status,rssize/1024/1024,hwmsize/1024/1024, shrinks from   v$rollstat order by rssize;  10. 再执行看UNDO表空间所有UNDO SEGMENT ONLINE; 1. select usn,xacts,status,rssize/1024/1024,hwmsize/1024/1024, shrinks from v$rollstat order by rssize;  11. 删除原有的UNDO表空间; 1. SQL> drop tablespace undotbs1 including contents;   2. Tablespace dropped.  12. 确认删除是否成功; 1. SQL> select name from v$tablespace;   2. NAME   3. ------------------------------   4. .......   5. UNDOTBS2   6. 12 rows selected.  13. 更新pfile 1. SQL> create pfile from spfile;   2. File created.  14. 册除原UNDO表空间的数据文件,其文件名为步骤中执行的结果。 1. #rm $ORACLE_BASE/oradata/$ORACLE_SID/undotbs01.dbf  关于Oracle 10g UNDO表空间过大导致磁盘空间不足的解决方法就介绍到这里了,希望本次的介绍能够对您有所收获! Oracle:如何释放过度占用 Undo表空间 oracle9i以后提供了undo tablespace.提供了便利的同时可能就会带来了另外一个问题:undo tablespace的过度扩展. 造成这个问题的原因是:undo_retention设置的过大.很多时候很多人会设置这个表空间自动扩展.那么很快,空间可能会被耗尽.如何解决呢? 方法很简单:创建一个新的undo tablespace,修改undo_tablespace参数,使用新的undo tablespace,然后重启数据库.可以删除掉之前的表空间. 网上也有类似的文章,很多.可是按照上述步骤操作.却在最后的时候发现删除之前的undo tablespace的时候提示:ora-1548. ORA-01548 active rollback segment 'string' found, terminate dropping tablespace Cause: An attempt was made to drop a tablespace that contains active rollback segment(s). Action: Shut down instances that use the active rollback segments in the tablespace and then drop the tablespace. 实际上是怎么回事呢?我们需要看该undo tablespace 上的回滚段还有没有被使用,没有事务. 网上提供的指南通常只有如下的: select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks from v$rollstat order by rssize; 实际上这样只能够看到哪些回滚段.但是这些回滚段属于哪些表空间呢,不知道.所以很难判断. 遇到这样一个问题,需要解决.搜了一下,好像没有找到对应的文章. 使用如下语句: select * from dictionary where table_name like '%ROLL%' ; 可以得到如下结果: DBA_ROLLBACK_SEGS Description of rollback segments V$ROLLNAME Synonym for V_$ROLLNAME V$ROLLSTAT Synonym for V_$ROLLSTAT 实际上我们用到的也就是这几个表了. SQL> desc DBA_ROLLBACK_SEGS; Name Type Nullable Default Comments --------------- ------------ -------- ------- --------------------------------------------------------- SEGMENT_NAME VARCHAR2(30) Name of the rollback segment OWNER VARCHAR2(6) Y Owner of the rollback segment TABLESPACE_NAME VARCHAR2(30) Name of the tablespace containing the rollback segment SEGMENT_ID NUMBER ID number of the rollback segment FILE_ID NUMBER ID number of the file containing the segment header BLOCK_ID NUMBER ID number of the block containing the segment header INITIAL_EXTENT NUMBER Y Initial extent size in bytes NEXT_EXTENT NUMBER Y Secondary extent size in bytes MIN_EXTENTS NUMBER Minimum number of extents MAX_EXTENTS NUMBER Maximum number of extents PCT_INCREASE NUMBER Y Percent increase for extent size STATUS VARCHAR2(16) Y Rollback segment status INSTANCE_NUM VARCHAR2(40) Y Rollback segment owning parallel server instance number RELATIVE_FNO NUMBER SQL> desc v$rollname; Name Type Nullable Default Comments ---- ------------ -------- ------- -------- USN NUMBER Y NAME VARCHAR2(30) SQL> desc v$rollstat; Name Type Nullable Default Comments --------- ------------ -------- ------- -------- USN NUMBER Y LATCH NUMBER Y EXTENTS NUMBER Y RSSIZE NUMBER Y WRITES NUMBER Y XACTS NUMBER Y GETS NUMBER Y WAITS NUMBER Y OPTSIZE NUMBER Y HWMSIZE NUMBER Y SHRINKS NUMBER Y WRAPS NUMBER Y EXTENDS NUMBER Y AVESHRINK NUMBER Y AVEACTIVE NUMBER Y STATUS VARCHAR2(15) Y CUREXT NUMBER Y CURBLK NUMBER Y 根据这些数据字典的描述.可以写出如下语句 ----看看表空间上的回滚段是否还有事务 select b.segment_name Name, b.TABLESPACE_NAME, a.rssize/1024/1024/1024, a.hwmsize/1024/1024/1024, a.shrinks, a.xacts XActs, b.status status from v$rollstat a, dba_rollback_segs b,   v$rollname c where c.name(+) = b.segment_name and a.usn(+) = c.usn 执行了一下,得到如下结果: NAME TABLESPACE_NAME A.RSSIZE/1024/1024/1024 A.HWMSIZE/1024/1024/1024 SHRINKS XACTS STATUS 1 SYSTEM SYSTEM 0.00035858154296875 0.00035858154296875 0 0 ONLINE 2 _SYSSMU1$ UNDOTBS1 2.03562927246094 2.03562927246094 0 1 ONLINE 3 _SYSSMU2$ UNDOTBS1 0.00890350341796875 1.56279754638672 27 0 ONLINE 4 _SYSSMU3$ UNDOTBS1 0.00890350341796875 0.0186691284179688 2 0 ONLINE 5 _SYSSMU4$ UNDOTBS1 0.125114440917969 0.125114440917969 0 0 ONLINE 6 _SYSSMU5$ UNDOTBS1 0.125114440917969 0.125114440917969 0 0 ONLINE 7 _SYSSMU6$ UNDOTBS1 0.204216003417969 3.98937225341797 0 0 ONLINE 8 _SYSSMU7$ UNDOTBS1 0.00988006591796875 0.0108566284179688 1 0 ONLINE 9 _SYSSMU8$ UNDOTBS1 0.185661315917969 0.185661315917969 0 1 ONLINE 10 _SYSSMU9$ UNDOTBS1 0.00988006591796875 0.00988006591796875 0 0 ONLINE 11 _SYSSMU10$ UNDOTBS1 0.0704269409179688 0.0704269409179688 0 0 ONLINE 12 _SYSSMU11$ UNDOTBS1 OFFLINE 13 _SYSSMU12$ UNDOTBS1 OFFLINE 14 _SYSSMU20$ UNDOOTBS3 OFFLINE 15 _SYSSMU21$ UNDOOTBS3 OFFLINE 16 _SYSSMU22$ UNDOOTBS3 OFFLINE 17 _SYSSMU23$ UNDOOTBS3 OFFLINE 18 _SYSSMU24$ UNDOOTBS3 OFFLINE 19 _SYSSMU25$ UNDOOTBS3 OFFLINE 20 _SYSSMU26$ UNDOOTBS3 OFFLINE 21 _SYSSMU27$ UNDOOTBS3 OFFLINE 22 _SYSSMU28$ UNDOOTBS3 OFFLINE 23 _SYSSMU29$ UNDOOTBS3 OFFLINE 所以,auto管理的undo tablespace可以通过这个语句得到不同的undo tablespace的回滚段使用情况,根据这个情况,就可以知道对应的undo tablespace是否可以drop掉了. 记下来,免得忘记了,供以后参考,呵呵. 另外,至于这个问题的前面的简单处理,就不在这里赘述,可以有很多地方参考,google一下即可解决问题.

下载文档到电脑,查找使用更方便

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 8 金币 [ 分享文档获得金币 ] 1 人已下载

下载文档