小小林熬夜学编程

这个屌丝很懒，什么也没留下！

热门标签

[20190130]删除tab$记录的恢复.txt_91p.vido.ws/index.php的新网址

作者：小小林熬夜学编程 | 2024-03-04 04:36:15

踩

91p.vido.ws/index.php的新网址

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

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

--//当时这些恢复,我tab$的索引i_tab1是禁用的.今天尝试恢复索引看看.

[20190225]删除tab$记录的恢复5.txt_ITPUB博客
--//做了这个测试:链接[20190226]测试使用bbed恢复索引.txt - lfree - 博客园

删除tab$记录的恢复.txt

--//网上提到许多删除tab$的案例,主要原因在于没有从官方正规渠道下载oracle版本,还有一些来自工具里面带有一些脚本删除tab$记录.

--//首先我并不知道许多人的恢复方法,仅仅简单提到恢复数据字典,我想到既然是删除,反向的操作就是恢复.也就是恢复tab$记录.
--//在我开始尝试时,我发现遇到的问题比原来想像的要复杂.tab$是CLUSTER C_OBJ#的一个表.本身cluster table的结果就与普通
--//堆表的结构不一样,还有可能遇到行链接和行迁移的情况.
--//这也是我最近许多帖子关于cluster table等方面的内容.
--//自己也尝试恢复看看.

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
 
SCOTT@book>column SQL_TEXT format a100
SCOTT@book> select * from (select * from SYS.BOOTSTRAP$ order by line#) where rownum<=5;
     LINE#       OBJ# SQL_TEXT
---------- ---------- ----------------------------------------------------------------------------------------------------
        -1         -1 8.0.0.0.0
         0          0 CREATE ROLLBACK SEGMENT SYSTEM STORAGE (  INITIAL 112K NEXT 56K MINEXTENTS 1 MAXEXTENTS 32765 OBJNO
                      0 EXTENTS (FILE 1 BLOCK 128))
 
         2          2 CREATE CLUSTER C_OBJ#("OBJ#" NUMBER) PCTFREE 5 PCTUSED 40 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL
                       136K NEXT 200K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 2 EXTENTS (FILE 1 BLOCK 144))
                       SIZE 800
 
         3          3 CREATE INDEX I_OBJ# ON CLUSTER C_OBJ# PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT
                       1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 3 EXTENTS (FILE 1 BLOCK 168))
 
         4          4 CREATE TABLE TAB$("OBJ#" NUMBER NOT NULL,"DATAOBJ#" NUMBER,"TS#" NUMBER NOT NULL,"FILE#" NUMBER NOT
                      NULL,"BLOCK#" NUMBER NOT NULL,"BOBJ#" NUMBER,"TAB#" NUMBER,"COLS" NUMBER NOT NULL,"CLUCOLS" NUMBER,"
                      PCTFREE$" NUMBER NOT NULL,"PCTUSED$" NUMBER NOT NULL,"INITRANS" NUMBER NOT NULL,"MAXTRANS" NUMBER NO
                      T NULL,"FLAGS" NUMBER NOT NULL,"AUDIT$" VARCHAR2(38) NOT NULL,"ROWCNT" NUMBER,"BLKCNT" NUMBER,"EMPCN
                      T" NUMBER,"AVGSPC" NUMBER,"CHNCNT" NUMBER,"AVGRLN" NUMBER,"AVGSPC_FLB" NUMBER,"FLBCNT" NUMBER,"ANALY
                      ZETIME" DATE,"SAMPLESIZE" NUMBER,"DEGREE" NUMBER,"INSTANCES" NUMBER,"INTCOLS" NUMBER NOT NULL,"KERNE
                      LCOLS" NUMBER NOT NULL,"PROPERTY" NUMBER NOT NULL,"TRIGFLAG" NUMBER,"SPARE1" NUMBER,"SPARE2" NUMBER,
                      "SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) STORAGE (  OBJNO 4 TA
                      BNO 1) CLUSTER C_OBJ#(OBJ#)
--//sys.tab$表是cluster table C_OBJ#下的一个表.OBJ#=2
 
SELECT ROWNUM -1 rn , a.*
  FROM (  SELECT *
            FROM dba_objects
           WHERE owner = 'SYS' AND data_object_id = 2
        ORDER BY object_id) a;
 
RN OWNER OBJECT_NAME SUBOBJECT_ OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED             LAST_DDL_TIME       TIMESTAMP           STATUS  T G S  NAMESPACE EDITION_NAME
-- ----- ----------- ---------- --------- -------------- ----------- ------------------- ------------------- ------------------- ------- - - - ---------- ------------
 0 SYS   C_OBJ#                         2              2 CLUSTER     2013-08-24 11:37:35 2013-08-24 11:37:35 2013-08-24:11:37:35 VALID   N N N          5
 1 SYS   TAB$                           4              2 TABLE       2013-08-24 11:37:35 2013-08-24 11:47:37 2013-08-24:11:37:35 VALID   N N N          1
 2 SYS   CLU$                           5              2 TABLE       2013-08-24 11:37:35 2013-08-24 11:37:35 2013-08-24:11:37:35 VALID   N N N          1
 3 SYS   IND$                          19              2 TABLE       2013-08-24 11:37:35 2013-08-24 11:47:37 2013-08-24:11:37:35 VALID   N N N          1
 4 SYS   ICOL$                         20              2 TABLE       2013-08-24 11:37:35 2013-08-24 11:47:37 2013-08-24:11:37:35 VALID   N N N          1
 5 SYS   COL$                          21              2 TABLE       2013-08-24 11:37:35 2013-08-24 11:52:40 2013-08-24:11:37:35 VALID   N N N          1
 6 SYS   LOB$                          80              2 TABLE       2013-08-24 11:37:36 2013-08-24 11:47:37 2013-08-24:11:37:36 VALID   N N N          1
 7 SYS   COLTYPE$                      83              2 TABLE       2013-08-24 11:37:36 2013-08-24 11:47:37 2013-08-24:11:37:36 VALID   N N N          1
 8 SYS   SUBCOLTYPE$                   86              2 TABLE       2013-08-24 11:37:36 2013-08-24 11:37:36 2013-08-24:11:37:36 VALID   N N N          1
 9 SYS   NTAB$                         88              2 TABLE       2013-08-24 11:37:36 2013-08-24 11:37:36 2013-08-24:11:37:36 VALID   N N N          1
10 SYS   REFCON$                       92              2 TABLE       2013-08-24 11:37:36 2013-08-24 11:37:36 2013-08-24:11:37:36 VALID   N N N          1
11 SYS   OPQTYPE$                      95              2 TABLE       2013-08-24 11:37:36 2013-08-24 11:37:36 2013-08-24:11:37:36 VALID   N N N          1
12 SYS   ICOLDEP$                     114              2 TABLE       2013-08-24 11:37:36 2013-08-24 11:37:36 2013-08-24:11:37:36 VALID   N N N          1
13 SYS   VIEWTRCOL$                   174              2 TABLE       2013-08-24 11:37:36 2013-08-24 11:37:36 2013-08-24:11:37:36 VALID   N N N          1
14 SYS   LIBRARY$                     252              2 TABLE       2013-08-24 11:37:39 2013-08-24 11:37:39 2013-08-24:11:37:39 VALID   N N N          1
15 SYS   ASSEMBLY$                    253              2 TABLE       2013-08-24 11:37:39 2013-08-24 11:37:39 2013-08-24:11:37:39 VALID   N N N          1
16 SYS   ATTRCOL$                     512              2 TABLE       2013-08-24 11:37:43 2013-08-24 11:37:43 2013-08-24:11:37:43 VALID   N N N          1
17 SYS   TYPE_MISC$                   517              2 TABLE       2013-08-24 11:37:43 2013-08-24 11:37:43 2013-08-24:11:37:43 VALID   N N N          1
18 rows selected.
--//可以发现tab$仅仅是cluster table中的1个.而是是第1个表(从0算起,0是cluster table)

2.如何tab$表被全部删除,自然导致数据库无法启动:

--//如何修复呢?
--//CLUSTER C_OBJ#的段头在dba 1,144.


