一个测试环境，由于主机工程师更换存储，在没有停数据库的情况下关闭操作系统，导致启动数据库时报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>
至此，该问题得到解决。
在正式生产环境，千万不要随便删除数据文件。如果遇到该问题，可能是数据文件的权限变动了，通过主机层面修改权限即可。
从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>
至此，该问题得到解决。
在正式生产环境，千万不要随便删除数据文件。如果遇到该问题，可能是数据文件的权限变动了，通过主机层面修改权限即可。
