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.
SQL> create tablespace TEST datafile '/u01/app/oracle/oradata/orcl/test01.dbf' size 10M; Tablespace created.
SQL> create table ORATAB tablespace TEST as select * from dba_objects; Table created.
SQL> select count(*) from ORATAB; COUNT(*) ---------- 71902 SQL> exit
ls -l /u01/app/oracle/oradata/orcl/test_rm.dbf -rw-r—– 1 oracle dba 10493952 Sep 19 15:21 24 /u01/app/oracle/oradata/orcl/test01.dbf
rm /u01/app/oracle/oradata/orcl/test01.dbf ls -l /u01/app/oracle/oradata/orcl/test01.dbf ls: /u01/app/oracle/oradata/orcl/test01.dbf: no such file or directory
sqlplus / as sysdba Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
——————————————————————————————-
SQL> select count(*) from ORATAB; select * from ORATAB * ERROR at line 1: ORA-01116: error in opening database file 5 ORA-01110: data file 5: '/u01/app/oracle/oradata/orcl/test01.dbf' ORA-27041: unable to open file Linux Error: 2: No such file or directory Additional information: 3
ps -edf | grep dbw [oracle@host01 ~]$ ps -edf | grep dbw oracle 6350 1 0 13:09 ? 00:00:01 ora_dbw0_orcl oracle 8237 8212 0 15:20 pts/1 00:00:00 grep dbw
ls -l /proc/_PID_OF_DBWRITER/fd | grep deleted
[oracle@host01 ~]$ ls -l /proc/6350/fd | grep test lrwx------ 1 oracle oinstall 64 Sep 19 15:21 24 -> /u01/app/oracle/oradata/orcl/test01.dbf (deleted)
[oracle@host01 ~]$ ls -l /proc/6350/fd/24 lrwx------ 1 oracle oinstall 64 Sep 19 15:21 /proc/6350/fd/24 -> /u01/app/oracle/oradata/orcl/test01.dbf (deleted)
ln -s /proc/6350/fd/24 -> /u01/app/oracle/oradata/orcl/test01.dbf
——————————————————————————————————————-
However we can now set the tablespace read only so that it is checkpointed, and no writes occurs on it.
——————————————————————————————————————-
SQL> alter tablespace TEST read only; Tablespace altered.
Now copy the file safely. Then we drop the symbolic link:
rm /u01/app/oracle/oradata/orcl/test01.dbf ls -l /u01/app/oracle/oradata/orcl/test01.dbf ls: /u01/app/oracle/oradata/orcl/test.dbf: No such file or directory
and we can now copy the file
cp -p /proc/6350/fd/24 /u01/app/oracle/oradata/orcl/test01.dbf ls -l /u01/app/oracle/oradata/orcl/test01.dbf -rw-r—– 1 oracle dba 10493952 Sep 19 14:54 /u01/app/oracle/oradata/orcl/test01.dbf
And datafile is now available again.
———————————————————————–
We have it back, lets put the tablespace back in read/write
————————————————————————
SQL> alter tablespace test_rm read write; Tablespace altered.
——————————-
Check data is still there:
——————————-
SQL> select count(*) from ORATAB; COUNT(*) ---------- 71902
This is not to be used like that in production. This is unsupported and may behave differently on different UNIX/Linux or Oracle flavors
