Using the PROC file system available on Unix and Linux, we can retrieve accidentally dropped datafiles. Below is a demonstration of this method on OEL4 and 10gR2 –
SQL> create tablespace my_test datafile '/tmp/my_test_01.dbf' size 200k; SQL> alter system check datafiles ; SQL> alter system checkpoint global ; SQL> host rm -rf /tmp/my_test_01.dbf -- since it is removed can not resize SQL> alter database datafile '/tmp/my_test_01.dbf' resize 250k; alter database datafile '/tmp/my_test_01.dbf' resize 250k * ERROR at line 1: ORA-01565: error in identifying file '/tmp/my_test_01.dbf' ORA-27037: unable to obtain file status Linux Error: 2: No such file or directory Additional information: 3 -- first take the process id of the dbwr(could be pmon, smon etc.) SQL> host ps -ef |grep tcell|grep dbw|grep -v grep oracle 5033 1 0 09:54 ? 00:00:00 ora_dbw0_tcell -- find open files for this process id SQL> host lsof -p 5033 |grep /tmp/my_test_01.dbf oracle 5033 oracle 28uW REG 253,0 212992 180316 /tmp/my_test_01.dbf (deleted) -- go to the file descriptors directory and make a copy of file using file descriptor(28uW for this example) SQL> host cat /proc/5033/fd/28 > /tmp/my_test_01.dbf SQL> host ls -alt /tmp/my_test_01.dbf -rw-r--r-- 1 oracle oinstall 212992 Sep 4 14:25 /tmp/my_test_01.dbf SQL> alter database datafile '/tmp/my_test_01.dbf' resize 250k;
This method can also be used for retrieving the deleted current redo logfile, but the limitations are –
a. Database is not restarted,
b. Server is not restarted,
c. The file was not offline before deletion.