有人误在RAC端创建本地数据文件
数据库后台告警
Errors in file /orasoft/oracle/diag/rdbms/ptdb/ptdb1/trace/ptdb1_dbw0_4838.trc:
ORA-01157: cannot identify/lock data file 64 - see DBWR trace file
ORA-01110: data file 64: ‘/orasoft/product/11.2.0/db_1/ts_st.dbf’
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
1.无法正常查询
select file_name,tablespace_name from dba_data_files where tablespace_name=‘TS_ST’;
2.发现该数据文件的物理文件已经被删除
select name from v$datafile;
3.客户确认后 ,进行逻辑删除
alter database datafile 64 offline drop;
4.再次查询发现数据文件的状态为AVAILABLE
select file_name,tablespace_name from dba_data_files where tablespace_name=‘TS_ST’;
5.删除整个表空间及数据文件
drop tablespace TL_SL including contents and datafiles;
正常情况来说,是可以删掉,但是删除表空间出现ORA-22868错误
在当前要删除的表空间中含有大类型比如blob类型的字段,并且该段可能存储于不同的表空间内
6.EVENT_FORM存在bolb类型的字段,drop掉即可
select owner,table_name,tablespace_name from dba_lobs where tablespace_name=‘TS_ST’;
7.drop成功
drop tablespace TS_ST including contents and datafiles;