Recover a deleted datafile on linux using symbolic link when database is up

On Linux and other unix box , when a datafile is accidently dropped , using process (PID) belong to the datafile we can recover the data.



Note : If the process lost , it is not possible to recover using symbolic link.

To have a look let us create a tablespace called DEMO,

SQL> create tablespace demo datafile '/u01/app/oracle/oradata/SABA1/demo01.dbf' size 10M;

Tablespace created.

SQL> create table test tablespace demo as select * from dba_users;


Table created.


SQL> select count(*) from test;


COUNT(*)
------------
34

SQL> select name from v$datafile;


NAME
--------------------------------------------------
/u01/app/oracle/oradata/SABA1/system01.dbf
/u01/app/oracle/oradata/SABA1/sysaux01.dbf
/u01/app/oracle/oradata/SABA1/undotbs01.dbf
/u01/app/oracle/oradata/SABA1/users01.dbf
/u01/app/oracle/oradata/SABA1/encrypted_ts01.dbf
/u01/app/oracle/oradata/SABA1/demo01.dbf


6 rows selected.

[oracle@11G dbs]$ cd /u01/app/oracle/oradata/SABA1/


Let me go ahead and drop demo01.dbf (datafile) manually from OS,
[oracle@11G SABA1]$ ls -lrt demo01.dbf
-rw-r----- 1 oracle oinstall 10493952 Aug 28 21:31 demo01.dbf


[oracle@11G SABA1]$ rm demo01.dbf
[oracle@11G SABA1]$ ls -lrt demo01.dbf
ls: demo01.dbf: No such file or directory


Let me connect to the database,

[oracle@11G ~]$ export ORACLE_SID=SABA1
[oracle@11G ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 28 22:15:52 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:


Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select count(*) from test;

COUNT(*)
----------
34

Opps .. Still I am able to see the records.


SQL> Alter system flush shared_pool;


System altered.


SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;


System altered.


Disconnect the session and open a new terminal and start a new session
[oracle@11G ~]$ export ORACLE_SID=SABA1
[oracle@11G ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 28 22:15:52 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:


Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/u01/app/oracle/oradata/SABA1/demo01.dbf'
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3


Yes … Now I am not able to see my records
At the OS let us check the process


[oracle@11G ~]$ ps -edf
grep dbw
oracle 4488 1 0 21:24 ? 00:00:01 ora_dbw0_SABA1
oracle 5269 5231 0 21:40 pts/1 00:00:00 grep dbw
[oracle@11G ~]$ ls -l /proc/4488/fd/
grep demo
lrwx------ 1 oracle oinstall 64 Aug 28 21:42 27 -> /u01/app/oracle/oradata/SABA1/demo01.dbf (deleted)
You will see the above message saying the file is deleted.

[oracle@11G ~]$ ls -l /proc/4488/fd/27
lrwx------ 1 oracle oinstall 64 Aug 28 21:45 /proc/4488/fd/27 -> /u01/app/oracle/oradata/SABA1/demo01.dbf (deleted)


To make sure let me check whether demo datafile is available at the OS level


[oracle@11G ~]$ cd /u01/app/oracle/oradata/SABA1
[oracle@11G SABA1]$ ls
control01.ctl redo01.log redo03.log system01.dbf undotbs01.dbf
encrypted_ts01.dbf redo02.log sysaux01.dbf temp01.dbf users01.dbf


Right i dont see demo01.dbf file. Now let us create a symbolic link using PID so that oracle can see it as it was before the delete


[oracle@11G SABA1]$ ln -s /proc/4488/fd/27 /u01/app/oracle/oradata/SABA1/demo01.dbf
[oracle@11G SABA1]$ ls
control01.ctl redo01.log sysaux01.dbf undotbs01.dbf
demo01.dbf redo02.log system01.dbf users01.dbf
encrypted_ts01.dbf redo03.log temp01.dbf


Yes, Symbolic link is created.


Let me connect to the database,


[oracle@11G ~]$ export ORACLE_SID=SABA1
[oracle@11G ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Aug 28 22:15:52 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:


Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select count(*) from test;

COUNT(*)
----------
34


Now then 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.


SQL> alter tablespace demo read only;

Tablespace altered.


Drop the symbolic link.


[oracle@11G ~]$ rm /u01/app/oracle/oradata/SABA1/demo01.dbf

Copy the data from PID to demo01.dbf


[oracle@11G ~]$ cp -p /proc/6264/fd/30 /u01/app/oracle/oradata/SABA1/demo01.dbf


SQL> alter tablespace demo read write;


Tablespace altered.


SQL> select count(*) from test;


COUNT(*)
----------
34