How to recover a deleted datafile when database is open

On development server a team member try to access the data from a table got below error messages as datafile not find after searching we found that datafile is removed

Physically our database is in archive mode and database is open  

SQL> insert into abc select * from abc;

insert into abc select * from abc

            *

ERROR at line 1:

ORA-01116: error in opening database file 4

ORA-01110: data file 4: '/home/oracle/10.2.0/product/oradata/orcl/users01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Now if we try to bring the tablespace offline normally but it shows error datafile is not exists

At it’s location.

SQL> alter tablespace users offline;

alter tablespace users offline

*

ERROR at line 1:

ORA-01116: error in opening database file 4

ORA-01110: data file 4: '/home/oracle/10.2.0/product/oradata/orcl/users01.dbf'

ORA-27041: unable to open file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Hence we bring tablespace offline using immediate option.

SQL> alter tablespace users offline immediate;

Tablespace altered.

Start recovery

SQL>  recover tablespace users;

ORA-00279: change 1730264 generated at 04/08/2013 10:54:45 needed for thread 1

ORA-00289: suggestion :

/home/oracle/10.2.0/product/flash_recovery_area/ORCL/archivelog/2013_04_08/o1_mf

_1_46_%u_.arc

ORA-00280: change 1730264 for thread 1 is in sequence #46

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/home/oracle/10.2.0/product/flash_recovery_area/ORCL/archivelog/2013_04_08/o1_mf

_1_46_%u_.arcORA-00308: cannot open archived log

'/home/oracle/10.2.0/product/flash_recovery_area/ORCL/archivelog/2013_04_08/o1_m

f'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/home/oracle/10.2.0/product/flash_recovery_area/ORCL/archivelog/2013_04_08/o1_m

fORA-00308: cannot open archived log

'_1_46_%u_.arc/home/oracle/10.2.0/product/flash_recovery_area/ORCL/archivelog/20

13_04_08/o1_m'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

/home/oracle/10.2.0/product/flash_recovery_area/ORCL/archivelog/2013_04_08/o1_mf_1_46_8p4ojowq_.arc

ORA-00308: cannot open archived log

'f/home/oracle/10.2.0/product/flash_recovery_area/ORCL/archivelog/2013_04_08/o1_

mf_1_46_8p4ojowq_.arc'

ORA-27037: unable to obtain file status

Linux-x86_64 Error: 2: No such file or directory

Additional information: 3

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

Log applied.

Media recovery complete.

Bring tablespace online .

SQL>  alter tablespace users online;

Tablespace altered.

Chek the status of datafile

SQL> select file_name,status from dba_data_files ;

FILE_NAME

---------------------------------------------------------------------

STATUS

---------

/home/oracle/10.2.0/product/oradata/orcl/users01.dbf

AVAILABLE

/home/oracle/10.2.0/product/oradata/orcl/sysaux01.dbf

AVAILABLE

/home/oracle/10.2.0/product/oradata/orcl/undotbs01.dbf

AVAILABE

/home/oracle/10.2.0/product/oradata/orcl/system01.dbf

AVAILABLE

/home/oracle/10.2.0/product/oradata/orcl/example01.dbf

AVAILABLE

/home/oracle/10.2.0/product/oradata/orcl/mytbs.dbf

AVAILABLE

6 rows selected.