SCOTT@book> select * from dba_extents where owner='SYS' and segment_name='C_OBJ#';
OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME EXTENT_ID FILE_ID BLOCK_ID   BYTES BLOCKS RELATIVE_FNO
----- ------------ ------------ --------------- --------- ------- -------- ------- ------ ------------
SYS   C_OBJ#       CLUSTER      SYSTEM                  0       1      144   65536      8            1
SYS   C_OBJ#       CLUSTER      SYSTEM                  1       1      152   65536      8            1
SYS   C_OBJ#       CLUSTER      SYSTEM                  2       1      160   65536      8            1
SYS   C_OBJ#       CLUSTER      SYSTEM                  3       1     3336   65536      8            1
SYS   C_OBJ#       CLUSTER      SYSTEM                  4       1     4392   65536      8            1
SYS   C_OBJ#       CLUSTER      SYSTEM                  5       1     5424   65536      8            1
SYS   C_OBJ#       CLUSTER      SYSTEM                  6       1     6152   65536      8            1
SYS   C_OBJ#       CLUSTER      SYSTEM                  7       1     7264   65536      8            1
SYS   C_OBJ#       CLUSTER      SYSTEM                  8       1     7912   65536      8            1
SYS   C_OBJ#       CLUSTER      SYSTEM                  9       1     8464   65536      8            1
SYS   C_OBJ#       CLUSTER      SYSTEM                 10       1     8496   65536      8            1
SYS   C_OBJ#       CLUSTER      SYSTEM                 11       1     8520   65536      8            1
SYS   C_OBJ#       CLUSTER      SYSTEM                 12       1     8552   65536      8            1
SYS   C_OBJ#       CLUSTER      SYSTEM                 13       1     8560   65536      8            1
SYS   C_OBJ#       CLUSTER      SYSTEM                 14       1     8600   65536      8            1
SYS   C_OBJ#       CLUSTER      SYSTEM                 15       1     8624   65536      8            1
SYS   C_OBJ#       CLUSTER      SYSTEM                 16       1     8704 1048576    128            1
SYS   C_OBJ#       CLUSTER      SYSTEM                 17       1     9856 1048576    128            1
SYS   C_OBJ#       CLUSTER      SYSTEM                 18       1    13312 1048576    128            1
SYS   C_OBJ#       CLUSTER      SYSTEM                 19       1    17792 1048576    128            1
SYS   C_OBJ#       CLUSTER      SYSTEM                 20       1    22400 1048576    128            1
SYS   C_OBJ#       CLUSTER      SYSTEM                 21       1    31488 1048576    128            1
SYS   C_OBJ#       CLUSTER      SYSTEM                 22       1    65920 1048576    128            1
SYS   C_OBJ#       CLUSTER      SYSTEM                 23       1    73984 1048576    128            1
SYS   C_OBJ#       CLUSTER      SYSTEM                 24       1    77824 1048576    128            1
SYS   C_OBJ#       CLUSTER      SYSTEM                 25       1    86016 1048576    128            1
SYS   C_OBJ#       CLUSTER      SYSTEM                 26       1    94208 1048576    128            1
27 rows selected.

--//system的表空间是mssm,bbed可以查看这种文件的段头.这些信息记录在


BBED> p /d dba 1,144  ktetb
struct ktetb[0], 8 bytes                    @108
   ub4 ktetbdba                             @108      4194449
   ub4 ktetbnbk                             @112      7

--//这里不算段头,记录数据部分.仅仅占7块.


struct ktetb[1], 8 bytes                    @116
   ub4 ktetbdba                             @116      4194456
   ub4 ktetbnbk                             @120      8
struct ktetb[2], 8 bytes                    @124
   ub4 ktetbdba                             @124      4194464
   ub4 ktetbnbk                             @128      8
struct ktetb[3], 8 bytes                    @132
   ub4 ktetbdba                             @132      4197640
   ub4 ktetbnbk                             @136      8
struct ktetb[4], 8 bytes                    @140
   ub4 ktetbdba                             @140      4198696
   ub4 ktetbnbk                             @144      8
..
struct ktetb[25], 8 bytes                   @308
   ub4 ktetbdba                             @308      4280320
   ub4 ktetbnbk                             @312      128
struct ktetb[26], 8 bytes                   @316
   ub4 ktetbdba                             @316      4288512
   ub4 ktetbnbk                             @320      128

--//我程序中使用的别名如下:


$ alias zdate='date +'\''%Y/%m/%d %T'\'''
$ export RLWRAP=$(which rlwrap)
$ type rlbbed
rlbbed is a function
rlbbed ()
{
    cd /home/oracle/bbed;
    $RLWRAP -s 9999 -c -r -i -f /usr/local/share/rlwrap/bbed $ORACLE_HOME/bin/bbed parfile=bbed.par cmdfile=cmd.par
}

--//关于bbed配置看相关文档.我下面有一些代码使用cut直接取对应位置的数据,可能width(宽度)设置要与我脚本保持一致.


$ cat cmd.par
set count 64
set width 160
 
$ cat bbed.par
blocksize=8192
listfile=$HOME/bbed/filelist.txt
mode=edit
PASSWORD=blockedit
SPOOL=Y

--//filelist.txt文件通过select file#||' '||name c100 from v$dbfile order by file#;生成.
--//首先确定扫描那些块的脚本.


/bin/rm /home/oracle/zzz430/bbed/scan*.txt
cd /home/oracle/zzz430/bbed
 
echo "process 1 start : `zdate` scan dba 1,144 ,  create scan1.txt about ktetbdba,ktetbnbk"
echo "p /d dba 1,144"  ktetb | rlbbed | egrep 'ktetbdba|ktetbnbk' | cut -c8-16,55- |tr " " "=" | paste -d ";" - - > scan1a.txt
high_water=`echo p /d dba 1,144 ktech.hwmark_ktech.blkno_ktehw | rlbbed | grep blkno_ktehw | cut -c60- | tr -d " " `
sed "\$s/ktetbnbk=.*$/ktetbnbk=$high_water/" scan1a.txt > scan1.txt
 
read -p "process 1 finish: `zdate`,enter continue..."

--//结果保存scan1.txt
--//也就是从dba=4194449开始,扫描7块. 从4194456开始,扫描8块....如此到结束。
--//注意仅仅扫描到高水位下的块.

3.分析扫描块的情况:


BBED> set dba 4194449
        DBA             0x00400091 (4194449 1,145)
 
BBED> p /d kdbt[1]
struct kdbt[1], 4 bytes                     @110
   sb2 kdbtoffs                             @110      8
   sb2 kdbtnrow                             @112      7

--//按照前面的查询，仅仅kdbt[1]记录相关表sys.tab$的记录信息，从偏移8(即*kdbr[8])开始,共有7条记录.


BBED> x /rnnnnnnnnnnnnncnnnnnnnntnnnnnnnnnncct *kdbr[8]
rowdata[7430]                               @7884
-------------
flag@7884: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@7885: 0x00
cols@7886:   31
col    0[2] @7888: 2
col    1[1] @7891: 0
col    2[2] @7893: 1
col    3[3] @7896: 144
col    4[2] @7900: 2
col    5[2] @7903: 4
col    6[2] @7906: 14
col    7[2] @7909: 1
col    8[1] @7912: 0
col    9[1] @7914: 0
col   10[1] @7916: 0
col   11[1] @7918: 0
col   12[3] @7920: 529
col  13[38] @7924: --------------------------------------
col   14[3] @7963: 7789
col   15[3] @7967: 1442
col   16[1] @7971: 0
col   17[1] @7973: 0
col   18[1] @7975: 0
col   19[2] @7977: 32
col   20[1] @7980: 0
col   21[1] @7982: 0
col   22[7] @7984: 2017-02-03 22:00:18
col   23[3] @7992: 7789
col   24[0] @7996: *NULL*
col   25[0] @7997: *NULL*
col   26[2] @7998: 14
col   27[2] @8001: 14
col   28[3] @8004: 1024
col   29[1] @8008: 0
col   30[1] @8010: 0
..


 
BBED> x /rnnnnnnnnnnnnncnnnnnnnntnnnnnnnnnncct *kdbr[14]
rowdata[6684]                               @7138
-------------
flag@7138: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@7139: 0x02
cols@7140:   31
ckix@7141:    7
 
