What will happen if dbf file accidentally deleted when database is still open and how to recover it?

What will happen if dbf file (non system) accidentally deleted when database is still open? Only for Unix and Linux platforms if accidentally deleted dbf file OS will not delete it, OS will rename it. Also be aware that process (pid) will goes on and use deleted file. You can find deleted file under /proc/<pid>/...

Let`s check.

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL>  

SQL> select tablespace_name from dba_tablespaces;

TABLESPACE_NAME

------------------------------

SYSTEM

SYSAUX

UNDOTBS1

TEMP

USERS

EXAMPLE

6 rows selected.

SQL> 

SQL> select file_id, file_name from dba_data_files order by 1;

   FILE_ID  FILE_NAME

------------  -------------------------------------------------------------------

         1 /oracle/product/11.2.0/oradata/testdb/system01.dbf

         2 /oracle/product/11.2.0/oradata/testdb/sysaux01.dbf

         3 /oracle/product/11.2.0/oradata/testdb/undotbs01.dbf

         4 /oracle/product/11.2.0/oradata/testdb/users01.dbf

         5 /oracle/product/11.2.0/oradata/testdb/example01.dbf

SQL>  

We will simulate on USERS tablespace`s datafile.

Now we will create new user (Ulfet), grant to him necessary privileges.  

SQL> create user ulfet identified by ulfet default tablespace users;

User created.

SQL> grant connect, resource to ulfet;

Grant succeeded.

SQL> conn ulfet/ulfet

Connected.

--Now create table and insert a record, after recover we will check data.

SQL> create table test (id number);

Table created.

SQL> insert into test values(1);

1 row created.

SQL> commit;

Commit complete.

SQL>  

Now, delete /oracle/product/11.2.0/oradata/testdb/users01.dbf data file.

[oracle@db-server testdb]$ pwd

/oracle/product/11.2.0/oradata/testdb

[oracle@db-server testdb]$ ls -l

total 1533652

-rw-r----- 1 oracle oinstall   9748480 Oct 21 14:17 control01.ctl

-rw-r----- 1 oracle oinstall 104865792 Oct 21 14:02 example01.dbf

-rw-r----- 1 oracle oinstall  52429312 Oct 21 13:57 redo01.log

-rw-r----- 1 oracle oinstall  52429312 Oct 21 14:17 redo02.log

-rw-r----- 1 oracle oinstall  52429312 Oct 21 13:53 redo03.log

-rw-r----- 1 oracle oinstall 513810432 Oct 21 14:17 sysaux01.dbf

-rw-r----- 1 oracle oinstall 713039872 Oct 21 14:17 system01.dbf

-rw-r----- 1 oracle oinstall  20979712 Oct 21 13:58 temp01.dbf

-rw-r----- 1 oracle oinstall  62922752 Oct 21 14:17 undotbs01.dbf

-rw-r----- 1 oracle oinstall   5251072 Oct 21 14:02 users01.dbf

[oracle@db-server testdb]$ rm users01.dbf

[oracle@db-server testdb]$

Now check to be sure file not exists:

[oracle@db-server testdb]$ ls -lrt users01.dbf

ls: users01.dbf: No such file or directory

[oracle@db-server testdb]$

There is no any such file...

Also check table is acceptable 

SQL> conn ulfet/ulfet

Connected.

SQL> select * from test;

        ID

----------

         1

SQL> select table_name, tablespace_name from user_tables;

TABLE_NAME                             TABLESPACE_NAME

------------------------------        ------------------------------

TEST                                                 USERS

SQL>

Hmmm, table acceptable yet, need to flush buffer cache or restart db.

SQL> conn /as sysdba

Connected.

SQL> alter system flush buffer_cache;

System altered.

--Check again

SQL> conn ulfet/ulfet

Connected.

SQL> select * from test;

select * from test

              *

ERROR at line 1:

ORA-01116: error in opening database file 4

ORA-01110: data file 4: '/oracle/product/11.2.0/oradata/testdb/users01.dbf'

ORA-27041: unable to open file

Linux Error: 2: No such file or directory

Additional information: 3

SQL> 

Oracle retrieve information about that users01.dbf is missing...

However, the datafile is missing, dbwriter backgound process still works.

Check dbwr process: 

[oracle@db-server testdb]$ ps -ef | grep dbw

oracle    3482     1  0 13:53 ?        00:00:04 ora_dbw0_testdb

oracle    4675  3296  0 14:25 pts/3    00:00:00 grep dbw

[oracle@db-server testdb]$

[oracle@db-server testdb]$ ls -lrt /proc/3482/fd/ | grep users

lrwx------ 1 oracle oinstall 64 Oct 21 14:28 23 -> /oracle/product/11.2.0/oradata/testdb/users01.dbf (deleted)

[oracle@db-server testdb]$ ls -lrt /proc/3482/fd/23

lrwx------ 1 oracle oinstall 64 Oct 21 14:28 /proc/3482/fd/23 -> /oracle/product/11.2.0/oradata/testdb/users01.dbf (deleted)

first we set a symbolic link so that oracle can see it as it was before the delete:

ln -s /proc/3482/fd/23 /oracle/product/11.2.0/oradata/testdb/users01.dbf

Switch tablespace to read only before restoring data file.

SQL> conn /as sysdba

Connected.

SQL> alter tablespace users read only;

Tablespace altered.

SQL>

We drop the symbolic link: 

[oracle@db-server testdb]$ rm users01.dbf

Copy the file to there where it was deleted.

cp -p /proc/3482/fd/23 /oracle/product/11.2.0/oradata/testdb/users01.dbf

[oracle@db-server testdb]$ ls -lrt users01.dbf

-rw-r----- 1 oracle oinstall 5251072 Oct 21 16:09 users01.dbf

So, datafile is available and time to take tablespace back to read write.

SQL> alter tablespace users read write;

Tablespace altered.

SQL>

Check Ulfet`s table is available ?

SQL> conn ulfet/ulfet

Connected.

SQL> select * from test;

        ID

----------

         1

SQL> 

Yes, we did it.

P.S: This is not acceptable for Windows.