ORA-01157: cannot identify/lock data file %s - see DBWR trace file的處理

一個測試環境，由於主機工程師更換儲存，在沒有停資料庫的情況下關閉作業系統，導致啟動資料庫時報ORA-01157: cannot identify/lock data file %s - see DBWR trace file錯誤。

從alert日誌中，我們可以看到 /ora10g/test/test.dbf檔案存在許可權問題
Mon Nov 26 17:41:47 2012
Errors in file /ora10g/admin/ora10g/bdump/ora10g_dbw0_7427.trc:
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: '/ora10g/test/test.dbf'
ORA-27037: unable to obtain file status
HP-UX Error: 13: Permission denied
Additional information: 3
ORA-1157 signalled during: ALTER DATABASE OPEN...

檢查日誌發現，datafile 6 '/ora10g/test/test.dbf' 存在問題，進一步檢查
lrwxr-x---   1 root       sys              7 Nov 23 16:38 test -> testbdf
-rw-r-----   1 root       sys        2105344 Nov 23 16:38 test.dbf
-rwxr-x---   1 root       sys            160 Nov 23 16:38 test.sh
drwxr-x---   2 root       sys             96 Nov 23 16:38 testbdf
發現，testbdf 目錄，ora10g使用者沒有讀寫許可權，所以刪除該檔案，這個問題可以解決。
也可以修改testbdf的所有者，問題也可以解決。
chown ora10g:dba testbdf

以下的操作是從資料庫中將問題的資料檔案刪除，讓資料庫啟動。
$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.4.0 - Production on Mon Nov 26 19:16:45 2012

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set linesize 500
SQL> col file_name format a60
SQL> col tablespace_name format a30
SQL>
SQL> select tablespace_name,file_name from dba_data_files;
select tablespace_name,file_name from dba_data_files
                                      *
ERROR at line 1:
ORA-01219: database not open: queries allowed on fixed tables/views only


SQL>
SQL> col name format a60
SQL>
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- ------------------------------------------------------------
         1 /ora10g/oradata/ora10g/system01.dbf
         2 /ora10g/oradata/ora10g/undotbs01.dbf
         3 /ora10g/oradata/ora10g/sysaux01.dbf
         4 /ora10g/oradata/ora10g/users01.dbf
         5 /ora10g/oradata/ora10g/example01.dbf
         6 /ora10g/test/test.dbf

6 rows selected.

SQL>
SQL> set linesize 100
SQL>
SQL> select ts#,file#,name from v$datafile;

       TS#      FILE# NAME
---------- ---------- ------------------------------------------------------------
         0          1 /ora10g/oradata/ora10g/system01.dbf
         1          2 /ora10g/oradata/ora10g/undotbs01.dbf
         2          3 /ora10g/oradata/ora10g/sysaux01.dbf
         4          4 /ora10g/oradata/ora10g/users01.dbf
         6          5 /ora10g/oradata/ora10g/example01.dbf
         7          6 /ora10g/test/test.dbf

6 rows selected.

SQL>
SQL> select ts#,name from v$tablespace;

       TS# NAME
---------- ------------------------------------------------------------
         0 SYSTEM
         1 UNDOTBS1
         2 SYSAUX
         4 USERS
         3 TEMP
         6 EXAMPLE
         7 TEST

7 rows selected.

SQL>
SQL> alter tablespace test offline;
alter tablespace test offline
*
ERROR at line 1:
ORA-01109: database not open


SQL> alter database datafile 6 offline;

Database altered.

SQL> alter database open;

Database altered.

SQL>
SQL>
SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

SQL>
SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME                FILE_NAME
------------------------------ ------------------------------------------------------------
USERS                          /ora10g/oradata/ora10g/users01.dbf
SYSAUX                         /ora10g/oradata/ora10g/sysaux01.dbf
UNDOTBS1                       /ora10g/oradata/ora10g/undotbs01.dbf
SYSTEM                         /ora10g/oradata/ora10g/system01.dbf
EXAMPLE                        /ora10g/oradata/ora10g/example01.dbf

SQL>
SQL>

至此，該問題得到解決。

在正式生產環境，千萬不要隨便刪除資料檔案。如果遇到該問題，可能是資料檔案的許可權變動了，通過主機層面修改許可權即可。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/11590946/viewspace-750214/，如需轉載，請註明出處，否則將追究法律責任。