col    0[2] @7142: 2
col    1[1] @7145: 0
col    2[2] @7147: 1
col    3[3] @7150: 144
col    4[2] @7154: 2
col    5[2] @7157: 3
col    6[2] @7160: 34
col    7[2] @7163: 1
col    8[1] @7166: 0
col    9[1] @7168: 0
col   10[1] @7170: 0
col   11[1] @7172: 0
col   12[3] @7174: 529
col  13[38] @7178: --------------------------------------
col   14[3] @7217: 5066
col   15[3] @7221: 1442
col   16[1] @7225: 0
col   17[1] @7227: 0
col   18[1] @7229: 0
col   19[2] @7231: 91
col   20[1] @7234: 0
col   21[1] @7236: 0
col   22[7] @7238: 2017-02-22 22:00:13
col   23[3] @7246: 5066
col   24[0] @7250: *NULL*
col   25[0] @7251: *NULL*
col   26[2] @7252: 34
col   27[2] @7255: 34
col   28[3] @7258: 1024
col   29[1] @7262: 0
col   30[1] @7264: 0

--//获取tab$的记录偏移以及数量.使用scan2.sh脚本.


echo "process 2 start : `zdate` scan block , get kdbtnrow,kdbtoffs ang grep kdbtnrow=0"
cat scan1.txt | while read i
do
    eval $i
    #echo $ktetbdba  $ktetbnbk
    for ((j=1; j<=$ktetbnbk ; j++))
    do
        echo -n "dba=$ktetbdba;" >> scan2a.txt
        echo "p /d dba $ktetbdba offset 0 kdbt[1]" | rlbbed | egrep 'kdbtoffs|kdbtnrow' |  cut -c8-16,55- |tr " " "=" | paste -d ";" - - >> scan2a.txt
        ktetbdba=$[ ktetbdba + 1 ]
        #echo $ktetbdba
    done
done
 
grep -v kdbtnrow=0 scan2a.txt > scan2.txt
read -p "process 2 finish: `zdate`,enter continue..."
..

--//生成scan2a.txt,过滤掉kdbtnrow=0.结果保存scan2.txt


$ head scan2.txt
dba=4194449;kdbtoffs=8;kdbtnrow=7
dba=4194450;kdbtoffs=8;kdbtnrow=5
dba=4194451;kdbtoffs=9;kdbtnrow=6
dba=4194452;kdbtoffs=10;kdbtnrow=9
dba=4194453;kdbtoffs=10;kdbtnrow=10
dba=4194454;kdbtoffs=10;kdbtnrow=10
dba=4194455;kdbtoffs=10;kdbtnrow=10
dba=4194456;kdbtoffs=10;kdbtnrow=10
dba=4194457;kdbtoffs=10;kdbtnrow=10
dba=4194458;kdbtoffs=7;kdbtnrow=7

3.生成修改记录flag偏移的脚本:


echo "process 3 start : `zdate` scan block , create bbed'script scan3_bbed.txt for modify delete of flag  and create scan4a.txt about block of ckix "
cat scan2.txt | while read i
do
    eval $i
    begin=$kdbtoffs
    end=$[ kdbtoffs + kdbtnrow -1 ]
    #  echo $dba $kdbtoffs $kdbtnrow  $begin $end
 
    kdbr_size=`echo map dba $dba| rlbbed | grep "sb2 kdbr" | sed -e "s/^.*\[//" -e "s/].*$//" `
 
    while [ $begin -le $end ]
    do
        kdbr_off=`echo p dba $dba offset 0 kdbr | rlbbed | grep "\[$begin\]" | cut -c55-`
        if [ $kdbr_off -gt $kdbr_size ]
        then
            echo "x /rc dba $dba *kdbr[$begin]" | rlbbed | grep '^flag@' | grep KDRHFD | sed -e 's/^flag@/offset=/' -e 's/ (.*)//' -e 's/: /;value=/' | tr -d " " | while read k
            #echo "x /rc dba $dba *kdbr[$begin]" | rlbbed | grep '^flag@' | sed -e 's/^flag@/offset=/' -e 's/ (.*)//' -e 's/: /;value=/' | tr -d " " | while read k
            do
                eval $k
                #echo $dba $offset $value
                value=`printf "0x%x" $(( value - 0x10  )) `
                echo "assign /x dba $dba offset $offset = $value " >> scan3_bbed.txt
            done
            #  if not found ckix@ and  found flag=0x7c , then ckix_value=0, and do not process chained row.
            echo "x /rx dba $dba *kdbr[$begin]" | rlbbed | grep "^ckix@" | sed -e "s/^ckix/dba=$dba;/" -e  's/@.*:/ckix_value=/' | tr -d " " >> scan4a.txt
            echo "x /rx dba $dba *kdbr[$begin]" | rlbbed | egrep "^ckix@" > /dev/null
            if [ $? -eq 1 ]
            then
                echo "x /rx dba $dba *kdbr[$begin]" | rlbbed | egrep "^^flag@.*: *0x7c" > /dev/null
                if [ $? -eq 0 ]
                then
                    echo "dba=$dba;ckix_value=0" >> scan4a.txt
                fi
            fi
        fi
        begin=$[ begin + 1 ]
    done
done
read -p "process 3 finish: `zdate`,enter continue..."

--//记录的偏移量如果小于kdbr_size,这些是覆盖的记录可能无法恢复,必须跳过.
--//注意仅仅过滤包含KDRHFD标识的记录需要修改,生成修改偏移的脚本保存在scan3_bbed.txt.
--//修改flag 使用当前值减去 0x10.
--//并且保存dba,ckix位置的脚本在scan4a.txt,注意使用注解部分,仅仅过滤KDRHFD标志的记录.(写脚本时tab$记录还没有删除)
--//注:如果过滤不存在ckix,使用ckix_value=0替代,如果存在行迁移,跳过不处理.


assign /x dba 4194449 offset 7884 = 0x5c
assign /x dba 4194449 offset 7756 = 0x5c
assign /x dba 4194449 offset 7632 = 0x5c
assign /x dba 4194449 offset 7512 = 0x5c
assign /x dba 4194449 offset 7388 = 0x5c
assign /x dba 4194449 offset 7266 = 0x5c
assign /x dba 4194449 offset 7138 = 0x5c
assign /x dba 4194450 offset 509 = 0x5c
assign /x dba 4194450 offset 7882 = 0x5c
assign /x dba 4194450 offset 7756 = 0x5c

--//注:实际上正常恢复大部分值是0x6c,删除前是0x7c.


$ head scan4a.txt
dba=4194449;ckix_value=0
dba=4194449;ckix_value=1
dba=4194449;ckix_value=2
dba=4194449;ckix_value=4
dba=4194449;ckix_value=5
dba=4194449;ckix_value=6
dba=4194449;ckix_value=7
dba=4194450;ckix_value=1
dba=4194450;ckix_value=3
dba=4194450;ckix_value=4

4.修改mref_offset标识:


echo "process 4 start : `zdate` create bbed's scan4_bbed.txt for modify cluster of mref of value "
sort scan4a.txt | uniq > scan4b.txt
cat scan4b.txt | while read i
do
    eval $i
    #echo $dba  $ckix_value
    echo -n "dba=$dba;" >> scan4c.txt
    echo "x /rn dba $dba *kdbr[$ckix_value]" | rlbbed | egrep "^kref@|^mref@" | sed -e "s/@/_offset=/" -e "s/:/;value=/" | tr -d " " | paste -d ";" - -  >> scan4c.txt
done
sed -e 's/;$/;mref_offset=0;value=0/' -e 's/value=/valuek=/' scan4c.txt > scan4.txt
 
cat scan4.txt | while read i
do
    eval $i
    # echo $dba $kref_offset $valuek $mref_offset $value
    if [ $mref_offset -eq 0 ]
    then
        mref_offset=$[ $kref_offset+ 2 ]
    fi
    valuem=$[ value + 1 ]
    if [ $valuem -lt $valuek ]
    then
        echo "assign dba $dba offset $mref_offset = $valuem" >> scan4m_bbed.txt
    else
        echo "assign dba $dba offset $mref_offset = $valuek" >> scan4k_bbed.txt
    fi
