Linux下通过文件句柄恢复误删除的数据文件

wangshuai00

2013-05-12

关注关注

环境：RedHat Enterprise Server

数据库：11.2.0.1.0

归档模式：非归档

提示：如果不小心通过操作系统命令误删除了数据文件，一定不要轻易的关闭实例或者offline数据文件.

在user表空间中新增一个数据文件/data/Oracle/oradata/orcl/user03.dbf

orcl@ SYS> selectfile#,status,name from v$datafile;

FILE# STATUSNAME

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

1 SYSTEM/data/oracle/oradata/orcl/system01.dbf

2 ONLINE/data/oracle/oradata/orcl/sysaux01.dbf

3 ONLINE/data/oracle/oradata/orcl/undotbs01.dbf

4 RECOVER/data/oracle/oradata/orcl/users01.dbf

5 ONLINE/data/oracle/oradata/orcl/test.dbf

6 ONLINE/data/oracle/oradata/orcl/user02.dbf

7 ONLINE/data/oracle/oradata/orcl/t1.dbf

7 rows selected.

Elapsed: 00:00:00.01

orcl@ SYS> altertablespace users add datafile'/data/oracle/oradata/orcl/user03.dbf' size 10M;

Tablespace altered.

Elapsed: 00:00:08.78

orcl@ SYS>

orcl@ SYS> select file#,status,name from v$datafile;

FILE# STATUSNAME

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

1 SYSTEM /data/oracle/oradata/orcl/system01.dbf

2 ONLINE /data/oracle/oradata/orcl/sysaux01.dbf

3 ONLINE /data/oracle/oradata/orcl/undotbs01.dbf

4 RECOVER /data/oracle/oradata/orcl/users01.dbf

5 ONLINE /data/oracle/oradata/orcl/test.dbf

6 ONLINE /data/oracle/oradata/orcl/user02.dbf

7 ONLINE /data/oracle/oradata/orcl/t1.dbf

8 ONLINE /data/oracle/oradata/orcl/user03.dbf

8 rows selected.

orcl@ SYS>

------------------------在操作系统用root用户删除新增数据文件user03.dbf--------

[root@linux128 dbs]#cd /data/oracle/oradata/orcl

[root@linux128orcl]# ll

总计 1599388

-rw-r----- 1 oracleoinstall10076160 05-11 10:54control01.ctl

-rw-r----- 1 oracleoinstall9748480 12-30 14:30control01.ctl.bk

-rw-r----- 1 oracleoinstall52429312 05-11 10:54 redo01.log

-rw-r----- 1 oracleoinstall52429312 05-11 10:09 redo02.log

-rw-r----- 1 oracleoinstall52429312 05-11 10:09 redo03.log

-rw-r----- 1 oracleoinstall 566239232 05-11 10:53 sysaux01.dbf

-rw-r----- 1 oracleoinstall 734011392 05-11 10:50 system01.dbf

-rw-r----- 1 oracleoinstall5251072 05-11 10:09 t1.dbf

-rw-r----- 1 oracleoinstall30416896 12-30 13:20 temp01.dbf

-rw-r----- 1 oracleoinstall5251072 12-30 14:00 temp02.dbf

-rw-r----- 1 oracleoinstall5251072 2012-10-22 temp03.dbf

-rw-r----- 1 oracleoinstall5251072 05-11 10:09 test.dbf

-rw-r----- 1 oracleoinstall78651392 05-11 10:50undotbs01.dbf

-rw-r----- 1 oracleoinstall31465472 05-11 10:09 user02.dbf

-rw-r----- 1 oracleoinstall10493952 05-11 10:50 user03.dbf

-rw-r----- 1 oracleoinstall5251072 05-11 10:18users01.dbf

[root@linux128orcl]# rm user03.dbf

rm：是否删除一般文件“user03.dbf”? y

[root@linux128orcl]# ll

总计 1589124

-rw-r----- 1 oracleoinstall10076160 05-11 10:54control01.ctl

-rw-r----- 1 oracleoinstall9748480 12-30 14:30control01.ctl.bk

-rw-r----- 1 oracleoinstall52429312 05-11 10:54 redo01.log

-rw-r----- 1 oracleoinstall52429312 05-11 10:09 redo02.log

-rw-r----- 1 oracleoinstall52429312 05-11 10:09 redo03.log

-rw-r----- 1 oracleoinstall 566239232 05-11 10:53 sysaux01.dbf

-rw-r----- 1 oracleoinstall 734011392 05-11 10:54 system01.dbf

-rw-r----- 1 oracleoinstall5251072 05-11 10:09 t1.dbf

-rw-r----- 1 oracleoinstall30416896 12-30 13:20 temp01.dbf

-rw-r----- 1 oracleoinstall5251072 12-30 14:00 temp02.dbf

-rw-r----- 1 oracleoinstall5251072 2012-10-22 temp03.dbf

-rw-r----- 1 oracleoinstall5251072 05-11 10:09 test.dbf

-rw-r----- 1 oracleoinstall78651392 05-11 10:54undotbs01.dbf

