问题:由于误删除数据文件,导致无法正常删除该表空间
以下实验对问题做了重现,并且提供了详细的解决方案,前提是故障出现后操作系统没有重启
实验:
1.创建表空间TEST001,并创建表A默认表空间为TEST001,并且插入数值
SYS@EMREP> create tablespace test001 datafile '/u01/app/oracle/oradata/EMREP/test001.dbf' size 1m;
Tablespace created.
SYS@EMREP> create table a(id number(10)) tablespace TEST001;
Table created.
SYS@EMREP> insert into a values(1);
1 row created.
SYS@EMREP> commit;
Commit complete.
2.模拟从操作系统删除数据文件
rm -rf /u01/app/oracle/oradata/EMREP/test001.dbf
3.此时删除表空间,发现报错
drop tablespace TEST001 INCLUDING CONTENTS AND DATAFILES
*
ERROR at line 1:
ORA-01116: error in opening database file 2
ORA-01110: data file 2: '/u01/app/oracle/oradata/EMREP/test001.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
4.解决方案
1)找到进程号
ps -ef|grep dbw0 |grep -v grep
2)进入进程句柄位置
cd /proc/25334/fd
3)查看被删除文件句柄
ls -lrth
4)把该句柄拷贝回原位置
cp /proc/25334/fd/263 /u01/app/oracle/oradata/EMREP/test001.dbf
5)此时,可以正常删除该表空间,问题解决
SYS@EMREP> drop tablespace TEST001 INCLUDING CONTENTS AND DATAFILES;
Tablespace dropped.