Recove datafile without backup

IF you newly created datafile has gone and you didn’t get change to take backup after creation if your database in archive mode then you will be recover the datafile just you need to create the datafile before recovery.   

SQL> startup

ORACLE instance started.

Total System Global Area  910163968 bytes

Fixed Size                  2024976 bytes

Variable Size             268438000 bytes

Database Buffers          633339904 bytes

Redo Buffers                6361088 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 5 - see DBWR trace file

ORA-01110: data file 5: '/home/oracle/10.2.0/product/oradata/MYDB/users02.dbf'

You cant restore the datafile as you don’t have backup .hence you have to create a datafile as reference of old datafile. And recover datafile

SQL>  alter database create datafile '/home/oracle/10.2.0/product/oradata/MYDB/users02.dbf' AS '/home/oracle/10.2.0/product/oradata/MYDB/users02.dbf'  ;

Database altered.

SQL> reocver datafile 5;

SP2-0734: unknown command beginning "reocver da..." - rest of line ignored.

SQL> recover datafile 5;

ORA-00279: change 578628 generated at 05/08/2013 09:17:47 needed for thread 1

ORA-00289: suggestion :

/home/oracle/10.2.0/product/flash_recovery_area/MYDB/archivelog/2013_05_09/o1_mf

_1_5_%u_.arc

ORA-00280: change 578628 for thread 1 is in sequence #5

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

auto

ORA-00279: change 623425 generated at 05/08/2013 10:26:43 needed for thread 1

ORA-00289: suggestion :

/home/oracle/10.2.0/product/flash_recovery_area/MYDB/archivelog/2013_05_09/o1_mf

_1_6_%u_.arc

ORA-00280: change 623425 for thread 1 is in sequence #6

ORA-00278: log file

'/home/oracle/10.2.0/product/flash_recovery_area/MYDB/archivelog/2013_05_08/o1_m

f_1_5_8rmprcx7_.arc' no longer needed for this recovery

ORA-00279: change 727053 generated at 05/08/2013 11:11:46 needed for thread 1

ORA-00289: suggestion :

/home/oracle/10.2.0/product/flash_recovery_area/MYDB/archivelog/2013_05_09/o1_mf

_1_7_%u_.arc

ORA-00280: change 727053 for thread 1 is in sequence #7

ORA-00278: log file

'/home/oracle/10.2.0/product/flash_recovery_area/MYDB/archivelog/2013_05_08/o1_m

f_1_6_8rmsdtrz_.arc' no longer needed for this recovery

ORA-00279: change 727988 generated at 05/08/2013 11:12:26 needed for thread 1

ORA-00289: suggestion :

/home/oracle/10.2.0/product/flash_recovery_area/MYDB/archivelog/2013_05_09/o1_mf

_1_8_%u_.arc

ORA-00280: change 727988 for thread 1 is in sequence #8

ORA-00278: log file

'/home/oracle/10.2.0/product/flash_recovery_area/MYDB/archivelog/2013_05_08/o1_m

f_1_7_8rmsg2lw_.arc' no longer needed for this recovery

Log applied.

Media recovery complete.

SQL>  alter database open;

Database altered.

After open the database you can check the status of  datafile

SQL>  select file_name,status from v$datafile;

 select file_name,status from v$datafile

        *

ERROR at line 1:

ORA-00904: "FILE_NAME": invalid identifier

SQL> select name ,status from v$datafile;

NAME

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

STATUS

-------

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

SYSTEM

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

ONLINE

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

ONLINE

NAME

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

STATUS

-------

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

ONLINE

/home/oracle/10.2.0/product/oradata/MYDB/users02.dbf

ONLINE