done
 
read -p "process 4 finish: `zdate`,enter continue..."

--//注:如果没有mref表示的情况其值等于0.实际上这步可以不做修复.对于读取tab$表没有问题的.
--//不做,verify类似如下错误.

Block Checking: DBA = 4288536, Block Type = KTB-managed data block
data header at 0x7f865724125c
kdbchk: key comref count wrong
keyslot=8
Block 94232 failed with check code 6121

5.建立sum apply脚本:


echo "process 5 start : create bbed's scan5_bbed.txt for sum apply"
sed -e 's/^dba=/sum apply dba /' -e 's/;.*$//' scan2.txt > scan5_bbed.txt
read -p "process 5 finish: `zdate`,enter continue..."

6.注意问题.

--//恢复记录后,tab$的索引I_TAB1与表tab$存在不一致的情况.


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> @ rowid AAAAA7AABAAAAILAAJ
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
        59          1        523          9   0x40020B           1,523                alter system dump datafile 1 block 523 ;
 
BBED> x /rnnc dba 1,523 *kdbr[9]
rowdata[1269]                               @4910
-------------
flag@4910: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@4911: 0x01
cols@4912:    3
 
col    0[2] @4913: 33
col    1[2] @4916: 33
col  2[189] @4919: CREATE INDEX I_TAB1 ON TAB$(BOBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483
645 PCTINCREASE 0 OBJNO 33 EXTENTS (FILE 1 BLOCK 312))

--//设置flag=3c,表示删除.

assign /x dba 1,523 offset 4910= 0x3c

7.剩下的就是测试.但愿OK.

--//太长,另外写一篇blog具体操作过程.另外对于已经出问题的系统,可能要设置_system_trig_enabled=false,job_queue_processes=0启动数据库.可能还有一些
--//细节需要注意.

[20190130]删除tab$记录的恢复2.txt

--//前面链接写好了脚本,开始测试删除后的恢复.千万不要在生产系统做这样的测试!!
--//参考链接:http://blog.itpub.net/267265/viewspace-2565245/=>[20190130]删除tab$记录的恢复.txt

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
 
create table ORACHK001 tablespace system  as select * from sys.tab$;
 
SYS@book> create table ORACHK001 tablespace system  as select * from sys.tab$;
Table created.
 
SYS@book> select count(*) from sys.tab$;
  COUNT(*)
----------
      2966
 
SYS@book> select count(*) from orachk001;
  COUNT(*)
----------
      2965

--//这样建立的表不包含orachk001.


SYS@book> select * from sys.tab$ minus select * from orachk001;
...
SYS@book> select  OBJ# ,  DATAOBJ# from sys.tab$ minus select OBJ# ,  DATAOBJ# from orachk001;
      OBJ#   DATAOBJ#
---------- ----------
     91090      91090
 
