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

1 2 下一頁