[oracle] housekeep UNDO tablespace 碰到的问题

大前天加班的时候，忽然收到一封warning message；讲有台机器上的某个slice空间满掉了。
正在为无所事事发愁，就赶忙登陆过去看看。却原来是一个oradata的目录，里面就都只有oracle的datafile.

比较了一下实际用掉的空间和datafile占用的空间
select tablespace_name, round(bytes/1024/1024,0) as "used MB" from sm$ts_used order by "used MB";

set long 10000
set pages 0
set line 120
col file_name for a65
select file_name,bytes/1024/1024 mb from dba_data_files;

然后通过resize datafile清理出一些空间。

问题发生在我housekeep UNDOTBS1 表空间的时候。UNDOTBS1占用了700M的空间，但是实际上只用到了20M.
所以我想也清理掉它：
方法：1). create UNDOTBS2 2). 设置UNDOTBS2为系统UNDO表空间 3).drop UNDOTBS1

sys@xxxx> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '/opt/oradata/xxxx/system/undotbs02.dbf' SIZE 20M REUSE AUTOEXTEND ON;

Tablespace created.

sys@xxxx> ALTER SYSTEM SET UNDO_TABLESPACE = UNDOTBS2;

System altered.

但是在我drop UNDOTBS1的时候碰到了如下错误：

Undo Tablespace 1 moved to Pending Switch-Out state.
*** active transactions found in undo tablespace 1 during switch-out.
Fri Jan 30 17:47:08 2009
ALTER SYSTEM SET undo_tablespace='UNDOTBS2' SCOPE=BOTH;
Fri Jan 30 17:48:38 2009
Undo Tablespace 1 moved to Pending Switch-Out state.
Fri Jan 30 17:51:36 2009
drop TABLESPACE UNDOTBS1
Fri Jan 30 17:51:36 2009
ORA-30013 signalled during: drop TABLESPACE UNDOTBS1...
Fri Jan 30 17:53:38 2009
Undo Tablespace 1 moved to Pending Switch-Out state.
Fri Jan 30 17:58:38 2009
Undo Tablespace 1 moved to Pending Switch-Out state.
Fri Jan 30 18:03:38 2009

这说明还有active的undo segment在UNDOTBS1上面。一般来讲，等到这个active的undo segment变inactive之后我的UNDOTBS1就可以offline了；也就是说当相关的transaction commit掉之后就可以了。

后来等了半个小时，我发现这个undo segment还是active的；仔细检查，发现v$transaction的count竟然是０；也就是说当前没有未commit的transaction...

看来是死事务？

sys@xxxx> select distinct KTUXECFL,count(*) from x$ktuxe group by KTUXECFL;

KTUXECFL COUNT(*)
------------------------ ----------

SCO|COL|REV|DEAD|EXTDTX 1
NONE 1921

参照了eygle网站上的一篇文章，确实发现了一个有可能是死事务的条目。

但是奇怪的是，这个条目的KTUXESIZ恒久为１，并不变化。

sys@xxxx> select ADDR,KTUXEUSN,KTUXESLT,KTUXESQN,KTUXESIZ
  2 from x$ktuxe where KTUXEUSN=10 and KTUXESLT=39;

ADDR KTUXEUSN KTUXESLT KTUXESQN KTUXESIZ
---------------- ---------- ---------- ---------- ----------

FFFFFFFF7A474318 10 39 12412 1

后来想会不会等段时间这个undo segment会自己inactive掉？

我就一直等到了今天（距离问题发生已经有３天时间了）。但是不幸的是，这个问题依然存在。

后来在沛三的指导下，解决了这个问题；但是我还是不知道为什么会出现这种情况．

方法如下：
1. create pfile

sys@xxxx> create pfile='/tmp/initxxxx.ora' from spfile;


2. 指定undo_management='MANUAL'和_offline_rollback_segments=(_SYSSMU10$)在/tmp/initxxxx．ora
值得注意的是，_offline_rollback_segments是oracle的隐藏参数，后面的_SYSSMU10$就是我的出问题的那个 undo segment．

3. stop db; start db with the modified pfile; drop undo datafile

sys@xxxx> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


sys@xxxx> startup mount pfile='/tmp/initxxxx.ora';
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 2031520 bytes
Variable Size 511705184 bytes
Database Buffers 16777216 bytes
Redo Buffers 6356992 bytes
Database mounted.
sys@xxxx> alter database datafile '/opt/oradata/xxxx/system/undotbs01.dbf' offline drop;

Database altered.
sys@xxxx> alter database open;


4.　停掉db,再以原来的spfile来启动

sys@xxxx> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
sys@xxxx> startup
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 2031520 bytes
Variable Size 511705184 bytes
Database Buffers 16777216 bytes
Redo Buffers 6356992 bytes
Database mounted.
Database opened.