ORA-01157報錯"cannot identify/lock data file"解決

標籤：方式   targe   狀態   檔案刪除   tmp   lte   lin   acl   ann   

 

 sqlplus以管理員方式接入資料庫，啟動時出現報錯，如下：> sqlplus "/as sysdba"SQL> startup......ORA-01157: cannot identify/lock data file 8 - see DBWR trace fileORA-01110: data file 8: ‘/tmp/test.dbf‘ 查看資料庫記錄檔alert_$ORACLE_SID.log,存在對應報錯資訊：Errors in file ....../aix85_psp0_454886.trc:查看此trc檔案中記錄資訊：*** SERVICE NAME:() 2011-07-18 10:05:00.769*** SESSION ID:(332.1) 2011-07-18 10:05:00.769ORA-01157: cannot identify/lock data file 8 - see DBWR trace fileORA-01110: data file 8: ‘/tmp/test.dbf‘ORA-27037: unable to obtain file statusIBM AIX RISC System/6000 Error: 2: No such file or directoryAdditional information: 3ORA-01157: cannot identify/lock data file 9 - see DBWR trace fileORA-01110: data file 9: ‘/tmp/test1.dbf‘ORA-27037: unable to obtain file statusIBM AIX RISC System/6000 Error: 2: No such file or directoryAdditional information: 3ORA-01157: cannot identify/lock data file 10 - see DBWR trace fileORA-01110: data file 10: ‘/tmp/indx1.dbf‘ORA-27037: unable to obtain file statusIBM AIX RISC System/6000 Error: 2: No such file or directoryAdditional information: 3ORA-01157: cannot identify/lock data file 11 - see DBWR trace fileORA-01110: data file 11: ‘/tmp/test2.dbf‘ORA-27037: unable to obtain file statusIBM AIX RISC System/6000 Error: 2: No such file or directoryAdditional information: 3ORA-01157: cannot identify/lock data file 12 - see DBWR trace file 查詢分析：查看/tmp目錄中已經沒有任何dbf檔案存在，查詢確認/tmp目錄中的幾個dbf是在之前資料庫運行過程中添加的測試空間檔案，測試完畢已經刪除，而且後續沒有任何人訪問到這幾個檔案，因此也沒有任何資料庫報錯，直到資料庫執行個體宕掉並重啟時出現啟動失敗。 解決辦法：既然出現報錯的幾個dbf檔案已經不用，則解決辦法相對簡單，只要將對應的資料檔案刪除，並繼續刪除對應新增的資料表空間即可。操作過程如下：SQL> shutdown immediate;SQL> startup mount;SQL> select file#,name,status from v$datafile;SQL> alter database datafile ‘/tmp/test.dbf‘ offline drop;      //此處若不加drop會報錯再次查看v$datafile表會發現對應的幾個dbf檔案狀態由ONLINE變為RECOVERSQL> select * from v$tablespace;SQL> drop tablespace test including contents cascade constraints;......刪除完畢，再次執行startup成功。

ORA-01157報錯"cannot identify/lock data file"解決