Retrieve deleted files on Unix / Linux using File Descriptors

Doc ID: Note:444749.1

Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7 to 10.2
Linux x86-64
Solaris Operating System (SPARC 64-bit)
Linux x86
Goal

Retrieve deleted datafiles/logfiles from the operating system if the database has not been restarted.
Solution

Using the PROC file system available on Unix/Linux, we can retrieve deleted datafile/logfile when all of the following hold good:-

1.) Database is not restarted.
2.) Server is not restarted.
3.) The file was not offline before deletion.


Background processes (DBWR, PMON, SMON etc.) have access to all the datafiles currently opened by the database. So, with the PID of a background procss, list of files currently opened by that process can be obtained by using 'lsof' command.
Also, there is a file descriptor associated with every file opened by a process. If the file gets accidently deleted from the operating system, its entry is not removed from the proc file system. Using that entry, we can recreate the deleted file.

This is explained with an example below.

1.) Create a tablespace

SQL> create tablespace my_test datafile '/emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf' size 200k;

Tablespace created.

2.) Accidently, datafile belonging to this tablespace got deleted

$ rm /emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf

3.) Try resizing the datafile

SQL> alter database datafile '/emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf' resize 250k;
alter database datafile '/emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf' resize 250k
*
ERROR at line 1:
ORA-01565: error in identifying file
'/emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3

Rescuing the file


1.) Find the process id of DBWr process:-

Format is:-

--> $ ps -ef |grep ''| grep ''

$ ps -ef |grep EMR102U6|grep dbw
emrdbms 21943 1 0 10:27:08 ? 0:00 ora_dbw0_EMR102U6

Note that Process ID for DBW0 here is 21943.

2.) Find open files for this Process Id using 'lsof' command :-

$ lsof -p 21943 |grep /emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf

Command PID USER FD TYPE DEVICE SIZE/OFF NODE NAME
oracle 21943 emrdbms 270uW VREG 304,25 212992 11273825 /emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf

Note:- If you are using NAS then the file name in above command may not be displayed properly and hence this procedure should not be used under these circumstances.

Note the value of FD in the table above (270) .

3.) Go to the file descriptors directory :-

Format is :-

--> $ cd /proc/ / '/

$ cd /proc/21943/fd/

4.) Make the tablespace containing "deleted" datafiles READ-ONLY:

alter tablespace my_test read only;

Making the tablespace read only freezes the file header, preventing updates
from being made to the file header. Since this datafile is then at a read
only state, it is possible to copy the file while the database is open.

This will allow users to select from the tablespace, but prevents them from
doing inserts, updates, and deletes.

5.) Make a copy of file using file descriptor (270uW) :-

$ cat 270 > /emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf

6.) To make sure that the old "deleted" copies of files will not be used after
the copy has been done, do the following:

a) Take datafile offline
alter tablespace my_test offline;

Query the view v$datafile to verify the datafile is offline:
select status from v$datafile where file#=;

b) Bring datafile back online
alter tablespace my_test online;

7.) Put tablespace back in read write mode:
alter tablespace my_test read write;

Query view dba_tablespaces to check status of the tablespace:
select tablespace_name,status from dba_tablespaces where tablespace_name='MY_TEST';

8.) Now datafile resize operation goes through fine.

SQL> alter database datafile '/emea/rdbms/64bit/app/oracle/oradata/EMR102U6/my_test_01.dbf' resize 250k;

Database altered.

Note:- This procedure can also be used for retrieving the deleted current redo logfile.
References
Note 115424.1 - How to Rename or Move Datafiles and Logfiles