删除undo表空间遭遇ORA-30013及ORA-01548错误解决方法

今天在一套RAC环境下删除、切换其中一个实例的undo表空间时，遭遇ORA-30013及ORA-01548的错误，下面记录过程及解决方法。1 创建新

删除undo表空间遭遇ORA-30013及ORA-01548错误解决方法

[日期：2014-03-28] 来源：Linux社区  作者：Linux [字体：]

今天在一套RAC环境下删除、切换其中一个实例的undo表空间时，遭遇ORA-30013及ORA-01548的错误，下面记录过程及解决方法。

1 创建新的UNDO表空间UNDOTBS3，并将当前实例的UNDO切换到UNDOTBS3之后，删除旧的UNDOTBS1时，遇到下述错误：

SQL> drop tablespace undotbs1 including contents and datafiles;

drop tablespace undotbs1 including contents and datafiles

*

ERROR at line 1:

ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

2 从上可以看到UNDOTBS1目前正在被使用。查询MetaLink,Unable to Drop Undo Tablespace ORA-30013 [ID 835944.1]获取基本思路，依据该文档给出的提示，执行下述命令：

SQL> select segment_name,owner,tablespace_name,status from dba_rollback_segs

2 where tablespace_name='UNDOTBS1' and status = 'ONLINE';

 

SEGMENT_NAME OWNER TABLESPACE_NAME STATUS

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

_SYSSMU2$ PUBLIC UNDOTBS1 ONLINE

_SYSSMU3$ PUBLIC UNDOTBS1 ONLINE

_SYSSMU6$ PUBLIC UNDOTBS1 ONLINE

_SYSSMU8$ PUBLIC UNDOTBS1 ONLINE

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ KTUXESTA Status,

2 KTUXECFL Flags FROM x$ktuxe WHERE ktuxesta!='INACTIVE';

KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS

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

2 38 583286 PREPARED SCO|COL|REV|DEAD|EXTDTX

3 29 982959 PREPARED SCO|COL|REV|DEAD|EXTDTX

6 14 945326 PREPARED SCO|COL|REV|DEAD|EXTDTX

8 7 957413 PREPARED SCO|COL|REV|DEAD|EXTDTX

13 19 507098 PREPARED SCO|COL|REV|EXTDTX

SQL> select local_tran_id, state from dba_2pc_pending;

LOCAL_TRAN_ID STATE

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

14.28.100017 collecting

2.38.583286 prepared

8.7.957413 prepared

3.29.982959 prepared

6.14.945326 prepared

13.19.507098 prepared

6 rows selected.

3 从上步的结果，结合文档ID 835944.1，基本上可以找到问题的原因：当前数据库中有未结束的分布式事务，而这些未结束的分布式事务占用了UNDOTBS1，最终导致不能删除UNDOTBS1。 文档ID 835944.1给出的解决方案是结束掉这些分布式事务。MetaLink上给出的思路是参照 Note 401302.1文档，而不凑巧的是，现在这篇文档无法查看，不得已，尝试重启数据库并删除UNDOTBS1。 重启之后，alert日志里看到如下错误信息：

*** SERVICE NAME:(SYS$BACKGROUND) 2012-02-10 11:27:15.527

*** SESSION ID:(431.1) 2012-02-10 11:27:15.527

*** 2012-02-10 11:27:15.527

ERROR, tran=14.28.100017, session#=1, ose=0:

ORA-02019: connection description for remote database not found

ORA-02019: connection description for remote database not found

*** 2012-02-10 11:27:50.676

ERROR, tran=14.28.100017, session#=1, ose=0:

ORA-02019: connection description for remote database not found

ORA-02019: connection description for remote database not found

*** 2012-02-10 11:28:42.707

ERROR, tran=14.28.100017, session#=1, ose=0:

ORA-02019: connection description for remote database not found

ORA-02019: connection description for remote database not found

尝试再次删除UNDOTBS1：

SQL> drop tablespace undotbs1 including contents and datafiles;

drop tablespace undotbs1 including contents and datafiles

*

ERROR at line 1:

ORA-01548: active rollback segment '_SYSSMU2$' found, terminate dropping tablespace

这次报出ORA-01548的错误！！！原来还是分布式事务未提交导致的：

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */ KTUXESTA Status,

2 KTUXECFL Flags FROM x$ktuxe WHERE ktuxesta!='INACTIVE';

KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS

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

2 38 583286 PREPARED SCO|COL|REV|DEAD|EXTDTX

3 29 982959 PREPARED SCO|COL|REV|DEAD|EXTDTX

6 14 945326 PREPARED SCO|COL|REV|DEAD|EXTDTX

8 7 957413 PREPARED SCO|COL|REV|DEAD|EXTDTX

13 19 507098 PREPARED SCO|COL|REV|EXTDTX

SQL> select local_tran_id, state from dba_2pc_pending;

LOCAL_TRAN_ID STATE

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

14.28.100017 collecting

2.38.583286 prepared

8.7.957413 prepared

3.29.982959 prepared

6.14.945326 prepared

13.19.507098 prepared

6 rows selected.

SQL>

4 继续MetaLink：ORA-1548 Dropping UNDO Tablespace Distributed Transaction Pending:Prepared / Dead [ID 1321093.1]根据该文档，执行下述命令：

SQL> Select segment_id,segment_name,status,tablespace_name

2 from dba_rollback_segs where status not in ('ONLINE','OFFLINE');

 

SEGMENT_ID SEGMENT_NAME STATUS TABLESPACE_NAME

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

2 _SYSSMU2$ PARTLY AVAILABLE UNDOTBS1

3 _SYSSMU3$ PARTLY AVAILABLE UNDOTBS1

6 _SYSSMU6$ PARTLY AVAILABLE UNDOTBS1

8 _SYSSMU8$ PARTLY AVAILABLE UNDOTBS1

SQL>

You find you have segments that are 'Partly Available' This usually means they still have active transactions pending and you can not drop the tablespace until the transaction is committed or rolled back. 当回滚段状态为Partly Available时，说明还是有事务没结束！！！

SQL> SELECT KTUXEUSN, KTUXESLT, KTUXESQN, /* Transaction ID */

2 KTUXESTA Status,

3 KTUXECFL Flags

4 FROM x$ktuxe

5 WHERE ktuxesta!='INACTIVE'

6 AND ktuxeusn

7 in(2,3,6,8);

 

KTUXEUSN KTUXESLT KTUXESQN STATUS FLAGS

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

2 38 583286 PREPARED SCO|COL|REV|DEAD|EXTDTX

3 29 982959 PREPARED SCO|COL|REV|DEAD|EXTDTX

6 14 945326 PREPARED SCO|COL|REV|DEAD|EXTDTX

8 7 957413 PREPARED SCO|COL|REV|DEAD|EXTDTX

SQL> select local_tran_id, state from dba_2pc_pending;

 

LOCAL_TRAN_ID STATE

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

14.28.100017 collecting

2.38.583286 prepared

8.7.957413 prepared

3.29.982959 prepared

6.14.945326 prepared

13.19.507098 prepared

 

6 rows selected.

 

SQL>