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

在数据库中创建表空间 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‘



结论:

数据库可以启动了,但是数据问价已经在数据库中drop了 所以数据都不存在了






【续】ORA-01157: cannot identify/lock data file 6 - see DBWR trace file ORA-01110: 解决方法,古老的榕树,5-wow.com

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。