oracle的还原表空间UNDO写满磁盘空间，解决该问题的具体步骤

一时失言乱红尘 2022-03-28 05:49 604阅读 0赞

产生问题的原因主要以下两点：  
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/undo

2、查看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\_used  
1 UNDOTBS1 32767.98 30000 29968 2799.98 91.46

或者通过如下脚本检查数据库表空间占用空间情况:  
select tablespace\_name,sum(bytes)/1024/1024/1024 GB  
from dba\_data\_files group by tablespace\_name  
union all  
select tablespace\_name,sum(bytes)/1024/1024/1024 GB  
from dba\_temp\_files group by tablespace\_name order by GB;

3、找出UNDO表空间的路径及大小  
SQL> select file\_name,bytes/1024/1024 from dba\_data\_files  
where 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,shrinks  
from v$rollstat order by rssize;

USN XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024 SHRINKS  
1 0 0 0.000358582 0.000358582 0  
2 14 0 0.796791077 0.796791077 735  
3 13 0 0.800453186 0.800453186 894  
4 12 0 0.805213928 0.805213928 728  
5 15 0 1.186126709 1.186126709 922  
6 1 0 1.723365784 1.963180542 946  
7 3 0 1.732704163 1.977462769 1051  
8 5 0 1.978370667 2.228370667 654  
9 2 0 2.032501221 2.034454346 707  
10 4 0 2.065216064 2.318145752 875  
11 11 0 2.100006104 2.100006104 1269  
12 8 0 2.630340576 2.700653076 897  
13 6 0 2.740814209 2.740814209 1030  
14 9 0 2.745697021 2.772064209 1037  
15 7 0 2.833526611 2.833526611 1033  
16 10 0 3.088363647 3.310592651 989

这还原表空间中还存在16个回滚的对象。

5、创建新的临时UNDO表空间  
可以在其它的磁盘空间临时创建还原表空间  
SQL>  
create undo tablespace undotbs2  
datafile '/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 undo

NAME TYPE VALUE  
\------------------------------------ ----------- --------------  
undo\_management string AUTO  
undo\_retention integer 900  
undo\_tablespace string UNDOTBS2

8、等待原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  
1 SYSTEM SYSTEM 0 ONLINE  
2 \_SYSSMU1$ UNDOTBS1 1 OFFLINE  
3 \_SYSSMU2$ UNDOTBS1 2 OFFLINE  
48 \_SYSSMU47$ UNDOTBS1 47 OFFLINE  
49 \_SYSSMU48$ UNDOTBS1 48 OFFLINE  
50 \_SYSSMU49$ UNDOTBS1 49 OFFLINE  
51 \_SYSSMU50$ UNDOTBS1 50 OFFLINE  
52 \_SYSSMU51$ UNDOTBS1 51 OFFLINE  
53 \_SYSSMU52$ UNDOTBS1 52 OFFLINE  
54 \_SYSSMU53$ UNDOTBS1 53 OFFLINE  
55 \_SYSSMU54$ UNDOTBS1 54 OFFLINE  
56 \_SYSSMU55$ UNDOTBS1 55 OFFLINE  
57 \_SYSSMU56$ UNDOTBS1 56 OFFLINE  
58 \_SYSSMU57$ UNDOTBS1 57 OFFLINE  
59 \_SYSSMU58$ UNDOTBS1 58 OFFLINE  
60 \_SYSSMU59$ UNDOTBS1 59 OFFLINE  
61 \_SYSSMU60$ UNDOTBS1 60 OFFLINE  
62 \_SYSSMU61$ UNDOTBS1 61 OFFLINE  
63 \_SYSSMU62$ UNDOTBS2 62 ONLINE  
64 \_SYSSMU63$ UNDOTBS2 63 ONLINE  
65 \_SYSSMU64$ UNDOTBS2 64 ONLINE  
66 \_SYSSMU65$ UNDOTBS2 65 ONLINE  
67 \_SYSSMU66$ UNDOTBS2 66 ONLINE  
68 \_SYSSMU67$ UNDOTBS2 67 ONLINE  
69 \_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 undotbs1  
datafile '/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 undo

NAME TYPE VALUE  
\------------------------------------ ----------- --------------  
undo\_management string AUTO  
undo\_retention integer 900  
undo\_tablespace string UNDOTBS1

4、等待原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://space.itpub.net/12778571/viewspace-689354][http_space.itpub.net_12778571_viewspace-689354]

[http_space.itpub.net_12778571_viewspace-689354]: http://space.itpub.net/12778571/viewspace-689354