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$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 故障一例 

您可能感兴趣的文章: