ORA-01157: cannot identify/lock data file

ORA-01157: cannot identify/lock data file

데이타 파일을 실수로 삭제해버렸다가 DB재시작하게되면 아래처럼 오류가 발생하게 된다.

내가 아닌 누군가가 dbf생성했다가 drop명령을 쓰지않고 unix 콘솔에서 dbf파일을 삭제하게 되면 발생하게되는 오류인데 생각보다 쉽게 해결할 수 있습니다.

ERROR at line 1:
ORA-01157: cannot identify/lock data file 29 - see DBWR trace file
ORA-01110: data file 29:
'/opt/oracle/product/oracle9i/dbs/C:oracleproduct10.2.0oradatapentahoptho_ts.dbf
'

from above datafile name you have realized that its a kinda jerk :s someone has made a datafile with no sense and then he/she have removed the file by O.S command, but he/she did'nt updated database about it !

So during the test when we were starting this database it came untill mount stage and then got stuck !!! i.e.

Problem:

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 29 - see DBWR trace file
ORA-01110: data file 29:
'/opt/oracle/product/oracle9i/dbs/C:oracleproduct10.2.0oradatapentahoptho_ts.dbf
'

so to fix it, i did following:

Solution:


SQL>alter database datafile 29 OFFLINE DROP;
SQL>alter database open;


출처 : http://nayyares.blogspot.kr/2009/08/ora-01157-cannot-identifylock-data-file.html