据库运行在非归档模式下，数据文件被误删的解决方法

 更新时间:2007-10-07作者：中国IT实验室 来源:中国IT实验室

本文关键词：    

 

 

只能用于数据文件中的数据无需恢复的情况下

SQL> alter database datafile '/opt/TZWX.dbf' offline drop;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

SQL> drop tablespace TZWX including contents;

 

Tablespace dropped.

 

[oracle@test11 oracle]$ sqlplus /nolog

 

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Aug 6 13:26:25 2005

 

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

 

SQL> connect / as sysdba;

Connected to an idle instance.

SQL> startup mount;

ORACLE instance started.

 

Total System Global Area  236000356 bytes

Fixed Size                   451684 bytes

Variable Size             201326592 bytes

Database Buffers           33554432 bytes

Redo Buffers                 667648 bytes

Database mounted.

SQL> alter database datafile 14 offline;      

alter database datafile 14 offline

*

ERROR at line 1:

ORA-01145: offline immediate disallowed unless media recovery enabled

 

 

SQL> alter datafile 14 offline;      

alter datafile 14 offline

      *

ERROR at line 1:

ORA-00940: invalid ALTER command

 

 

SQL>  recover datafile 14;    

ORA-00283: recovery session canceled due to errors

ORA-01110: data file 14: '/opt/TZWX.dbf'

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

ORA-01110: data file 14: '/opt/TZWX.dbf'

 

 

SQL> alter database datafile 14 offline;    

alter database datafile 14 offline

*

ERROR at line 1:

ORA-01145: offline immediate disallowed unless media recovery enabled

 

 

SQL> alter database open;

alter database open

*

ERROR at line 1:

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

ORA-01110: data file 14: '/opt/TZWX.dbf'

 

 

SQL> alter database datafile'/opt/TZWX.dbf' offline;    

alter database datafile'/opt/TZWX.dbf' offline

*

ERROR at line 1:

ORA-01145: offline immediate disallowed unless media recovery enabled

 

 

SQL> alter database datafile '/opt/TZWX.dbf' offline;    

alter database datafile '/opt/TZWX.dbf' offline

*

ERROR at line 1:

ORA-01145: offline immediate disallowed unless media recovery enabled

 

 

SQL> alter database datafile '/opt/TZWX.dbf' offline immediate;

alter database datafile '/opt/TZWX.dbf' offline immediate

                                                *

ERROR at line 1:

ORA-00933: SQL command not properly ended

 

 

SQL> alter database datafile '/opt/TZWX.dbf' offline drop;

 

Database altered.

 

SQL> alter database open;

 

Database altered.

数据库打开后还需删除该数据文件所在的表空间

 

SQL> select tablespace_name,status from dba_tablespaces;

 

TABLESPACE_NAME                STATUS

------------------------------ ---------

SYSTEM                         ONLINE

UNDOTBS1                       ONLINE

TEMP                           ONLINE

CWMLITE                        ONLINE

DRSYS                          ONLINE

EXAMPLE                        ONLINE

INDX                           ONLINE

ODM                            ONLINE

TOOLS                          ONLINE

USERS                          ONLINE

XDB                            ONLINE

 

TABLESPACE_NAME                STATUS

------------------------------ ---------

SZJLT                          ONLINE

GAMETEST                       ONLINE

SZJLT_CHAT                     ONLINE

TZWX                           ONLINE

 

15 rows selected.

 

SQL>  drop user tzwx cascade;

 

User dropped.

 

SQL> drop tablespace TZWX ;

drop tablespace TZWX

*

ERROR at line 1:

ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

 

 

SQL> rop tablespace TZWX including contents;

SP2-0734: unknown command beginning "rop tables..." - rest of line ignored.

SQL> drop tablespace TZWX including contents;

 

Tablespace dropped.

 

SQL>

#注意：要先删除用户，然后再删除已经offline drop的表空间.

参考：

 

 

 

推荐阅读与     相关开发者在线内容

恢复 备份 数据库 Oracle相关资讯

恢复 备份 数据库 Oracle相关技术文章

 

用户评论

• 评论列表
• 发表评论

• 用户名
• 评论内容

用户名：      邮件地址：

BBS讨论

 

热门栏目推荐

 

热门软件技术推荐

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

开发者在线  最新报道

开发者在线  最新报道

开发者在线  最新报道

开发者在线  最新报道

 

 

 

 |   |   |   |   |  |   |   |   |   |   | 

 |   |   |   |   |   |   |   |   | 

•  | 
•  | 
•  | 
•  | 

旗下网站: 选择网站BNETCNET.comCNET Download.comCNET News.comCNET ReviewsCNET Shopper.comGameSpotMP3.commySimonRelease 1.0Search.comTechRepublicTV.comWebshotsZDNet 

Copyright © 1997-2013 CNET Networks 版权所有。 ZDNet 是CNET Networks公司注册服务商标。

中华人民共和国电信与信息服务业务经营许可证编号：京ICP证010391号 京ICP备09041801号-159

京公网安备：1101082134