-rw-r----- 1 oracleoinstall31465472 05-11 10:09 user02.dbf

-rw-r----- 1 oracleoinstall5251072 05-11 10:18users01.dbf

[root@linux128orcl]#

-------我们通过过滤dbw进程，找出进程号，并查找到对应的文件句柄-------

此时务必不能做数据库级别的offline或者关闭实例，否则将无法定位删除文件。

[root@linux128 test]#ps -ef| grep dbw

oracle356710 09:58 ?00:00:01 ora_dbw0_orcl

root528744050 10:50 pts/700:00:00 grep dbw

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

[root@linux128 fd]# cd /proc/3567/fd

[root@linux128 fd]#ll

总计 0

lr-x------ 1 oracleoinstall 64 05-11 10:19 0 -> /dev/null

l-wx------ 1 oracleoinstall 64 05-11 10:19 1 -> /dev/null

l-wx------ 1 oracleoinstall 64 05-11 10:19 10 ->/data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3546.trc

l-wx------ 1 oracleoinstall 64 05-11 10:19 11 ->/data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3546.trm

lr-x------ 1 oracleoinstall 64 05-11 10:19 12 ->/data/oracle/product/11.2/db_1/rdbms/mesg/orazhs.msb

lr-x------ 1 oracleoinstall 64 05-11 10:19 13 -> /dev/zero

lr-x------ 1 oracleoinstall 64 05-11 10:19 14 -> /proc/3567/fd

lr-x------ 1 oracleoinstall 64 05-11 10:19 15 -> /dev/zero

lr-x------ 1 oracleoinstall 64 05-11 10:19 16 ->/data/oracle/product/11.2/db_1/rdbms/mesg/oraus.msb

lrwx------ 1 oracleoinstall 64 05-11 10:19 17 -> /data/oracle/product/11.2/db_1/dbs/hc_orcl.dat

lrwx------ 1 oracleoinstall 64 05-11 10:19 18 -> /data/oracle/product/11.2/db_1/dbs/lkORCL

lrwx------ 1 oracleoinstall 64 05-11 10:19 19 -> /data/oracle/oradata/orcl/control01.ctl

l-wx------ 1 oracleoinstall 64 05-11 10:19 2 -> /dev/null

lrwx------ 1 oracleoinstall 64 05-11 10:19 20 ->/data/oracle/flash_recovery_area/orcl/control02.ctl

lrwx------ 1 oracleoinstall 64 05-11 10:19 21 -> socket:[80458]

lrwx------ 1 oracleoinstall 64 05-11 10:19 22 -> /data/oracle/oradata/orcl/system01.dbf

lrwx------ 1 oracleoinstall 64 05-11 10:19 23 -> /data/oracle/oradata/orcl/sysaux01.dbf

lrwx------ 1 oracleoinstall 64 05-11 10:19 24 -> /data/oracle/oradata/orcl/undotbs01.dbf

lrwx------ 1 oracleoinstall 64 05-11 10:19 25 -> /data/oracle/oradata/orcl/test.dbf

lrwx------ 1 oracleoinstall 64 05-11 10:19 26 -> /data/oracle/oradata/orcl/user02.dbf

lrwx------ 1 oracleoinstall 64 05-11 10:19 27 -> /data/oracle/oradata/orcl/t1.dbf

lr-x------ 1 oracleoinstall 64 05-11 10:19 28 ->/data/oracle/product/11.2/db_1/rdbms/mesg/orazhs.msb

lr-x------ 1 oracleoinstall 64 05-11 10:19 29 ->/data/oracle/product/11.2/db_1/rdbms/mesg/oraus.msb

l-wx------ 1 oracleoinstall 64 05-11 10:19 3 ->/data/oracle/product/11.2/db_1/rdbms/log/orcl_ora_3546.trc

lrwx------ 1 oracleoinstall 64 05-11 10:52 30 -> /data/oracle/oradata/orcl/user03.dbf (deleted)

lr-x------ 1 oracleoinstall 64 05-11 10:19 4 -> /dev/null

lr-x------ 1 oracleoinstall 64 05-11 10:19 5 -> /dev/null

lr-x------ 1 oracleoinstall 64 05-11 10:19 6 -> /dev/null

lrwx------ 1 oracleoinstall 64 05-11 10:19 7 -> /data/oracle/product/11.2/db_1/dbs/hc_orcl.dat

lrwx------ 1 oracleoinstall 64 05-11 10:19 8 -> /data/oracle/product/11.2/db_1/dbs/lkinstorcl(deleted)

lr-x------ 1 oracleoinstall 64 05-11 10:19 9 -> /proc/3567/fd

---------进入fd目录后，可以看到user03.dbf已经被标示为delete，我们将查找到的文件拷贝到新目录下，并改名----

[root@linux128 fd]# cp -p /proc/3567/fd/30 /data/user03.dbf

[root@linux128 test]# ll/data/user03.dbf

总计 215276

-rw-r--r-- 1rootroot104858053 05-11 10:45 test2.file

