Oracle归档模式下恢复一个被offline drop的datafile的方法

参考自：
HOW TO RECOVER OFFLINE DROPPED DATAFILE IN ARCHIVELOG MODE （文档 ID 286355.1）如下的实验基于Oracle 11.2.0.4 linux x86-64bit完成[oracle@rhel63single u02]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 15 20:33:17 2015
Copyright （c） 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/arch
Oldest online log sequence 126
Next log sequence to archive 128
Current log sequence 128
SQL> select file_id from dba_data_files; FILE_ID
----------
4
3
2
1
5
6
7
8
9
10
1111 rows selected.SQL> select name from v$dbfile;NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/test/users01.dbf
/u01/app/oracle/oradata/test/undotbs01.dbf
/u01/app/oracle/oradata/test/sysaux01.dbf
/u01/app/oracle/oradata/test/system01.dbf
/u01/app/oracle/oradata/test/ten01.dbf
/u01/app/oracle/oradata/test/tb_test_01.dbf
/u01/app/oracle/oradata/test/ts1.dbf
/u01/app/oracle/oradata/test/ts2.dbf
/u01/app/oracle/oradata/test/test01.dbf
/u01/app/oracle/oradata/test/test_uni_sz_2m_01.dbf
/u01/app/oracle/oradata/test/test_uni_sz_1m_01.dbf11 rows selected.SQL> set lines 290
SQL> col file_name format a60
SQL> select FILE_NAME,file_Id from v$dbfile;FILE_NAME FILE_ID
------------------------------------------------------------ ----------
/u01/app/oracle/oradata/test/users01.dbf 4
/u01/app/oracle/oradata/test/undotbs01.dbf 3
/u01/app/oracle/oradata/test/sysaux01.dbf 2
/u01/app/oracle/oradata/test/system01.dbf 1
/u01/app/oracle/oradata/test/ten01.dbf 5
/u01/app/oracle/oradata/test/tb_test_01.dbf 6
/u01/app/oracle/oradata/test/ts1.dbf 7
/u01/app/oracle/oradata/test/ts2.dbf 8
/u01/app/oracle/oradata/test/test01.dbf 9
/u01/app/oracle/oradata/test/test_uni_sz_2m_01.dbf 10
/u01/app/oracle/oradata/test/test_uni_sz_1m_01.dbf 1111 rows selected.SQL> alter database datafile 9 offline drop;Database altered.SQL> select file#, status from v$datafile where file#="9"; FILE# STATUS
---------- -------
9 RECOVERSQL> select file#, status from v$datafile_header where file#="9"; FILE# STATUS
---------- -------
9 OFFLINESQL> alter system switch logfile ;System altered.SQL> /System altered.SQL> /
/
/System altered.SQL>
System altered.SQL>System altered.SQL> SQL>
SQL>
SQL> /System altered.SQL>
SQL>
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u02/arch
Oldest online log sequence 132
Next log sequence to archive 134
Current log sequence 134
SQL> recover datafile 9;
ORA-00279: change 3155176 generated at 02/15/2015 20:34:05 needed for thread 1
ORA-00289: suggestion : /u02/arch/1_128_807882551.dbf
ORA-00280: change 3155176 for thread 1 is in sequence #128
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto --------------->敲入auto
Log applied.
Media recovery complete.
SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 133 52428800 512 1 YES INACTIVE 3155684 15-FEB-15 3155687 15-FEB-15
2 1 134 52428800 512 1 NO CURRENT 3155687 15-FEB-15 2.8147E+14
3 1 132 52428800 512 1 YES INACTIVE 3155681 15-FEB-15 3155684 15-FEB-15SQL> select file#,status from v$datafile where file#=9; FILE# STATUS
---------- -------
9 OFFLINESQL> select file#,status from v$datafile_header where file#=9; FILE# STATUS
---------- -------
9 OFFLINESQL> alter database datafile 9 online;Database altered.SQL> select file#,status from v$datafile where file#=9; FILE# STATUS
---------- -------
9 ONLINESQL>知识点：
1.The only case in which the offline dropped datafile can not be online is
when you have added to many datafiles in the database after offline drop2.在非归档模式下，为了让一个datafile 变成offline，必须带drop关键字。
drop关键字不会把datafile从database 中 remove掉。
To do that, you must drop the tablespace in which the datafile resides. Until you
do so, the datafile remains in the data dictionary with the status RECOVER or OFFLINE.

若是database处于归档模式，Oracle会忽略掉drop 关键字.更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址