[转帖]如何回缩Undo表空间释放过度占用空间_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛

总帖数

每页帖数

1/1页

返回列表

发起投票

查看: 3815 | 回复: 0

主题： [转帖]如何回缩Undo表空间释放过度占用空间

kim

注册用户

等级：中校
经验：1729
发帖：222
精华：0
注册：2011-7-21
状态：离线
发送短消息息给kim

加好友发送短消息息给kim

发消息

发表于：

2011-8-2 9:28:44 | [全部帖] [楼主帖]

楼主

摘要：在使用Oracle9i/Oracle10g的自动Undo管理表空间（AUM）特性时，经常会因为各种原因而导致Undo表空间过度扩展。本文通过一则案例诊断，介绍如何收缩过度扩展的Undo表空间，释放占用空间。

环境:

OS: Red Hat Enterprise Linux AS release 4 (Nahant)
DB: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

某日，一台Oracle10gR2数据库报出如下错误:

ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in  tablespace SYSAUX
ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in  tablespace SYSAUX
ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in  tablespace SYSAUX
ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in  tablespace SYSAUX
ORA-1653: unable to extend table SYSMAN.MGMT_SYSTEM_ERROR_LOG by 8 in  tablespace SYSAUX

数据库无法正常使用。

登陆检查，发现是SYSAUX表空间空间用尽,不能扩展,尝试手工扩展SYSAUX表空间:

alter database datafile '+ORADG/danaly/datafile/sysaux.266.600173881' resize 800m
Tue Nov 29 23:31:38 2005
ORA-1237 signalled during: alter database datafile '+ORADG/danaly/datafile/sysaux.266.600173881' resize 800m...

出现ORA-1237错误，提示空间不足。这时候我们认识到是磁盘空间可能被用完了.

是谁“偷偷的”用了那么多空间呢(本来有几十个G的Free磁盘空间的)?

通过如下脚本检查数据库表空间占用空间情况:

SQL> select tablespace_name,sum(bytes)/1024/1024/1024 GB
2  from dba_data_files group by tablespace_name
3  union all
4  select tablespace_name,sum(bytes)/1024/1024/1024 GB
5  from dba_temp_files group by tablespace_name order by GB;
TABLESPACE_NAME                        GB
------------------------------ ----------
USERS                          .004882813
UNDOTBS2                        .09765625
SYSTEM                         .478515625
SYSAUX                         .634765625
WAPCM_TS_VISIT_DETAIL            .9765625
HY_DS_DEFAULT                           1
MINT_TS_DEFAULT                         1
MMS_TS_DATA2                        1.375
MMS_IDX_SJH                             2
MMS_TS_DEFAULT                          2
IVRCN_TS_DATA                           2
TABLESPACE_NAME                        GB
------------------------------ ----------
MMS_TS_DATA1                            2
CM_TS_DEFAULT                           5
TEMP                           20.5498047
UNDOTBS1                       27.1582031
15 rows selected.

不幸的发现，UNDO表空间已经扩展至27G，而TEMP表空间也扩展至20G，这2个表空间加起来占用了47G的磁盘空间,导致了空间不足。

显然曾经有大事务占用了大量的UNDO表空间和Temp表空间，Oracle的AUM(Auto Undo Management)从出生以来就经常出现只扩展,不收缩(shrink)的情况(通常我们可以设置足够的UNDO表空间大小,然后取消其自动扩展属性)。

现在我们可以采用如下步骤回收UNDO空间:

1.确认文件

SQL> select file_name,bytes/1024/1024 from dba_data_files
2 where tablespace_name like 'UNDOTBS1';
FILE_NAME
--------------------------------------------------------------------------------
BYTES/1024/1024
---------------
+ORADG/danaly/datafile/undotbs1.265.600173875
27810

2.检查UNDO Segment状态

SQL> select usn,xacts,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
2  from v$rollstat order by rssize;
USN      XACTS RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS
---------- ---------- --------------------- ---------------------- ----------
0          0            .000358582             .000358582          0
2          0            .071517944             .071517944          0
3          0             .13722229              .13722229          0
9          0            .236984253             .236984253          0
10          0            .625144958             .625144958          0
5          1            1.22946167             1.22946167          0
8          0            1.27175903             1.27175903          0
4          1            1.27895355             1.27895355          0
7          0            1.56770325             1.56770325          0
1          0            2.02474976             2.02474976          0
6          0             2.9671936              2.9671936          0
11 rows selected.

3.创建新的UNDO表空间

SQL> create undo tablespace undotbs2;
Tablespace created.

4.切换UNDO表空间为新的UNDO表空间

SQL> alter system set undo_tablespace=undotbs2 scope=both;
System altered.

此处使用spfile需要注意，以前曾经记录过这样一个案例，Oracle诊断案例-Spfile案例一则。另外，如果使用的RAC环境，请注意，修改spfile时，要指定sid参数。

5.等待原UNDO表空间所有UNDO SEGMENT OFFLINE

SQL> select usn,xacts,status,rssize/1024/1024/1024,hwmsize/1024/1024/1024,shrinks
2 from v$rollstat order by rssize;
USN      XACTS STATUS          RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS
---------- ---------- --------------- --------------------- ---------------------- ----------
14          0 ONLINE                     .000114441             .000114441          0
19          0 ONLINE                     .000114441             .000114441          0
11          0 ONLINE                     .000114441             .000114441          0
12          0 ONLINE                     .000114441             .000114441          0
13          0 ONLINE                     .000114441             .000114441          0
20          0 ONLINE                     .000114441             .000114441          0
15          1 ONLINE                     .000114441             .000114441          0
16          0 ONLINE                     .000114441             .000114441          0
17          0 ONLINE                     .000114441             .000114441          0
18          0 ONLINE                     .000114441             .000114441          0
0          0 ONLINE                     .000358582             .000358582          0
USN      XACTS STATUS          RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS
---------- ---------- --------------- --------------------- ---------------------- ----------
6          0 PENDING OFFLINE             2.9671936              2.9671936          0
12 rows selected.

等候一段时间，再次查看:

11:32:11 SQL> /
USN      XACTS STATUS          RSSIZE/1024/1024/1024 HWMSIZE/1024/1024/1024    SHRINKS
---------- ---------- --------------- --------------------- ---------------------- ----------
15          1 ONLINE                     .000114441             .000114441          0
11          0 ONLINE                     .000114441             .000114441          0
12          0 ONLINE                     .000114441             .000114441          0
13          0 ONLINE                     .000114441             .000114441          0
14          0 ONLINE                     .000114441             .000114441          0
20          0 ONLINE                     .000114441             .000114441          0
16          0 ONLINE                     .000114441             .000114441          0
17          0 ONLINE                     .000114441             .000114441          0
18          0 ONLINE                     .000114441             .000114441          0
19          0 ONLINE                     .000114441             .000114441          0
0          0 ONLINE                     .000358582             .000358582          0
11 rows selected.
Elapsed: 00:00:00.00

6.删除原UNDO表空间

11:34:00 SQL> drop tablespace undotbs1 including contents;
Tablespace dropped.
Elapsed: 00:00:03.13

7.检查空间情况

由于我使用的ASM管理,可以使用10gR2提供的信工具asmcmd来察看空间占用情况.

[oracle@danaly ~]$ export ORACLE_SID=+ASM
[oracle@danaly ~]$ asmcmd
ASMCMD> du
Used_MB Mirror_used_MB
21625 21625
ASMCMD> exit

至此空间已经释放。