lock data file 6 - see DBWR trace file

2个月以前使用swingbench的时候，cc这个user创建了17GB的测试数据（ccdata.dbf），测试完成后，好久没用了，后面发现测试机空间不足，直接手动rm –rf /Data/oradata/ora11g/ccdata.dbf这个文件之后因其他测试时重启DB时碰到了ORA-01157错误，

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: "/Data/oradata/ora11g/ccdata.dbf"

root case:手动ccdata.dbf文件，既然问题已经知道，那就下手处理吧：

方案有2种:

1.测试库，数据对象不需要了，在这种情况下，最简单的方法是offline并删除该数据文件；

2.正式库，必须使用rman和archive恢复了

因为是测试库，我暂时采取方案1：

ora11g</Data/oradata/ora11g>$sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Mar 27 08:28:41 2014

Copyright （c） 1982, 2011, Oracle. All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> startup force;

SQL> startup force;

ORACLE instance started.

Total System Global Area 7816675328 bytes

Fixed Size 2243712 bytes

Variable Size 4529849216 bytes

Database Buffers 3271557120 bytes

Redo Buffers 13025280 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: "/Data/oradata/ora11g/ccdata.dbf"

SQL> col file# for a10;

SQL> col name for a50;

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

FILE# NAME

---------- --------------------------------------------------

1 /Data/oradata/ora11g/system01.dbf

2 /Data/oradata/ora11g/sysaux01.dbf

3 /Data/oradata/ora11g/undotbs01.dbf

4 /Data/oradata/ora11g/users01.dbf

5 /Data/oradata/ora11g/example01.dbf

6 /Data/oradata/ora11g/ccdata.dbf

7 /Data/oradata/datafile/dp01.dbf

SQL> alter database datafile 6 offline;

Database altered.

SQL> alter database open;

Database altered.

SQL> DROP TABLESPACE CCDATA INCLUDING CONTENTS AND DATAFILES;

Tablespace dropped.

SQL> col file_name for a35;

SQL> col tablespace_namefor a15;

SQL> select tablespace_name,file_name from dba_data_files

TABLESPACE FILE_NAME

---------- -----------------------------------

USERS /Data/oradata/ora11g/users01.dbf

UNDOTBS1 /Data/oradata/ora11g/undotbs01.dbf

SYSAUX /Data/oradata/ora11g/sysaux01.dbf

SYSTEM /Data/oradata/ora11g/system01.dbf

EXAMPLE /Data/oradata/ora11g/example01.dbf

DP_TABLE /Data/oradata/datafile/dp01.dbf

6 rows selected.

至此问题已经解决

测试库，暂时用startup force启动一次吧：

SQL> startup force;

ORACLE instance started.

Total System Global Area 7816675328 bytes
Fixed Size 2243712 bytes
Variable Size 4529849216 bytes
Database Buffers 3271557120 bytes
Redo Buffers 13025280 bytes
Database mounted.
Database opened.

警示：正式库DB万万不要手动rm 数据文件ORA-01157: cannot identify/lock data file n 故障一例 http://www.linuxidc.com/Linux/2012-11/74151.htm更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12数据库启动报ORA-27154 ORA-27300 ORA-27301 ORA-27302错误OSW监控使用相关资讯 ORA-01157

ORA-01157&ORA-01110故障解决（07/17/2015 12:48:04）
ORA-01157: cannot identify/lock （03/03/2014 16:53:39）
Oracle数据库启动时出现ORA-01157 （06/26/2013 09:32:12）

Oracle数据库中出现ORA-01157: 无（08/01/2014 11:16:04）
DG不能自动mount导致数据库不能正（12/12/2013 09:19:58）
ORA-01157和ORA-01110 错误解决（12/21/2012 10:04:34）

本文评论查看全部评论（0）