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

2009-12-14 18:46| 发布者: admin| 查看: 22| 评论: 0|原作者: 段誉

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

只能用于数据文件中的数据无需恢复的情况下
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的表空间.
参考：http://www.oracle.com.cn/viewthread.php?tid=12097&highlight=cannot%2Bidentify%2Flock%2Bdata%2Bfile

收藏分享邀请

		自动登录	找回密码
密码			注册

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

最新评论

相关分类

下级分类