应用linux文件句柄恢复误删除的Oracle数据文件

使用linux文件句柄恢复误删除的Oracle数据文件
测试环境：OEL+Oracle 11.2.0.1 首先查看当前的数据文件及数据文件状态：SQL> select file_id,tablespace_name,file_name,status from dba_data_files; FILE_ID TABLESPACE FILE_NAME STATUS---------- ---------- -------------------------------------- --------- 4 USERS /oradata/orcl2/users01.dbf AVAILABLE 3 UNDOTBS1 /oradata/orcl2/undotbs01.dbf AVAILABLE 2 SYSAUX /oradata/orcl2/sysaux01.dbf AVAILABLE 1 SYSTEM /oradata/orcl2/system01.dbf AVAILABLE 6 BOOK /oradata/orcl2/book.dbf AVAILABLE 5 SG /oradata/orcl2/SG01.dbf AVAILABLE SQL> select file#,status,name from v$datafile; FILE# STATUS NAME---------- ------- ---------------------------------------- 1 SYSTEM /oradata/orcl2/system01.dbf 2 ONLINE /oradata/orcl2/sysaux01.dbf 3 ONLINE /oradata/orcl2/undotbs01.dbf 4 ONLINE /oradata/orcl2/users01.dbf 5 ONLINE /oradata/orcl2/SG01.dbf 6 ONLINE /oradata/orcl2/book.dbf 准备对数据文件/oradata/orcl2/SG01.dbf进行rm删除并使用Linux句柄进行恢复，删除后切记不可将实例shutdown（其实shutdown后也没关系，只是会使恢复更麻烦）；删除数据文件/oradata/orcl2/SG01.dbf[oracle@DBA2 orcl2]$ rm SG01.dbf [oracle@DBA2 orcl2]$ 查看文件句柄，发现/oradata/orcl2/SG01.dbf状态为（delete）如红显处：[oracle@DBA2 orcl2]$ ps -ef|grep dbw|grep -v grep|awk '{print $2}'3140PS：3140 为DBWn进程的pid，DBWn进程引用此句柄对数据文件操作，其实也可以用LGWR等其他Oracle进程，不一定非得用DBWn这个进程的pid；[oracle@DBA2 orcl2]$ cd /proc/3140/fd[oracle@DBA2 fd]$ ls -altotal 0dr-x------ 2 oracle oinstall 0 Sep 4 09:28 .dr-xr-xr-x 7 oracle oinstall 0 Sep 4 09:28 ..lr-x------ 1 oracle oinstall 64 Sep 4 09:41 0 -> /dev/nulll-wx------ 1 oracle oinstall 64 Sep 4 09:41 1 -> /dev/nulll-wx------ 1 oracle oinstall 64 Sep 4 09:41 10 -> /u/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_3056.trcl-wx------ 1 oracle oinstall 64 Sep 4 09:41 11 -> /u/oracle/diag/rdbms/orcl2/orcl2/trace/orcl2_ora_3056.trmlr-x------ 1 oracle oinstall 64 Sep 4 09:41 12 -> /u/oracle/product/11.2/rdbms/mesg/oraus.msblr-x------ 1 oracle oinstall 64 Sep 4 09:41 13 -> /dev/zerolr-x------ 1 oracle oinstall 64 Sep 4 09:41 14 -> /proc/3140/fdlr-x------ 1 oracle oinstall 64 Sep 4 09:41 15 -> /dev/zerolrwx------ 1 oracle oinstall 64 Sep 4 09:41 16 -> /u/oracle/product/11.2/dbs/hc_orcl2.datlrwx------ 1 oracle oinstall 64 Sep 4 09:41 17 -> /u/oracle/product/11.2/dbs/lkORCL2lrwx------ 1 oracle oinstall 64 Sep 4 09:41 18 -> /oradata/orcl2/control01.ctllrwx------ 1 oracle oinstall 64 Sep 4 09:41 19 -> /u/oracle/flash_recovery_area/orcl2/control02.ctll-wx------ 1 oracle oinstall 64 Sep 4 09:41 2 -> /dev/nulllrwx------ 1 oracle oinstall 64 Sep 4 09:41 20 -> /oradata/orcl2/system01.dbflrwx------ 1 oracle oinstall 64 Sep 4 09:41 21 -> /oradata/orcl2/sysaux01.dbflrwx------ 1 oracle oinstall 64 Sep 4 09:41 22 -> /oradata/orcl2/undotbs01.dbflrwx------ 1 oracle oinstall 64 Sep 4 09:41 23 -> /oradata/orcl2/users01.dbflrwx------ 1 oracle oinstall 64 Sep 4 09:41 24 -> /oradata/orcl2/SG01.dbf (deleted)lrwx------ 1 oracle oinstall 64 Sep 4 09:41 25 -> /oradata/orcl2/book.dbflrwx------ 1 oracle oinstall 64 Sep 4 09:41 26 -> /oradata/orcl2/temp01.dbflrwx------ 1 oracle oinstall 64 Sep 4 09:41 27 -> /oradata/orcl2/temp02.dbflrwx------ 1 oracle oinstall 64 Sep 4 09:41 28 -> /oradata/orcl2/temo01.dbflr-x------ 1 oracle oinstall 64 Sep 4 09:41 29 -> /u/oracle/product/11.2/rdbms/mesg/oraus.msbl-wx------ 1 oracle oinstall 64 Sep 4 09:41 3 -> /u/oracle/product/11.2/rdbms/log/orcl2_ora_3056.trclr-x------ 1 oracle oinstall 64 Sep 4 09:41 4 -> /dev/nulllr-x------ 1 oracle oinstall 64 Sep 4 09:41 5 -> /dev/nulllr-x------ 1 oracle oinstall 64 Sep 4 09:41 6 -> /dev/nulllrwx------ 1 oracle oinstall 64 Sep 4 09:41 7 -> /u/oracle/product/11.2/dbs/hc_orcl2.datlrwx------ 1 oracle oinstall 64 Sep 4 09:41 8 -> /u/oracle/product/11.2/dbs/lkinstorcl2 (deleted)lr-x------ 1 oracle oinstall 64 Sep 4 09:41 9 -> /proc/3140/fd将此文件cp到一个新的位置：[oracle@DBA2 fd]$ cp 24 /oradata/SG.dbf 恢复数据文件SQL> select file#,status,name from v$datafile; FILE# STATUS NAME---------- ------- -------------------------------------- 1 SYSTEM /oradata/orcl2/system01.dbf 2 ONLINE /oradata/orcl2/sysaux01.dbf 3 ONLINE /oradata/orcl2/undotbs01.dbf 4 ONLINE /oradata/orcl2/users01.dbf 5 ONLINE /oradata/orcl2/SG01.dbf 6 ONLINE /oradata/orcl2/book.dbfoffline数据文件：SQL> alter database datafile 5 offline;Database altered.SQL> select file#,status,name from v$datafile; FILE# STATUS NAME---------- ------- -------------------------------------- 1 SYSTEM /oradata/orcl2/system01.dbf 2 ONLINE /oradata/orcl2/sysaux01.dbf 3 ONLINE /oradata/orcl2/undotbs01.dbf 4 ONLINE /oradata/orcl2/users01.dbf 5 RECOVER /oradata/orcl2/SG01.dbf 6 ONLINE /oradata/orcl2/book.dbfrename数据文件到新的位置：SQL> alter database rename file '/oradata/orcl2/SG01.dbf' to '/oradata/SG.dbf';Database altered. 此时数据文件状态依然为RECOVER状态，需要恢复一下：SQL> recover datafile 5;Media recovery complete.SQL> select file#,status,name from v$datafile; FILE# STATUS NAME---------- ------- -------------------------------------- 1 SYSTEM /oradata/orcl2/system01.dbf 2 ONLINE /oradata/orcl2/sysaux01.dbf 3 ONLINE /oradata/orcl2/undotbs01.dbf 4 ONLINE /oradata/orcl2/users01.dbf 5 OFFLINE /oradata/SG.dbf 6 ONLINE /oradata/orcl2/book.dbf好了，恢复过来了，变成OFFLINE了，现在将数据文件online：SQL> alter database datafile 5 online;Database altered.SQL> select file#,status,name from v$datafile; FILE# STATUS NAME---------- ------- -------------------------------------- 1 SYSTEM /oradata/orcl2/system01.dbf 2 ONLINE /oradata/orcl2/sysaux01.dbf 3 ONLINE /oradata/orcl2/undotbs01.dbf 4 ONLINE /oradata/orcl2/users01.dbf 5 ONLINE /oradata/SG.dbf 6 ONLINE /oradata/orcl2/book.dbfOK，成功了，利用Linux句柄将误删除的数据文件恢复过来了，并从新online可使用！

方法来自于网络；

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

QQ: 584307876

作者: Seven

原文链接:http://blog.csdn.net/sunjiapeng/article/details/11037233

邮箱: seven_ginna@foxmail.com

应用linux文件句柄恢复误删除的Oracle

热点推荐