SYS@book> insert into orachk001 select * from sys.tab$ where (OBJ#,DATAOBJ#) in ((91090,91090));
1 row created.
 
SYS@book> commit ;
Commit complete.

2.做一个冷备份:
--//关闭数据库略.

$ cp -r /mnt/ramdisk/book /home/oracle/backup/

--//重启数据库.


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> delete from sys.tab$;
2966 rows deleted.
 
SYS@book> commit ;
Commit complete.
 
SYS@book> alter system checkpoint ;
System altered.
 
SYS@book> alter system checkpoint ;
System altered.
 
SYS@book> alter system checkpoint ;
System altered.
 
SYS@book> shutdown immediate ;
ORA-00957: duplicate column name
 
SYS@book> shutdown abort ;
ORACLE instance shut down.

--//再保留一份坏的备份.


$ mv /home/oracle/backup/book /home/oracle/backup/book_20190122_good
`/home/oracle/backup/book' -> `/home/oracle/backup/book_20190122_good'
 
$ cp -r /mnt/ramdisk/book /home/oracle/backup/
$ mv /home/oracle/backup/book /home/oracle/backup/book_20190122_bad
`/home/oracle/backup/book' -> `/home/oracle/backup/book_20190122_bad'

3.开始恢复:

--//执行如下脚本,自己建立目录/home/oracle/zzz430,zdate,rlbbed是别名.看前面的链接.
--//脚本scan.sh:


#! /bin/bash
/bin/rm /home/oracle/zzz430/bbed/scan*.txt
cd /home/oracle/zzz430/bbed
 
echo "process 1 start : `zdate` scan dba 1,144 ,  create scan1.txt about ktetbdba,ktetbnbk"
echo "p /d dba 1,144"  ktetb | rlbbed | egrep 'ktetbdba|ktetbnbk' | cut -c8-16,55- |tr " " "=" | paste -d ";" - - > scan1a.txt
high_water=`echo p /d dba 1,144 ktech.hwmark_ktech.blkno_ktehw | rlbbed | grep blkno_ktehw | cut -c60- | tr -d " " `
sed "\$s/ktetbnbk=.*$/ktetbnbk=$high_water/" scan1a.txt > scan1.txt
 
read -p "process 1 finish: `zdate`,enter continue..."
 
echo "process 2 start : `zdate` scan block , get kdbtnrow,kdbtoffs ang grep kdbtnrow=0"
cat scan1.txt | while read i
do
        eval $i
        #echo $ktetbdba  $ktetbnbk
    for ((j=1; j<=$ktetbnbk ; j++))
    do
                echo -n "dba=$ktetbdba;" >> scan2a.txt
                echo "p /d dba $ktetbdba offset 0 kdbt[1]" | rlbbed | egrep 'kdbtoffs|kdbtnrow' |  cut -c8-16,55- |tr " " "=" | paste -d ";" - - >> scan2a.txt
                ktetbdba=$[ ktetbdba + 1 ]
                #echo $ktetbdba
        done
done
 
grep -v kdbtnrow=0 scan2a.txt > scan2.txt
read -p "process 2 finish: `zdate`,enter continue..."
 
echo "process 3 start : `zdate` scan block , create bbed'script scan3_bbed.txt for modify delete of flag  and create scan4a.txt about block of ckix "
cat scan2.txt | while read i
do
        eval $i
    begin=$kdbtoffs
        end=$[ kdbtoffs + kdbtnrow -1 ]
        #  echo $dba $kdbtoffs $kdbtnrow  $begin $end
 
        kdbr_size=`echo map dba $dba| rlbbed | grep "sb2 kdbr" | sed -e "s/^.*\[//" -e "s/].*$//" `
 
        while [ $begin -le $end ]
        do
                kdbr_off=`echo p dba $dba offset 0 kdbr | rlbbed | grep "\[$begin\]" | cut -c55-`
                if [ $kdbr_off -gt $kdbr_size ]
                then
                        echo "x /rc dba $dba *kdbr[$begin]" | rlbbed | grep '^flag@' | grep KDRHFD | sed -e 's/^flag@/offset=/' -e 's/ (.*)//' -e 's/: /;value=/' | tr -d " " | while read k
                        #echo "x /rc dba $dba *kdbr[$begin]" | rlbbed | grep '^flag@' | sed -e 's/^flag@/offset=/' -e 's/ (.*)//' -e 's/: /;value=/' | tr -d " " | while read k
                        do
                                eval $k
                                #echo $dba $offset $value
                                value=`printf "0x%x" $(( value - 0x10  )) `
                                echo "assign /x dba $dba offset $offset = $value " >> scan3_bbed.txt
                        done
                        #  if not found ckix@ and  found flag=0x7c , then ckix_value=0, and do not process chained row.
                        echo "x /rx dba $dba *kdbr[$begin]" | rlbbed | grep "^ckix@" | sed -e "s/^ckix/dba=$dba;/" -e  's/@.*:/ckix_value=/' | tr -d " " >> scan4a.txt
                        echo "x /rx dba $dba *kdbr[$begin]" | rlbbed | egrep "^ckix@" > /dev/null
                        if [ $? -eq 1 ]
                        then
                                echo "x /rx dba $dba *kdbr[$begin]" | rlbbed | egrep "^^flag@.*: *0x7c" > /dev/null
                                if [ $? -eq 0 ]
                                then
                                        echo "dba=$dba;ckix_value=0" >> scan4a.txt
                                fi
                        fi
                fi
                begin=$[ begin + 1 ]
        done
done
read -p "process 3 finish: `zdate`,enter continue..."
 
echo "process 4 start : `zdate` create bbed's scan4_bbed.txt for modify cluster of mref of value "
sort scan4a.txt | uniq > scan4b.txt
cat scan4b.txt | while read i
do
        eval $i
        #echo $dba  $ckix_value
        echo -n "dba=$dba;" >> scan4c.txt
        echo "x /rn dba $dba *kdbr[$ckix_value]" | rlbbed | egrep "^kref@|^mref@" | sed -e "s/@/_offset=/" -e "s/:/;value=/" | tr -d " " | paste -d ";" - -  >> scan4c.txt
done
sed -e 's/;$/;mref_offset=0;value=0/' -e 's/value=/valuek=/' scan4c.txt > scan4.txt
 
cat scan4.txt | while read i
do
    eval $i
    # echo $dba $kref_offset $valuek $mref_offset $value
    if [ $mref_offset -eq 0 ]
    then
        mref_offset=$[ $kref_offset+ 2 ]
    fi
    valuem=$[ value + 1 ]
    if [ $valuem -lt $valuek ]
    then
        echo "assign dba $dba offset $mref_offset = $valuem" >> scan4m_bbed.txt
    else
        echo "assign dba $dba offset $mref_offset = $valuek" >> scan4k_bbed.txt
    fi
done
 
read -p "process 4 finish: `zdate`,enter continue..."
 
echo "process 5 start : create bbed's scan5_bbed.txt for sum apply"
sed -e 's/^dba=/sum apply dba /' -e 's/;.*$//' scan2.txt > scan5_bbed.txt
read -p "process 5 finish: `zdate`,enter continue..."

--//执行脚本scan.sh.


$ . scan.sh
process 1 start : 2019/01/30 10:20:32 scan dba 1,144 ,  create scan1.txt about ktetbdba,ktetbnbk
process 1 finish: 2019/01/30 10:20:33,enter continue...
process 2 start : 2019/01/30 10:20:33 scan block , get kdbtnrow,kdbtoffs ang grep kdbtnrow=0
process 2 finish: 2019/01/30 10:21:04,enter continue...
process 3 start : 2019/01/30 10:21:28 scan block , create bbed'script scan3_bbed.txt for modify delete of flag  and create scan4a.txt about block of ckix
process 3 finish: 2019/01/30 10:26:36,enter continue...
process 4 start : 2019/01/30 10:26:42 create bbed's scan4_bbed.txt for modify cluster of mref of value
process 4 finish: 2019/01/30 10:27:47,enter continue...
process 5 start : create bbed's scan5_bbed.txt for sum apply
process 5 finish: 2019/01/30 10:27:52,enter continue...

--//主要时间消耗在第3步,大约需要5分多钟.看看生成修改的脚本:


$ ls -l scan*bbed*
-rw-r--r-- 1 oracle oinstall 124731 2019-01-30 10:26:36 scan3_bbed.txt
-rw-r--r-- 1 oracle oinstall 105556 2019-01-30 10:27:47 scan4k_bbed.txt
-rw-r--r-- 1 oracle oinstall     70 2019-01-30 10:27:47 scan4m_bbed.txt
-rw-r--r-- 1 oracle oinstall  12782 2019-01-30 10:27:52 scan5_bbed.txt
 
$ head scan3_bbed.txt
assign /x dba 4194449 offset 7884 = 0x6c
assign /x dba 4194449 offset 7756 = 0x6c
assign /x dba 4194449 offset 7632 = 0x6c
assign /x dba 4194449 offset 7512 = 0x6c
assign /x dba 4194449 offset 7388 = 0x6c
assign /x dba 4194449 offset 7266 = 0x6c
assign /x dba 4194449 offset 7138 = 0x6c
assign /x dba 4194450 offset 509 = 0x6c
assign /x dba 4194450 offset 7882 = 0x6c
assign /x dba 4194450 offset 7756 = 0x6c
 
$ head scan4k_bbed.txt
assign dba 4194449 offset 8171 = 17
assign dba 4194449 offset 8149 = 15
assign dba 4194449 offset 8127 = 27
assign dba 4194449 offset 8083 = 11
assign dba 4194449 offset 8061 = 20
assign dba 4194449 offset 8039 = 8
assign dba 4194449 offset 8017 = 34
assign dba 4194450 offset 8149 = 21
assign dba 4194450 offset 8105 = 34
assign dba 4194450 offset 8083 = 27
 
$ head scan4m_bbed.txt
assign dba 4288539 offset 8169 = 1
assign dba 4288546 offset 8145 = 1
 
$ head scan5_bbed.txt
sum apply dba 4194449
sum apply dba 4194450
sum apply dba 4194451
sum apply dba 4194452
sum apply dba 4194453
sum apply dba 4194454
sum apply dba 4194455
sum apply dba 4194456
sum apply dba 4194457
sum apply dba 4194458

--//注意检查修改flag不是0x6c的记录.


$ grep -v "0x6c $" scan3_bbed.txt
assign /x dba 4194451 offset 7349 = 0x20
assign /x dba 4197642 offset 7888 = 0x20
assign /x dba 4207636 offset 7087 = 0x20
assign /x dba 4225801 offset 3621 = 0x4c
assign /x dba 4225801 offset 4436 = 0x4c
assign /x dba 4288537 offset 7717 = 0x4c
 
$ grep  "0x6c $" scan3_bbed.txt |wc
   2965   23720  124479

--//可以发现dba=4194451发生了行迁移行,删除后一样存在标识KDRHFD.(注:101是好的system01.dbf文件)


BBED> x /rx dba 4194451 *kdbr[14]
rowdata[6848]                               @7349
-------------
flag@7349: 0x30 (KDRHFD, KDRHFH)
lock@7350: 0x02
cols@7351:    0
 
BBED> x /rx dba 101,147 *kdbr[14]
rowdata[6848]                               @7349
-------------
flag@7349: 0x20 (KDRHFH)
lock@7350: 0x02
cols@7351:    0
nrid@7352:0x00407b09.1
 
--//dba=4197642也是原来发生了行迁移.
BBED> x /rx dba 4197642 *kdbr[8]
rowdata[7431]                               @7888
-------------
flag@7888: 0x30 (KDRHFD, KDRHFH)
lock@7889: 0x02
cols@7890:    0
 
BBED> x /rx dba 101,3338 *kdbr[8]
rowdata[7431]                               @7888
-------------
flag@7888: 0x20 (KDRHFH)
lock@7889: 0x00
cols@7890:    0
nrid@7891:0x00407b09.0

--//dba=4207636也是原来发生了行迁移.


BBED> x /rx dba 4207636 *kdbr[16]
rowdata[6599]                               @7087
-------------
flag@7087: 0x30 (KDRHFD, KDRHFH)
lock@7088: 0x02
cols@7089:    0
 
BBED> x /rx dba 101,13332 *kdbr[16]
rowdata[6599]                               @7087
-------------
flag@7087: 0x20 (KDRHFH)
lock@7088: 0x00
cols@7089:    0
nrid@7090:0x00417019.2

--//分析assign /x dba 4225801 offset 3621 = 0x4c的情况.


BBED> x /rx dba 4225801 *kdbr[10]
rowdata[0]                                  @3621
----------
flag@3621: 0x5c (KDRHFL, KDRHFF, KDRHFD, KDRHFC)
lock@3622: 0x02
cols@3623:    0
ckix@3624:    6
 
BBED> x /rx dba 101,31497 *kdbr[10]
rowdata[0]                                  @3621
----------
flag@3621: 0x4c (KDRHFL, KDRHFF, KDRHFC)
lock@3622: 0x00
cols@3623:   31
ckix@3624:    6
hrid@3625:0x00400d0a.1
 
col    0[3] @3631:  0xc2  0x06  0x1b
col    1[1] @3635:  0x80

--//0x400d0a=4197642,4197642= alter system dump datafile 1 block 3338,实际上行迁移的部分.
--//实际上你可以看出我多恢复了几条. 3+2965 = 2968,这样多恢复2条.

--//我写脚本扫描块,取出tailchk值,发现大部分都是0x5f5[cdef]的信息.


 
$ cat scanx.sh
#! /bin/bash
grep dba scan3_bbed.txt | cut -d" " -f4 | uniq | while read dba
do
        echo -n $dba :
        echo "p dba $dba offset 8188"| rlbbed | grep "ub4 tailchk"
done
 
$ . scanx.sh | cut -c64-71 | sort|uniq -c
      1 0x2d3e06
     69 0x5f5c06
      5 0x5f5d06
    499 0x5f5f06
      1 0xdde306
 
$ . scanx.sh | grep -v  '0x5f5[0-9abcdef]06'
4288539 :ub4 tailchk                                 @8188     0xdde30601
4288546 :ub4 tailchk                                 @8188     0x2d3e0601
 
$ egrep -n '4288539|4288546' scan3_bbed.txt
2962:#assign /x dba 4288539 offset 7920 = 0x6c
2971:#assign /x dba 4288546 offset 7851 = 0x6c

--//可以确定这两个块不需要修改,因为scn号相差太大.而且正好2条记录,估计以前删除表留下痕迹,与前面的信息正好吻合.
--//注解对应行取消它的执行.

$ cat scan4m_bbed.txt
assign dba 4288539 offset 8169 = 1
assign dba 4288546 offset 8145 = 1
--//以上脚本也许不需要执行.但是还有如下tailchk不是0x5f5f是什么回事呢?
69 0x5f5c06
5 0x5f5d06

--//实际上这个是延迟块块提交的产物,当dml修改块很多的情况下,仅仅部分块做快速提交(好像是缓存的某个百分比).
--//也就是有74块需要特殊处理.不然在open时报错,或者数据库直接关闭.参考链接:
--//http://blog.itpub.net/267265/viewspace-2564717/ => [20190124]bbed恢复数据遇到延迟块清除的问题2.txt
--//也就是oracle对于system表空间检测更加严格,不知道有什么参数可以临时关闭或者绕过这种检测.

4.执行生成的bbed脚本.


bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/zzz430/bbed/scan3_bbed.txt
bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/zzz430/bbed/scan4k_bbed.txt
bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/zzz430/bbed/scan5_bbed.txt

--//注意输入Y.
--//如果通过管道执行脚本,必须修改脚本在第2行加入Y.

5.禁用sys.tab$的索引I_TAB1.
--//这样恢复，索引与表存在不一致情况，要禁用sys.tab$的索引I_TAB1.


BBED> x /rnnc dba 1,523 *kdbr[9]
rowdata[1269]                               @4910
-------------
flag@4910: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@4911: 0x01
cols@4912:    3
 
col    0[2] @4913: 33
col    1[2] @4916: 33
col  2[189] @4919: CREATE INDEX I_TAB1 ON TAB$(BOBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483
645 PCTINCREASE 0 OBJNO 33 EXTENTS (FILE 1 BLOCK 312))

--//设置flag=3c,表示删除.


BBED> assign /x  dba 1,523 offset 4910= 0x3c
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub1 rowdata[0]                              @4910     0x3c
 
BBED> sum apply dba 1,523
Check value for File 1, Block 523:
current = 0x7e6b, required = 0x7e6b

6.启动数据库看看:


SYS@book> create pfile='/tmp/@.ora' from spfile ;
File created.

--//修改/tmp/book.ora文件，加入修改如下:


*._system_trig_enabled=false
*.job_queue_processes=0

--//启动遇到如下错误:


ORA-00600: internal error code, arguments: [kdBlkCheckError], [1], [94232], [6110], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kdBlkCheckError], [1], [9951], [6110], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kdBlkCheckError], [1], [31548], [6110], [], [], [], [], [], [], [], []

