Recover datafile without backup

Backup and Recovery

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: {=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