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

环境：RedHat Enterprise Server数据库：11.2.0.1.0归档模式：非归档提示：如果不小心通过操作系统命令误删除了数据文件，一定不要轻易的关闭实例或者offline数据文件.
在user表空间中新增一个数据文件/data/Oracle/oradata/orcl/user03.dbforcl@ SYS> selectfile#,status,name from v$datafile;
FILE# STATUSNAME---------- ------------------------------------------------------------1 SYSTEM/data/oracle/oradata/orcl/system01.dbf2 ONLINE/data/oracle/oradata/orcl/sysaux01.dbf3 ONLINE/data/oracle/oradata/orcl/undotbs01.dbf4 RECOVER/data/oracle/oradata/orcl/users01.dbf5 ONLINE/data/oracle/oradata/orcl/test.dbf6 ONLINE/data/oracle/oradata/orcl/user02.dbf7 ONLINE/data/oracle/oradata/orcl/t1.dbf
7 rows selected.
Elapsed: 00:00:00.01orcl@ SYS> altertablespace users add datafile"/data/oracle/oradata/orcl/user03.dbf" size 10M;
Tablespace altered.
Elapsed: 00:00:08.78orcl@ SYS>orcl@ SYS> select file#,status,name from v$datafile;
FILE# STATUSNAME---------- ------------------------------------------------------------1 SYSTEM /data/oracle/oradata/orcl/system01.dbf2 ONLINE /data/oracle/oradata/orcl/sysaux01.dbf3 ONLINE /data/oracle/oradata/orcl/undotbs01.dbf4 RECOVER /data/oracle/oradata/orcl/users01.dbf5 ONLINE /data/oracle/oradata/orcl/test.dbf6 ONLINE /data/oracle/oradata/orcl/user02.dbf7 ONLINE /data/oracle/oradata/orcl/t1.dbf8 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.dbfrm：是否删除一般文件“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 dbworacle356710 09:58 ？00:00:01 ora_dbw0_orclroot528744050 10:50 pts/700:00:00 grep dbw---------------------------[root@linux128 fd]# cd /proc/3567/fd[root@linux128 fd]#ll总计 0lr-x------ 1 oracleoinstall 64 05-11 10:19 0 -> /dev/nulll-wx------ 1 oracleoinstall 64 05-11 10:19 1 -> /dev/nulll-wx------ 1 oracleoinstall 64 05-11 10:19 10 ->/data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3546.trcl-wx------ 1 oracleoinstall 64 05-11 10:19 11 ->/data/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_3546.trmlr-x------ 1 oracleoinstall 64 05-11 10:19 12 ->/data/oracle/product/11.2/db_1/rdbms/mesg/orazhs.msblr-x------ 1 oracleoinstall 64 05-11 10:19 13 -> /dev/zerolr-x------ 1 oracleoinstall 64 05-11 10:19 14 -> /proc/3567/fdlr-x------ 1 oracleoinstall 64 05-11 10:19 15 -> /dev/zerolr-x------ 1 oracleoinstall 64 05-11 10:19 16 ->/data/oracle/product/11.2/db_1/rdbms/mesg/oraus.msblrwx------ 1 oracleoinstall 64 05-11 10:19 17 -> /data/oracle/product/11.2/db_1/dbs/hc_orcl.datlrwx------ 1 oracleoinstall 64 05-11 10:19 18 -> /data/oracle/product/11.2/db_1/dbs/lkORCLlrwx------ 1 oracleoinstall 64 05-11 10:19 19 -> /data/oracle/oradata/orcl/control01.ctll-wx------ 1 oracleoinstall 64 05-11 10:19 2 -> /dev/nulllrwx------ 1 oracleoinstall 64 05-11 10:19 20 ->/data/oracle/flash_recovery_area/orcl/control02.ctllrwx------ 1 oracleoinstall 64 05-11 10:19 21 -> socket:[80458]lrwx------ 1 oracleoinstall 64 05-11 10:19 22 -> /data/oracle/oradata/orcl/system01.dbflrwx------ 1 oracleoinstall 64 05-11 10:19 23 -> /data/oracle/oradata/orcl/sysaux01.dbflrwx------ 1 oracleoinstall 64 05-11 10:19 24 -> /data/oracle/oradata/orcl/undotbs01.dbflrwx------ 1 oracleoinstall 64 05-11 10:19 25 -> /data/oracle/oradata/orcl/test.dbflrwx------ 1 oracleoinstall 64 05-11 10:19 26 -> /data/oracle/oradata/orcl/user02.dbflrwx------ 1 oracleoinstall 64 05-11 10:19 27 -> /data/oracle/oradata/orcl/t1.dbflr-x------ 1 oracleoinstall 64 05-11 10:19 28 ->/data/oracle/product/11.2/db_1/rdbms/mesg/orazhs.msblr-x------ 1 oracleoinstall 64 05-11 10:19 29 ->/data/oracle/product/11.2/db_1/rdbms/mesg/oraus.msbl-wx------ 1 oracleoinstall 64 05-11 10:19 3 ->/data/oracle/product/11.2/db_1/rdbms/log/orcl_ora_3546.trclrwx------ 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/nulllr-x------ 1 oracleoinstall 64 05-11 10:19 5 -> /dev/nulllr-x------ 1 oracleoinstall 64 05-11 10:19 6 -> /dev/nulllrwx------ 1 oracleoinstall 64 05-11 10:19 7 -> /data/oracle/product/11.2/db_1/dbs/hc_orcl.datlrwx------ 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.19orcl@ SYS>orcl@ SYS>orcl@ SYS> selectfile#,status,name from v$datafile;
FILE# STATUSNAME---------- ------------------------------------------------------------1 SYSTEM/data/oracle/oradata/orcl/system01.dbf2 ONLINE/data/oracle/oradata/orcl/sysaux01.dbf3 ONLINE/data/oracle/oradata/orcl/undotbs01.dbf4 RECOVER/data/oracle/oradata/orcl/users01.dbf5 ONLINE/data/oracle/oradata/orcl/test.dbf6 ONLINE/data/oracle/oradata/orcl/user02.dbf7 ONLINE/data/oracle/oradata/orcl/t1.dbf8 RECOVER/data/oracle/oradata/orcl/user03.dbf
8 rows selected.
Elapsed: 00:00:00.03orcl@ SYS>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 fileORA-01110: data file8: "/data/oracle/oradata/orcl/user03.dbf"Elapsed: 00:00:00.03orcl@ SYS>--------------我们对删除的user03.dbf重命名执行新路径----orcl@ SYS> alterdatabase rename file "/data/oracle/oradata/orcl/user03.dbf" to"/data/user03.dbf";
Database altered.
Elapsed: 00:00:00.07orcl@ SYS>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 recoveryORA-01110: data file8: "/data/user03.dbf" Elapsed: 00:00:00.04orcl@ SYS>orcl@ SYS>orcl@ SYS> recoverdatafile 8;Media recoverycomplete.orcl@ SYS>orcl@ SYS>orcl@ SYS> alterdatabase datafile"/data/user03.dbf"online;
Database altered.
Elapsed: 00:00:00.03orcl@ SYS>orcl@ SYS>orcl@ SYS>selectfile#,status,name fromv$datafile;
FILE# STATUSNAME---------- ------------------------------------------------------------1 SYSTEM/data/oracle/oradata/orcl/system01.dbf2 ONLINE/data/oracle/oradata/orcl/sysaux01.dbf3 ONLINE/data/oracle/oradata/orcl/undotbs01.dbf4 RECOVER/data/oracle/oradata/orcl/users01.dbf5 ONLINE/data/oracle/oradata/orcl/test.dbf6 ONLINE/data/oracle/oradata/orcl/user02.dbf7 ONLINE/data/oracle/oradata/orcl/t1.dbf8 ONLINE/data/user03.dbf
8 rows selected.
Elapsed: 00:00:00.04orcl@ SYS>----------数据文件成功恢复------需要注意的，如果数据库没有归档，那么操作一定要尽快，防止redo中关于user03.dbf的日志被冲掉。更多RedHat相关信息见RedHat 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=10redhat6.3下安装drbd修改CentOS 6.3启动级别以及启动级别配置错误的修改问题相关资讯 Linux恢复数据文件本文评论查看全部评论（0）