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/