Luckily on Linux, you can recover a deleted file, as long as there is still some process attached to the deleted file. And in our case, the DBW is still attached to the file. The process is quite simple, but first of all don't panic and keep the DB up, otherwise the file is gone.
Below is the real test which I've done in one of our databases, together with some embedded comments:
Creating a tablespace and a table on it, dedicated to the test:
SQL> create tablespace test1 datafile '/MYDB/oradata/ora_data00/MYDB_test1_01.dbf' size 200M;
Tablespace created.
SQL> l
1* create table my_test tablespace test1 as select * from all_objects
where rownum < 101;
Table created.
SQL> select count(*) from my_test;
COUNT(*)
----------
100
SQL> l
1 select FILE_NAME from dba_data_files
2* where TABLESPACE_NAME='TEST1'
SQL> /
Datafile Name
----------------------------------------
/MYDB/oradata/ora_data00/MYDB_test1_01
.dbf
Deleting the data file:
rm -f /MYDB/oradata/ora_data00/MYDB_test1_01.dbf
Identifying the PID for the DBW processes and finding the fd (file descriptor) pointing to our deleted file:
[host1@BMLUS]/u01/app/oracle >ps -fu oracle|grep dbw
oracle 22343 1 0 17:28 ? 00:00:00 ora_dbw0_MYDB
oracle 22347 1 0 17:28 ? 00:00:00 ora_dbw1_MYDB
oracle 22351 1 0 17:28 ? 00:00:00 ora_dbw2_MYDB
oracle 22355 1 0 17:28 ? 00:00:00 ora_dbw3_MYDB
[host1@MYDB]/proc >ll 10139/fd|grep del
lrwx------ 1 oracle dba 64 Mar 4 01:30 9 -> /u01/app/oracle/product/11.1.0/dbs/lkinstMYDB (deleted)
l-wx------ 1 oracle dba 64 Mar 4 01:30 12 -> /u01/app/oracle/admin/MYDB/diag/rdbms/MYDB/MYDB/trace/MYDB_ora_10104.trm (deleted)
l-wx------ 1 oracle dba 64 Mar 4 01:30 11 -> /u01/app/oracle/admin/MYDB/diag/rdbms/MYDB/MYDB/trace/MYDB_ora_10104.trc (deleted)
lrwx------ 1 oracle dba 64 Mar 31 17:16 32 -> /MYDB/oradata/ora_data00/MYDB_test1_01.dbf (deleted)
Now trying to select from the table my_test:
SQL> select count(*) from my_test;
COUNT(*)
----------
100
SQL> alter system flush buffer_cache;
System altered.
SQL> select count(*) from my_test;
select count(*) from my_test
*
ERROR at line 1:
ORA-01116: error in opening database file 9
ORA-01110: data file 9: '/MYDB/oradata/ora_data00/MYDB_test1_01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
The 1st magic to do is to create a symbolic link as below, pointing to the file descriptor.
[host1@MYDB]/proc >ln -s /proc/10139/fd/32 /MYDB/oradata/ora_data00/MYDB_test1_01.dbf
SQL> select count(*) from my_test;
COUNT(*)
----------
100
Make the tablespace read only:
SQL> alter tablespace test1 read only;
Tablespace altered.
Copy the file to a different name:
cp /MYDB/oradata/ora_data00/MYDB_test1_01.dbf /MYDB/oradata/ora_data00/MYDB_test1_01_recovered.dbf
Shutdown abort:
SQL> shutdown abort
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Delete the symbolic link that we've created and rename the data file to its original name:
[host1@MYDB]/proc >ll /MYDB/oradata/ora_data00/MYDB_test1_01.dbf
lrwxrwxrwx 1 oracle dba 17 Mar 31 17:19 /MYDB/oradata/ora_data00/MYDB_test1_01.dbf -> /proc/10139/fd/32
[host1@MYDB]/proc >rm /MYDB/oradata/ora_data00/MYDB_test1_01.dbf
[host1@MYDB]/proc >mv /MYDB/oradata/ora_data00/MYDB_test1_01_recovered.dbf /MYDB/oradata/ora_data00/MYDB_test1_01.dbf
Startup the DB and make the tablespace read write.
SQL> startup
ORACLE instance started.
Total System Global Area 2689212416 bytes
Fixed Size 2162640 bytes
Variable Size 570425392 bytes
Database Buffers 2097152000 bytes
Redo Buffers 19472384 bytes
Database mounted.
Database opened.
SQL> alter tablespace test1 read write;
Tablespace altered.
SQL> conn /
Connected.
SQL> select count(*) from my_test;
COUNT(*)
----------
100
We are done, back to life, in one piece :-)
