ORA-01157: cannot identify/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 数据文件