有人误在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;

ITPub博客

oracle ORA-01157: cannot identify/lock data file 64