当前位置: 首页 > Oracle, oracle 10g, oracle 11g > 正文

UNDO表空间无法扩展发现的问题

前几天在帮开发人员往中间库迁移一批数据的时候,遇到UNDO表空间无法自动扩展的问题,告警日志如下:

Tue Jul 21 11:26:13 2015
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was suspended due to
    ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTB1'
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was resumed
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was suspended due to
    ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTB1'
Tue Jul 21 11:27:21 2015
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was resumed
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was suspended due to
    ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTB1'
Tue Jul 21 11:31:29 2015
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was resumed
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was suspended due to
    ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTB1'
Tue Jul 21 11:33:52 2015
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was resumed
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was suspended due to
    ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTB1'
Tue Jul 21 11:34:14 2015
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was resumed
statement in resumable session 'CHGSHS.SYS_IMPORT_TABLE_01.2' was suspended due to
    ORA-30036: unable to extend segment by 8 in undo tablespace 'UNDOTB1'

看到UNDO表空间不足,很自然的往UNDO表空间加了一个数据文件,并没有仔细看错误信息,在加文件的时候还遇到了数据文件已存在的问题。

sys@IVLDB(10.0.97.40)> select tablespace_name,file_name from dba_data_files where tablespace_name='UNDOTBS1';

TABLESPACE_NAME			FILE_NAME
---------------			------------------------------------------
UNDOTBS1				/data/oracle/datafile/IVLDB/undotbs01.dbf

sys@IVLDB(10.0.97.40)> alter tablespace UNDOTBS1 add datafile '/data/oracle/datafile/IVLDB/undotbs02.dbf' size 5G autoextend on;
alter tablespace UNDOTBS1 add datafile '/data/oracle/datafile/IVLDB/undotbs02.dbf' size 5G autoextend on
*
ERROR at line 1:
ORA-01537: cannot add file '/data/oracle/datafile/IVLDB/undotbs02.dbf' - file already part of
database

去操作系统查看,发现的确有好多的UNDO相关的数据文件。

[oracle@SL010A-IVO03 trace]$ cd /data/oracle/datafile/IVLDB/
[oracle@SL010A-IVO03 IVLDB]$ ll undo*
-rw-r----- 1 oracle oinstall 34359730176 Jul 21 11:31 undotbs01.dbf
-rw-r----- 1 oracle oinstall 10737426432 Jul 21 11:31 undotbs02.dbf
-rw-r----- 1 oracle oinstall 21474844672 Jul 21 11:31 undotbs03.dbf
-rw-r----- 1 oracle oinstall 32212262912 Jul 21 11:40 undotbs04.dbf

啥情况,难道是遗留的?这个表空间明明只有一个数据文件,先加上在说。

sys@IVLDB(10.0.97.40)> alter tablespace UNDOTBS1 add datafile '/data/oracle/datafile/IVLDB/undotbs05.dbf' size 5G autoextend on;

Tablespace altered.

加完之后,突然想到,是不是数据库有多个UNDO表空间,查看下发现还真有好几个。

sys@IVLDB(10.0.97.40)> select name from v$tablespace;

NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
TEST
TS_WORK
TS_IDX
UNDOTB2
UNDOTB1

10 rows selected.

那么数据库用的是哪个呢?

sys@IVLDB(10.0.97.40)> show parameter undo

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
undo_management                      string      AUTO
undo_retention                       integer     900
undo_tablespace                      string      UNDOTB1

坑爹啦,数据库用的竟然不是默认的UNDO表空间,这是什么情况?数据库建3个undo表空间干什么?只能用一个呀,这是个单实例的数据库,猜测可能是添加UNDO表空间的人把UNDO和TEMP表空间搞混了,以为每个用户都可以使用不同的UNDO表空间。可是当前服务器的磁盘空间已经很紧张了,直接删掉无用的UNDO表空间。

sys@IVLDB(10.0.97.40)> drop tablespace UNDOTB2  including contents and datafiles;

Tablespace dropped.

sys@IVLDB(10.0.97.40)> alter tablespace UNDOTB1 add datafile '/data/oracle/datafile/IVLDB/undotbs02.dbf' size 5G autoextend on;

Tablespace altered.

sys@IVLDB(10.0.97.40)> drop tablespace  UNDOTBS1 including contents and datafiles;

Tablespace dropped.

sys@IVLDB(10.0.97.40)> select tablespace_name,file_name from dba_data_files where tablespace_name='UNDOTB1'; 

TABLESPACE_NAME                FILE_NAME
------------------------------ --------------------------------------------------
UNDOTB1                        /data/oracle/datafile/IVLDB/undotbs02.dbf
UNDOTB1                        /data/oracle/datafile/IVLDB/undotbs04.dbf

这个项目跟久了,奇葩问题无处不在。

本文固定链接: http://www.dbdream.com.cn/2015/07/undo%e8%a1%a8%e7%a9%ba%e9%97%b4%e6%97%a0%e6%b3%95%e6%89%a9%e5%b1%95%e5%8f%91%e7%8e%b0%e7%9a%84%e9%97%ae%e9%a2%98/ | 信春哥,系统稳,闭眼上线不回滚!

该日志由 dbdream 于2015年07月27日发表在 Oracle, oracle 10g, oracle 11g 分类下, 你可以发表评论,并在保留原文地址及作者的情况下引用到你的网站或博客。
原创文章转载请注明: UNDO表空间无法扩展发现的问题 | 信春哥,系统稳,闭眼上线不回滚!
关键字: , ,

UNDO表空间无法扩展发现的问题:等您坐沙发呢!

发表评论

快捷键:Ctrl+Enter