释放oracle undo表空间,undo表空间释放

一.概述:

使用IMPDP工具导入大表(166G)数据时,报undo表空间不能扩展,导入工作失败.手工停止了impdp后,undo表空间存在无法自动释放的故障.本文主要描述如何通过重建undo表空间来手工释放undo表空间.

数据库环境的描述:

OS: AIX 6.1+HACMP 5.3

DB: ORACLE 10.2.0.5 RAC

二.问题的描述

impdp 导入数据时,报ora-30036错误

$impdp user/passwd directory=imp_dir dumpfile=big_table_%U.dmp parallel=10 logfile=imp_big_table.log

ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS1′[@more@]

检查数据库的归档日志文件,也发现了这个报警

more /oracle/admin/*/bdump/alert_{SID}.log|grep undo

ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS1’

ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS1’

ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS1’

强行终止了impdp的操作,impdp的进程在操作中已不见

# ps -ef|grep impdp

root 8650752 8061396 0 15:02:08 pts/1 0:00 grep impdp

卸载数据文件所在的文件系统时,发现无法下载

SQL>select * from dba_directories

OWNER DIRECTORY_NAME DIRECTORY_PATH

—— ————– —————-

SYS imp_dir /imp_data

#umount /imp_data

umount: 0506-349 Cannot unmount /dev/imp_data: The requested resource is busy.

检查UNDO 表空间的使用情况,如下,存在一个两个比较大的EXPIRED的undo segment.

SQL> select owner,segment_name,sum(bytes/1024/1024) from dba_undo_extents group by owner,segment_name order by 3

OWN SEGMENT_NAME SUM(BYTES/1024/1024)

— —————————— ——————–

………

SYS _SYSSMU12$ 26.125

SYS _SYSSMU9$ 27.125

SYS _SYSSMU7$ 45.125

SYS _SYSSMU18$ 72.125

SYS _SYSSMU20$ 72.125

SYS _SYSSMU10$ 72.125

SYS _SYSSMU1$ 83.125

SYS _SYSSMU6$ 3563.1875

SYS _SYSSMU8$ 9524.4375

SQL> select tablespace_name,status,sum(bytes/1024/1024) from dba_undo_extents group by tablespace_name,status;

TABLESPACE_NAME STATUS SUM(BYTES/1024/1024)

————— ——— ——————–

UNDOTBS1 ACTIVE 47.0625

UNDOTBS2 EXPIRED 291.25

UNDOTBS1 UNEXPIRED 285.875

UNDOTBS1 EXPIRED 13063.6875

在metalink上,查找相关信息,看到有一个bug,说的是：undo表空间不足的时候，不会overwrite expired的undo segment。这个bug是10.2.0.3，9.2.0.8版本上发生，当前数据库的版本为10.2.0.5，不应该是这个bug引起的。

三.问题的分析

数据库的环境是10.2.0.5 RAC，UNDO表空间不足发生在node1的undotbs1上，为了不影响数据库的运行，首先为该表空间增加了空间。

SQL>alter tablespace undotbs1 add datafile ‘/dev/rora_data_03’ size 11518m;

尝试通过重新启动数据库，来释放undo表空间上的内容，结果失败，undotbs1仍然存在大量的expried的segment不能释放(这个数据库的undo 是自动管理的)

因为umount 导入文件系统也失败，考虑是不是因为手工强行停止了impdp的操作，导致了操作系统中还有相关进程没有完全停掉。所以采用了重新启动数据库服务器的方式来释放相关的文件系统的锁。

四.问题的解决

重新启动数据库服务器后，尝试umount 文件系统(/imp_data),结果成功.

现在剩下的问题是,如何shrink undo segment的问题,总不能让那么多的undo表空间就象太空垃圾一样,存在在数据库中.

在metalink上找到相关的文档:How to Shrink the datafile of Undo Tablespace [ID 268870.1]

按照文档介绍的方法,进行了下面的操作

SQL>create undo tablespace undotbs3 datafile ‘/dev/rora_data_02’ size 11518m extent management local;

SQL>alter system set undo_tablespace=’UNDOTBS3′ scope=both sid=’JLZDH1′;

SQL>drop tablespace undotbs1 including contents;

ORA-30013 : undo tablespace undotbs1 is currently in use

在删除undo表空间undotbs1时,报表空间在使用,无法删除的错误.

重新启动数据库,再次查看undo表空间的情况

SQL> show parameter undo

NAME TYPE VALUE

———————————— ———– ——————————

undo_management string AUTO

undo_retention integer 450

undo_tablespace string UNDOTBS3

SQL> select tablespace_name,status,sum(bytes/1024/1024) from dba_undo_extents group by tablespace_name,status;

TABLESPACE_NAME STATUS SUM(BYTES/1024/1024)

—————————— ——— ——————–

UNDOTBS3 UNEXPIRED 6.25

UNDOTBS2 EXPIRED 291.25

UNDOTBS1 EXPIRED 7152.1875

UNDOTBS3 EXPIRED 13.0625

再次尝试删除undotbs1,成功了.

SQL> drop tablespace undotbs1 including contents;

Tablespace dropped.

SQL> select tablespace_name,status,sum(bytes/1024/1024) from dba_undo_extents group by tablespace_name,status;

TABLESPACE_NAME STATUS SUM(BYTES/1024/1024)

—————————— ——— ——————–

UNDOTBS3 UNEXPIRED 2.3125

UNDOTBS2 EXPIRED 291.25

UNDOTBS3 EXPIRED 17

五.总结

impdp/expdp是oracle提供的数据库数据导入/导出的工具,速度比旧工具imp/exp要快很多,但也存在很多的bug,由于加入了job方式的管理,异常出现的机会比较多.曾经遇到过,手工终止impdp操作,导致了system表空间被大量占用的问题.本次又遇到undo表空间不能释放的问题.虽然数据泵有很多问题,但使用了parallel并行处理后,速度提升不是一般的多,所以日常工作中,还是首选数据泵工具.

undo表空间不能释放时,最好的解决办法就是:

1.重新建立一个新的undo表空间.

2.设置数据库的undo表空间为新的undo表空间

3.删除旧的undo表空间及其内容