【续】ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: 解决方法

浏览数：27 / 时间：2015年06月12日

在数据库中创建表空间 tyger2 在linux系统文件中删除了数据文件 tyger2_01.dbf 启动数据库报错，

重新创建名字为tyger2_01.dbf 文件，数据库正常启动，但是数据文件中的数据一定不在了。

启动 Oracle 数据库实例：OPEN
数据库操作正常意味着实例已启动、数据库已装载且已打开。通过执行正常数据库操作，任何有效用户都可连接到数据库，而且可执行典型数据访问操作。
打开数据库过程包括执行以下任务：
打开联机数据文件
打开联机重做日志文件
如果尝试打开数据库时任一数据文件或联机重做日志文件不存在，则 Oracle 服务器返回错误。
在最后这个阶段，Oracle 服务器会验证是否可以打开所有的数据文件和联机重做日志文件，还会检查数据库的一致性。如有必要，系统监视器 (SMON) 后台进程将启动实例恢复。
可以在受限模式下启动数据库实例，使得只让有管理权限的用户使用该实例。要在受限模式下启动实例，请在“Advanced Startup Options（高级启动选项）”页上选择“Restrict access to database（限制对数据库进行访问）”选项。

接着实验：

http://blog.csdn.net/wanghui5767260/article/details/20293807

【精】ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: 解决方法

[oracle@tyger ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Mar 2 17:21:38 2014

Copyright (c) 1982, 2005, Oracle. All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

SYS@ORCL>create tablespace tyger2 datafile ‘/u01/app/oracle/oradata/ORCL/tyger2_01.dbf‘ size 5m;

Tablespace created.

SYS@ORCL>create user tygeru2 identified by tygeru2 account unlock default tablespace tyger2;
create user tygeru2 identified by tygeru2 account unlock default tablespace tyger2
*
ERROR at line 1:
ORA-01920: user name ‘TYGERU2‘ conflicts with another user or role name

SYS@ORCL>alter user tygeru2 default tablespace tyger2;

User altered.

SYS@ORCL>conn tygeru2/tygeru2
Connected.
TYGERU2@ORCL>select username,default_tablespace from user_users;

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
TYGERU2 TYGER2

TYGERU2@ORCL>create table u2_t1 as select * from scott.emp;

Table created.

TYGERU2@ORCL>
TYGERU2@ORCL>commit;

Commit complete.

TYGERU2@ORCL>select count(*) from u2_t1;

COUNT(*)
----------
14

TYGERU2@ORCL>!
[oracle@tyger ~]$ cd $ORACLE_BASE/oradata/ORCL
[oracle@tyger ORCL]$ ll
total 1068456
-rw-r----- 1 oracle oinstall 7061504 Mar 2 17:25 control01.ctl
-rw-r----- 1 oracle oinstall 7061504 Mar 2 17:25 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 Mar 2 17:25 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Mar 2 17:19 example01.dbf
-rw-r----- 1 oracle oinstall 52429312 Mar 2 17:19 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Mar 2 17:19 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Mar 2 17:25 redo03.log
-rw-r----- 1 oracle oinstall 251666432 Mar 2 17:24 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 Mar 2 17:24 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 Mar 1 08:42 temp01.dbf
-rw-r----- 1 oracle oinstall 5251072 Mar 2 17:25 tyger2_01.dbf
-rw-r----- 1 oracle oinstall 26222592 Mar 2 17:24 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Mar 2 17:19 users01.dbf
[oracle@tyger ORCL]$ mv tyger2_01.dbf tyger2_01.dbf.bak
[oracle@tyger ORCL]$ ll
total 1068456
-rw-r----- 1 oracle oinstall 7061504 Mar 2 17:25 control01.ctl
-rw-r----- 1 oracle oinstall 7061504 Mar 2 17:25 control02.ctl
-rw-r----- 1 oracle oinstall 7061504 Mar 2 17:25 control03.ctl
-rw-r----- 1 oracle oinstall 104865792 Mar 2 17:19 example01.dbf
-rw-r----- 1 oracle oinstall 52429312 Mar 2 17:19 redo01.log
-rw-r----- 1 oracle oinstall 52429312 Mar 2 17:19 redo02.log
-rw-r----- 1 oracle oinstall 52429312 Mar 2 17:25 redo03.log
-rw-r----- 1 oracle oinstall 251666432 Mar 2 17:24 sysaux01.dbf
-rw-r----- 1 oracle oinstall 503324672 Mar 2 17:24 system01.dbf
-rw-r----- 1 oracle oinstall 20979712 Mar 1 08:42 temp01.dbf
-rw-r----- 1 oracle oinstall 5251072 Mar 2 17:25 tyger2_01.dbf.bak
-rw-r----- 1 oracle oinstall 26222592 Mar 2 17:24 undotbs01.dbf
-rw-r----- 1 oracle oinstall 5251072 Mar 2 17:19 users01.dbf
[oracle@tyger ORCL]$ exit
exit

TYGERU2@ORCL>conn / as sysdba
Connected.
SYS@ORCL>shutdown abort
ORACLE instance shut down.
SYS@ORCL>startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1218992 bytes
Variable Size 138413648 bytes
Database Buffers 142606336 bytes
Redo Buffers 2973696 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 6 - see DBWR trace file
ORA-01110: data file 6: ‘/u01/app/oracle/oradata/ORCL/tyger2_01.dbf‘

SYS@ORCL>alter database datafile 6 offline drop;

Database altered.

SYS@ORCL>alter database open;

Database altered.

SYS@ORCL>conn tygeru2/tygeru2
Connected.
TYGERU2@ORCL>select count(*) from u2_t1;
select count(*) from u2_t1
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: ‘/u01/app/oracle/oradata/ORCL/tyger2_01.dbf‘

结论：