ORA-01157: cannot identify/lock data file n 故障一例

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命令