linux 误删除Oracle数据文件恢复

本文偶得，权当记录查询，原文转至现某炬Oracle lunar 女神

[oracle@lunar ~]$ sqlplus /  as  sysdba   SQL*Plus: Release 11.2.0.3.0 Production  on  Fri Mar 15 23:46:35 2013   Copyright (c) 1982, 2011, Oracle.   All  rights reserved.     Connected  to : Oracle  Database  11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With  the Partitioning, OLAP, Data Mining  and  Real  Application Testing options   SQL>  select  tablespace_name  from  dba_tablespaces;   TABLESPACE_NAME ------------------------------------------------------------ SYSTEM SYSAUX UNDOTBS1                    TEMP USERS EZIO   6  rows  selected.

创建测试表空间

SQL>  create  tablespace lunar_test datafile  '/test/lunar_test01.dbf'  size  10M;   Tablespace created.   SQL>  alter  user  lunar  default  tablespace lunar_test;   User  altered.

创建表插入数据

SQL> conn lunar/lunar Connected. SQL>  create  table  test_table ( name  varchar2(300)) tablespace lunar_test;   Table  created.   SQL>  insert  into  test_table  values  ( 'Love you forever, my baby, Happy Childrens Day!' );   1 row created.   SQL>  commit ;   Commit  complete.   SQL> SQL>  select  *  from  test_table;   NAME -------------------------------------------------------------------------------- Love you forever, my baby, Happy Childrens  Day !

删除datafile

SQL> !rm -rf /test/lunar_test01.dbf   SQL> !ls -lrt /test/lunar_test01.dbf ls: /test/lunar_test01.dbf:  No  such file  or  directory   SQL>

数据还在，因为从buffer cache中读到的

SQL> conn lunar/lunar Connected. SQL>  select  *  from  test_table;   NAME -------------------------------------------------------------------------------- Love you forever, my baby, Happy Childrens  Day !

执行flush buffer cache

SQL> conn /  as  sysdba Connected. SQL>  alter  system flush buffer_cache;   System altered.

可以看见，再次查询，报错文件状态不对了（找不到了）

SQL> conn lunar/lunar Connected. SQL>  select  *  from  test_table; select  *  from  test_table * ERROR  at  line 1: ORA-01116: error  in  opening  database  file 6 ORA-01110: data file 6:  '/test/lunar_test01.dbf' ORA-27041: unable  to  open  file Linux-x86_64 Error: 2:  No  such file  or  directory Additional information: 3

检查dbwr进程的spid

SQL> !ps -ef|grep dbw oracle    2757     1  0 12:29 ?        00:00:14 ora_dbw0_bb oracle    8912  8872  0 23:50 pts/3    00:00:00 /bin/bash -c ps -ef|grep dbw oracle    8914  8912  0 23:50 pts/3    00:00:00 grep dbw

找到dbwr的句柄

SQL> !/usr/sbin/lsof -p 2757 COMMAND  PID    USER    FD   TYPE DEVICE    SIZE / OFF     NODE  NAME oracle  2757 oracle  cwd    DIR  253,0       4096 6947181 /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs oracle  2757 oracle  rtd    DIR  253,0       4096       2 / oracle  2757 oracle  txt    REG  253,0  232399473 1179781 /u01/app/oracle/product/11.2.0.3/dbhome_1/bin/oracle oracle  2757 oracle  DEL    REG    0,4            1933317 /SYSV00000000 oracle  2757 oracle  DEL    REG    0,4            1966086 /SYSV00000000 oracle  2757 oracle  DEL    REG    0,4            1998855 /SYSV2b7268c4 oracle  2757 oracle  mem    REG  253,0     144776 2293762 /lib64/ld-2.5.so oracle  2757 oracle  mem    REG  253,0       5328 4822547 /usr/lib64/libaio.so.1.0.1 oracle  2757 oracle  mem    REG  253,0    1722328 2293764 /lib64/libc-2.5.so oracle  2757 oracle  mem    REG  253,0      23360 2293771 /lib64/libdl-2.5.so oracle  2757 oracle  mem    REG  253,0     615136 2293809 /lib64/libm-2.5.so oracle  2757 oracle  mem    REG  253,0     145872 2293775 /lib64/libpthread-2.5.so oracle  2757 oracle  mem    REG  253,0      53448 2293781 /lib64/librt-2.5.so oracle  2757 oracle  mem    REG  253,0     114352 2293986 /lib64/libnsl-2.5.so oracle  2757 oracle  mem    REG  253,0      58949 6955646 /u01/app/oracle/product/11.2.0.3/dbhome_1/lib/libnque11.so oracle  2757 oracle  262uW  REG  253,0   87040000 4784725 /u01/app/oracle/oradata/bb/temp01.dbf oracle  2757 oracle  263uW  REG  253,0   10493952 1639050 /test/lunar_test01.dbf (deleted)   可以看见文件被标识为 delete

进入dbwr进程的File Descriptor number目录中

