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