-rw-r--r-- 1rootroot104857600 05-11 10:43 test.file

-rw-r----- 1 oracleoinstall10493952 05-11 10:50 user03.dbf

[root@linux128 test]#

---因为是用的操作系统命令删除文件，oracle此时并无异常，但是在offline后尝试online时是会提示文件无法锁定----

orcl@ SYS> alterdatabase datafile 8 offline drop;

Database altered.

Elapsed: 00:00:00.19

orcl@ SYS>

orcl@ SYS> selectfile#,status,name from v$datafile;

FILE# STATUSNAME

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

1 SYSTEM/data/oracle/oradata/orcl/system01.dbf

2 ONLINE/data/oracle/oradata/orcl/sysaux01.dbf

3 ONLINE/data/oracle/oradata/orcl/undotbs01.dbf

4 RECOVER/data/oracle/oradata/orcl/users01.dbf

5 ONLINE/data/oracle/oradata/orcl/test.dbf

6 ONLINE/data/oracle/oradata/orcl/user02.dbf

7 ONLINE/data/oracle/oradata/orcl/t1.dbf

8 RECOVER/data/oracle/oradata/orcl/user03.dbf

8 rows selected.

Elapsed: 00:00:00.03

orcl@ SYS>

orcl@ SYS> alterdatabase datafile 8 online;

alter databasedatafile 8 online

ERROR at line 1:

ORA-01157: cannotidentify/lock data file 8 - see DBWR trace file

ORA-01110: data file8: '/data/oracle/oradata/orcl/user03.dbf'

Elapsed: 00:00:00.03

orcl@ SYS>

--------------我们对删除的user03.dbf重命名执行新路径----

orcl@ SYS> alterdatabase rename file '/data/oracle/oradata/orcl/user03.dbf' to'/data/user03.dbf';

Database altered.

Elapsed: 00:00:00.07

orcl@ SYS>

orcl@ SYS> alterdatabase datafile'/data/user03.dbf'online;

alter databasedatafile'/data/user03.dbf' online

ERROR at line 1:

ORA-01113: file 8needs media recovery

ORA-01110: data file8: '/data/user03.dbf'

Elapsed: 00:00:00.04

orcl@ SYS>

orcl@ SYS> recoverdatafile 8;

Media recoverycomplete.

orcl@ SYS>

orcl@ SYS> alterdatabase datafile'/data/user03.dbf'online;

Database altered.

Elapsed: 00:00:00.03

orcl@ SYS>

orcl@ SYS>selectfile#,status,name fromv$datafile;

FILE# STATUSNAME

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

1 SYSTEM/data/oracle/oradata/orcl/system01.dbf

2 ONLINE/data/oracle/oradata/orcl/sysaux01.dbf

3 ONLINE/data/oracle/oradata/orcl/undotbs01.dbf

4 RECOVER/data/oracle/oradata/orcl/users01.dbf

5 ONLINE/data/oracle/oradata/orcl/test.dbf

6 ONLINE/data/oracle/oradata/orcl/user02.dbf

7 ONLINE/data/oracle/oradata/orcl/t1.dbf

8 ONLINE/data/user03.dbf

8 rows selected.

Elapsed: 00:00:00.04

orcl@ SYS>

----------数据文件成功恢复------

需要注意的，如果数据库没有归档，那么操作一定要尽快，防止redo中关于user03.dbf的日志被冲掉。

oracle 文件句柄 linux系统 data dbf 表空间 oracle数据库 oracle表空间

安科网

Linux下通过文件句柄恢复误删除的数据文件

wangshuai00

wangshuai00

相关推荐

Oracle和MySQL的数据导入为何差别这么大

Oracle数据库的实例/表空间/用户/表之间关系简单讲解

浅谈入门级oracle数据库数据导入导出步骤

Oracle期末复习

Oracle 导入数据库dmp文件

关于使用PLSQL Developer时出现报错ora-12514的问题

oracle锁表该如何解决

Oracle表字段有Oracle关键字出现异常解决方案

甲骨文推出Oracle Exadata云服务X8M

oracle备份之备份测试脚本的方法（冷备、热备、rman)

限制ip访问Oracle数据库的方法步骤

oracle数据库的删除方法详解

Oracle通过LogMiner实现数据同步迁移

ORACLE数据库中Rownum用法详解

oracle数据库关于索引建立及使用的详细介绍

Oracle 11g 数据库的部署的图文教程

静默安装卸载 ORACLE

oracle数据库跟踪工具

踩坑了！踩坑了！NHibernate使用批量插入Oracle数据，Batch属性

oracle 11g修改归档日志目录及大小

Logstash Oracle同步设置

服务器断电导致的ORACLE异常 : ORA-00214 ORA-01033 ORA-01034 ORA-00172 ORA-27101

关于ORACLE索引的几种扫描方式

oracle 授予权限和收回权限 grant和revoke 以及角色的创建删除

oracle执行计划打印

Oracle数据库视图创建以及优点分析

wangshuai00