eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

« One Story About Love | Blog首页 | Oracle HowTo:如何通过RMAN进行裸设备和文件系统之间的数据文件迁移 »

Oracle HowTo:如何Shrink 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案例一则
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

空间已经释放。

历史上的今天...
    >> 2011-11-30文章:
    >> 2008-11-30文章:
    >> 2006-11-30文章:

无觅

By eygle on 2005-11-30 11:28 | Comments (12) | HowTo | 552 |

12 Comments

曾经这么做过,没有问题。
不过,这个数据库得undo为什么会这么大呢?undo应该会自动shrink到optimal大小的啊。
tmp表空间如果设置了循环使用是不是就不会变得这么大了?我一般会设置tmp有一个最大值,然后循环使用,不知道是否合理。

大,是因为有大事务的原因!
而且Oracle不能自动Shrink的情况太多了.

提个问题,让你完善你的文章,呵呵。
加入磁盘空间现在已经不允许再创建一个新的undo表空间了呢?

我这个就没空间了的!

随便把一个数据表空间resize缩小了100M,就行了...

:D

问一下:要是UNDO一直被占用怎么办?

我一般都会设定一个特定大小的UNDO,关键是在需求test时候把握好。我想正式上线后是不会发生这种事情滴。或者也会给UNDO一个最大值!:)

hehe
应该顺手把temp也干掉……

temp给resize了,没写进来:)

我的数据库有1.5T大小,经常需要一些两个大表的连接(一个表将近100G),temp扩到了140G还是提示不够,怎么办?eygle请给点建议

通常的方法是,优化相关的SQL,调整PGA以优化排序

如果是DSS系统,类似情况还是常见的.

怎么看undotbs1已经不再使用了啊,看哪个字段的。


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com