本文共 5863 字,大约阅读时间需要 19 分钟。
产生问题的原因主要以下两点:
1. 有较大的事务量让Oracle Undo自动扩展,产生过度占用磁盘空间的情况;2. 有较大事务没有收缩或者没有提交所导制;说明:本问题在ORACLE系统管理中属于比较正常的一现象,日常维护多注意对磁盘空间的监控。UNDO表空间介绍
UNDO表空间用于存放UNDO数据,当执行DML操作(INSERT,UPDATE和DELETE)时,oracle会将这些操作的旧数据写入到UNDO段,在oracle9i之前,管理UNDO数据时使用(Rollback Segment)完成的.从oracle9i开始,管理UNDO数据不仅可以使用回滚段,还可以使用UNDO表空间.因为规划和管理回滚段比较复杂,所有oracle database 10g已经完全丢弃用回滚段.并且使用UNDO表空间来管理UNDO数据。1、查看系统磁盘状态
AIX系统:/> df -g (Linux系统: df -h)Filesystem GB blocks Free %Used Iused %Iused Mounted on/dev/undolv 30.00 0.00 100% 9 1% /u01/app/u01/app/oracle/undo2、查看Oracle数据库表空间的占有率
select a.tablespace_name, round((a.maxbytes / 1024 / 1024), 2) "sum MB", round((a.bytes / 1024 / 1024), 2) "datafile MB", round(((a.bytes - b.bytes) / 1024 / 1024), 2) "used MB", round(( (a.maxbytes-a.bytes+b.bytes) / 1024 / 1024), 2) "free MB", round(((a.bytes - b.bytes) / a.maxbytes) * 100, 2) "percent_used" from (select tablespace_name, sum(bytes) bytes,sum(maxbytes) maxbytes from dba_data_files where maxbytes!=0 group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes, max(bytes) largest from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name order by ((a.bytes - b.bytes) / a.maxbytes) desc Tablespace_name SumDatafile(MB) Datafile Used Free Precent_used1 UNDOTBS1 32767.98 30000 29968 2799.98 91.46 或者通过如下脚本检查数据库表空间占用空间情况:select tablespace_name,sum(bytes)/1024/1024/1024 GBfrom dba_data_files group by tablespace_nameunion allselect tablespace_name,sum(bytes)/1024/1024/1024 GBfrom dba_temp_files group by tablespace_name order by GB; 3、找出UNDO表空间的路径及大小SQL> select file_name,bytes/1024/1024 from dba_data_fileswhere tablespace_name like 'UNDOTBS1' /u01/app/oracle/undo/undotbs01.dbf 30000 4、检查UNDO Segment状态SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinksfrom v$rollstat order by rssize;USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS
1 0 0 0.000358582 0.000358582 02 14 0 0.796791077 0.796791077 7353 13 0 0.800453186 0.800453186 8944 12 0 0.805213928 0.805213928 7285 15 0 1.186126709 1.186126709 9226 1 0 1.723365784 1.963180542 9467 3 0 1.732704163 1.977462769 10518 5 0 1.978370667 2.228370667 6549 2 0 2.032501221 2.034454346 70710 4 0 2.065216064 2.318145752 87511 11 0 2.100006104 2.100006104 126912 8 0 2.630340576 2.700653076 89713 6 0 2.740814209 2.740814209 103014 9 0 2.745697021 2.772064209 103715 7 0 2.833526611 2.833526611 103316 10 0 3.088363647 3.310592651 989这还原表空间中还存在16个回滚的对象。
5、创建新的临时UNDO表空间
可以在其它的磁盘空间临时创建还原表空间SQL>create undo tablespace undotbs2datafile '/u01/app/oracle/pub/undotbs02.dbf'size 10M autoextend on;Tablespace created.
6、切换UNDO表空间为新的UNDO表空间SQL> alter system set undo_tablespace=undotbs2 scope=both;
System altered.7、验证当前数据库的还原表空间
SQL> show parameter undoNAME TYPE VALUE
------------------------------------ ----------- --------------undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS28、等待原UNDO表空间所有UNDO SEGMENT OFFLINE
select t.segment_name,t.tablespace_name,t.segment_id,t.status from dba_rollback_segs t;
SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID STATUS1 SYSTEM SYSTEM 0 ONLINE2 _SYSSMU1$ UNDOTBS1 1 OFFLINE3 _SYSSMU2$ UNDOTBS1 2 OFFLINE48 _SYSSMU47$ UNDOTBS1 47 OFFLINE49 _SYSSMU48$ UNDOTBS1 48 OFFLINE50 _SYSSMU49$ UNDOTBS1 49 OFFLINE51 _SYSSMU50$ UNDOTBS1 50 OFFLINE52 _SYSSMU51$ UNDOTBS1 51 OFFLINE53 _SYSSMU52$ UNDOTBS1 52 OFFLINE54 _SYSSMU53$ UNDOTBS1 53 OFFLINE55 _SYSSMU54$ UNDOTBS1 54 OFFLINE56 _SYSSMU55$ UNDOTBS1 55 OFFLINE57 _SYSSMU56$ UNDOTBS1 56 OFFLINE58 _SYSSMU57$ UNDOTBS1 57 OFFLINE59 _SYSSMU58$ UNDOTBS1 58 OFFLINE60 _SYSSMU59$ UNDOTBS1 59 OFFLINE61 _SYSSMU60$ UNDOTBS1 60 OFFLINE62 _SYSSMU61$ UNDOTBS1 61 OFFLINE63 _SYSSMU62$ UNDOTBS2 62 ONLINE64 _SYSSMU63$ UNDOTBS2 63 ONLINE65 _SYSSMU64$ UNDOTBS2 64 ONLINE66 _SYSSMU65$ UNDOTBS2 65 ONLINE67 _SYSSMU66$ UNDOTBS2 66 ONLINE68 _SYSSMU67$ UNDOTBS2 67 ONLINE69 _SYSSMU68$ UNDOTBS2 68 ONLINE上面对应的UNDOTBS1还原表空间所对应的回滚段均为OFFLINE
9、删除原UNDO表空间
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.
10、可以再次查看系统磁盘空间:AIX系统:/> df -g (Linux系统: df -h) 如果需要规范数据库的表空间和路径,还原表空间名称undotbs1和路径不能改变,可以安装刚才的步骤进行切换回来。1、创建新的原来的UNDO表空间可以在其它的磁盘空间临时创建还原表空间SQL>create undo tablespace undotbs1datafile '/u01/app/oracle/undo/undotbs01.dbf'size 10M autoextend on maxsize 15G;刚开始为10M,设置自动扩展,最大为15GB
Tablespace created.
2、切换UNDO表空间为新的UNDO表空间SQL> alter system set undo_tablespace=undotbs1 scope=both;
System altered.3、验证当前数据库的还原表空间
SQL> show parameter undoNAME TYPE VALUE
------------------------------------ ----------- --------------undo_management string AUTOundo_retention integer 900undo_tablespace string UNDOTBS14、等待原UNDO表空间所有UNDO SEGMENT OFFLINE
select t.segment_name,t.tablespace_name,t.segment_id,t.status from dba_rollback_segs t;
SEGMENT_NAME TABLESPACE_NAME SEGMENT_ID STATUS
上面对应的UNDOTBS2还原表空间所对应的回滚段均为OFFLINE
5、删除UNDO2表空间
SQL> drop tablespace undotbs2 including contents and datafiles;
Tablespace dropped.
6、可以再次查看系统磁盘空间:AIX系统:/> df -g (Linux系统: df -h)undo_retention:指定事物commit后undo 将要保存的时间(秒),在ORACLE10g中默认的是900秒。
GUARANTEE : 保证undo_retention参数所设定的时间有效,这个是10g的新功能。
SQL> ALTER TABLESPACE undotbs1 RETENTION GUARANTEE;
SQL> ALTER TABLESPACE undotbs1 RETENTION NOGUARANTEE;
在没有guarantee的保证下,ORACLE并不能保证能够将undo信息存储900秒,如果undo表空间不足,那么ORACLE将忽略undo_retention的设置,直接覆盖掉以前的undo,这个时候有可能会产生ORA-01555错误。如果undo表空间空间足够,那么undo将会保存很长一段时间,直到undo表空间达到maxsize,这个时候才会覆盖undo信息,而且ORACLE会从最古老的undo信息开始覆盖。
ORACLE推荐我们将undo 表空间中的datafile 设定MAXSIZE ,不要让它一直自动扩展,如果ORACLE获得了自动扩展的能力,那么旧的undo不会被覆盖,到后来undo表空间会越来越大,越来越大,直到将磁盘空间耗尽。
在有guarantee的保证下,ORACLE将会保证undo信息能够保存到undo_retention设定的值之后才被覆盖,如果这个时候同时执行了很多事物,将undo表空间耗完了,那么那个事物会失败,会报ORA-30036 错误,所以使用guarantee一定要慎用,如果非要使用guarantee,那么尽量将undo 表空间设大 一点。
Oracle10g开始,如果你设置UNDO_RETENTION为0,那么Oracle启用自动调整以满足最长运行查询的需要。当然如果空间不足,那么Oracle满足最大允许的长时间查询,而不再需要用户手工调整。
FROM:
转载地址:http://hytai.baihongyu.com/