利用Linux的特性恢复误删的数据文件

原文地址：http://www.itpub.net/thread-1044449-1-2.html

      帖子中提到如下场景：Oracle on Linux的环境，在没有shutdown数据库的情况下误删数据文件system01.dbf。对于有完好rman备份或者Data Guard的情况下，神马都是浮云，但古语有云“屋漏偏逢连夜雨，船迟又遇打头风”，祸不单行的事情海了去，对于nobackup有什么好办法呢？

      少说废话，直接上处理过程：

      首先是模拟出这个囧境：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

[ora11g@test06 ~]$ sqlplus / as sysdba   SQL*Plus: Release 11.2.0.2.0 Production on Thu Feb 10 15:43:36 2011   Copyright (c) 1982, 2010, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options   SQL> select name from v$datafile;   NAME -------------------------------------------------------------------------------- /u02/oradata/ORCL/system01.dbf /u02/oradata/ORCL/sysaux01.dbf /u02/oradata/ORCL/undotbs01.dbf /u02/oradata/ORCL/users01.dbf   SQL> ! [ora11g@test06 ~]$ ll /u02/oradata/ORCL/system01.dbf -rw-r----- 1 ora11g oradba 765468672 Feb 10 15:44 /u02/oradata/ORCL/system01.dbf [ora11g@test06 ~]$ rm -rf /u02/oradata/ORCL/system01.dbf [ora11g@test06 ~]$ ll /u02/oradata/ORCL/system01.dbf                                               ls: /u02/oradata/ORCL/system01.dbf: No such file or directory [ora11g@test06 ~]$   SQL> connect scott/tiger ERROR: ORA-00604: error occurred at recursive SQL level 1 ORA-01116: error in opening database file 1 ORA-01110: data file 1: '/u02/oradata/ORCL/system01.dbf' ORA-27041: unable to open file Linux-x86_64 Error: 2: No such file or directory Additional information: 3     Warning: You are no longer connected to ORACLE.

      数据文件system01.dbf让俺“误”删了，有后悔药可以吃吗？

      先找出 db writer 进程，该进程肯定会写数据文件的，也可以通过lsof命令找出打开system01.dbf的所有进程。

1 2 3 4

[ora11g@test06 ~]$ ps -ef | grep ora_db ora11g   24861     1  0 Jan24 ?        00:00:00 ora_dbrm_ORA11G ora11g   24867     1  0 Jan24 ?        00:00:28 ora_dbw0_ORA11G ora11g   32637 32587  0 15:45 pts/3    00:00:00 grep ora_db

      进程号是 24867

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31

