oracle 扩容undo,诊断案例之-释放过度扩展的Undo空间

从Oracle9i开始，当我们使用AUM管理时，通常会选择设置UNDO表空间自动扩展，这就带来了另外一个问题，经常会出现UNDO表空间过度扩展而不能回缩的问题。这类问题有的是因为Bug引起的，以下的案例来自Oracle10g，但是对于Oracle9i同样适用。

环境: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

------------------------------ ----------

UNDOTBS2                        .09765625

SYSTEM                         .478515625

SYSAUX                         .634765625

。。。。。。。

IVRCN_TS_DATA                           2

MMS_TS_DATA1                            2

CM_TS_DEFAULT                           5

TEMP                           20.5498047

UNDOTBS1                       28.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状态

发现有的回滚段大小已经扩展到了约3G的大小：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需要注意，需要让修改同时变更到spfile文件。

5.等待原UNDO表空间所有UNDO SEGMENT OFFLINESQL> 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

6          0 PENDING OFFLINE             2.9671936              2.9671936          0

12 rows selected.

确认原回滚表空间所有回滚段都正常OFFLINE: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.

6.删除原UNDO表空间11:34:00 SQL> drop tablespace undotbs1 including contents;

Tablespace dropped.

8.检查空间情况

由于我使用的ASM管理,可以使用10gR2提供的信工具asmcmd来察看空间占用情况.[oracle@danaly ~]$ export ORACLE_SID=+ASM

[oracle@danaly ~]$ asmcmd

ASMCMD> du

Used_MB      Mirror_used_MB

21625               21625

ASMCMD> exit

此时空间已经释放。本案例包含了常规UNDO表空间重建、切换等过程，这也是UNDO表空间常规维护操作之一，需要熟记。