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/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/system01.dbf

/u01/app/oracle/oradata/sysaux01.dbf

/u01/app/oracle/oradat/undotbs01.dbf

/u01/app/oracle/oradata/users01.dbf

/u01/app/oracle/oradata/encrypted_ts01.dbf

/u01/app/oracle/oradata/demo01.dbf

6 rows selected.

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

Let me  go ahead and drop demo01.dbf (datafile) manually from OS,

[oracle@11G]$ ls -lrt demo01.dbf

-rw-r----- 1 oracle oinstall 10493952 Aug 28 21:31 demo01.dbf

[oracle@11G]$ rm demo01.dbf

[oracle@11G]$ ls -lrt demo01.dbf

ls: demo01.dbf: No such file or directory

Let me connect to the database,

[oracle@11G ~]$ export ORACLE_SID=proddb01

[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=proddb01

[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/demo01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

Yes …  Now we are 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_ proddb01

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/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/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/

[oracle@11G]$ ls

control01.ctl       redo01.log  redo03.log    system01.dbf  undotbs01.dbf

encrypted_ts01.dbf  redo02.log  sysaux01.dbf  temp01.dbf    users01.dbf

don't 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]$ ln -s /proc/4488/fd/27 /u01/app/oracle/oradata/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=proddb01

[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/demo01.dbf

Copy the data from PID to demo01.dbf

$cp -p /proc/6264/fd/30 /u01/app/oracle/oradata/demo01.dbf

SQL> alter tablespace demo read write;

Tablespace altered.

SQL> select count(*) from test;

           COUNT(*)

            ----------

                34