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

来源：互联网发布：正确的捆条算法编辑：程序博客网时间：2024/05/01 21:09

转自 http://mojijs.com/2014/03/131121/index.html http://mojijs.com/

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

标签：see DBWR trace file file 6 data cannot identify lock ORA-01157

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

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;

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 数据文件

0 0