SQL> !ls -ltar /proc/2757/fd/ total 0 dr-xr-xr-x 7 oracle oinstall  0 Mar 15 12:29 .. dr-x ------ 2 oracle oinstall  0 Mar 15 12:29 . lr-x ------ 1 oracle oinstall 64 Mar 15 23:30 9 -> /dev/null lr-x ------ 1 oracle oinstall 64 Mar 15 23:30 8 -> /dev/null lrwx ------ 1 oracle oinstall 64 Mar 15 23:30 7 -> /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/hc_bb.dat lr-x ------ 1 oracle oinstall 64 Mar 15 23:30 6 -> /dev/null lr-x ------ 1 oracle oinstall 64 Mar 15 23:30 5 -> /dev/null lr-x ------ 1 oracle oinstall 64 Mar 15 23:30 4 -> /dev/null lr-x ------ 1 oracle oinstall 64 Mar 15 23:30 3 -> /dev/null lrwx ------ 1 oracle oinstall 64 Mar 15 23:30 263 -> /test/lunar_test01.dbf (deleted)   可以看见dbwr的进程的FD号 lrwx ------ 1 oracle oinstall 64 Mar 15 23:30 262 -> /u01/app/oracle/oradata/bb/temp01.dbf lrwx ------ 1 oracle oinstall 64 Mar 15 23:30 261 -> /u01/app/oracle/oradata/bb/ezio01.dbf lrwx ------ 1 oracle oinstall 64 Mar 15 23:30 260 -> /u01/app/oracle/oradata/bb/users01.dbf lrwx ------ 1 oracle oinstall 64 Mar 15 23:30 259 -> /u01/app/oracle/oradata/bb/undotbs01.dbf lrwx ------ 1 oracle oinstall 64 Mar 15 23:30 258 -> /u01/app/oracle/oradata/bb/sysaux01.dbf lrwx ------ 1 oracle oinstall 64 Mar 15 23:30 257 -> /u01/app/oracle/oradata/bb/system01.dbf lrwx ------ 1 oracle oinstall 64 Mar 15 23:30 256 -> /u01/app/oracle/oradata/bb/control01.ctl l-wx ------ 1 oracle oinstall 64 Mar 15 23:30 2 -> /dev/null lr-x ------ 1 oracle oinstall 64 Mar 15 23:30 18 -> /u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms/mesg/oraus.msb lrwx ------ 1 oracle oinstall 64 Mar 15 23:30 17 -> /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/lkBB lrwx ------ 1 oracle oinstall 64 Mar 15 23:30 16 -> /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/hc_bb.dat lr-x ------ 1 oracle oinstall 64 Mar 15 23:30 15 -> /dev/zero lr-x ------ 1 oracle oinstall 64 Mar 15 23:30 14 -> /proc/2757/fd lr-x ------ 1 oracle oinstall 64 Mar 15 23:30 13 -> /u01/app/oracle/product/11.2.0.3/dbhome_1/rdbms/mesg/oraus.msb lrwx ------ 1 oracle oinstall 64 Mar 15 23:30 12 -> /u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/hc_bb.dat lr-x ------ 1 oracle oinstall 64 Mar 15 23:30 11 -> /dev/zero lr-x ------ 1 oracle oinstall 64 Mar 15 23:30 10 -> /dev/zero l-wx ------ 1 oracle oinstall 64 Mar 15 23:30 1 -> /dev/null lr-x ------ 1 oracle oinstall 64 Mar 15 23:30 0 -> /dev/null lrwx ------ 1 oracle oinstall 64 Mar 15 23:37 19 -> socket:[42951]

恢复过程

[oracle@lunar ~]$  cd  /proc/2757/fd/ --根据File Descriptor number重建数据文件： [oracle@lunar fd]$  cat  263 >  /test/lunar01 .dbf

检查下，文件已经恢复完成，大小为10m

[oracle@lunar fd]$  ls  -lrt  /test/lunar01 .dbf -rw-r--r-- 1 oracle oinstall 10493952 Mar 15 23:50  /test/lunar01 .dbf

将数据文件offline

SQL>  alter  database  datafile  '/test/lunar_test01.dbf'  offline;   Database  altered. [/sql 修改控制文件的文件信息，因为已经将rm的/test/lunar_test01.dbf，创建到/test/lunar01.dbf，因此要同步控制文件中的信息 1 SQL>  alter  database  rename file  '/test/lunar_test01.dbf'  to  '/test/lunar01.dbf' ;   Database  altered.

恢复datafile

SQL> recover datafile  '/test/lunar01.dbf' ; Media recovery complete.

将数据文件online

SQL>  alter  database  datafile  '/test/lunar01.dbf'  online;   Database  altered.

完成恢复

SQL> conn lunar/lunar Connected. SQL>  select  *  from  test_table;   NAME -------------------------------------------------------------------------------- Love you forever, my baby, Happy Childrens  Day !   SQL> SQL>  select  tablespace_name  from  dba_tablespaces;   TABLESPACE_NAME ------------------------------------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS EZIO LUNAR_TEST   7  rows  selected.   SQL>  select  name  from  v$datafile;   NAME -------------------------------------------------------------------------------- /u01/app/oracle/oradata/bb/system01.dbf /u01/app/oracle/oradata/bb/sysaux01.dbf /u01/app/oracle/oradata/bb/undotbs01.dbf /u01/app/oracle/oradata/bb/users01.dbf /u01/app/oracle/oradata/bb/ezio01.dbf /test/lunar01.dbf   6  rows  selected