参考自：
HOW TO RECOVER OFFLINE DROPPED DATAFILE IN ARCHIVELOG MODE (文档 ID 286355.1)
[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 options
SQL> 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
? ? ? ? 11
11 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.dbf
11 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? ? ? ? ? ? ? ? ? 11
11 rows selected.
SQL> alter database datafile 9 offline drop;
Database altered.
SQL> select file#, status from v$datafile where file#='9';
? ? FILE# STATUS
---------- -------
? ? ? ? 9 RECOVER
SQL> select file#, status from v$datafile_header where file#='9';
? ? FILE# STATUS
---------- -------
? ? ? ? 9 OFFLINE
SQL> 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: {
auto --------------->敲入auto
Log applied.
Media recovery complete.
SQL> select *