Recover a deleted datafile when the Oracle database is running in noarchive mode.

On Unix/Linux, when a file is deleted, It is stored in its recycle bin area just like windows. And you can access the deleted file under /proc//fd .

In the following example, we will see how we can restore the deleted datafile

Let us create a tablespace, and populate a table in it.

SQL> create tablespace RECVR_TEST datafile '/oradata/tmp/recvr_test.dbf' size 10M;
Tablespace created.

SQL> create table DATA_TEST tablespace recvr_test as select * from dba_objects;
Table created.

SQL> select count(*) from DATA_TEST;
COUNT(*)
----------
12708

SQL> exit
Disconnected from Oracle Database 10g Express Edition Release 10.2.0.1.0

ls -l /oradata/tmp/recvr_test.dbf
-rw-r----- 1 oracle dba 10493952 Sep 26 14:25 /oradata/tmp/recvr_test.dbf


Let us drop the datafile from unix prompt.

rm /oradata/tmp/recvr_test.dbf
ls -l /oradata/tmp/recvr_test.dbf
ls: /oradata/tmp/recvr_test.dbf: no such file or directory

We lost the Datafile

Let us try to access the data
sqlplus / as sysdba

Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0

SQL> REM and we check if table data is accessible:
SQL> select count(*) from DATA_TEST;


ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: '/oradata/tmp/recvr_test.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

Now we have a “Oh My god” Situation

We cannot access the data because we lost the datafile. But we will check the open file descriptor by oracle background process to locate the deleted file.

To check the dbwriter pid:
ps -edf | grep dbw
oracle 2661 1 0 Sep26 ? 00:00:06 xe_dbw0_XE
oracle 7044 7037 0 14:40 pts/1 00:00:00 /bin/bash -c ps -edf | grep dbw
oracle 7046 7044 0 14:40 pts/1 00:00:00 grep dbw

and we check its opened file descriptors for our file:
ls -l /proc/2661/fd | grep recvr_test
lrwx------ 1 oracle dba 64 Sep 26 14:02 66 -> /oradata/tmp/recvr_test.dbf (deleted)

here it is:
ls -l /proc/2661/fd/66
lrwx------ 1 oracle dba 64 Sep 26 14:02 /proc/2661/fd/66 -> /oradata/tmp/recvr_test.dbf (deleted)

In some other unix, lsof may be needed to map the file descriptor with the deleted file name

Now let us  set a symbolic link so that oracle can see it as it was before the delete:
ln -s /proc/2661/fd/66 /oradata/tmp/recvr_test.dbf

Let us put the tablespace in readonly mode to restrict users from writing.

SQL> alter tablespace RECVR_TEST read only;
Tablespace altered.

Let us copy the file to the original location.

cp -p /proc/2661/fd/66 /oradata/tmp/recvr_test.dbf
ls -l /oradata/tmp/recvr_test.dbf

-rw-r----- 1 oracle dba 10493952 Mar 26 14:54 /oradata/tmp/recvr_test.dbf

Now we have the datafile back and let us put the tablespace in read/write mode.


SQL> alter tablespace recvr_test read write;
Tablespace altered.

Now let us check the data is still there:
SQL> select count(*) from DATA_TEST;

COUNT(*)
----------
12708

YES, WE RESTORED THE DATAFILE.