删除UNDO表空间和数据文件之后磁盘剩余空间不变

OS: Enterprise Linux Server release 5.2 i686

DB: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

症状: 删除UNDO表空间和数据文件之后磁盘剩余空间不变

原因: 有oracle进程正在打开这些文件

解决办法: 重启数据库,或者干掉相关进程

[@more@]

具体过程如下:

服务器空间告急,

1, 首先检查diagnostic_dest下空间使用状况,经查正常;

2, 然后看各个表空间数据文件的分配情况,发现UNDO的数据文件总和已经超过60G了,而实际使用情况也就2G以内, 选择了创建一个新的UNDO 表空间,命名UNDO2,然后将undo_tablespace参数设置为UNDO2;

3, 然后删除旧的UNDO:

SQL> drop tablespace undo including contents and datafiles

命令成功执行

然后再次查看OS磁盘空间,发现剩余空间没有任何变化!!!

在alert日志里, 有删除数据文件的相关记录:

drop tablespace undo including contents and datafiles

Thu Mar 24 17:13:28 2011

Deleted file /opt/oracle/oradata/agile92g/UNDO01AGILE92G.ORA

Deleted file /opt/oracle/oradata/agile92g/UNDO02AGILE92G0228.ORA

Deleted file /opt/oracle/oradata/agile92g/UNDO03AGILE92G0229.ORA

Deleted file /opt/oracle/oradata/agile92g/UNDO03AGILE92G0304.ORA

Deleted file /opt/oracle/oradata/agile92g/UNDO04AGILE92G0308.ORA

Deleted file /opt/oracle/oradata/agile92g/UNDO04AGILE92G0309.ORA

Deleted file /opt/oracle/oradata/agile92g/UNDO05AGILE92G0310.ORA

Completed: drop tablespace undo including contents and datafiles

在对应目录确实已经无法找到文件,确认数据文件已经被删除.

纳闷…

灵机一动想起了用lsof查看一下打开文件的情况:

$ /usr/sbin/lsof | grep UNDO | grep agile92g | grep -v UNDO2

oracle 1658 oracle 14u REG 253,0 6291464192 360873986 /opt/oracle/oradata/agile92g/UNDO03AGILE92G0229.ORA (deleted)

oracle 3560 oracle 19u REG 253,0 34359730176 313498177 /opt/oracle/oradata/agile92g/UNDO01AGILE92G.ORA (deleted)

oracle 3560 oracle 20u REG 253,0 10737426432 360873987 /opt/oracle/oradata/agile92g/UNDO03AGILE92G0304.ORA (deleted)

oracle 3560 oracle 21u REG 253,0 6291464192 360873986 /opt/oracle/oradata/agile92g/UNDO03AGILE92G0229.ORA (deleted)

oracle 3560 oracle 22u REG 253,0 3221233664 360873991 /opt/oracle/oradata/agile92g/UNDO05AGILE92G0310.ORA (deleted)

oracle 3560 oracle 23u REG 253,0 5368717312 360873990 /opt/oracle/oradata/agile92g/UNDO04AGILE92G0309.ORA (deleted)

oracle 3560 oracle 24u REG 253,0 4294975488 360873989 /opt/oracle/oradata/agile92g/UNDO04AGILE92G0308.ORA (deleted)

oracle 3560 oracle 25u REG 253,0 3145736192 360873985 /opt/oracle/oradata/agile92g/UNDO02AGILE92G0228.ORA (deleted)

似乎找到点线索. 看看这两个进程是个什么东西:

$ ps -ef | egrep "1658|3560"

oracle 1658 1 0 Mar23 ? 00:00:01 oracleagile92g (LOCAL=NO)

oracle 3560 1 0 Mar18 ? 00:00:02 ora_q000_agile92g

从v$session中发现这两个进程都处于WAITING状态,而且seconds_in_wait显示都等待好几天了,没有犹豫用alter system kill session干掉其第一个, q000属于DB的后台进程, 在系统上kill -9干掉

之后的结果嘛,如你所料,OS上剩余空间变大了.

文件都删了,还有进程打开着这些文件,这个应该算DB的bug吧.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/17168579/viewspace-1047738/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/17168579/viewspace-1047738/