[ora11g@test06 ~]$ cd /proc/24867/fd/ [ora11g@test06 fd]$ ll total 0 lr-x------ 1 ora11g oradba 64 Feb 10 15:33 0 -> /dev/null l-wx------ 1 ora11g oradba 64 Feb 10 15:33 1 -> /dev/null lr-x------ 1 ora11g oradba 64 Feb 10 15:33 10 -> /u01/app/ora11g/product/11.2.0.2/db_1/rdbms/mesg/oraus.msb lr-x------ 1 ora11g oradba 64 Feb 10 15:33 11 -> /proc/24867/fd lr-x------ 1 ora11g oradba 64 Feb 10 15:33 12 -> /dev/zero lr-x------ 1 ora11g oradba 64 Feb 10 15:33 13 -> /dev/zero lrwx------ 1 ora11g oradba 64 Feb 10 15:33 14 -> /u01/app/ora11g/product/11.2.0.2/db_1/dbs/hc_ORA11G.dat lr-x------ 1 ora11g oradba 64 Feb 10 15:33 15 -> /u01/app/ora11g/product/11.2.0.2/db_1/network/mesg/nlus.msb l-wx------ 1 ora11g oradba 64 Feb 10 15:33 16 -> /u01/app/ora11g/product/11.2.0.2/db_1/network/log/sqlnet.log lr-x------ 1 ora11g oradba 64 Feb 10 15:33 17 -> /dev/zero lrwx------ 1 ora11g oradba 64 Feb 10 15:33 18 -> /u01/app/ora11g/product/11.2.0.2/db_1/dbs/hc_ORA11G.dat lrwx------ 1 ora11g oradba 64 Feb 10 15:33 19 -> /u01/app/ora11g/product/11.2.0.2/db_1/dbs/lkORCL l-wx------ 1 ora11g oradba 64 Feb 10 15:33 2 -> /dev/null lr-x------ 1 ora11g oradba 64 Feb 10 15:33 22 -> /u01/app/ora11g/product/11.2.0.2/db_1/rdbms/mesg/oraus.msb lrwx------ 1 ora11g oradba 64 Feb 10 15:33 256 -> /u02/oradata/ORCL/control01.ctl lrwx------ 1 ora11g oradba 64 Feb 10 15:33 257 -> /u01/app/ora11g/fast_recovery_area/ORCL/control02.ctl lrwx------ 1 ora11g oradba 64 Feb 10 15:33 258 -> /u02/oradata/ORCL/system01.dbf (deleted) lrwx------ 1 ora11g oradba 64 Feb 10 15:33 259 -> /u02/oradata/ORCL/sysaux01.dbf lrwx------ 1 ora11g oradba 64 Feb 10 15:33 260 -> /u02/oradata/ORCL/undotbs01.dbf lrwx------ 1 ora11g oradba 64 Feb 10 15:33 261 -> /u02/oradata/ORCL/users01.dbf lrwx------ 1 ora11g oradba 64 Feb 10 15:33 262 -> /u02/oradata/ORCL/temp01.dbf lr-x------ 1 ora11g oradba 64 Feb 10 15:33 3 -> /dev/null lr-x------ 1 ora11g oradba 64 Feb 10 15:33 4 -> /dev/null lrwx------ 1 ora11g oradba 64 Feb 10 15:33 5 -> /u01/app/ora11g/product/11.2.0.2/db_1/dbs/hc_ORA11G.dat lr-x------ 1 ora11g oradba 64 Feb 10 15:33 6 -> /dev/null lr-x------ 1 ora11g oradba 64 Feb 10 15:33 7 -> /dev/null lr-x------ 1 ora11g oradba 64 Feb 10 15:33 8 -> /dev/null lr-x------ 1 ora11g oradba 64 Feb 10 15:33 9 -> /dev/null

      可以看到 258 对应的文件是在闪烁的，并且有一个 (deleted) 标记符，先将这个文件复制到另外的目录：

1 2	[ora11g@test06 fd]$ cat 258 > /home/ora11g/system01.dbf [ora11g@test06 fd]$ cp /home/ora11g/system01.dbf /u02/oradata/ORCL/system01.dbf

1 2	SQL> connect scott/tiger Connected.

      貌似没问题了，重启一下数据库看看：

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

SQL> connect /  as sysdba Connected. SQL> SQL> SQL> shutdown immediate ; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ; ORACLE instance started.   Total System Global Area  534462464 bytes Fixed Size                  2228200 bytes Variable Size             394264600 bytes Database Buffers          130023424 bytes Redo Buffers                7946240 bytes Database mounted. ORA-01113: file 1 needs media recovery ORA-01110: data file 1: '/u02/oradata/ORCL/system01.dbf' SQL>  select OPEN_MODE from v$database;   OPEN_MODE -------------------- MOUNTED

      这个问题不大，system01.dbf相当于没有alter database begin backup那样进行了热备，recover 一下就好：

1 2 3 4 5 6 7 8 9 10 11

SQL> recover database ;  Media recovery complete. SQL> alter database open ;   Database altered.   SQL>  select OPEN_MODE from v$database;     OPEN_MODE -------------------- READ WRITE

      搞定。

     PS:添加自己的总结：

     如果是生产库，没有办法重启的情况下如何进行数据恢复操作。（实际上就是更新一下数据文件头部的SCN号）

     1. 首先需要将需要恢复的数据文件offline,
  alter database datafile file#(数据文件号) offline;
     2. 执行在线恢复并且online该数据文件。
    recover datafile file#
    alter database datafile file# online;
     3. 进行验证
    create table test tablespace 表空间(所在的数据文件)