When for some reason a datafile is lost while the database is still up and running, you are not lost !
You can easily recover the datafile as the database was using the datafile and therefor still has a file descripter pointing to the deleted datafile.
The datafile filesize is not returned as free space after deleting a file which is in use. Instead only the inode is removed and the deleted file “stays” to exist.
When the file descripter is closed, the deleted file is perminatly removed and free space is then given back. So when dealing with a running instance, the files are still in use and therefor recoverable by following these steps.
Here are the steps i follow to restore a datafile which was delete on a running database instance.
oracle@xxx:1001 $ ls -ltr
total 1656572
-rw-r—– 1 oracle oinstall 20979712 Jul 9 22:00 temp01.dbf
-rw-r—– 1 oracle oinstall 52429312 Jul 9 22:01 redo02.log
-rw-r—– 1 oracle oinstall 52429312 Jul 9 22:03 redo01.log
-rw-r—– 1 oracle oinstall 262152192 Jul 9 22:03 users01.dbf
-rw-r—– 1 oracle oinstall 199237632 Jul 10 14:05 sysaux01.dbf
-rw-r—– 1 oracle oinstall 209723392 Jul 10 14:23 undotbs01.dbf
-rw-r—– 1 oracle oinstall 314580992 Jul 10 14:25 system01.dbf
-rw-r—– 1 oracle oinstall 536879104 Jul 10 14:27 xwiki01.dbf
-rw-r—– 1 oracle oinstall 52429312 Jul 10 14:27 redo03.log
-rw-r—– 1 oracle oinstall 7094272 Jul 10 14:27 control03.ctl
-rw-r—– 1 oracle oinstall 7094272 Jul 10 14:27 control01.ctl
A simple select on a table housed into our xwiki tablespace.
SQL> select * from t;
A B
———- ———-
1 john
SQL> exit
Now we remove the datafile which contains this data.
oracle@xxx:1003 $ rm xwiki01.dbf
oracle@xxx:1004 $ ls -i xwiki01.dbf
ls: xwiki01.dbf: No such file or directory
When logging into the database now , you might encounter some errors while performing transactions.
A select goes well , as this is “cached” , but an insert shows us there is a problem.
SQL> select * from t;
A B
———- ———-
1 john
SQL> insert into t values(2,’john2′);
insert into t values(2,’john2′)
*
ERROR at line 1:
ORA-01116: error in opening database file 5
ORA-01110: data file 5: ‘/u02/oradata/xwiki/xwiki01.dbf’
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Now we will search the process which is still using our datafile ( the database writer process dbw )
oracle@xxx:1005 $ ps -edf | grep dbw
oracle 1712 1534 0 14:35 pts/1 00:00:00 grep dbw
oracle 27691 1 0 Jul08 ? 00:00:03 ora_dbw0_xwiki
This will give us the proc which has the file descripter ( or file handle ) open to our lost file;
oracle@xxx:1006 $ ls -l /proc/27691/fd | grep xwiki01
lrwx—— 1 oracle oinstall 64 Jul 10 14:35 26 -> /u02/oradata/xwiki/xwiki01.dbf (deleted)
This number is our reference to our lost datafile and can be used to recover it.
oracle@xxx:1007 $ ls -l /proc/27691/fd/26
lrwx—— 1 oracle oinstall 64 Jul 10 14:35 /proc/27691/fd/26 -> /u02/oradata/xwiki/xwiki01.dbf (deleted)
We now create a symbolic link to it, so that the database is again able to adres the datafile which is managed threw its tablespace.
oracle@xxx:1008 $ ln -s /proc/27691/fd/26 /u02/oradata/xwiki/xwiki01.dbf
oracle@xxx:1009 $ ll
total 1131760
-rw-r—– 1 oracle oinstall 7094272 Jul 10 14:37 control01.ctl
-rw-r—– 1 oracle oinstall 7094272 Jul 10 14:37 control03.ctl
-rw-r—– 1 oracle oinstall 52429312 Jul 9 22:03 redo01.log
-rw-r—– 1 oracle oinstall 52429312 Jul 9 22:01 redo02.log
-rw-r—– 1 oracle oinstall 52429312 Jul 10 14:36 redo03.log
-rw-r—– 1 oracle oinstall 199237632 Jul 10 14:28 sysaux01.dbf
-rw-r—– 1 oracle oinstall 314580992 Jul 10 14:31 system01.dbf
-rw-r—– 1 oracle oinstall 20979712 Jul 9 22:00 temp01.dbf
-rw-r—– 1 oracle oinstall 209723392 Jul 10 14:28 undotbs01.dbf
-rw-r—– 1 oracle oinstall 262152192 Jul 9 22:03 users01.dbf
lrwxrwxrwx 1 oracle oinstall 17 Jul 10 14:36 xwiki01.dbf -> /proc/27691/fd/26
We now put our tablespace in read only mode so that we can restore the datafile.
SQL> alter tablespace xwiki read only;
Tablespace altered.
The datafile is not used anymore now , so we can start “restoring” the datafile back from our file descripter.
First we remove the symbolic link created earlier and copy the fd back to the correct datafile name.
oracle@xxx:1010 $ rm xwiki01.dbf
oracle@xxx:1011 $ cp -p /proc/27691/fd/26 /u02/oradata/xwiki/xwiki01.dbf
Now we see that our datafile is back with the correct size !
oracle@xxx:1012 $ ll
total 1656572
-rw-r—– 1 oracle oinstall 7094272 Jul 10 14:38 control01.ctl
-rw-r—– 1 oracle oinstall 7094272 Jul 10 14:38 control03.ctl
-rw-r—– 1 oracle oinstall 52429312 Jul 9 22:03 redo01.log
-rw-r—– 1 oracle oinstall 52429312 Jul 9 22:01 redo02.log
-rw-r—– 1 oracle oinstall 52429312 Jul 10 14:37 redo03.log
-rw-r—– 1 oracle oinstall 199237632 Jul 10 14:28 sysaux01.dbf
-rw-r—– 1 oracle oinstall 314580992 Jul 10 14:31 system01.dbf
-rw-r—– 1 oracle oinstall 20979712 Jul 9 22:00 temp01.dbf
-rw-r—– 1 oracle oinstall 209723392 Jul 10 14:37 undotbs01.dbf
-rw-r—– 1 oracle oinstall 262152192 Jul 9 22:03 users01.dbf
-rw-r—– 1 oracle oinstall 536879104 Jul 10 14:37 xwiki01.dbf
oracle@xxx:1013 $
We now can put our tablespace back to read write mode and test our insert again.
SQL> alter tablespace xwiki read write;
Tablespace altered.
SQL> insert into t values(2,’john2′);
1 row created.
SQL> select * from t;
A B
———- ———-
1 john
2 john2
SQL>
Good luck recovering :)
