在歸檔模式下，恢複一個被offline drop的datafile的方法，offlinedatafile

在歸檔模式下，恢複一個被offline drop的datafile的方法，offlinedatafile

參考自：
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 sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 15 20:33:17 2015Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> archive log list;Database log mode              Archive ModeAutomatic archival             EnabledArchive destination            /u02/archOldest online log sequence     126Next log sequence to archive   128Current log sequence           128SQL> 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 290SQL> col file_name format a60SQL> 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 ModeAutomatic archival             EnabledArchive destination            /u02/archOldest online log sequence     132Next log sequence to archive   134Current log sequence           134SQL> recover datafile 9;ORA-00279: change 3155176 generated at 02/15/2015 20:34:05 needed for thread 1ORA-00289: suggestion : /u02/arch/1_128_807882551.dbfORA-00280: change 3155176 for thread 1 is in sequence #128Specify log: {<RET>=suggested | filename | AUTO | CANCEL}auto --------------->敲入autoLog 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 drop

2.在非歸檔模式下，為了讓一個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 關鍵字.