OS层面删除数据文件后的恢复方式(LINUX)：

此次删除数据文件后，请不要重启数据库或者关闭数据库，可直接在线恢复。
测试方式：
先从数据库中查看数据库数据文件的文件号及路径：

SQL> select  FILE# ,name from v$datafile;

 FILE#  NAME
----------  -----------------------------------------------------------------------
     1 /u01/app/oracle/oradata/coffexiang/system01.dbf
     2 /u01/app/oracle/oradata/coffexiang/tbs1_1.dbf
     3 /u01/app/oracle/oradata/coffexiang/sysaux01.dbf
     4 /u01/app/oracle/oradata/coffexiang/undotbs01.dbf
     5 /u01/app/oracle/oradata/coffexiang/example01.dbf
     6 /u01/app/oracle/oradata/coffexiang/users01.dbf
     7 /u01/app/oracle/oradata/coffexiang/undotbs1.dbf
     8 /u01/app/oracle/oradata/coffexiang/tbs1_2.dbf

     
SQL> select * from v$tablespace;
   TS# NAME                           INC BIG FLA ENC     CON_ID
-----------------------------------   --- --- --- --- ----------
     1 SYSAUX                         YES NO  YES              0
     0 SYSTEM                         YES NO  YES              0
     2 UNDOTBS1                       YES NO  YES              0
     4 USERS                          YES NO  YES              0
     3 TEMP                           NO  NO  YES              0
     6 EXAMPLE                        YES NO  YES              0
     9 TBS1                           YES NO  YES              0

SQL> select tablespace_name,status from dba_tablespaces;

 TABLESPACE_NAME                STATUS
 ------------------------   ---------
 SYSTEM                         ONLINE
 SYSAUX                         ONLINE
 UNDOTBS1                       ONLINE
 TEMP                           ONLINE
 USERS                          ONLINE
 EXAMPLE                        ONLINE
 TBS1                           ONLINE         

删除表空间TBS1的数据文件

[oracle@xiang66 ~]$ cd /u01/app/oracle/oradata/coffexiang/
[oracle@xiang66 coffexiang]$ rm tbs1_1.dbf tbs1_2.dbf

建表测试，显示失败：

SQL> create table xiang1.tbs1 as select * from scott.emp;
create table xiang1.tbs1 as select * from scott.emp
                                            *
ERROR at line 1:
ORA-01116: error in opening database file 2
ORA-01110: data file 2: '/u01/app/oracle/oradata/coffexiang/tbs1_1.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

然后我们通过dbw进程找到spid号，通过/proc/'spid号'/fd 路径下找到被删除的数据文件

--找到spid号

[oracle@xiang66 coffexiang]$ ps -ef| grep dbw|grep -v grep
oracle     6471      1  0 07:52 ?        00:00:00 ora_dbw0_coffexiang

[oracle@xiang66 fd]$  ll /proc/6471/fd
total 0
lr-x------. 1 oracle oinstall 64 Jan 13 08:08 0 -> /dev/null
l-wx------. 1 oracle oinstall 64 Jan 13 08:08 1 -> /dev/null
lr-x------. 1 oracle oinstall 64 Jan 13 08:08 10 -> 
/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/mesg/oraus.msb
l-wx------. 1 oracle oinstall 64 Jan 13 08:08 2 -> /dev/null
lrwx------. 1 oracle oinstall 64 Jan 13 08:08 256 -> 
/u01/app/oracle/oradata/coffexiang/control01.ctl
lrwx------. 1 oracle oinstall 64 Jan 13 08:08 257 -> 
/u01/app/oracle/oradata/coffexiang/control02.ctl
lrwx------. 1 oracle oinstall 64 Jan 13 08:08 258 -> 
/u01/app/oracle/oradata/coffexiang/system01.dbf
lrwx------. 1 oracle oinstall 64 Jan 13 08:08 259 -> 
/u01/app/oracle/oradata/coffexiang/tbs1_1.dbf (deleted)
lrwx------. 1 oracle oinstall 64 Jan 13 08:08 260 -> 
/u01/app/oracle/oradata/coffexiang/sysaux01.dbf
lrwx------. 1 oracle oinstall 64 Jan 13 08:08 261 -> 
/u01/app/oracle/oradata/coffexiang/undotbs01.dbf
lrwx------. 1 oracle oinstall 64 Jan 13 08:08 262 -> 
/u01/app/oracle/oradata/coffexiang/example01.dbf
lrwx------. 1 oracle oinstall 64 Jan 13 08:08 263 -> 
/u01/app/oracle/oradata/coffexiang/users01.dbf
lrwx------. 1 oracle oinstall 64 Jan 13 08:08 264 -> 
/u01/app/oracle/oradata/coffexiang/undotbs1.dbf
lrwx------. 1 oracle oinstall 64 Jan 13 08:08 265 -> 
/u01/app/oracle/oradata/coffexiang/temp01.dbf
lrwx------. 1 oracle oinstall 64 Jan 13 08:08 266 -> 
/u01/app/oracle/oradata/coffexiang/tbs1_2.dbf (deleted)
lr-x------. 1 oracle oinstall 64 Jan 13 08:08 3 -> /dev/null
 lr-x------. 1 oracle oinstall 64 Jan 13 08:08 4 -> 
/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/mesg/oraus.msb
lr-x------. 1 oracle oinstall 64 Jan 13 08:08 5 -> /proc/6471/fd
lrwx------. 1 oracle oinstall 64 Jan 13 08:08 6 -> 
/u01/app/oracle/product/12.1.0/dbhome_1/dbs/hc_coffexiang.dat
lrwx------. 1 oracle oinstall 64 Jan 13 08:08 9 -> 
/u01/app/oracle/product/12.1.0/dbhome_1/dbs/lkCOFFEXIANG

然后找到被删除的数据文件恢复到原来的路径里面：

[oracle@xiang66 fd]$ cp 259 /u01/app/oracle/oradata/coffexiang/tbs1_1.dbf
[oracle@xiang66 fd]$
[oracle@xiang66 fd]$ cp 266 /u01/app/oracle/oradata/coffexiang/tbs1_2.dbf
[oracle@xiang66 fd]$

最后offline 数据文件 recover后在进行online

SQL> alter database datafile 2 offline;

Database altered.

SQL> alter database datafile 8 offline;

Database altered.

SQL> recover datafile 2;
Media recovery complete.
SQL> recover datafile 8;
Media recovery complete.
SQL> alter database datafile 2 online;

Database altered.

SQL> alter database datafile 8 online;

Database altered.

测试建立相关表：

SQL> create table xiang1.tbs1 as select * from scott.emp;

Table created.

至此恢复完成。