Recover datafile that accidentally deleted

Thanks to Martin's blog, which show us the data file recovery when it accidentally dropped.
(apologies i lost the original link)

***** DO IT ON YOUR OWN RISK *****

Created tablespace and users and some tables.

	SQL> create tablespace test datafile '/oradata/TESTTest01.dbf' size 100m;
	Tablespace created.

	SQL> create user test identified by test default tablespace test;
	User created.

	SQL> grant connect,resource to test;
	Grant succeeded.

	SQL> grant dba to test;
	Grant succeeded.

	SQL> create table t1 as select * from dba_objects;
	Table created.

	SQL> create table t2 as select * from dba_users;
	Table created.

	SQL> conn test/test
	Connected

	SQL> select count(*) from t1;
	35000

	SQL> select count(*) from t2;
	46
	

Removed the datafile

	rm -f /oradata/TESTTest01.dbf

Datafile showing recover status as its got deleted

	SQL> Select file#,name,status from v$datafile;


	     FILE#	NAME				STATUS
	----------	-------------------------- 	--------------- 
		38	/oradata/TESTTest01.dbf		RECOVER
	

Check for the dbwr process ID, The process id for db writer process is 14906, where the dbwriter locks the file headers and useful for this diagnosis.

	
	ps -eaf | grep dbw*_
	oracle   14306     1  0 Apr03 ?        00:02:16 ora_dbw0_
	

using lsof, find which files the dbwr process accessing or its hanged on,

	[oracle@TESTRAC1]/users/oracle # lsof -p 14306

	COMMAND   PID   USER   FD   TYPE    DEVICE   SIZE/OFF    NODE NAME
	oracle  14306 oracle  cwd    DIR     253,7       4096  615765 /u01/app/oracle/ora112/dbs
	oracle  14306 oracle  rtd    DIR     104,2       4096       2 /
	oracle  14306 oracle  txt    REG     253,7  218550485 1130499 /u01/app/oracle/ora112/bin/oracle
	oracle  14306 oracle  DEL    REG      0,13            2228239 /SYSV48ff1ce4
	oracle  14306 oracle  mem    CHR       1,5               3894 /dev/zero
	oracle  14306 oracle  mem    REG     104,2      35688   83628 /usr/lib64/libnuma.so.1
	oracle  14306 oracle  mem    REG     104,2    1661454   16169 /lib64/libc-2.11.1.so
	oracle  14306 oracle  mem    REG     104,2     108213   16180 /lib64/libnsl-2.11.1.so
	oracle  14306 oracle  mem    REG     104,2     135646   16195 /lib64/libpthread-2.11.1.so
	oracle  14306 oracle  mem    REG     104,2     388274   16177 /lib64/libm-2.11.1.so
	oracle  14306 oracle  mem    REG     104,2      19114   16175 /lib64/libdl-2.11.1.so
	oracle  14306 oracle  mem    REG     104,2       5560   16214 /lib64/libaio.so.1.0.1
	oracle  14306 oracle  mem    REG     253,7     150599   84476 /u01/app/oracle/ora112/lib/libocrutl11.so
	oracle  14306 oracle  mem    REG     253,7    3295575   84394 /u01/app/oracle/ora112/lib/libocrb11.so
	oracle  14306 oracle  mem    REG     253,7    1559829   84362 /u01/app/oracle/ora112/lib/libocr11.so
	oracle  14306 oracle  mem    REG     253,7      12763   84499 /u01/app/oracle/ora112/lib/libskgxn2.so
	oracle  14306 oracle  mem    REG     253,7   16067428  402373 /u01/app/oracle/ora112/lib/libhasgen11.so
	oracle  14306 oracle  mem    REG     253,7     161828   84503 /u01/app/oracle/ora112/lib/libdbcfg11.so
	oracle  14306 oracle  mem    REG     253,7     216789   84310 /u01/app/oracle/ora112/lib/libclsra11.so
	oracle  14306 oracle  mem    REG     253,7    7925240   84384 /u01/app/oracle/ora112/lib/libnnz11.so
	oracle  14306 oracle  mem    REG     104,2      47206   16199 /lib64/librt-2.11.1.so
	oracle  14306 oracle  mem    REG     253,7     993144   84391 /u01/app/oracle/ora112/lib/libskgxp11.so
	oracle  14306 oracle  mem    REG     253,7     532417   84323 /u01/app/oracle/ora112/lib/libcell11.so
	oracle  14306 oracle  mem    REG     104,2     149797   16162 /lib64/ld-2.11.1.so
	oracle  14306 oracle  DEL    REG     253,6              73768 /var/run/nscd/dbQpkhsj
	oracle  14306 oracle  mem    REG     253,7      12315   84306 /u01/app/oracle/ora112/lib/libodm11.so
	oracle  14306 oracle    0r   CHR       1,3        0t0    3883 /dev/null
	oracle  14306 oracle    1w   CHR       1,3        0t0    3883 /dev/null
	oracle  14306 oracle    2w   CHR       1,3        0t0    3883 /dev/null
	oracle  14306 oracle    3r   CHR       1,3        0t0    3883 /dev/null
	oracle  14306 oracle    4r   CHR       1,3        0t0    3883 /dev/null
	oracle  14306 oracle    5r   CHR       1,3        0t0    3883 /dev/null
	oracle  14306 oracle    7r   CHR       1,3        0t0    3883 /dev/null
	oracle  14306 oracle    8r   CHR       1,3        0t0    3883 /dev/null
	oracle  14306 oracle    9r   CHR       1,3        0t0    3883 /dev/null
	oracle  14306 oracle   10r   CHR       1,5        0t0    3894 /dev/zero
	oracle  14306 oracle   11r   CHR       1,5        0t0    3894 /dev/zero
	oracle  14306 oracle   13r   REG     253,7    1092608  583904 /u01/app/oracle/ora112/rdbms/mesg/oraus.msb
	oracle  14306 oracle   14r   DIR       0,3          0  137759 /proc/14306/fd
	oracle  14306 oracle   15r   CHR       1,5        0t0    3894 /dev/zero
	oracle  14306 oracle   18r   REG     253,7    1092608  583904 /u01/app/oracle/ora112/rdbms/mesg/oraus.msb
	oracle  14306 oracle  256u   REG 199,17000   10108928     559 /oradata/TEST/Control1.ctl
	oracle  14306 oracle  257u   REG 199,17000   10108928     560 /oradata/TEST/Control2.ctl
	oracle  14306 oracle  258uW  REG 199,17000  419438592     138 /oradata/TEST/system.dbf
	oracle  14306 oracle  259uW  REG 199,17000  419438592     141 /oradata/TEST/sysaux.dbf
	oracle  14306 oracle  260uW  REG 199,17000 1992302592     142 /oradata/TEST/rollbacks.dbf
	oracle  14306 oracle  295uW  REG 199,17000 1677729792     143 /oradata/TEST/temp.dbf
	oracle  14306 oracle  296uW  REG 199,17000  104865792  260497 /oradata/TEST/Test01.dbf (deleted)
	

