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/&＃8217;spid号&＃8217;/fd 路径下找到被删除的数据文件

&＃8211;找到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.


至此恢复完成。