Recover Deleted Oracle Files

Do we have a chance to recover if our datafile or redolog file is accidentally deleted? If we are on a linux environment, yes we have.

“Most people know that on linux and unixes a file keeps on living (=available) in the context of a process as long as the file is kept open by the process, despite deletion by another process. This file is gone for all processes that try to access it after the deletion, but the deleted files will only get really disposed once all processes that have the file opened close the the connection (file descriptor) to it.”

Yes it seems we have chance to do that. But how? Linux has the proc file system which has sub-directories named with process numbers and under these directories we can find file descriptors for that process in fd directories. For example consider we have a database with SID “ORCL” that is running on a linux machine. With the following command we can see the process number of database writer process.

ps -ef | grep dbw0_ORCL

oracle 20013 1 0 Dec 24 ? 459:23 ora_dbw0_ORCL

You can list your deleted datafile with

ls -l /proc/20013/fd | grep deleted

lrwx------ 1 oracle dba 64 Feb 16 14:40 15 -> /oracle/app/oracle/datafile/”lost_data_file”.dbf (deleted)

And with a symbolic link you can access the datafile that you deleted before.

ln -s /proc/20013/fd/15 /oracle/app/oracle/datafile/”lost_data_file”.dbf

After this step you shuold restore the file because if your database goes down you’ll lose the datafile. You can use RMAN for this purpose with the following script

rman target /

backup as copy datafile YOUR_DATAFILE_NUMBER format '/location_of_your_database/new_name_for_File.dbf';

sql 'alter database datafile YOUR_DATAFILE_NUMBER offline';

switch datafile YOUR_DATAFILE_NUMBER to copy;

recover datafile YOUR_DATAFILE_NUMBER;

sql 'alter database datafile YOUR_DATAFILE_NUMBER online';

exit;

You can find details and scenarios on these links: 1 2