今天下午在rac节点2做statspack的时候，不小心将perfstat表空间的dbf文件创建到了本地磁盘上，没有放到存储上。用户登录时候报错

SQL> select file_name from dba_data_files;
ERROR:
ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
ORA-01110: data file 12: '/home/oracle/perfstat.dbf'

SQL> select file#,name from v$datafile;

     FILE#
----------
NAME
------------------------------------------------------------------------------
         1
+DATADG/xhhdb/datafile/system.256.804356809
         2
+DATADG/xhhdb/datafile/sysaux.257.804356809
         3
+DATADG/xhhdb/datafile/undotbs1.258.804356809
         4
+DATADG/xhhdb/datafile/users.259.804356809
         5
+DATADG/xhhdb/datafile/undotbs2.264.804357053
         6
+DATADG/xhhdb/datafile/ts_fin_data.dbf
         7
+DATADG/xhhdb/datafile/ts_finsch_data.dbf
         8
+DATADG/xhhdb/datafile/ts_finresp.dbf
         9
+DATADG/xhhdb/datafile/ts_ods.dbf
        10
+DATADG/xhhdb/datafile/portal.dbf
        11
+DATADG/xhhdb/datafile/ts_comm_data.dbf
        12
/home/oracle/perfstat.dbf

12 rows selected.

SQL>
物理文件还在

先将表空间修改为offline，在drop

SQL> alter database datafile '/home/oracle/perfstat.dbf' offline drop;

Database altered.

SQL>
SQL>
SQL> select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------------------------------
+DATADG/xhhdb/datafile/users.259.804356809
+DATADG/xhhdb/datafile/undotbs1.258.804356809
+DATADG/xhhdb/datafile/sysaux.257.804356809
+DATADG/xhhdb/datafile/system.256.804356809
+DATADG/xhhdb/datafile/undotbs2.264.804357053
+DATADG/xhhdb/datafile/ts_fin_data.dbf
+DATADG/xhhdb/datafile/ts_finsch_data.dbf
+DATADG/xhhdb/datafile/ts_finresp.dbf
+DATADG/xhhdb/datafile/ts_ods.dbf
+DATADG/xhhdb/datafile/portal.dbf
+DATADG/xhhdb/datafile/ts_comm_data.dbf
/home/oracle/perfstat.dbf

12 rows selected.

SQL> drop tablespace perfstat including contents;

Tablespace dropped.

SQL> select file_name from dba_data_files;

FILE_NAME
------------------------------------------------------------------------------
+DATADG/xhhdb/datafile/users.259.804356809
+DATADG/xhhdb/datafile/undotbs1.258.804356809
+DATADG/xhhdb/datafile/sysaux.257.804356809
+DATADG/xhhdb/datafile/system.256.804356809
+DATADG/xhhdb/datafile/undotbs2.264.804357053
+DATADG/xhhdb/datafile/ts_fin_data.dbf
+DATADG/xhhdb/datafile/ts_finsch_data.dbf
+DATADG/xhhdb/datafile/ts_finresp.dbf
+DATADG/xhhdb/datafile/ts_ods.dbf
+DATADG/xhhdb/datafile/portal.dbf
+DATADG/xhhdb/datafile/ts_comm_data.dbf

11 rows selected.

SQL>
SQL>
alert日志

ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
ORA-01110: data file 12: '/home/oracle/perfstat.dbf'
File 12 not verified due to error ORA-01157
ORA-1157 signalled during: drop tablespace perfstat...
Tue Jan 29 16:27:04 2013
alter database datafile '/home/oracle/perfstat.dbf' offline drop
Completed: alter database datafile '/home/oracle/perfstat.dbf' offline drop
Tue Jan 29 16:28:26 2013
drop tablespace perfstat including contents
Completed: drop tablespace perfstat including contents

drop后，恢复正常。

ITPub博客

ORA-01110: data file 12: '/home/oracle/perfstat.dbf'

最新文章