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.
Refences Used :
Note:444749.1 – Retrieve deleted files on Unix / Linux using File Descriptors