Retrieve deleted files on Unix / Linux using File Descriptors [ID 444749.1]

Retrieve deleted files on Unix / Linux using File Descriptors [ID 444749.1]
	Modified 04-MAY-2009 Type HOWTO Status MODERATED

In this Document

This document is being delivered to you via Oracle Support's (RaV) process, and therefore has not been subject to an independent technical review.

Applies to:
Oracle Server - Enterprise Edition - Version: 8.1.7.0 to 10.2.0.1
Linux x86-64
Sun Solaris SPARC (64-bit)
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:-
--&gt $ 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 :-
--&gt $ 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
- How to Rename or Move Datafiles and Logfiles[@more@]的