linux 分割后恢复文件路径,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.

至此恢复完成。