Did you observed?, the last line shows the file we just deleted. Lets check in the process for 296

	[oracle@TESTRAC1] cd /proc/14306/fd # 
	

Cat the process 296 to a orbitory file

	[oracle@TESTRAC1]/proc/14306/fd # cat 296 > /tmp/Test01.dbf
	

File got created in /tmp

	[oracle@TESTRAC1]/proc/14306/fd # ls -ltr /tmp/Test01.dbf
	-rw-rw-r-- 1 oracle oracle 104865792 2013-04-24 08:45 /tmp/Test01.dbf
	

Make the old file offline drop

	SQL> alter database datafile '/oradata/TEST/Test01.dbf' offline drop;
	Database altered.
	

Rename the old file name to new file (i.e in new location)

	SQL> alter database rename file '/oradata/TEST/Test01.dbf' to '/tmp/Test01.dbf';

	SQL> alter database datafile '/tmp/Test01.dbf' online;
	alter database datafile '/tmp/Test01.dbf' online
	*
	ERROR at line 1:
	ORA-01113: file 38 needs media recovery
	ORA-01110: data file 38: '/tmp/Test01.dbf'
	

Recover the datafile

	SQL> recover datafile '/tmp/Test01.dbf';
	Media recovery complete.
	

Online the datafile

	SQL> alter database datafile '/tmp/Test01.dbf' online;
	Database altered.
	

Check the status

	SQL> select file#,name,status from v$datafile;


	     FILE#	NAME				STATUS
	-----------	---------------------------	----------------------------------
		38	/tmp/Test01.dbf			ONLINE
	

Bingo your file is back, But wait, this may work only when your database is not bounced after the deletion of the file.

Hope this helps
-Thanks
Geek DBA