诗檀软件专业数据库修复团队
ORA-01157
oerr ora 1157
01157, 00000, "cannot identify/lock data file %s - see DBWR trace file"
// *Cause: The background process was either unable to find one of the data
// files or failed to lock it because the file was already in use.
// The database will prohibit access to this file but other files will
// be unaffected. However the first instance to open the database will
// need to access all online data files. Accompanying error from the
// operating system describes why the file could not be identified.
// *Action: Have operating system make file available to database. Then either
// open the database or do ALTER SYSTEM CHECK DATAFILES.
适用于：
Oracle Database - Enterprise Edition – 版本 11.2.0.3 及以上
本文信息适用于任何平台。
症状
查看alert.log文件，报告以下错误：
Errors in file /u01/app/oracle/diag/rdbms/ps2jfmsm/ps2jfmsm1/trace/ps2jfmsm1_m000_27934.trc:
ORA-01157: cannot identify/lock data file 76 - see DBWR trace file
ORA-01110: data file 76: '/u01/app/oracle/product/11.2.0.3/db_1/dbs/glacloseindexdata'
更改
数据文件在OS级别被删除。
原因
该错误是由于数据文件丢失。
检查位置 /u01/app/oracle/product/11.2.0.3/db_1/dbs
显示文件不在那里
解决方案
首先检查数据文件在该表空间是唯一的：
SQL> select ts# from v$datafile where file# = 76;
TS#
----------
33
SQL> select file# from v$datafile where ts# = 33;
FILE#
----------
76
所以只有数据文件76存在于表空间33
SQL> select name from v$tablespace where ts# = 33;
NAME
------------------------------
GLACLOSEINDEX
Try to drop the tablespace:
SQL> drop tablespace glacloseindex;
drop tablespace glacloseindex
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 76 - see DBWR trace file
ORA-01110: data file 76:
'/u01/app/oracle/product/11.2.0.3/db_1/dbs/glacloseindexdata'
SQL> select * from v$recover_file;
no rows selected
So the datafile must still be online.
SQL> alter database datafile 76 offline drop;
Database altered.
SQL>
SQL>
SQL> drop tablespace glacloseindex;
Tablespace dropped.