ORA-01157: cannot identify/lock data file %s - see DBWR trace file的处理
来源:互联网 发布:单片机流水灯汇编程序 编辑:程序博客网 时间:2024/05/02 00:17
一个测试环境,由于主机工程师更换存储,在没有停数据库的情况下关闭操作系统,导致启动数据库时报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>
至此,该问题得到解决。
在正式生产环境,千万不要随便删除数据文件。如果遇到该问题,可能是数据文件的权限变动了,通过主机层面修改权限即可。
0 0
- ORA-01157: cannot identify/lock data file %s - see DBWR trace file的处理
- 【续】ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: 解决方法
- 【精】ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: 解决方法
- ORA-01157: cannot identify/lock data file 27 - see DBWR trace file
- ORA-01157: cannot identify/lock data file 12 - see DBWR trace file
- ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
- ORA-01157: cannot identify/lock data file 6 – see DBWR trace file
- ORA-01157: cannot identify/lock data file 201 - see DBWR trace file
- ORA-01157: cannot identify/lock data file - see DBWR tracefile
- ORA-1157, cannot identify/lock data file - see DBWR trace file
- ORA-01157: cannot identify/lock data file
- ORA-01157报错"cannot identify/lock data file"解决
- ORA-01157: cannot identify/lock data file 7
- oracle 启动时出现ORA-01157: cannot identify/lock data和ORA-01110: data file 错误
- ORA-01157: cannot identify/lock data file n 故障一例
- oracle启动报错ORA-01157: cannot identify/lock data file 7
- Oracle ORA-01157报错"cannot identify/lock data file"解决
- ORA-01157报错"cannot identify/lock data file" ORA-01219:database not open:queries allowed on fixed
- HDU 1103.Flo's Restaurant【模拟】【3月2】
- MySql之错误异常--Binary logging not possible. Message: Transaction level 'READ-COMMITTED' in InnoDB is no
- [异常] Android - Your project contains errors,please fix them before running your application
- java并发编程 Lock
- 杭电2095
- ORA-01157: cannot identify/lock data file %s - see DBWR trace file的处理
- SPOJ LCS(后缀自动机)
- C语言数组
- ZOJ 3603Draw Something Cheat(模拟)
- codeforces--623C The Smallest String Concatenation
- iOS开发多线程的理论介绍
- ORA-00257: archiver error --11GR2 RAC 设置归档路径和开启flashback
- Hibernate连接MySQL
- 寻找屏幕上的点击位置--Queen核心技术分享