ORA-01157: cannot identify/lock data file n 故障一例-阿里云开发者社区

最近在使用swingbench的时候碰到了ORA-01157故障，下面是其具体描述与解决。

1、故障现象
--查询视图dba_data_files时出现ORA-01157故障
SQL> select file_name,tablespace_name from dba_data_files where tablespace_name='SOE';
select file_name,tablespace_name from dba_data_files where tablespace_name='SOE'
                                      *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/oracle/db/dbs/soe.dbf'

--尝试drop tablespace 收到同样的错误
SQL> drop tablespace soe including contents and datafiles;
drop tablespace soe including contents and datafiles
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/oracle/db/dbs/soe.dbf'

2、分析
--从错误号后的文字可判断DBWR不能识别或锁定文件号6，后面的ORA-01110给出了具体的文件位置
--下面是错误号对应的具体描述
SQL> ho oerr ora 01157
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.

--上面的描述指出了后台进程不能寻找到数据文件或者是因为文件在被其他进程使用而DBWR无法对其锁定。
--对于象这类文件数据库将禁止对其进行访问，而其他数据文件则不受影响。
--给出的决办法是确认错误号后的数据文件是否存在或可用，以及在open状态下执行ALTER SYSTEM CHECK DATAFILES命令

3、解决
--尝试执行alter system check datafiles
SQL> alter system check datafiles;

System altered.

--执行后故障依旧如下
SQL> select file_name,tablespace_name from dba_data_files where tablespace_name='SOE';
select file_name,tablespace_name from dba_data_files where tablespace_name='SOE'
                                      *
ERROR at line 1:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/oracle/db/dbs/soe.dbf'

--查看数据字典记录的信息表明当前的表空间为online状态
SQL> select tablespace_name,status,contents from dba_tablespaces where tablespace_name='SOE';

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SOE                            ONLINE    PERMANENT

--查看alert日志文件，也给出了该错误提示，提示给出了tarce文件
oracle@v2048db01p:/u01/oracle/admin/SYISDB/bdump> tail -8 alert_SYISDB1.log
Additional information: 3
Tue Nov 13 09:43:17 2012
Errors in file /u01/oracle/admin/SYISDB/bdump/syisdb1_dbw0_5925.trc:
ORA-01186: file 6 failed verification tests
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/u01/oracle/db/dbs/soe.dbf'
Tue Nov 13 09:43:17 2012
File 6 not verified due to error ORA-01157

--查看表空间soe对应的数据文件是否存在
oracle@v2048db01p:~> export ORACLE_SID=+ASM1
oracle@v2048db01p:~> asmcmd
ASMCMD> cd +DG2/SYISDB/DATAFILE
ASMCMD> ls
CCDATA.289.799174049
SYSAUX.260.796819341
SYSTEM.259.796819335
UNDOTBS1.261.796819339
UNDOTBS2.257.796819343
USERS.256.796819343
X.290.799234531
ccdata.dbf

ASMCMD> ls *soe*   --#没有任何含soe的数据文件
asmcmd: entry '*soe*' does not exist in directory '+DG2/SYISDB/DATAFILE/'
ASMCMD> ls *SOE*   --#没有任何含soe的数据文件，由此可知表空间soe对应的数据文件已经丢失
asmcmd: entry '*SOE*' does not exist in directory '+DG2/SYISDB/DATAFILE/'

--因此直接删除该表空间及数据文件，注，生产环境不建议此操作
SQL> alter database datafile 6 offline drop;

Database altered.

--再次查看数据字典信息，依然处于Online状态
--Author : Robinson
--Blog   : http://blog.csdn.net/robinson_0612
SQL> select tablespace_name,status,contents from dba_tablespaces where tablespace_name='SOE';

TABLESPACE_NAME                STATUS    CONTENTS
------------------------------ --------- ---------
SOE                            ONLINE    PERMANENT

--下面的查询貌似也有问题，对应的数据文件在上一命令中已经清除了，而此时依旧显示AVAILABLE
SQL> col file_name format a55
SQL> set linesize 160
SQL> select file_name,tablespace_name,status  from dba_data_files where tablespace_name='SOE';

FILE_NAME                                TABLESPACE_NAME                STATUS
---------------------------------------- ------------------------------ ---------
/u01/oracle/db/dbs/soe.dbf               SOE                            AVAILABLE

--尝试在该表空间创建对象，收到了ORA-01658错误
SQL> create table t tablespace soe as select * from dba_objects;
create table t tablespace soe as select * from dba_objects
                                               *
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace SOE

--查看对应的错误信息
--错误信息表明没有足够的连续空间分配初始extent.
SQL> ho oerr ora 01658
01658, 00000, "unable to create INITIAL extent for segment in tablespace %s"
// *Cause:  Failed to find sufficient contiguous space to allocate INITIAL
//          extent for segment being created.
// *Action: Use ALTER TABLESPACE ADD DATAFILE to add additional space to the
//          tablespace or retry with a smaller value for INITIAL

--再次查看状态，发现此时对应的数据文件为RECOVER
SQL> col file_name format a40
SQL> select file_name,tablespace_name,status,ONLINE_STATUS from dba_data_files where tablespace_name='SOE';

FILE_NAME                                TABLESPACE_NAME                STATUS    ONLINE_
---------------------------------------- ------------------------------ --------- -------
/u01/oracle/db/dbs/soe.dbf               SOE                            AVAILABLE RECOVER

--查看v$recover_file视图，给出文件未找到OFFLINE FILE NOT FOUND
SQL> select * from v$recover_file;

     FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME
---------- ------- ------- ----------------------------------------------------------------- ---------- ------------------
         6 OFFLINE OFFLINE FILE NOT FOUND                                                             0

--查看对应的数据文件也不存在
SQL> ho ls -hltr /u01/oracle/db/dbs/soe.dbf
ls: /u01/oracle/db/dbs/soe.dbf: No such file or directory

--删除整个表空间及数据文件
SQL> drop tablespace soe including contents and datafiles;

Tablespace dropped.

--下面的查询表示表空间soe已经被彻底清除
SQL> select * from v$recover_file;

no rows selected

SQL>  select file_name,tablespace_name,status,ONLINE_STATUS from dba_data_files where tablespace_name='SOE';

no rows selected

总结：
  ORA-01157通常由后台进程DBWR锁定而产生。
  如果在恢复期间，如数据库已经mount，而一个或多个数据文件不能打开导致数据库不能open时会出现该提示。
  数据文件丢失，数据文件的许可问题，如数据文件oracle用户没有写权限等都会产生ORA-01157。
  如果open状态的情形下，ORA-01157未列出的数据文件不会受到影响。

补充说明：
  细心的朋友应该可能已经发现当时在检查对应的数据文件的时候，只检查了ASM磁盘是否存在对应的数据文件。
  由于出错数据库为RAC，因此忽略了检查提示中的文件系统对应的数据文件。说来还是不够仔细，狂汗......
  就其原因应该是这样，在使用swingbench时，创建soe表空间时直接一路next，导致将数据文件创建到了文件系统，而文件系统是非共享的。(RAC环境)

更多参考：

ORA-01157错误解决