In archive mode, restore the offline drop datafile method.

In archive mode, restore the offline drop datafile method.

Reference:
How to recover offline dropped datafile in archivelog mode (Document ID 286355.1)

The following experiment is completed based on 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 2015 Copyright (c) 1982,201 3, 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 126 Next log sequence to archive 128 Current 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 users/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 certificate ----------/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>/System altered. SQL> archive log list; database log mode Archive ModeAutomatic archival EnabledArchive destination/u02/archOldest online log sequence 132 Next log sequence to archive 134 Current 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/109128_807882551.dbfora-00280: change 3155176 for thread 1 is in sequence #128 Specify log: {<RET> = suggested | filename | AUTO | CANCEL} auto ---------------> input autoLog applied. media recovery complete. SQL> select * from v $ log; GROUP # THREAD # SEQUENCE # bytes blocksize members arc status history # BYTES NEXT_CHANGE # NEXT_TIME ---------- --- hour -------------- ----------- 1 133 52428800 512 1 yes inactive 3155684 limit 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>

Knowledge point:
1. The only case in which the offline dropped datafile can not be online is
When you have added to upload datafiles in the database after offline drop

2. In non-archive mode, the drop keyword must be included to change a datafile to offline.
The drop keyword does not remove datafile from the database.
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.

If the database is in archive mode, Oracle ignores the drop keyword.