歡迎來到Linux教程網
Linux教程網
Linux教程網
Linux教程網
您现在的位置: Linux教程網 >> UnixLinux >  >> Linux基礎 >> Linux技術

Linux中誤刪除數據文件和歸檔日志的恢復方法

誤刪除前,歸檔日志2個,用戶數據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、用戶繼續插入200w條數據,歸檔日志最終是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記錄產生的歸檔

復制代碼代碼如下:
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的數據量是6480064

復制代碼代碼如下:
SQL> select count(*) from test;
COUNT(*)
----------
6480064

5、刪除users表空間下的所有數據文件

復制代碼代碼如下:
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表空間創建表,報錯了

復制代碼代碼如下:
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進程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進程會打開所有數據文件的句柄。在/proc目錄中可以查到,目錄名是進程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) "字樣,表示該文件已經被刪除

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、恢復數據文件

復制代碼代碼如下:
[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、數據完全恢復

復制代碼代碼如下:
SQL> conn hr/hr
Connected.
SQL> select count(*) from test;
COUNT(*)
----------
6480064

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

參考蓋國強的鏈接: http://www.eygle.com/archives/2011/12/oracle_linux_rm_rf.html 51cto博文索引,共同進步 http://lhf507.blog.51cto.com/5489375/1112400
Copyright © Linux教程網 All Rights Reserved