--//实际上我修复dba=1,94232后出现dba=1,9951.接着修复,接着在出现1,31548.而且这块还标识为坏块.即使启动ok,错误不断,都是
--//涉及tab$的块记录.实际上就是我前面提到延迟提交的问题.

--//分析dba=1,94232


BBED> set dba 1 , 94232
        DBA             0x00417018 (4288536 1,94232)
 
BBED> p tailchk
ub4 tailchk                                 @8188     0x5f5d0601

--//可以这些都是延迟块提交导致对应块没有更新.


BBED> p dba 1, 94232 ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes                @68
   struct ktbitxid, 8 bytes                 @68
      ub2 kxidusn                           @68       0x0005
      ub2 kxidslt                           @70       0x0018
      ub4 kxidsqn                           @72       0x00000751
   struct ktbituba, 8 bytes                 @76
      ub4 kubadba                           @76       0x00c0050f
      ub2 kubaseq                           @80       0x04eb
      ub1 kubarec                           @82       0x0b
   ub2 ktbitflg                             @84       0x0001 (NONE)
   union _ktbitun, 2 bytes                  @86
      sb2 _ktbitfsc                         @86       126
      ub2 _ktbitwrp                         @86       0x007e
   ub4 ktbitbas                             @88       0x00000000
 
BBED> p   dba 1, 9951 ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes                @68
   struct ktbitxid, 8 bytes                 @68
      ub2 kxidusn                           @68       0x0005
      ub2 kxidslt                           @70       0x0018
      ub4 kxidsqn                           @72       0x00000751
   struct ktbituba, 8 bytes                 @76
      ub4 kubadba                           @76       0x00c0013f
      ub2 kubaseq                           @80       0x04de
      ub1 kubarec                           @82       0x1b
   ub2 ktbitflg                             @84       0x0005 (NONE)
   union _ktbitun, 2 bytes                  @86
      sb2 _ktbitfsc                         @86       695
      ub2 _ktbitwrp                         @86       0x02b7
   ub4 ktbitbas                             @88       0x00000000
 
BBED> p  dba 1, 31548 ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes                @68
   struct ktbitxid, 8 bytes                 @68
      ub2 kxidusn                           @68       5
      ub2 kxidslt                           @70       24
      ub4 kxidsqn                           @72       1873
   struct ktbituba, 8 bytes                 @76
      ub4 kubadba                           @76       12583968
      ub2 kubaseq                           @80       1251
      ub1 kubarec                           @82       19
   ub2 ktbitflg                             @84       5 (NONE)
   union _ktbitun, 2 bytes                  @86
      sb2 _ktbitfsc                         @86       570
      ub2 _ktbitwrp                         @86       570
   ub4 ktbitbas                             @88       0
 
BBED> p  dba 1, 31548 ktbbh.ktbbhitl[1]
struct ktbbhitl[1], 24 bytes                @68
   struct ktbitxid, 8 bytes                 @68
      ub2 kxidusn                           @68       0x0005
      ub2 kxidslt                           @70       0x0018
      ub4 kxidsqn                           @72       0x00000751
   struct ktbituba, 8 bytes                 @76
      ub4 kubadba                           @76       0x00c00420
      ub2 kubaseq                           @80       0x04e3
      ub1 kubarec                           @82       0x13
   ub2 ktbitflg                             @84       0x0005 (NONE)
   union _ktbitun, 2 bytes                  @86
      sb2 _ktbitfsc                         @86       570
      ub2 _ktbitwrp                         @86       0x023a
   ub4 ktbitbas                             @88       0x00000000

---//注意.xid=0x0005.0x0018.0x00000751.其对应的ktbitbas是0. 其ktbitflg也可以看出没有提交.
--//我单独写一个脚本:


$ cat scanx.sh
 
#! /bin/bash
grep dba scan3_bbed.txt | cut -d" " -f4 | uniq | while read dba
do
        echo -n $dba :
        echo "p dba $dba offset 8188"| rlbbed | grep "ub4 tailchk"
done

