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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/11590946/viewspace-750214/