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文件，既然问题已经知道，那就下手处理吧：

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 6 - see DBWR trace file,古老的榕树,5-wow.com