$ . scanx.sh | grep -v 0x5f5f06 >| clearout.txt
--//先取出taichk不是5f5f的记录.注意删除dba=4288539,4288546 两行.


 
$ cat scana.sh
#! /bin/bash
cat clearout.txt | while read dba
do
    echo set dba $dba
    echo -n "assign "
    echo -e  "set dba $dba \np ktbbh" |rlbbed | /bin/grep -B 4 -A 9 "ub4 kxidsqn \+@.*0x00000751$"| egrep "ktbbhitl|ktbitflg" | cut -c11-21,55-60 | paste -d. - - | sed -e 's/   /=/'
    echo sum apply dba $dba
done

--//注:仅仅读取存在ub4 kxidsqn @72 0x00000751,不大可能别的ITL槽kxidsqn也正好是这个值.


$ . scana.sh >| clearout_bbed.txt
 
$ head  clearout_bbed.txt
set dba 4204236
assign ktbbhitl[1].ktbitflg=0x0002
sum apply dba 4204236
set dba 4204237
assign ktbbhitl[1].ktbitflg=0x0003
sum apply dba 4204237
set dba 4204241
assign ktbbhitl[1].ktbitflg=0x0002
sum apply dba 4204241
set dba 4204243

--//使用vim执行:%s/=0x00/=0x20/g.也就是设置提交标识.注意检查替换是否74行.
--//不想在写脚本了.^_^.


$ bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/zzz430/bbed/clearout_bbed.txt
$ bbed parfile=/home/oracle/bbed/bbed.par cmdfile=/home/oracle/zzz430/bbed/scan5_bbed.txt
 
SYS@book> startup pfile='/tmp/book.ora'
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> shutdown  immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.

--//OK.马上关闭以只读打开看看.


SYS@book> startup open read only pfile='/tmp/book.ora'
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.
 
select /*+ full(tab$) */ * from tab$;

--//没有任何错误.


SYS@book> select count(*) from tab$;
  COUNT(*)
----------
      2966
 
SYS@book> select /*+ full(tab$) */ count(*) from tab$;
  COUNT(*)
----------
      2966

--//完全能与前面的对上.


 
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

--//几乎完美恢复.
--//一般情况下这样的数据库做好导出到另外的数据库,执行如下类似导出操作,没有任何问题.仅仅owner=oe出现如下错误:


 
$ exp system/oracle file=a.dmp owner=oe BUFFER=8388608
Export: Release 11.2.0.4.0 - Production on Wed Jan 30 11:56:02 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
 
About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user OE
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user OE
About to export OE's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
EXP-00056: ORACLE error 8181 encountered
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.XMLTYPE", line 138
EXP-00000: Export terminated unsuccessfully

--//不过我读写模式下打开数据库没有问题,视乎执行如下:


SELECT VALUE (p$)
  FROM "XDB"."XDB$SCHEMA" AS OF SNAPSHOT ( :2) p$
 WHERE SYS_NC_OID$ = :1

--//我在toad下schema模式下点击data(open read only),调用如下sql语句:


SYS@book> SELECT X.SYS_NC_ROWINFO$.GetClobVal () AS SYS_NC_ROWINFO$ FROM XDB.XDB$SCHEMA X;
ERROR:
ORA-08181: specified number is not a valid system change number
ORA-06512: at "SYS.XMLTYPE", line 138
no rows selected

--//有点奇怪的是在dg下active dataguard mode下(read only),不报错.另外写一篇blog分析这个问题.

4.后记:

--//我脚本实际上元旦之前就写差不多,当然还有许多细节没有考虑好.比如行迁移或者链接问题.
--//还有bbed 如果ckix=0,mref=0不显示的问题.
--//还有就是延迟提交导致的问题,等等许多细节几乎导致我放弃恢复测试.

--//使用bash shell写脚本实际上执行效率很低的操作,通过这个恢复,了解许多oracle cluster table许多相关知识,
--//还有一些细节的处理,许多编码是使用cut硬性编码取出对于值.不能保证你遇到类似问题,直接拿来使用.^_^
--//何况真实的生产系统可能比我在测试环境遇到的问题更加复杂.

[20190212]删除tab$记录的恢复3.txt

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

--//实际上我一直认为能拷贝出来数据就ok了.这样恢复的数据库不能在使用,问题多多.
--//我继续测试实际上建表等操作都会报类似的:
ORA-00600: internal error code, arguments: [kdBlkCheckError], [1], [94232], [6110], [], [], [], [], [], [], [], []
--//这样的错误.主要问题在于system块的检查更加严格.

--//今天尝试恢复6110,6111,6112之类的错误.

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

--//前面已经恢复只读状态exp操作是正常的.具体看前面的文章.
--//首先更正前面脚本的几个错误:http://blog.itpub.net/267265/viewspace-2565250/=> [20190130]删除tab$记录的恢复2.txt


 57                 echo "x /rx dba $dba *kdbr[$begin]" | rlbbed | egrep "^^flag@.*: *0x7c" > /dev/null
--//原来写成echo "x /rx dba $dba *kdbr[$begin]" | rlbbed | egrep "^^flag@.*: 0x7c" > /dev/null ,可能出现多个空格的情况.
 58                 if [ $? -eq 0 ]
 59                 then
 60                     echo "dba=$dba;ckix_value=0" >> scan4a.txt
 61                 fi
 
$ cat scana.sh
#! /bin/bash
cat clearout.txt | while read dba
do
    echo set dba $dba
    echo -n "assign "
    echo -e  "set dba $dba \np ktbbh" |rlbbed | /bin/grep -B 4 -A 9 "ub4 kxidsqn \+@.*0x00000751$"| egrep "ktbbhitl|ktbitflg" | cut -c11-21,55-60 | paste -d. - - | sed -e 's/   /=/'
--//这里写错,原来写成4204236,实际上应该是$dba,
    echo sum apply dba $dba
done
 
--//注:http://blog.itpub.net/267265/viewspace-2565250/=> [20190130]删除tab$记录的恢复2.txt 里面的脚本已经更正.

2.我前面做了冷备份.首先恢复冷备份:


$ /bin/cp -r /home/oracle/backup/book_20190122_bad/* /mnt/ramdisk/book/
*/
 
$ . scan.sh
process 1 start : 2019/02/12 08:45:44 scan dba 1,144 ,  create scan1.txt about ktetbdba,ktetbnbk
process 1 finish: 2019/02/12 08:45:44,enter continue...
process 2 start : 2019/02/12 08:45:45 scan block , get kdbtnrow,kdbtoffs ang grep kdbtnrow=0
process 2 finish: 2019/02/12 08:46:16,enter continue...
process 3 start : 2019/02/12 08:46:50 scan block , create bbed'script scan3_bbed.txt for modify delete of flag  and create scan4a.txt about block of ckix
process 3 finish: 2019/02/12 08:52:01,enter continue...
process 4 start : 2019/02/12 08:52:06 create bbed's scan4_bbed.txt for modify cluster of mref of value
process 4 finish: 2019/02/12 08:53:12,enter continue...
process 5 start : create bbed's scan5_bbed.txt for sum apply
process 5 finish: 2019/02/12 08:54:11,enter continue...

--//查看生成的bbed脚本:


$ cat scan4m_bbed.txt
assign dba 4288539 offset 8169 = 1
assign dba 4288546 offset 8145 = 1

--//前面我提到过这2块dba的不需要恢复(里面记录的scn不是这个事务产生的),或者讲scan4m_bbed.txt脚本记录的dba可能存在多恢复记录的情况.最好仔细检查.


 
$ grep -v 0x6c scan3_bbed.txt
assign /x dba 4194451 offset 7349 = 0x20
assign /x dba 4197642 offset 7888 = 0x20
assign /x dba 4207636 offset 7087 = 0x20
assign /x dba 4225801 offset 3621 = 0x4c
assign /x dba 4225801 offset 4436 = 0x4c
assign /x dba 4288537 offset 7717 = 0x4c

--//这3条记录存在行链接或者迁移的情况,前面已经解析不再说明.
--//修改scan3_bbed.txt文件,注解如下2行不需要恢复.


$ grep ^# scan3_bbed.txt
#assign /x dba 4288539 offset 7920 = 0x6c
#assign /x dba 4288546 offset 7851 = 0x6c
 
$ grep  0x6c scan3_bbed.txt | grep -v "^#" |wc
   2963   23704  124395

--//2963+3=2966,这样恢复的记录数量与实际情况相符.

