在没有备份的情况下，运行中的数据库突然丢失了或者误删除一个非关键数据文件，
这时候，记得不要马上关闭数据库，这个问题可以从系统层上来恢复丢失了的数据文件。

系统层恢复数据库运行下的数据文件过程如下：

1、创建测试表：
SQL> create table t2 tablespace TS_ORA11GR2 as select 1 id from dual;
Table created.

2、删除表空间TS_ORA11GR2的数据文件：
[oracle@enmo OCMU]$ rm ts_ora11gr2_01.dbf
[oracle@enmo OCMU]$ ls
control01.ctl example01.dbf redo02.log redo05.log system01.dbf ts_audit01.dbf users01.dbf
control03.ctl MYTEST02.dbf redo03.log redo06.log temp01.dbf tscatalog.dbf
create redo01.log redo04.log sysaux.dbf test01.dbf undotbs01.dbf

3、尝试创建新表到丢失的数据文件上：
SQL> create table t3 tablespace TS_ORA11GR2 as select 1 id from dual;
create table t3 tablespace ts_test as select 1 id from dual
*
ERROR at line 1:
ORA-00959: tablespace 'TS_ORA11GR2' does not exist

4、操作系统层查看句柄：
[oracle@enmo ~]$ ps -ef|grep dbw|grep -v grep
oracle 4610 1 0 16:43 ? 00:00:01 ora_dbw0_OCMU
[oracle@enmo ~]$

total 0
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 9 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/hc_OCMU.dat
lr-x------ 1 oracle oinstall 64 Oct 12 23:33 8 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Oct 12 23:33 7 -> /proc/4610/fd
lr-x------ 1 oracle oinstall 64 Oct 12 23:33 6 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
lr-x------ 1 oracle oinstall 64 Oct 12 23:33 5 -> /dev/null
lr-x------ 1 oracle oinstall 64 Oct 12 23:33 4 -> /dev/null
lr-x------ 1 oracle oinstall 64 Oct 12 23:33 3 -> /dev/null
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 271 -> /u01/app/oracle/oradata/mytemp.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 270 -> /u01/app/oracle/oradata/OCMU/temp01.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 269 -> /u01/app/oracle/oradata/OCMU/tscatalog.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 268 -> /u01/app/oracle/oradata/OCMU/ts_audit01.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 267 -> /u01/app/oracle/oradata/OCMU/MYTEST02.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 266 -> /u01/app/oracle/oradata/OCMU/test01.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 265 -> /u01/app/oracle/oradata/OCMU/ts_ora11gr2_01.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 264 -> /u01/app/oracle/oradata/OCMU/example01.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 263 -> /u01/app/oracle/oradata/OCMU/users01.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 262 -> /u01/app/oracle/oradata/OCMU/sysaux.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 261 -> /u01/app/oracle/oradata/OCMU/undotbs01.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 260 -> /u01/app/oracle/oradata/OCMU/system01.dbf
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 259 -> /u01/app/FRA/control04.ctl
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 258 -> /u01/app/oracle/oradata/OCMU/control03.ctl
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 257 -> /u01/app/oracle/fast_recovery_area/control02.ctl
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 256 -> /u01/app/oracle/oradata/OCMU/control01.ctl
l-wx------ 1 oracle oinstall 64 Oct 12 23:33 2 -> /dev/null
lr-x------ 1 oracle oinstall 64 Oct 12 23:33 11 -> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 Oct 12 23:33 10 -> /u01/app/oracle/product/11.2.0/dbhome_1/dbs/lkORA11GR2
l-wx------ 1 oracle oinstall 64 Oct 12 23:33 1 -> /dev/null
lr-x------ 1 oracle oinstall 64 Oct 12 23:33 0 -> /dev/null

5、根据句柄找回文件：
cp /proc//4610/fd/265 /u01/app/oracle/oradata/OCMU/ts_ora11gr2_01.dbf

6、查看文件位置：
SQL> select file#,status,name from v$datafile;

FILE# STATUS
---------- -------
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
1 SYSTEM
/u01/app/oracle/oradata/OCMU/system01.dbf

2 ONLINE
/u01/app/oracle/oradata/OCMU/undotbs01.dbf

3 ONLINE
/u01/app/oracle/oradata/OCMU/sysaux.dbf

FILE# STATUS
---------- -------
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
4 ONLINE
/u01/app/oracle/oradata/OCMU/users01.dbf

5 ONLINE
/u01/app/oracle/oradata/OCMU/example01.dbf

6 ONLINE
/u01/app/oracle/oradata/OCMU/ts_ora11gr2_01.dbf

7、修改文件状态：
SQL> alter database datafile 6 offline;
Database altered.

8、再次查看：
SQL> select file#,status,name from v$datafile;
FILE# STATUS
---------- -------
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
1 SYSTEM
/u01/app/oracle/oradata/OCMU/system01.dbf

2 ONLINE
/u01/app/oracle/oradata/OCMU/undotbs01.dbf

3 ONLINE
/u01/app/oracle/oradata/OCMU/sysaux.dbf

FILE# STATUS
---------- -------
NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
4 ONLINE
/u01/app/oracle/oradata/OCMU/users01.dbf

5 ONLINE
/u01/app/oracle/oradata/OCMU/example01.dbf

6 RECOVER
/u01/app/oracle/oradata/OCMU/ts_ora11gr2_01.dbf

9、恢复文件：
SQL> recover datafile 6;
Media recovery complete.

10、设置数据文件online：
SQL> alter database datafile 6 online;

Database altered.

恢复完成。

ITPub博客

操作系统层恢复非关键数据文件

最新文章