本文共 6184 字,大约阅读时间需要 20 分钟。
下面来模拟误删除users表空中的数据文件users01.dbf
[root@jingyong jingyong]# ls
control01.ctl example01_bak.dbf jy01.dbf redo02.log sysaux01.dbf system01.dbf undotbs01.dbf users01.dbf.bakcontrol02.ctl example01.dbf redo01.log redo03.log system_01.dbf temp01.dbf users01.dbf删除users01.dbf
[root@jingyong jingyong]# rm -rf users01.dbfSQL> create table testjy(id number(20));ERROR at line 1:ORA-01116: error in opening database file 4ORA-01110: data file 4:‘/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf’ORA-27041: unable to open fileLinux Error: 2: No such file or directoryAdditional information: 3检查dbwr的进程PID
[root@jingyong ~]# ps -ef|grep dbw0|grep -v greporacle 2236 1 0 06:40 ? 00:00:01 ora_dbw0_jingyongdbwr会打开所有数据文件的句柄。在proc目录中可以查到,目录名是进程PID,fd表示文件描述符
[root@jingyong ~]# cd /proc/2236/fd[root@jingyong fd]# ls -l
total 0lr-x—— 1 oracle oinstall 64 May 31 08:15 0 -> /dev/nulll-wx—— 1 oracle oinstall 64 May 31 08:15 1 -> /dev/nulll-wx—— 1 oracle oinstall 64 May 31 08:15 10 -> /u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2213.trcl-wx—— 1 oracle oinstall 64 May 31 08:15 11 -> /u01/app/oracle/diag/rdbms/jingyong/jingyong/trace/jingyong_ora_2213.trmlr-x—— 1 oracle oinstall 64 May 31 08:15 12 -> /u01/app/oracle/product/11.2.0/db/rdbms/mesg/oraus.msblr-x—— 1 oracle oinstall 64 May 31 08:15 13 -> /dev/zerolr-x—— 1 oracle oinstall 64 May 31 08:15 14 -> /proc/2236/fdlr-x—— 1 oracle oinstall 64 May 31 08:15 15 -> /dev/zerolrwx—— 1 oracle oinstall 64 May 31 08:15 16 -> /u01/app/oracle/product/11.2.0/db/dbs/hc_jingyong.datlrwx—— 1 oracle oinstall 64 May 31 08:15 17 -> /u01/app/oracle/product/11.2.0/db/dbs/lkJINGYONGlrwx—— 1 oracle oinstall 64 May 31 08:15 18 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/control01.ctllrwx—— 1 oracle oinstall 64 May 31 08:15 19 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/control02.ctll-wx—— 1 oracle oinstall 64 May 31 08:15 2 -> /dev/nulllrwx—— 1 oracle oinstall 64 May 31 08:15 20 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/system01.dbflrwx—— 1 oracle oinstall 64 May 31 08:15 21 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/sysaux01.dbflrwx—— 1 oracle oinstall 64 May 31 08:15 22 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/undotbs01.dbflrwx—— 1 oracle oinstall 64 May 31 08:15 23 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf (deleted)lrwx—— 1 oracle oinstall 64 May 31 08:15 24 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/example01.dbflrwx—— 1 oracle oinstall 64 May 31 08:15 25 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/jy01.dbflrwx—— 1 oracle oinstall 64 May 31 08:15 26 -> /u01/app/oracle/product/11.2.0/oradata/jingyong/temp01.dbflr-x—— 1 oracle oinstall 64 May 31 08:15 27 -> /u01/app/oracle/product/11.2.0/db/rdbms/mesg/oraus.msbl-wx—— 1 oracle oinstall 64 May 31 08:15 3 -> /u01/app/oracle/product/11.2.0/db/rdbms/log/jingyong_ora_2213.trclr-x—— 1 oracle oinstall 64 May 31 08:15 4 -> /dev/nulllr-x—— 1 oracle oinstall 64 May 31 08:15 5 -> /dev/nulllr-x—— 1 oracle oinstall 64 May 31 08:15 6 -> /dev/nulllrwx—— 1 oracle oinstall 64 May 31 08:15 7 -> /u01/app/oracle/product/11.2.0/db/dbs/hc_jingyong.dat注意其中”/u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf (deleted)”字样,表示该文件已经被删除,
直接cp该句柄文件名回原位置[root@jingyong fd]# cp 23 /u01/app/oracle/product/11.2.0/oradata/jingyong/users01.dbf数据文件users01.dbf恢复回来了,因为了用的是root用户操作的要修改一下权限
[root@jingyong jingyong]# ls -lrttotal 2564428-rw-r—– 1 root root 723525632 May 16 13:33 system_01.dbf-rw-r—– 1 root root 104865792 May 22 15:46 example01_bak.dbf-rw-r—– 1 oracle oinstall 52429312 May 31 06:40 redo02.log-rw-r—– 1 oracle oinstall 52429312 May 31 06:40 redo01.log-rw-r—– 1 oracle oinstall 1056768 May 31 06:40 jy01.dbf-rw-r—– 1 oracle oinstall 104865792 May 31 06:40 example01.dbf-rw-r—– 1 root root 24911872 May 31 08:16 users01.dbf.bak-rw-r—– 1 oracle oinstall 31465472 May 31 08:20 temp01.dbf-rw-r—– 1 oracle oinstall 608182272 May 31 08:21 sysaux01.dbf-rw-r—– 1 oracle oinstall 104865792 May 31 08:22 undotbs01.dbf-rw-r—– 1 oracle oinstall 723525632 May 31 08:22 system01.dbf-rw-r—– 1 oracle oinstall 52429312 May 31 08:22 redo03.log-rw-r—– 1 root root 24911872 May 31 08:23 users01.dbf-rw-r—– 1 oracle oinstall 10076160 May 31 08:23 control02.ctl-rw-r—– 1 oracle oinstall 10076160 May 31 08:23 control01.ctl[root@jingyong jingyong]# chown oracle:oinstall users01.dbf
[root@jingyong jingyong]# chmod 777 users01.dbf[root@jingyong jingyong]# ls -lrttotal 2564428-rw-r—– 1 root root 723525632 May 16 13:33 system_01.dbf-rw-r—– 1 root root 104865792 May 22 15:46 example01_bak.dbf-rw-r—– 1 oracle oinstall 52429312 May 31 06:40 redo02.log-rw-r—– 1 oracle oinstall 52429312 May 31 06:40 redo01.log-rw-r—– 1 oracle oinstall 1056768 May 31 06:40 jy01.dbf-rw-r—– 1 oracle oinstall 104865792 May 31 06:40 example01.dbf-rw-r—– 1 root root 24911872 May 31 08:16 users01.dbf.bak-rw-r—– 1 oracle oinstall 31465472 May 31 08:20 temp01.dbf-rw-r—– 1 oracle oinstall 723525632 May 31 08:22 system01.dbf-rwxrwxrwx 1 oracle oinstall 24911872 May 31 08:23 users01.dbf-rw-r—– 1 oracle oinstall 104865792 May 31 08:23 undotbs01.dbf-rw-r—– 1 oracle oinstall 608182272 May 31 08:23 sysaux01.dbf-rw-r—– 1 oracle oinstall 52429312 May 31 08:23 redo03.log-rw-r—– 1 oracle oinstall 10076160 May 31 08:23 control02.ctl-rw-r—– 1 oracle oinstall 10076160 May 31 08:23 control01.ctl进行数据文件恢复
[oracle@jingyong ~]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.1.0 Production on Fri May 31 08:24:35 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsSQL> alter database datafile 4 offline;
Database altered.
SQL> recover datafile 4;
Media recovery complete.SQL> alter database datafile 4 online;Database altered.
恢复的原理是,在Linux操作系统中,如果文件从操作系统级别被rm掉,之前打开该文件的进程仍然持有相应的文件句柄,所指向的文件仍然可以读写,并且该文件的文件描述符可以从/proc目录中获得。但是要注意的是,此时如果关闭数据库,则此句柄会消失.
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26015009/viewspace-763506/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26015009/viewspace-763506/