[20210930]bbed恢復刪除的資料.txt
[20210930]bbed恢復刪除的資料.txt
--//以前寫了使用讀取資料塊資料,快放假了沒事,做一個delete後,使用bbed恢復刪除資料的指令碼.
--//首先即使delete後有許多方法恢復,利用as of timestamp來查詢恢復,選擇bbed來恢復應該放在最後選項.
--//另外有朋友反映bbed讀取指令碼資料塊很慢,主要問題在於可能掃描快很多以及bbed呼叫次數比較多,我嘗試改寫減少bbed呼叫次數.
$ cat ffd.sh
#! /bin/bash -x
# argv1=file_number argv2=begin_block argvs3=end_block argv4=data_object_id argv5=bbed /x format
file_number=$1
begin_block=$2
end_block=$3
data_object_id=$4
ff="/r"${5}
# scan begin_block to end_block,define Scope.
/bin/rm scan1.txt scan_tmp1.txt 2>/dev/null
seq -f "%-1.0f" $begin_block $end_block | xargs -IQ echo -e "host echo $file_number,Q \np /d dba $file_number,Q ktbbh.ktbbhsid.ktbbhod1" | \
rlbbed | grep "^BBED" | egrep "$file_number,|ub4 ktbbhod1" | awk '{print $NF}' | paste -d" " - - | awk -v a=$data_object_id '$2==a {print $1}' >| scan1.txt
# scan2 kdbr and if find KDRHFD then assign offset flag of value - 0x10.
cat scan1.txt |while read dba
do
#echo $dba
#kdbr_size=$(echo map dba $dba | rlbbed | grep "sb2 kdbr" | sed -e "s/^.*\[//;s/].*$//")
kdbr_size=$(echo p dba $dba kdbt[0].kdbtnrow | rlbbed| grep kdbtnrow | awk '{print $NF}')
#echo $kdbr_size
begin=0
end=$[ kdbr_size -1 ]
#echo $begin $end
#echo set dba $dba
echo p dba $dba offset 0 kdbr | rlbbed | grep "sb2 kdbr" | sed "1s/^BBED> //" | awk -F"[][ ]+" -v a=$kdbr_size '$NF > a {print $3}' | \
sed "s+^+x $ff dba $dba *kdbr[+;s+$+]+"| rlbbed | grep '^flag@' | grep KDRHFD | awk -F"[@: ]+" -v dba=$dba '{print "assign /d dba",dba,"offset",$2,"=",strtonum($3)-16}'
echo sum apply dba $dba
done
--//簡單說明： seq -f "%-1.0f" $begin_block $end_block 主要因為一些版本seq 在大於1e6時顯示科學記數問題.
$ seq 4000000 4000001
4e+006
4e+006
$ seq -f "%-1.0f" 4000000 4000001
4000000
4000001
--//scan begin_block to end_block,define Scope.呼叫bbed僅僅1次.
--//資料塊掃描 呼叫bbed每塊3次.
--//簡單測試看看.
1.環境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.測試:
SCOTT@book> create table t as select * from all_objects where rownum<=1000;
Table created.
SCOTT@book> select object_id,data_object_id from dba_objects where owner=user and object_name='T';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
93766 93766
SCOTT@book> select rowid from t where rownum=1;
ROWID
------------------
AAAW5GAAEAAAAILAAA
SCOTT@book> @ rowid AAAW5GAAEAAAAILAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
93766 4 523 0 0x100020B 4,523 alter system dump datafile 4 block 523 ;
SCOTT@book> create table tx tablespace tea as select * from t;
Table created.
SCOTT@book> delete from t where mod(object_id,100)=0;
9 rows deleted.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system checkpoint ;
System altered.
--//注意髒塊一定要寫盤,不然讀取錯誤.
$ . ffd.sh 4 523 600 93766 x
sum apply dba 4,523
assign /d dba 4,524 offset 7301 = 44
sum apply dba 4,524
sum apply dba 4,525
assign /d dba 4,526 offset 6170 = 44
sum apply dba 4,526
assign /d dba 4,527 offset 4782 = 44
sum apply dba 4,527
assign /d dba 4,528 offset 4153 = 44
sum apply dba 4,528
assign /d dba 4,529 offset 4119 = 44
sum apply dba 4,529
assign /d dba 4,530 offset 3602 = 44
sum apply dba 4,530
assign /d dba 4,531 offset 1786 = 44
sum apply dba 4,531
sum apply dba 4,532
assign /d dba 4,533 offset 2936 = 44
sum apply dba 4,533
assign /d dba 4,534 offset 1814 = 44
sum apply dba 4,534
sum apply dba 4,535
--//44 = 0x2c,我使用十進位制應該也沒有什麼問題.共9個assign,與前面能夠對上.
BBED> set dba 4,524
DBA 0x0100020c (16777740 4,524)
BBED> x /rcccnncttcccccnc *kdbr[10]
rowdata[6149] @7301
-------------
flag@7301: 0x3c (KDRHFL, KDRHFF, KDRHFD, KDRHFH)
lock@7302: 0x02
cols@7303: 0
--//可以對上.
SCOTT@book> delete from t where mod(object_id,99)=0;
7 rows deleted.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system checkpoint ;
System altered.
$ . ffd.sh 4 523 600 93766 x | grep assign|wc
16 128 592
--//開始嘗試恢復.
$ . ffd.sh 4 523 600 93766 x >| aaa.txt
--//編輯aaa.txt指令碼在第三行加入,不然透過管道執行會報錯.
$ head -5 aaa.txt
sum apply dba 4,523
assign /d dba 4,524 offset 7381 = 44
Y
assign /d dba 4,524 offset 7301 = 44
sum apply dba 4,524
$ cat aaa.txt | rlbbed
SCOTT@book> select * from t minus select * from tx;
no rows selected
SCOTT@book> select * from tx minus select * from t;
no rows selected
--//OK,說明完全恢復。
3.增加一點點難度看看:
SCOTT@book> drop table t purge;
Table dropped.
SCOTT@book> create table t as select * from all_objects where rownum<=1000;
Table created.
SCOTT@book> select object_id,data_object_id from dba_objects where owner=user and object_name='T';
OBJECT_ID DATA_OBJECT_ID
---------- --------------
93770 93770
SCOTT@book> select rowid from t where rownum=1;
ROWID
------------------
AAAW5JAAEAAAAILAAA
SCOTT@book> @ rowid AAAW5JAAEAAAAILAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
93770 4 523 0 0x100020B 4,523 alter system dump datafile 4 block 523 ;
SCOTT@book> update t set owner=lpad('A',30,'A') where DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid)=524;
84 rows updated.
SCOTT@book> commit ;
Commit complete.
--//這樣會發生行遷移現象.
SCOTT@book> delete from t where DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid)=524;
84 rows deleted.
SCOTT@book> commit ;
Commit complete.
--//我僅僅刪除該塊dba=4,524的全部記錄.
SCOTT@book> alter system checkpoint ;
System altered.
$ . ffd.sh 4 523 600 93770 x
sum apply dba 4,523
assign /d dba 4,524 offset 8082 = 44
assign /d dba 4,524 offset 7976 = 44
assign /d dba 4,524 offset 7870 = 44
assign /d dba 4,524 offset 7762 = 44
assign /d dba 4,524 offset 7654 = 44
assign /d dba 4,524 offset 7547 = 44
assign /d dba 4,524 offset 7438 = 44
assign /d dba 4,524 offset 7323 = 44
assign /d dba 4,524 offset 7218 = 44
assign /d dba 4,524 offset 7111 = 44
assign /d dba 4,524 offset 7004 = 44
assign /d dba 4,524 offset 6893 = 44
assign /d dba 4,524 offset 6775 = 44
assign /d dba 4,524 offset 6666 = 44
assign /d dba 4,524 offset 6554 = 44
assign /d dba 4,524 offset 6446 = 44
assign /d dba 4,524 offset 6332 = 44
assign /d dba 4,524 offset 6218 = 44
assign /d dba 4,524 offset 6108 = 44
assign /d dba 4,524 offset 5999 = 44
assign /d dba 4,524 offset 5888 = 44
assign /d dba 4,524 offset 5777 = 44
assign /d dba 4,524 offset 5669 = 44
assign /d dba 4,524 offset 5554 = 44
assign /d dba 4,524 offset 5449 = 44
assign /d dba 4,524 offset 5345 = 44
assign /d dba 4,524 offset 5236 = 44
assign /d dba 4,524 offset 5127 = 44
assign /d dba 4,524 offset 5018 = 44
assign /d dba 4,524 offset 4907 = 44
assign /d dba 4,524 offset 4796 = 44
assign /d dba 4,524 offset 4787 = 32
assign /d dba 4,524 offset 4678 = 44
assign /d dba 4,524 offset 4574 = 44
assign /d dba 4,524 offset 4565 = 32
assign /d dba 4,524 offset 4459 = 44
assign /d dba 4,524 offset 4352 = 44
assign /d dba 4,524 offset 4243 = 44
assign /d dba 4,524 offset 4234 = 32
assign /d dba 4,524 offset 4118 = 44
assign /d dba 4,524 offset 4008 = 44
assign /d dba 4,524 offset 3902 = 44
assign /d dba 4,524 offset 3893 = 32
assign /d dba 4,524 offset 3788 = 44
assign /d dba 4,524 offset 3680 = 44
assign /d dba 4,524 offset 3573 = 44
assign /d dba 4,524 offset 3564 = 32
assign /d dba 4,524 offset 3458 = 44
assign /d dba 4,524 offset 3346 = 44
assign /d dba 4,524 offset 3337 = 32
assign /d dba 4,524 offset 3222 = 44
assign /d dba 4,524 offset 3104 = 44
assign /d dba 4,524 offset 2992 = 44
assign /d dba 4,524 offset 2983 = 32
assign /d dba 4,524 offset 2873 = 44
assign /d dba 4,524 offset 2760 = 44
assign /d dba 4,524 offset 2636 = 44
assign /d dba 4,524 offset 2627 = 32
assign /d dba 4,524 offset 2517 = 44
assign /d dba 4,524 offset 2406 = 44
assign /d dba 4,524 offset 2295 = 44
assign /d dba 4,524 offset 2286 = 32
assign /d dba 4,524 offset 2169 = 44
assign /d dba 4,524 offset 2052 = 44
assign /d dba 4,524 offset 1943 = 44
assign /d dba 4,524 offset 1934 = 32
assign /d dba 4,524 offset 1823 = 44
assign /d dba 4,524 offset 1712 = 44
assign /d dba 4,524 offset 1703 = 32
assign /d dba 4,524 offset 1589 = 44
assign /d dba 4,524 offset 1475 = 44
assign /d dba 4,524 offset 1361 = 44
assign /d dba 4,524 offset 1352 = 32
assign /d dba 4,524 offset 1238 = 44
assign /d dba 4,524 offset 1123 = 44
assign /d dba 4,524 offset 1008 = 44
assign /d dba 4,524 offset 999 = 32
assign /d dba 4,524 offset 884 = 44
assign /d dba 4,524 offset 774 = 44
assign /d dba 4,524 offset 661 = 44
assign /d dba 4,524 offset 652 = 32
assign /d dba 4,524 offset 542 = 44
assign /d dba 4,524 offset 430 = 44
assign /d dba 4,524 offset 315 = 44
sum apply dba 4,524
sum apply dba 4,525
sum apply dba 4,526
sum apply dba 4,527
sum apply dba 4,528
sum apply dba 4,529
sum apply dba 4,530
sum apply dba 4,531
sum apply dba 4,532
sum apply dba 4,533
sum apply dba 4,534
sum apply dba 4,535
$ cat aaa.txt | rlbbed
--//注意加入Y.
SCOTT@book> alter system flush buffer_cache;
System altered.
SCOTT@book> select count(*) from t where DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid)=524;
COUNT(*)
----------
70
--//很明顯出現行遷移漏掉了14條記錄.
$ grep "= 32$" aaa.txt
assign /d dba 4,524 offset 4787 = 32
assign /d dba 4,524 offset 4565 = 32
assign /d dba 4,524 offset 4234 = 32
assign /d dba 4,524 offset 3893 = 32
assign /d dba 4,524 offset 3564 = 32
assign /d dba 4,524 offset 3337 = 32
assign /d dba 4,524 offset 2983 = 32
assign /d dba 4,524 offset 2627 = 32
assign /d dba 4,524 offset 2286 = 32
assign /d dba 4,524 offset 1934 = 32
assign /d dba 4,524 offset 1703 = 32
assign /d dba 4,524 offset 1352 = 32
assign /d dba 4,524 offset 999 = 32
assign /d dba 4,524 offset 652 = 32
--//注意修改標識是0x20=32.
BBED> x /rcccnncttcccccnc offset 4787
rowdata[4472] @4787
-------------
flag@4787: 0x20 (KDRHFH)
lock@4788: 0x03
cols@4789: 0
nrid@4790:0x010002e1.0
--//010002e1 = set dba 4,737 = alter system dump datafile 4 block 737 = 16777953
--//噢,跑到dba = 4,737位置,我掃描範圍不夠大.重來看看.
$ . ffd.sh 4 523 740 93770 x
sum apply dba 4,523
sum apply dba 4,524
sum apply dba 4,525
sum apply dba 4,526
sum apply dba 4,527
sum apply dba 4,528
sum apply dba 4,529
sum apply dba 4,530
sum apply dba 4,531
sum apply dba 4,532
sum apply dba 4,533
sum apply dba 4,534
sum apply dba 4,535
assign /d dba 4,737 offset 8076 = 12
assign /d dba 4,737 offset 7964 = 12
assign /d dba 4,737 offset 7844 = 12
assign /d dba 4,737 offset 7724 = 12
assign /d dba 4,737 offset 7613 = 12
assign /d dba 4,737 offset 7491 = 12
assign /d dba 4,737 offset 7376 = 12
assign /d dba 4,737 offset 7262 = 12
assign /d dba 4,737 offset 7142 = 12
assign /d dba 4,737 offset 7024 = 12
assign /d dba 4,737 offset 6904 = 12
assign /d dba 4,737 offset 6784 = 12
assign /d dba 4,737 offset 6663 = 12
assign /d dba 4,737 offset 6550 = 12
sum apply dba 4,737
sum apply dba 4,738
sum apply dba 4,739
sum apply dba 4,740
--//正好14行.
$ cat aaa.txt | rlbbed
--//注意加入Y.
SCOTT@book> alter system flush buffer_cache;
System altered.
SCOTT@book> select count(*) from t where DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid)=524;
COUNT(*)
----------
84
--//ok恢復成功.
SCOTT@book> select object_id from t minus select object_id from tx;
no rows selected
SCOTT@book> select object_id from tx minus select object_id from t;
no rows selected
4.改進僅僅grep KDRHFD 才做sum apply 操作,並且修改採用16進位制,感覺這樣好看一點點.
$ cat ffd.sh
#! /bin/bash -x
# argv1=file_number argv2=begin_block argvs3=end_block argv4=data_object_id argv5=bbed /x format
file_number=$1
begin_block=$2
end_block=$3
data_object_id=$4
ff="/r"${5}
# scan begin_block to end_block,define Scope.
/bin/rm scan1.txt scan_tmp1.txt 2>/dev/null
seq -f "%-1.0f" $begin_block $end_block | xargs -IQ echo -e "host echo $file_number,Q \np /d dba $file_number,Q ktbbh.ktbbhsid.ktbbhod1" | \
rlbbed | grep "^BBED" | egrep "$file_number,|ub4 ktbbhod1" | awk '{print $NF}' | paste -d" " - - | awk -v a=$data_object_id '$2==a {print $1}' >| scan1.txt
# scan2 kdbr and if find KDRHFD then assign offset flag of value - 0x10.
cat scan1.txt |while read dba
do
#echo $dba
#kdbr_size=$(echo map dba $dba | rlbbed | grep "sb2 kdbr" | sed -e "s/^.*\[//;s/].*$//")
kdbr_size=$(echo p dba $dba kdbt[0].kdbtnrow | rlbbed| grep kdbtnrow | awk '{print $NF}')
#echo $kdbr_size
begin=0
end=$[ kdbr_size -1 ]
#echo $begin $end
#echo set dba $dba
echo p dba $dba offset 0 kdbr | rlbbed | grep "sb2 kdbr" | sed "1s/^BBED> //" | awk -F"[][ ]+" -v a=$kdbr_size '$NF > a {print $3}' | \
# sed "s+^+x $ff dba $dba *kdbr[+;s+$+]+"| rlbbed | grep '^flag@' | grep KDRHFD | awk -F"[@: ]+" -v dba=$dba '{print "assign /d dba",dba,"offset",$2,"=",strtonum($3)-16}'
sed "s+^+x $ff dba $dba *kdbr[+;s+$+]+"| rlbbed | grep '^flag@' | grep KDRHFD | awk -F"[@: ]+" -v dba=$dba '{printf "assign dba %s offset %d = 0x%x\nsum apply dba %s\n",dba,$2,strtonum($3)-16,dba}'
# echo sum apply dba $dba
done
--//重複前面的操作。
$ . ffd.sh 4 523 740 93772 x
assign dba 4,524 offset 7301 = 0x2c
sum apply dba 4,524
assign dba 4,526 offset 6170 = 0x2c
sum apply dba 4,526
assign dba 4,527 offset 4782 = 0x2c
sum apply dba 4,527
assign dba 4,528 offset 4153 = 0x2c
sum apply dba 4,528
assign dba 4,529 offset 4119 = 0x2c
sum apply dba 4,529
assign dba 4,530 offset 3602 = 0x2c
sum apply dba 4,530
assign dba 4,531 offset 1786 = 0x2c
sum apply dba 4,531
assign dba 4,533 offset 2936 = 0x2c
sum apply dba 4,533
assign dba 4,534 offset 1814 = 0x2c
sum apply dba 4,534
--//OK這樣好看多了.看來以前我刪除tab$的指令碼處理行遷移也許有問題.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2795028/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【BBED】使用bbed恢復已經刪除的行資料
- [20190213]學習bbed-恢復刪除的資料.txt
- [20121009]學習bbed-恢復刪除的資料.txt
- 實驗1：利用BBED工具恢復刪除的資料行
- oracle恢復誤刪除資料Oracle
- 【北亞資料恢復】誤刪除oracle表和誤刪除oracle表資料的資料恢復方法資料恢復Oracle
- PostgreSQL中刪除的資料能否恢復SQL
- Oracle閃回刪除恢復誤刪資料Oracle
- Sybase ASE資料庫恢復，Sybase資料恢復，資料誤刪除恢復工具READSYBDEVICE資料庫資料恢復dev
- [20210930]bbed讀取資料塊7 fffext.sh.txt
- Oracle恢復誤刪除的資料檔案Oracle
- truncate table 誤刪除資料後的恢復
- [20170213]刪除資料沒有備份可以恢復嗎.txt
- Flashback Query恢復誤刪除資料(轉）
- RM 刪除資料檔案恢復操作
- 恢復Oracle資料庫誤刪除資料的語句Oracle資料庫
- SQL Server資料庫恢復，SQL Server資料恢復，SQL Server資料誤刪除恢復工具SQLRescueSQLServer資料庫資料恢復
- 誤刪除資料了怎麼辦？小編交易誤刪除資料的恢復方法
- 【儲存資料恢復】HP EVA儲存誤刪除VDISK的資料恢復案例資料恢復
- 【儲存資料恢復】NetApp儲存誤刪除的資料恢復案例資料恢復APP
- 【伺服器資料恢復】LINUX誤刪除、格式化的資料恢復伺服器資料恢復Linux
- 【伺服器資料恢復】EMC Unity儲存誤刪除的資料恢復案例伺服器資料恢復Unity
- 【儲存資料恢復案例】Netapp誤操作刪除lun的資料恢復資料恢復APP
- 利用rman全備恢復刪除的資料庫資料庫
- Oracle資料恢復 - Linux / Unix 誤刪除的檔案恢復(轉）Oracle資料恢復Linux
- bbed_recover:恢復資料塊資料庫資料庫
- Sybase SQL Anywhere（ASA）資料庫恢復，ASA資料恢復，資料誤刪除恢復工具ReadASADBSQL資料庫資料恢復
- 誤刪除儲存SqlServer資料庫資料恢復SQLServer資料庫資料恢復
- 【資料庫資料恢復】LINUX環境下ORACLE資料庫誤刪除的資料恢復資料庫資料恢復LinuxOracle
- 【oracle資料庫資料恢復】誤操作導致的資料庫誤刪除的資料恢復案例Oracle資料庫資料恢復
- 【伺服器資料恢復】伺服器誤刪除lun如何恢復資料？伺服器資料恢復
- MySQL資料庫表誤刪除恢復(一)MySql資料庫
- 恢復rm -f物理刪除資料檔案
- 恢復被rm意外刪除資料檔案
- 伺服器資料恢復-LINUX誤刪除/格式化資料的資料恢復方案伺服器資料恢復Linux
- 【NetApp資料恢復案例】針對NetApp誤刪除資料的恢復APP資料恢復
- 【伺服器資料恢復】EMC Isilon儲存誤刪除的資料恢復案例伺服器資料恢復
- 伺服器資料恢復—NTFS誤操作刪除/格式化的資料恢復案例伺服器資料恢復