3.执行生成的bbed脚本并修复块 6110,6111,6112错误.


 
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/scan5_bbed.txt

--//前面提到启动遇到如下错误,主要是因为延迟块提交的问题:


ORA-00600: internal error code, arguments: [kdBlkCheckError], [1], [94232], [6110], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kdBlkCheckError], [1], [9951], [6110], [], [], [], [], [], [], [], []
ORA-00600: internal error code, arguments: [kdBlkCheckError], [1], [31548], [6110], [], [], [], [], [], [], [], []

--//编写脚本如下,主要获得延迟块提交的数据块,设置提交标识.并且将对应的ktbbhitl[N]._ktbitun._ktbitfsc=0.
--//这样会导致verify时出现如下错误,例子:


BBED> set dba 4204236
        DBA             0x004026cc (4204236 1,9932)
 
BBED> verify
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 9932
 
Block Checking: DBA = 4204236, Block Type = KTB-managed data block
data header at 0x7fb110a7b25c
kdbchk: space available on commit is incorrect
        tosp=5052 fsc=0 stb=0 avsp=4937
Block 9932 failed with check code 6111

--//解决方法就是assign kdbhtosp=kdbhavsp;sum apply就ok了.
--//我以前处理这个错误有点繁琐.实际上设置fsc=0,设置提交标识为快速提交标识0x2,在执行assign kdbhtosp=kdbhavsp,
--//这样记录里面lock标识不需要设置为0x0,这样简单许多.


 
$ cat scanb.sh
#! /bin/bash
# get dba of tailchk ,grep begin # line, save scan6.txt
# and then grep -v 0x5f5f06,save clearout.txt,other save notclearout.txt
 
grep dba scan3_bbed.txt | grep -v "^#" | cut -d" " -f4 | uniq | while read dba
do
        echo -n $dba :
        echo "p dba $dba offset 0 tailchk 8188"| rlbbed | grep "ub4 tailchk"
done >| scan6.txt
 
grep -v 0x5f5f06 scan6.txt >|  clearout.txt
grep    0x5f5f06 scan6.txt >|  notclearout.txt
 
# create modify ktbbhitl[N].ktbitflg , ktbbhitl[N]._ktbitun._ktbitfsc=0 and kdbhtosp=kdbhavsp of script.
cat clearout.txt | cut -d" " -f1 |while read dba
do
        echo set dba $dba
    echo -n "assign "
    echo -e  "set dba $dba \np ktbbh" |rlbbed | /bin/grep -B 4 -A 9 "ub4 kxidsqn \+@.*0x00000751$"| egrep "ktbbhitl|ktbitflg" | cut -c11-21,55-60 | paste -d. - - | sed -e 's/   /=/'
    echo -n "assign "
    echo -e  "set dba $dba \np ktbbh" |rlbbed | /bin/grep -B 4 -A 9 "ub4 kxidsqn \+@.*0x00000751$" | egrep "ktbbhitl"| cut -c11-21 | sed -e 's/$/._ktbitun._ktbitfsc=0/'
        echo "assign kdbhtosp=kdbhavsp"
    echo sum apply dba $dba
done >| clearout_bbed.txt
 
# create modify ktbbhitl[N]._ktbitun._ktbitfsc=0 and kdbhtosp=kdbhavsp of script.
cat notclearout.txt | cut -d" " -f1 |while read dba
do
        echo set dba $dba
#   echo -n "assign "
#   echo -e  "set dba $dba \np ktbbh" |rlbbed | /bin/grep -B 4 -A 9 "ub4 kxidsqn \+@.*0x00000751$"| egrep "ktbbhitl|ktbitflg" | cut -c11-21,55-60 | paste -d. - - | sed -e 's/   /=/'
    echo -n "assign "
    echo -e  "set dba $dba \np ktbbh" |rlbbed | /bin/grep -B 4 -A 9 "ub4 kxidsqn \+@.*0x00000751$" | egrep "ktbbhitl"| cut -c11-21 | sed -e 's/$/._ktbitun._ktbitfsc=0/'
        echo "assign kdbhtosp=kdbhavsp"
    echo sum apply dba $dba
done >| notclearout_bbed.txt

--//执行以上脚本:


$ . scanb.sh
$ grep  "ktbbhitl[1].ktbitflg=" clearout_bbed.txt | grep -v 0x0
$ echo $?
1

--//说明这些块都没有打上提交标识.


$ head -5 clearout_bbed.txt
set dba 4204236
assign ktbbhitl[1].ktbitflg=0x0002
assign ktbbhitl[1]._ktbitun._ktbitfsc=0
assign kdbhtosp=kdbhavsp
sum apply dba 4204236

--//使用vim执行:%s/ktbitflg=0x0/ktbitflg=0x2/.也就是设置提交标识.注意检查替换是否74行.
--//补充说明一下,我前面测试执行的是%s/=0x00/=0x20/,实际上提交标识仅仅占半个字节(4位),这样写有点问题,不过一般不会出现问题.
--//1块修改255条以上的情况在这里不会出现(至少对于这个表是如此).


 
$ head -5 clearout_bbed.txt
set dba 4204236
assign ktbbhitl[1].ktbitflg=0x2002
assign ktbbhitl[1]._ktbitun._ktbitfsc=0
assign kdbhtosp=kdbhavsp
sum apply dba 4204236

--//执行如下:


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
 
$ dbv  file=/mnt/ramdisk/book/system01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Tue Feb 12 10:37:09 2019
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
DBVERIFY - Verification starting : FILE = /mnt/ramdisk/book/system01.dbf
DBVERIFY - Verification complete
Total Pages Examined         : 97280
Total Pages Processed (Data) : 64316
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 13442
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 4185
Total Pages Processed (Seg)  : 1
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 15337
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 393502590 (3.393502590)

--//现在OK了.
4.禁用sys.tab$的索引I_TAB1.

--//这样恢复，索引与表存在不一致情况，要禁用sys.tab$的索引I_TAB1.


 
BBED> x /rnnc dba 1,523 *kdbr[9]
rowdata[1269]                               @4910
-------------
flag@4910: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@4911: 0x01
cols@4912:    3
col    0[2] @4913: 33
col    1[2] @4916: 33
col  2[189] @4919: CREATE INDEX I_TAB1 ON TAB$(BOBJ#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483
645 PCTINCREASE 0 OBJNO 33 EXTENTS (FILE 1 BLOCK 312))

--//设置flag=3c,表示删除.


BBED> assign /x  dba 1,523 offset 4910= 0x3c
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
ub1 rowdata[0]                              @4910     0x3c
 
BBED> sum apply dba 1,523
Check value for File 1, Block 523:
current = 0x7e6b, required = 0x7e6b
 
BBED> verify dba 1,523
DBVERIFY - Verification starting
FILE = /mnt/ramdisk/book/system01.dbf
BLOCK = 523
 
Block Checking: DBA = 4194827, Block Type = KTB-managed data block
data header at 0x6eee44
kdbchk: the amount of space used is not equal to block size
        used=4398 fsc=0 avsp=3525 dtl=8120
Block 523 failed with check code 6110

--//先不理会这个错误.

5.启动数据库看看:


SYS@book> startup pfile='/tmp/@.ora';
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> shutdown  immediate ;
Database closed.
Database dismounted.
ORACLE instance shut down.
 
SYS@book> startup open read only pfile='/tmp/book.ora'
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.

--//注/tmp/book.ora最好加入修改如下:


*._system_trig_enabled=false
*.job_queue_processes=0
 
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

--//OK,几乎完美恢复.我执行如下select * from sh.sales;,顺利读出,没有任何问题.
--//现在建立删除表没有任何问题.


 
SCOTT@book> create table t as select * from all_objects;
Table created.
 
SCOTT@book> drop table t purge ;
Table dropped.

6.剩下恢复tab$.索引I_TAB1,

写得有点长.另写一篇修复索引的帖子,感觉这步很难,也许要先放一放.
--//最后说明一点,我的是测试环境,也许真实的环境更加复杂.我可能还遗漏一些细节...^_^.

[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$的恢复包括索引已经完美完成.累...........

声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/w/小小林熬夜学编程/article/detail/186497