今天下午在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'
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
----------
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;
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
------------------------------------------------------------------------------
+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
------------------------------------------------------------------------------
+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日志
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
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博客 ” ,链接:http://blog.itpub.net/8541492/viewspace-753504/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8541492/viewspace-753504/