Linux中誤刪除數(shù)據(jù)文件和歸檔日志的恢復(fù)方法

發(fā)布時(shí)間：2013-01-22 22:11:17 作者：佚名

Linux誤刪除數(shù)據(jù)文件和歸檔日志的恢復(fù)方法，需要的朋友可以參考下

誤刪除前，歸檔日志2個(gè)，用戶數(shù)據(jù)4480064條

復(fù)制代碼

代碼如下:

[oracle@station90 datafile]$ sqlplus /nolog
SQL> conn hr/hr
Connected.
SQL> host ls -l /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_17/*.arc
-rw-r----- 1 oracle oinstall 50048000 Jan 17 16:56 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_17/o1_mf_1_5_8hhh5hhp_.arc
-rw-r----- 1 oracle oinstall 50049536 Jan 17 16:56 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_17/o1_mf_1_6_8hhh5x0t_.arc
SQL> select count(*) from test;
COUNT(*)
----------
4480064

2、用戶繼續(xù)插入200w條數(shù)據(jù)，歸檔日志最終是3個(gè)

復(fù)制代碼

代碼如下:

SQL> insert into test select * from test where rownum<=2000000;
2000000 rows created.
SQL> commit;
Commit complete.
SQL> host ls -l /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_17/*.arc
-rw-r----- 1 oracle oinstall 50048000 Jan 17 16:56 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_17/o1_mf_1_5_8hhh5hhp_.arc
-rw-r----- 1 oracle oinstall 50049536 Jan 17 16:56 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_17/o1_mf_1_6_8hhh5x0t_.arc
-rw-r----- 1 oracle oinstall 50049024 Jan 17 17:08 /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_17/o1_mf_1_7_8hhhx0h7_.arc

3、刪除所有的歸檔日志，其中一個(gè)歸檔是新插入了200w記錄產(chǎn)生的歸檔

復(fù)制代碼

代碼如下:

SQL> host rm /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_17/*.arc
SQL> host ls -l /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_17/*.arc
ls: /u01/app/oracle/flash_recovery_area/ORCL/archivelog/2013_01_17/*.arc: No such file or directory

4、最終test的數(shù)據(jù)量是6480064

復(fù)制代碼

代碼如下:

SQL> select count(*) from test;
COUNT(*)
----------
6480064

5、刪除users表空間下的所有數(shù)據(jù)文件

復(fù)制代碼

代碼如下:

SQL> conn /as sysdba
Connected.
SQL> select file_name from dba_data_files where tablespace_name='USERS';
FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_5qd5zcsh_.dbf
/u01/app/oracle/oradata/ORCL/datafile/users01.dbf
SQL> host rm /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_5qd5zcsh_.dbf
SQL> host rm /u01/app/oracle/oradata/ORCL/datafile/users01.dbf

5、在Users表空間創(chuàng)建表，報(bào)錯(cuò)了

復(fù)制代碼

代碼如下:

SQL> conn hr/hr
Connected.
SQL> create table test33 (a number);
create table test33 (a number)
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4:
'/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_5qd5zcsh_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SQL> quit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

5、查看dbwr進(jìn)程PID

復(fù)制代碼

代碼如下:

[oracle@station90 ~]$ ps -ef | grep ora_dbw | grep -v grep
oracle 9878 1 0 16:20 ? 00:00:02 ora_dbw0_orcl

6、dbwr進(jìn)程會(huì)打開所有數(shù)據(jù)文件的句柄。在/proc目錄中可以查到，目錄名是進(jìn)程PID，fd表示文件描述符

復(fù)制代碼

代碼如下:

[oracle@station90 ~]$ cd /proc/9878/fd
[oracle@station90 fd]$ ls -l
total 0
lr-x------ 1 oracle oinstall 64 Jan 17 17:11 0 -> /dev/null
lr-x------ 1 oracle oinstall 64 Jan 17 17:11 1 -> /dev/null
lrwx------ 1 oracle oinstall 64 Jan 17 17:11 10 -> /u01/app/oracle/admin/orcl/adump/ora_9870.aud
lr-x------ 1 oracle oinstall 64 Jan 17 17:11 11 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Jan 17 17:11 12 -> /dev/zero
lr-x------ 1 oracle oinstall 64 Jan 17 17:11 13 -> /u01/app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 Jan 17 17:11 14 -> /u01/app/oracle/product/10.2.0/db_1/dbs/hc_orcl.dat
lrwx------ 1 oracle oinstall 64 Jan 17 17:11 15 -> /u01/app/oracle/product/10.2.0/db_1/dbs/lkORCL
lrwx------ 1 oracle oinstall 64 Jan 17 17:11 16 -> /u01/app/oracle/oradata/ORCL/controlfile/o1_mf_5qd60qj5_.ctl
lrwx------ 1 oracle oinstall 64 Jan 17 17:11 17 -> /u01/app/oracle/flash_recovery_area/ORCL/controlfile/o1_mf_5qd60qq6_.ctl
lrwx------ 1 oracle oinstall 64 Jan 17 17:11 18 -> /u01/app/oracle/oradata/ORCL/datafile/o1_mf_system_5qd5zcop_.dbf
lrwx------ 1 oracle oinstall 64 Jan 17 17:11 19 -> /u01/app/oracle/oradata/ORCL/datafile/o1_mf_undotbs1_5qd5zcrn_.dbf
lr-x------ 1 oracle oinstall 64 Jan 17 17:11 2 -> /dev/null
lrwx------ 1 oracle oinstall 64 Jan 17 17:11 20 -> /u01/app/oracle/oradata/ORCL/datafile/o1_mf_sysaux_5qd5zcot_.dbf
lrwx------ 1 oracle oinstall 64 Jan 17 17:11 21 -> socket:[138219]
lrwx------ 1 oracle oinstall 64 Jan 17 17:11 22 -> /u01/app/oracle/oradata/ORCL/datafile/o1_mf_example_5qd616wf_.dbf
lrwx------ 1 oracle oinstall 64 Jan 17 17:11 23 -> /u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_5qd614y5_.tmp
lr-x------ 1 oracle oinstall 64 Jan 17 17:11 24 -> /u01/app/oracle/product/10.2.0/db_1/rdbms/mesg/oraus.msb
lrwx------ 1 oracle oinstall 64 Jan 17 17:11 25 -> /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_5qd5zcsh_.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Jan 17 17:11 26 -> /u01/app/oracle/oradata/ORCL/datafile/users01.dbf (deleted)
lr-x------ 1 oracle oinstall 64 Jan 17 17:11 3 -> /dev/null
lr-x------ 1 oracle oinstall 64 Jan 17 17:11 4 -> /dev/null
l-wx------ 1 oracle oinstall 64 Jan 17 17:11 5 -> /u01/app/oracle/admin/orcl/udump/orcl_ora_9870.trc
l-wx------ 1 oracle oinstall 64 Jan 17 17:11 6 -> /u01/app/oracle/admin/orcl/bdump/alert_orcl.log
lrwx------ 1 oracle oinstall 64 Jan 17 17:11 7 -> /u01/app/oracle/product/10.2.0/db_1/dbs/lkinstorcl (deleted)
l-wx------ 1 oracle oinstall 64 Jan 17 17:11 8 -> /u01/app/oracle/admin/orcl/bdump/alert_orcl.log
lrwx------ 1 oracle oinstall 64 Jan 17 17:11 9 -> /u01/app/oracle/product/10.2.0/db_1/dbs/hc_orcl.dat
[oracle@station90 fd]$ ls -l | grep users
lrwx------ 1 oracle oinstall 64 Jan 17 17:11 25 -> /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_5qd5zcsh_.dbf (deleted)
lrwx------ 1 oracle oinstall 64 Jan 17 17:11 26 -> /u01/app/oracle/oradata/ORCL/datafile/users01.dbf (deleted)
注意其中"/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_5qd5zcsh_.dbf(deleted) "字樣，表示該文件已經(jīng)被刪除

7、直接cp該句柄文件名回原位置

復(fù)制代碼

代碼如下:

[oracle@station90fd]$cp25 /u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_5qd5zcsh_.dbf
[oracle@station90 fd]$ cp 26 /u01/app/oracle/oradata/ORCL/datafile/users01.dbf

8、恢復(fù)數(shù)據(jù)文件

復(fù)制代碼

代碼如下:

[oracle@station90 fd]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jan 17 17:12:27 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn /as sysdba
Connected.
SQL> select file_id,file_name from dba_data_files where tablespace_name='USERS';
FILE_ID
----------
FILE_NAME
--------------------------------------------------------------------------------
4
/u01/app/oracle/oradata/ORCL/datafile/o1_mf_users_5qd5zcsh_.dbf
6
/u01/app/oracle/oradata/ORCL/datafile/users01.dbf

SQL> alter database datafile 4 offline;
Database altered.
SQL> recover datafile 4;
Media recovery complete.
SQL> alter database datafile 4 online;
Database altered.
SQL> alter database datafile 6 offline;
Database altered.
SQL> recover datafile 6;
Media recovery complete.
SQL> alter database datafile 6 online;
Database altered.

9、數(shù)據(jù)完全恢復(fù)

復(fù)制代碼

代碼如下:

SQL> conn hr/hr
Connected.
SQL> select count(*) from test;
COUNT(*)
----------
6480064

恢復(fù)的原理是，在Linux操作系統(tǒng)中，如果文件從操作系統(tǒng)級(jí)別被rm掉，之前打開該文件的進(jìn)程仍然持有相應(yīng)的文件句柄，所指向的文件仍然可以讀寫，并且該文件的文件描述符可以從/proc目錄中獲得。但是要注意的是，此時(shí)如果關(guān)閉數(shù)據(jù)庫，則此句柄會(huì)消失，那么除了掃描磁盤進(jìn)行文件恢復(fù)之外就沒有其它方法了，因此在數(shù)據(jù)庫出現(xiàn)問題的時(shí)候，如果不確認(rèn)情況的復(fù)雜程度，千萬不要隨便關(guān)閉數(shù)據(jù)庫。重啟數(shù)據(jù)庫往往是沒有意義的，甚至是致命的，
另外，若控制文件被rm了，通過這種方式是無法恢復(fù)的,
同時(shí)，通過linux的rm刪除了數(shù)據(jù)文件，甚至連歸檔日志都全部刪除了，只要數(shù)據(jù)庫沒有崩潰，dbw0進(jìn)程還在，就可以將數(shù)據(jù)完全恢復(fù)，和是否有歸檔日志沒有關(guān)系，但是和是否開啟了歸檔是有區(qū)別的

參考蓋國強(qiáng)的鏈接：

http://www.eygle.com/archives/2011/12/oracle_linux_rm_rf.html

51cto博文索引，共同進(jìn)步

http://lhf507.blog.51cto.com/5489375/1112400