Oracle 当尝试drop一个表空间时ora-1157 无法标识/锁定数据文件 - 请参阅 DBWR 跟踪文件

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 ： 13764045638 QQ号:47079569 邮箱：service@parnassusdata.com

ora-1157 无法标识/锁定数据文件  - 请参阅 DBWR 跟踪文件

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.