Dropping wrongly added DATAFILE to Tablespace in 10gR2

Today in OTN forum, question regarding drop wrongly added datafile to a tablespace, here i documented it.
dropped datafile can't be removed from database

Question asked by gspersi answered by Tim Boles
I have a new dba on my team that mistakenly added a datafile to a tablespace in the wrong directory. When attempting to remove it he offlined it and then tried to drop it. It would not drop. He deleted the file from the file system. I am in archivelog mode and tried to recover datafile but no success.

Oracle 10.2.0.4 on solaris 10 Any ideas?

Solution:

To re-create a datafile for recovery:

Create a new, empty datafile to replace a damaged datafile that has no corresponding backup. For example, assume that the datafile ?/oradata/trgt/users01.dbf has been damaged, and no backup is available. The following statement re-creates the original datafile (same size) on disk2:

ALTER DATABASE CREATE DATAFILE '/oradata/trgt/users01.dbf' AS
'/disk2/users01.dbf';
This statement creates an empty file that is the same size as the lost file. The database looks at information in the control file and the data dictionary to obtain size information. The old datafile is renamed as the new datafile.

Perform media recovery on the empty datafile. For example, enter:

RECOVER DATAFILE '/disk2/users01.dbf'
All archived logs written after the original datafile was created must be applied to the new, empty version of the lost datafile during recovery.

SQL> alter tablespace users add datafile '/u01/users02.dbf' size 5m;
---wrongly added datafile
Tablespace altered.

SQL> alter database datafile  '/u01/users02.dbf' offline;
 ---NEW DBA Tried to remove
Database altered.


SQL> ! rm -rf /u01/users02.dbf 

SQL> col file_name format a45
SQL> col status format a10
SQL> select file_name,status , online_status from dba_data_files;
--SENIOR DBA FOUND DATAFILE MISSING
FILE_NAME                                     STATUS     ONLINE_
--------------------------------------------- ---------- -------
/u01/app/oracle/oradata/cdbs/users01.dbf      AVAILABLE  ONLINE
/u01/app/oracle/oradata/cdbs/sysaux01.dbf     AVAILABLE  ONLINE
/u01/app/oracle/oradata/cdbs/undotbs01.dbf    AVAILABLE  ONLINE
/u01/app/oracle/oradata/cdbs/system01.dbf     AVAILABLE  SYSTEM
/u01/users02.dbf                              AVAILABLE  RECOVER

SQL> alter system switch logfile;

System altered.

SQL> alter database create datafile '/u01/users02.dbf';
--CREATE NEW DATAFILE IN THE SAME NAME
Database altered.

SQL> select file_name,status , online_status from dba_data_files;

FILE_NAME                                     STATUS     ONLINE_
--------------------------------------------- ---------- -------
/u01/app/oracle/oradata/cdbs/users01.dbf      AVAILABLE  ONLINE
/u01/app/oracle/oradata/cdbs/sysaux01.dbf     AVAILABLE  ONLINE
/u01/app/oracle/oradata/cdbs/undotbs01.dbf    AVAILABLE  ONLINE
/u01/app/oracle/oradata/cdbs/system01.dbf     AVAILABLE  SYSTEM
/u01/users02.dbf                              AVAILABLE  RECOVER

SQL> recover datafile '/u01/users02.dbf';
Media recovery complete.
SQL> select file_name,status , online_status from dba_data_files;

FILE_NAME                                     STATUS     ONLINE_
--------------------------------------------- ---------- -------
/u01/app/oracle/oradata/cdbs/users01.dbf      AVAILABLE  ONLINE
/u01/app/oracle/oradata/cdbs/sysaux01.dbf     AVAILABLE  ONLINE
/u01/app/oracle/oradata/cdbs/undotbs01.dbf    AVAILABLE  ONLINE
/u01/app/oracle/oradata/cdbs/system01.dbf     AVAILABLE  SYSTEM
/u01/users02.dbf                              AVAILABLE  OFFLINE

SQL> alter database datafile '/u01/users02.dbf' online;

Database altered.

SQL> alter tablespace users drop datafile '/u01/users02.dbf';
--Drop empty datafile From Tablespace (10gR2 NEW FEATURE)
Tablespace altered.

SQL> select file_name,status , online_status from dba_data_files;

FILE_NAME                                     STATUS     ONLINE_
--------------------------------------------- ---------- -------
/u01/app/oracle/oradata/cdbs/users01.dbf      AVAILABLE  ONLINE
/u01/app/oracle/oradata/cdbs/sysaux01.dbf     AVAILABLE  ONLINE
/u01/app/oracle/oradata/cdbs/undotbs01.dbf    AVAILABLE  ONLINE
/u01/app/oracle/oradata/cdbs/system01.dbf     AVAILABLE  SYSTEM

SQL>

you can drop empty datafiles by

alter tablespace drop datafile 'datafile_name'

command if it is 10gR2.

Drop Empty Datafiles
Imagine that you just added a datafile to the wrong directory or tablespace—a fairly common error. All is not lost; the datafile doesn't contain any data yet, so you can easily drop it, right?

Unfortunately, you can't. Prior to Oracle Database 10g Release 2, your only clean option for removing a datafile is to drop the entire tablespace and then rebuild it without that particular file. If the tablespace contains data, you have to go through the time-consuming and laborious process of storing the data on a separate location and reinstating it. In addition to its inconvenience, this process makes the tablespace unavailable.

Thankfully, in Oracle Database 10g Release 2 the process has been simplified: You can just drop the datafile. For example, the following command will remove the indicated datafile from the tablespace as well as from the server.

alter tablespace test  drop datafile '/disk1/oradata/datafile/test02.dbf';

There are a couple restrictions, however: The datafile must be empty to be dropped. You can't drop the last datafile in a tablespace; the tablespace itself must be dropped. And the tablespace must be online and in read-write status.

reference:
http://forums.oracle.com/forums/message.jspa?messageID=4468071#4468071
http://www.oracle.com/technology/pub/articles/10gdba/nanda_10gr2dba_part2.html