What will happen if dbf file accidentally deleted when the database is still open and how to recover it?

On Unix/Linux, when a file is deleted, but a process still has the file open, the file is still there in the filesystem, and only the inode is removed.
But the process can continue to use its file handle, and the file can also be accessible under /proc/<pid>/fd.

In the following example, we use that behavior to recover a lost data file after it has been dropped from the OS (with rm) but the datafile is still open by the background processes.

First, we create a tablespace and populate a table in it.

—————————-
Create a tablespace: 
—————————-
 

————————————-
Create a table in tablespace: 
————————————-
 

———————————————-
Check that table data is accessible: 
———————————————-
 

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – 64bit Production
———————————————————–
Then, we remove the data file from Unix CLI.
———————————————————–
here is the data file

we ‘accidently’ remove the data file 

———————————————————-
Here the data file is lost. Now we connect again.
———————————————————-

——————————————————————————————-

Check if table data is accessible & you getting below error some times: 
——————————————————————————————-

————————————————————–
The datafile is lost and data is not accessible.
————————————————————–
——————————————————————————————————————
However, the datafile should still have an open file descriptor by an Oracle background process
——————————————————————————————————————
Check the dbwriter pid: 

List the deleted file handles for that DBWRITER process.

or
Check its opened file descriptors for our file: 

here it is: 

In some other unix, lsof may be needed to map the file descriptor with the deleted file name
first we set a symbolic link so that oracle can see it as it was before the delete:

here data is accessible, but that will be lost if dbwriter closes it file handle (i.e if the database is closed)

——————————————————————————————————————-
However we can now set the tablespace read only so that it is checkpointed, and no writes occurs on it.
——————————————————————————————————————-

Now copy the file safely. Then we drop the symbolic link: 

and we can now copy the file 

And datafile is now available again.

———————————————————————–
We have it back, lets put the tablespace back in read/write 
————————————————————————


——————————-
Check data is still there: 
——————————-

This is not to be used like that in production. This is unsupported and may behave differently on different UNIX/Linux or Oracle flavors

About The Author

Leave a Reply