[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$的脚本处理行迁移也许有问题.