利用Linux的特性恢復(fù)誤刪的數(shù)據(jù)文件

Oracle on Linux的環(huán)境，在沒有shutdown數(shù)據(jù)庫的情況下誤刪數(shù)據(jù)文件system01.dbf。對于有完好rman備份或者Data Guard的情況下，神馬都是浮云，但古語有云“屋漏偏逢連夜雨，船遲又遇打頭風(fēng)”，禍不單行的事情海了去，對于nobackup有什么好辦法呢？

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

      帖子中提到如下場景：Oracle on Linux的環(huán)境，在沒有shutdown數(shù)據(jù)庫的情況下誤刪數(shù)據(jù)文件system01.dbf。對于有完好rman備份或者Data Guard的情況下，神馬都是浮云，但古語有云“屋漏偏逢連夜雨，船遲又遇打頭風(fēng)”，禍不單行的事情海了去，對于nobackup有什么好辦法呢？

      少說廢話，直接上處理過程：

      首先是模擬出這個(gè)囧境：

[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.

      數(shù)據(jù)文件system01.dbf讓俺“誤”刪了，有后悔藥可以吃嗎？

      先找出 db writer 進(jìn)程，該進(jìn)程肯定會(huì)寫數(shù)據(jù)文件的，也可以通過lsof命令找出打開system01.dbf的所有進(jìn)程。

[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

      進(jìn)程號是 24867

[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 對應(yīng)的文件是在閃爍的，并且有一個(gè) (deleted) 標(biāo)記符，先將這個(gè)文件復(fù)制到另外的目錄：

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

SQL> connect scott/tiger
Connected.

      貌似沒問題了，重啟一下數(shù)據(jù)庫看看：

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

      這個(gè)問題不大，system01.dbf相當(dāng)于沒有alter database begin backup那樣進(jìn)行了熱備，recover 一下就好：

SQL> recover database ;  
Media recovery complete.
SQL> alter database open ;

Database altered.

SQL>  select OPEN_MODE from v$database;   

OPEN_MODE
--------------------
READ WRITE

      搞定。