誤刪除前,歸檔日志2個,用戶數(shù)據(jù)4480064條
[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個
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、刪除所有的歸檔日志,其中一個歸檔是新插入了200w記錄產(chǎn)生的歸檔
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
SQL> select count(*) from test;
COUNT(*)
----------
6480064
5、刪除users表空間下的所有數(shù)據(jù)文件
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)建表,報錯了
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
[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)程會打開所有數(shù)據(jù)文件的句柄。在/proc目錄中可以查到,目錄名是進(jìn)程PID,fd表示文件描述符
[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該句柄文件名回原位置
[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ù)文件
[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ù)
SQL> conn hr/hr
Connected.
SQL> select count(*) from test;
COUNT(*)
----------
6480064
恢復(fù)的原理是,在Linux操作系統(tǒng)中,如果文件從操作系統(tǒng)級別被rm掉,之前打開該文件的進(jìn)程仍然持有相應(yīng)的文件句柄,所指向的文件仍然可以讀寫,并且該文件的文件描述符可以從/proc目錄中獲得。但是要注意的是,此時如果關(guān)閉數(shù)據(jù)庫,則此句柄會消失,那么除了掃描磁盤進(jìn)行文件恢復(fù)之外就沒有其它方法了,因此在數(shù)據(jù)庫出現(xiàn)問題的時候,如果不確認(rèn)情況的復(fù)雜程度,千萬不要隨便關(guān)閉數(shù)據(jù)庫。重啟數(shù)據(jù)庫往往是沒有意義的,甚至是致命的,
另外,若控制文件被rm了,通過這種方式是無法恢復(fù)的,
同時,通過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