Nice article on recovering from a deleted datafile without downtime
http://www.pythian.com/blogs/656/how-to-recover-deleted-oracle-datafiles-with-no-downtime
ps -ef | grep dbw0_SID_OF_YOUR_DB
ls -l /proc/_PID_OF_DBWRITER/fd | grep deleted
lrwx------ 1 oracle oinstall 64 Oct 15 11:24 23 -> /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k6xzjpm_.dbf (deleted)
Create a symbolic link to your datafile with the original name.
ln -s /proc/PID_OF_DBWRITER/fd/23 /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k6xzjpm_.dbf
alter system checkpoint;
rman target /
report schema;
backup as copy datafile YOUR_DATAFILE_NUMBER format '/location_of_your_database/new_name_for_File.dbf';
sql 'alter database datafile YOUR_DATAFILE_NUMBER offline';
switch datafile YOUR_DATAFILE_NUMBER to copy;
recover datafile YOUR_DATAFILE_NUMBER;
sql 'alter database datafile YOUR_DATAFILE_NUMBER online';
exit;
Restore file for NoArchivelog:
select distinct tablespace_name from dba_data_files where file_name = 'YOUR_DELETED_FILE';
alter tablespace THE_TABLESPACE read only;
Copy the file from the symlink to a new name
cp SIM_LINK_DATA_FILE NEW_NAME_FOR_DATAFILE.dbf
/*WAIT FOR COPY!!!*/
shutdown abort;
rm -i SIM_LINK_DATA_FILE
mv NEW_NAME_FOR_DATAFILE.dbf SIM_LINK_DATA_FILE
startup
alter tablespace THE_TABLESPACE read write;
