[20190226]删除tab$记录的恢复6.txt

[20190226]删除tab$记录的恢复6.txt

--//春节前几天做了删除tan$记录的测试,链接:
http://blog.itpub.net/267265/viewspace-2565245/=> [20190130]删除tab$记录的恢复.txt
http://blog.itpub.net/267265/viewspace-2565250/=> [20190130]删除tab$记录的恢复2.txt

--//我这样恢复后,仅仅能读,不能建表等操作,必须解决bbed verify报6110,6111,6112之类的错误问题.链接:
http://blog.itpub.net/267265/viewspace-2629866/=> [20190212]删除tab$记录的恢复3.txt

--//当时这些恢复,我tab$的索引i_tab1是禁用的.今天尝试恢复索引看看.
--//做了这个测试:链接https://www.cnblogs.com/lfree/p/10438177.html

1.环境:
sys@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

sys@book> select rowid,a.* from sys.bootstrap$ a where a.sql_text like '%i_tab1%';

rowid                   line#       obj# sql_text
------------------ ---------- ---------- ------------------------------------------------------------
aaaaa7aabaaaailaaj         33         33 create index i_tab1 on tab$(bobj#) pctfree 10 initrans 2 max
                                         trans 255 storage (  initial 64k next 1024k minextents 1 max
                                         extents 2147483645 pctincrease 0 objno 33 extents (file 1 bl
                                         ock 312))
sys@book> select count(*) from tab$ where bobj# is not null ;
  count(*)
----------
       139

--//可以发现索引的键值仅仅139个,也就是索引很小.可以尝试使用bbed解决这个问题.
--//索引段头在dba =1,312,这样索引root节点在dba =1,313.

2.先观察没有删除的情况:
bbed> info all
 file#  name                                                  size(blks)
 -----  ----                                                  ----------
     1  /mnt/ramdisk/book/system01.dbf                                 0
   101  /home/oracle/backup/book_20190122_good/system01.dbf            0
   201  /home/oracle/backup/book_20190122_bad/system01.dbf             0

bbed> set dba 1,313
        dba             0x00400139 (4194617 1,313)

bbed> p kd_off
sb2 kd_off[0]                               @124      8032
sb2 kd_off[1]                               @126      0
sb2 kd_off[2]                               @128      8020
sb2 kd_off[3]                               @130      7996
sb2 kd_off[4]                               @132      7972
sb2 kd_off[5]                               @134      7960
sb2 kd_off[6]                               @136      7900
sb2 kd_off[7]                               @138      7864
sb2 kd_off[8]                               @140      7852
sb2 kd_off[9]                               @142      7840
sb2 kd_off[10]                              @144      7828
sb2 kd_off[11]                              @146      7816
...
sb2 kd_off[134]                             @392      6260
sb2 kd_off[135]                             @394      6218
sb2 kd_off[136]                             @396      6232
sb2 kd_off[137]                             @398      6190
sb2 kd_off[138]                             @400      6204

--//正好139个行目录.在一个块里面修复相对简单.
--//注:bbed看索引块存在问题,实际上kd_off[0], kd_off[1]记录的偏移量不对.实际上从kd_off[2]开始.

bbed> x /rnx dba 1,313 *kd_off[2]
rowdata[2002]                               @8112
-------------
flag@8112:     0x00 (none)
lock@8113:     0x00
data key:
col    0[2] @8115: 2
col    1[6] @8118:  0x00  0x40  0x00  0x91  0x00  0x00


bbed> x /rnx dba 201,313 *kd_off[2]
rowdata[2002]                               @8112
-------------
flag@8112:     0x01 (kdxrdel)
lock@8113:     0x02
data key:
col    0[2] @8115: 2
col    1[6] @8118:  0x00  0x40  0x00  0x91  0x00  0x00

--//注:201是删除tab$的对应的数据文件.可以发现删除键值的flag=0x01.
--//这样恢复就简单了.但是注意这样遗漏2条索引键值记录.

$ seq 2 138 | xargs -i{} echo 'x /rnx dba 201,313 *kd_off[{}]' | rlbbed | grep -b1 "^lock@.*:*0x02$"  | grep "^flag@.*:.*0x01"|cut -d: -f1 | cut -d@ -f2 | xargs -i{} echo assign dba 1,313 offset {}=0x00 > index_bbed.txt
--//grep 过滤lock的相关信息你的测试可能与我的不同(可能使用不同itl槽处理事务),根据实际需要修改.
--//注意:我写的脚本坏文件号为201.

$ head -3 index_bbed.txt
assign dba 1,313 offset 8112=0x00
assign dba 1,313 offset 8088=0x00
assign dba 1,313 offset 8064=0x00

$ tail -3 index_bbed.txt
assign dba 1,313 offset 6324=0x00
assign dba 1,313 offset 6282=0x00
assign dba 1,313 offset 6296=0x00

$ wc index_bbed.txt
 137  685 4658 index_bbed.txt

--//137条,还差2条.

bbed> p kd_off[138]
sb2 kd_off[138]                             @400      6204

bbed> dump  offset 402 count 4
 file: /mnt/ramdisk/book/system01.dbf (1)
 block: 313      offsets:  402 to  405    dba:0x00400139
---------------------------------------------------------
 94178617
<64 bytes per line>

--//说明: kd_off[138]的偏移量记录在偏移400,下面2条记录的偏移记录从402算起.
--// 9417 颠倒过来就是  0x1794=6036, 记录相对偏移从kdxle算起(当前是92,通过map命令可以确定kdxle的偏移), 6036+92 = 6128.
--// 8617 颠倒过来就是  0x1786=6022, 记录相对偏移从kdxle算起(当前是92,通过map命令可以确定kdxle的偏移), 6022+92 = 6114.

bbed> x /rnx dba 201,313 offset 6128
rowdata[18]                                 @6128
-----------
flag@6128:     0x01 (kdxrdel)
lock@6129:     0x02
data key:
col    0[4] @6131: 90426
col    1[6] @6136:  0x00  0x41  0x70  0x1e  0x00  0x01


bbed> x /rnx dba 201,313 offset 6114
rowdata[4]                                  @6114
----------
flag@6114:     0x01 (kdxrdel)
lock@6115:     0x02
data key:
col    0[4] @6117: 90426
col    1[6] @6122:  0x00  0x41  0x70  0x1e  0x00  0x02

--//追加如下2行到index_bbed.txt
echo assign dba 1,313 offset 6128=0x00 >> index_bbed.txt
echo assign dba 1,313 offset 6114=0x00 >> index_bbed.txt

3.开始尝试索引恢复:
--//先关闭数据库.恢复坏的数据库.

$ /bin/cp -r /home/oracle/backup/book_20190122_bad/* /mnt/ramdisk/book
*/

--//执行前面测试生成的脚本:
bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/hrp430/bbed/scan3_bbed.txt
bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/hrp430/bbed/scan4k_bbed.txt
bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/hrp430/bbed/clearout_bbed.txt
bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/hrp430/bbed/notclearout_bbed.txt
bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/hrp430/bbed/scan5_bbed.txt

--//修复索引标识标本.
bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/hrp430/bbed/index_bbed.txt

--//以下是修复一些错误.

bbed> set dba 1,313
        dba             0x00400139 (4194617 1,313)

bbed> sum apply
warning: contents of previous bifile will be lost. proceed? (y/n) y
check value for file 1, block 313:
current = 0x9b89, required = 0x9b89

bbed> verify
dbverify - verification starting
file = /mnt/ramdisk/book/system01.dbf
block = 313

block checking: dba = 4194617, block type = ktb-managed data block
**** actual free space = 3700 < kdxcoavs = 5848
**** actual rows marked deleted = 0 != kdxlende = 139
---- end index block validation
block 313 failed with check code 6401

bbed> p kdxle.kdxlende
sb2 kdxlende                                @110      139

bbed> assign  kdxle.kdxlende =0
sb2 kdxlende                                @110      0

bbed> sum apply
check value for file 1, block 313:
current = 0x9b02, required = 0x9b02

bbed> verify
dbverify - verification starting
file = /mnt/ramdisk/book/system01.dbf
block = 313

block checking: dba = 4194617, block type = ktb-managed data block
**** actual free space = 3700 < kdxcoavs = 5848
---- end index block validation
block 313 failed with check code 6401

bbed> p kdxle.kdxlexco.kdxcoavs
sb2 kdxcoavs                                @106      5848

bbed> assign kdxle.kdxlexco.kdxcoavs= 3700
sb2 kdxcoavs                                @106      3700

bbed> sum apply
check value for file 1, block 313:
current = 0x83ae, required = 0x83ae

bbed> verify
dbverify - verification starting
file = /mnt/ramdisk/book/system01.dbf
block = 313
--//ok,现在修复了.

4.启动检查数据库情况:
sys@book> startup
oracle instance started.

total system global area  643084288 bytes
fixed size                  2255872 bytes
variable size             205521920 bytes
database buffers          427819008 bytes
redo buffers                7487488 bytes
database mounted.
database opened.

sys@book> select * from sys.tab$ minus select * from orachk001;
no rows selected

sys@book> select * from orachk001 minus select * from sys.tab$;
no rows selected

sys@book> analyze table sys.tab$ validate structure cascade;
table analyzed.

sys@book> select count(*) from sys.tab$ where bobj# is not null ;
  count(*)
----------
       139

sys@book> @ dpc '' ''
plan_table_output
-------------------------------------
sql_id  7nkaks3h1mmd2, child number 0
-------------------------------------
select count(*) from sys.tab$ where bobj# is not null
plan hash value: 3621218687
----------------------------------------------------------------------------
| id  | operation        | name   | e-rows |e-bytes| cost (%cpu)| e-time   |
----------------------------------------------------------------------------
|   0 | select statement |        |        |       |     1 (100)|          |
|   1 |  sort aggregate  |        |      1 |     2 |            |          |
|*  2 |   index full scan| i_tab1 |    139 |   278 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------
query block name / object alias (identified by operation id):
-------------------------------------------------------------
   1 - sel$1
   2 - sel$1 / tab$@sel$1
predicate information (identified by operation id):
---------------------------------------------------
   2 - filter("bobj#" is not null)

--//可以正常读取索引,到此删除tab$的恢复包括索引已经完美完成.累...........