Sql数据库MDF数据文件数据库恢复

核心提示： 只能用于数据文件中的数据无需恢复的情况下SQL> alter database datafile '/opt/TZWX.dbf' offline drop; Database altered. SQL> alter database open; Databas

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

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