Daily Archives: September 4, 2007

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