[20210817]如何通过bbed确定undo段.txt

[20210817]如何通过bbed确定undo段.txt --//链接http://www.itpub.net/thread-2142534-1-1.html,要确定undo段加入参数_corrupted_rollback_segments中. --//正好以前写过一个bbed读取数据块的脚本,链接http://blog.itpub.net/267265/viewspace-2764314/=>[20210323]bbed读取数据块5.txt --//验证看看. 1.首先确定sys.bootstrap$的段头: BBED> p dba 1,1 kcvfh.kcvfhrdb ub4 kcvfhrdb                                @96       0x00400208 BBED> set dba 0x00400208         DBA             0x00400208 (4194824 1,520) BBED> p dba 1,520 ktetb struct ktetb[0], 8 bytes                    @108    ub4 ktetbdba                             @108      0x00400209    ub4 ktetbnbk                             @112      0x00000007 --//数据段从dba=0x00400209开始.最多7个块. --//0x00400209 = set dba 1,521 = alter system dump datafile 1 block 521 = 4194825 BBED> p dba 1,520 ktech.hwmark_ktech.blkno_ktehw ub4 blkno_ktehw                             @52       0x00000003 --//实际上高水位在3. BBED> p dba 1,521 ktbbh.ktbbhsid union ktbbhsid, 4 bytes                     @24    ub4 ktbbhsg1                             @24       0x0000003b    ub4 ktbbhod1                             @24       0x0000003b --//数据段号3b = 59 $ . fff.sh 1 521 523 59 rnnc | grep -ni undo 8:15 |15|CREATE TABLE UNDO$("US#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"USER#" NUMBER NOT NULL,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"SCNBAS" NUMBER,"SCNWRP" NUMBER,"XACTSQN" NUMBER,"UNDOSQN" NUMBER,"INST#" NUMBER,"STATUS$" NUMBER NOT NULL,"TS#" NUMBER,"UGRP#" NUMB 9:34 |34|CREATE UNIQUE INDEX I_UNDO1 ON UNDO$(US#) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 34 EXTENTS (FILE 1 BLOCK 320)) 10:35 |35|CREATE INDEX I_UNDO2 ON UNDO$(NAME) PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 35 EXTENTS (FILE 1 BLOCK 328)) 32:16 |16|CREATE TABLE TS$("TS#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"OWNER#" NUMBER NOT NULL,"ONLINE$" NUMBER NOT NULL,"CONTENTS$" NUMBER NOT NULL,"UNDOFILE#" NUMBER,"UNDOBLOCK#" NUMBER,"BLOCKSIZE" NUMBER NOT NULL,"INC#" NUMBER NOT NULL,"SCNWRP" NUMBER,"SCNBAS" NUMBER,"DFLMI --//太长看不出完整语句.不过从前面的行号可以推断在dba=1,521块的行7。" BBED> x /rnnc dba 1,521 *kdbr[7] rowdata[4739]                               @6029 ------------- flag@6029: 0x2c (KDRHFL, KDRHFF, KDRHFH) lock@6030: 0x01 cols@6031:    3 col    0[2] @6032: 15 col    1[2] @6035: 15 col  2[600] @6038: CREATE TABLE UNDO$("US#" NUMBER NOT NULL,"NAME" VARCHAR2(30) NOT NULL,"USER#" NUMBER NOT NULL,"FILE#" NUMBER NOT NULL,"BLOCK#" NUMBER NOT NULL,"SCNBAS" NUMBER,"SCNWRP" NUMBER,"XACTSQN" NUMBER,"UNDOSQN" NUMBER,"INST#" NUMBER,"STATUS$" NUMBER NOT NULL,"TS#" NUMBER,"UGRP#" NUMB ER,"KEEP" NUMBER,"OPTIMAL" NUMBER,"FLAGS" NUMBER,"SPARE1" NUMBER,"SPARE2" NUMBER,"SPARE3" NUMBER,"SPARE4" VARCHAR2(1000),"SPARE5" VARCHAR2(1000),"SPARE6" DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE (  INITIAL 64K NEXT 1024K MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 OBJNO 15 EXTENTS (FILE 1 BLOCK 224)) --//确定sys.undo$ 段头在 dba=1,224,OBJNO=15 . 2.继续分析: BBED> p  dba 1,224 ktetb struct ktetb[0], 8 bytes                    @108    ub4 ktetbdba                             @108      0x004000e1    ub4 ktetbnbk                             @112      0x00000007 BBED> p dba 1,224 ktech.hwmark_ktech.blkno_ktehw ub4 blkno_ktehw                             @52       0x00000001 --//实际上高水位在1. BBED> p dba 1,225 ktbbh.ktbbhsid.ktbbhod1 ub4 ktbbhod1                                @24       0x0000000f --//数据段号0x0000000f = 15 --//0x004000e1 = set dba 1,225 = alter system dump datafile 1 block 225 = 4194529 $ . fff.sh 1 225 225 15 rncnnnnnnnnnnnnnnnnncct | head 0 |SYSTEM|0 |1|128|0|0|0|0|0|3|0|||||0 1 |_SYSSMU1_3724004606$|1 |3|128|2140315900|3|2687|1997|0|3|2|||||2 2 |_SYSSMU2_2996391332$|1 |3|144|2140316178|3|2925|1668|0|3|2|||||2 3 |_SYSSMU3_1723003836$|1 |3|160|2140316172|3|2912|2192|0|3|2|||||2 4 |_SYSSMU4_1254879796$|1 |3|176|2140315868|3|2702|2436|0|3|2|||||2 5 |_SYSSMU5_898567397$|1 |3|192|2140316184|3|2941|2863|0|3|2|||||2 6 |_SYSSMU6_1263032392$|1 |3|208|2140315752|3|3065|2096|0|3|2|||||2 7 |_SYSSMU7_2070203016$|1 |3|224|2140315848|3|2690|1640|0|3|2|||||2 8 |_SYSSMU8_517538920$|1 |3|240|2140315882|3|2990|2331|0|3|2|||||2 9 |_SYSSMU9_1650507775$|1 |3|256|2140315860|3|4615|2364|0|3|2|||||2 --//脚本还是有问题,数字后面还是有空格,为什么? 噢格式多输出一个r $  . fff.sh 1 225 225 15 ncnnnnnnnnnnnnnnnnncct | head 0|SYSTEM|0|1|128|0|0|0|0|0|3|0|||||0 1|_SYSSMU1_3724004606$|1|3|128|2140315900|3|2687|1997|0|3|2|||||2 2|_SYSSMU2_2996391332$|1|3|144|2140316178|3|2925|1668|0|3|2|||||2 3|_SYSSMU3_1723003836$|1|3|160|2140316172|3|2912|2192|0|3|2|||||2 4|_SYSSMU4_1254879796$|1|3|176|2140315868|3|2702|2436|0|3|2|||||2 5|_SYSSMU5_898567397$|1|3|192|2140316184|3|2941|2863|0|3|2|||||2 6|_SYSSMU6_1263032392$|1|3|208|2140315752|3|3065|2096|0|3|2|||||2 7|_SYSSMU7_2070203016$|1|3|224|2140315848|3|2690|1640|0|3|2|||||2 8|_SYSSMU8_517538920$|1|3|240|2140315882|3|2990|2331|0|3|2|||||2 9|_SYSSMU9_1650507775$|1|3|256|2140315860|3|4615|2364|0|3|2|||||2 --//ok现在正确了. $ . fff.sh 1 225 225 15 ncnnnnnnnnnnnnnnnnncct | awk -F"|" '{print $2}' SYSTEM _SYSSMU1_3724004606$ _SYSSMU2_2996391332$ _SYSSMU3_1723003836$ _SYSSMU4_1254879796$ _SYSSMU5_898567397$ _SYSSMU6_1263032392$ _SYSSMU7_2070203016$ _SYSSMU8_517538920$ _SYSSMU9_1650507775$ _SYSSMU10_1197734989$ _SYSSMU11_2918886963$ _SYSSMU12_4232189298$ _SYSSMU13_3658426373$ _SYSSMU14_305930084$ _SYSSMU15_1906014507$ _SYSSMU16_2558871358$ _SYSSMU17_3169116773$ _SYSSMU18_461200155$ _SYSSMU19_460450082$ _SYSSMU20_639455322$ _SYSSMU21_595816423$ _SYSSMU22_2431798092$ _SYSSMU23_1687285339$ _SYSSMU24_2794791659$ _SYSSMU25_3968518491$ _SYSSMU26_4192235551$ _SYSSMU27_413528929$ _SYSSMU28_1350594702$ _SYSSMU29_135784801$ _SYSSMU30_3427570509$ _SYSSMU31_94956245$ _SYSSMU32_701194505$ _SYSSMU33_3949722347$ _SYSSMU34_2800552639$ _SYSSMU35_3318551732$ _SYSSMU36_518159296$ _SYSSMU37_2548288284$ _SYSSMU38_3305547598$ _SYSSMU39_2309498568$ _SYSSMU40_2100453613$ _SYSSMU41_3998304919$ _SYSSMU42_2128841267$ _SYSSMU43_3402481974$ _SYSSMU44_1493063561$ _SYSSMU45_2709977100$ _SYSSMU46_250809541$ _SYSSMU47_527804903$ _SYSSMU48_379832540$ _SYSSMU49_1348148011$ _SYSSMU50_2922012488$ _SYSSMU51_3205847872$ _SYSSMU52_2900205628$ _SYSSMU53_179595608$ _SYSSMU54_215934843$ _SYSSMU55_1564295623$ _SYSSMU56_1174278115$ _SYSSMU57_178576483$ _SYSSMU58_581652980$ _SYSSMU59_632849411$ _SYSSMU60_2713787583$ _SYSSMU61_100489360$ _SYSSMU62_2192713506$ _SYSSMU63_3457312279$ _SYSSMU64_2868032965$ _SYSSMU65_3548846672$ _SYSSMU66_1454500216$ _SYSSMU67_1652581570$ _SYSSMU68_2815995547$ _SYSSMU69_442032523$ _SYSSMU70_281987117$ _SYSSMU71_1169965077$ _SYSSMU72_2010299848$ _SYSSMU73_101365697$ _SYSSMU74_633306031$ _SYSSMU75_2119376231$ --//共76行,包括SYSTEM的undo段. SCOTT@book> select count(name) from sys.undo$; COUNT(NAME) -----------          76 --//我自己有点奇怪的是查询的输出顺序. SCOTT@book> select rowid,name from sys.undo$ ; ROWID              NAME ------------------ ---------------------------------------- AAAAAPAABAAAADhAAA SYSTEM AAAAAPAABAAAADhAAK _SYSSMU10_1197734989$ AAAAAPAABAAAADhAAL _SYSSMU11_2918886963$ AAAAAPAABAAAADhAAM _SYSSMU12_4232189298$ AAAAAPAABAAAADhAAN _SYSSMU13_3658426373$ AAAAAPAABAAAADhAAO _SYSSMU14_305930084$ AAAAAPAABAAAADhAAP _SYSSMU15_1906014507$ AAAAAPAABAAAADhAAQ _SYSSMU16_2558871358$ AAAAAPAABAAAADhAAR _SYSSMU17_3169116773$ AAAAAPAABAAAADhAAS _SYSSMU18_461200155$ AAAAAPAABAAAADhAAT _SYSSMU19_460450082$ AAAAAPAABAAAADhAAB _SYSSMU1_3724004606$ AAAAAPAABAAAADhAAU _SYSSMU20_639455322$ AAAAAPAABAAAADhAAV _SYSSMU21_595816423$ AAAAAPAABAAAADhAAW _SYSSMU22_2431798092$ AAAAAPAABAAAADhAAX _SYSSMU23_1687285339$ AAAAAPAABAAAADhAAY _SYSSMU24_2794791659$ AAAAAPAABAAAADhAAZ _SYSSMU25_3968518491$ AAAAAPAABAAAADhAAa _SYSSMU26_4192235551$ AAAAAPAABAAAADhAAb _SYSSMU27_413528929$ AAAAAPAABAAAADhAAc _SYSSMU28_1350594702$ AAAAAPAABAAAADhAAd _SYSSMU29_135784801$ AAAAAPAABAAAADhAAC _SYSSMU2_2996391332$ AAAAAPAABAAAADhAAe _SYSSMU30_3427570509$ AAAAAPAABAAAADhAAf _SYSSMU31_94956245$ AAAAAPAABAAAADhAAg _SYSSMU32_701194505$ AAAAAPAABAAAADhAAh _SYSSMU33_3949722347$ AAAAAPAABAAAADhAAi _SYSSMU34_2800552639$ AAAAAPAABAAAADhAAj _SYSSMU35_3318551732$ AAAAAPAABAAAADhAAk _SYSSMU36_518159296$ AAAAAPAABAAAADhAAl _SYSSMU37_2548288284$ AAAAAPAABAAAADhAAm _SYSSMU38_3305547598$ AAAAAPAABAAAADhAAn _SYSSMU39_2309498568$ AAAAAPAABAAAADhAAD _SYSSMU3_1723003836$ AAAAAPAABAAAADhAAo _SYSSMU40_2100453613$ AAAAAPAABAAAADhAAp _SYSSMU41_3998304919$ AAAAAPAABAAAADhAAq _SYSSMU42_2128841267$ AAAAAPAABAAAADhAAr _SYSSMU43_3402481974$ AAAAAPAABAAAADhAAs _SYSSMU44_1493063561$ AAAAAPAABAAAADhAAt _SYSSMU45_2709977100$ AAAAAPAABAAAADhAAu _SYSSMU46_250809541$ AAAAAPAABAAAADhAAv _SYSSMU47_527804903$ AAAAAPAABAAAADhAAw _SYSSMU48_379832540$ AAAAAPAABAAAADhAAx _SYSSMU49_1348148011$ AAAAAPAABAAAADhAAE _SYSSMU4_1254879796$ AAAAAPAABAAAADhAAy _SYSSMU50_2922012488$ AAAAAPAABAAAADhAAz _SYSSMU51_3205847872$ AAAAAPAABAAAADhAA0 _SYSSMU52_2900205628$ AAAAAPAABAAAADhAA1 _SYSSMU53_179595608$ AAAAAPAABAAAADhAA2 _SYSSMU54_215934843$ AAAAAPAABAAAADhAA3 _SYSSMU55_1564295623$ AAAAAPAABAAAADhAA4 _SYSSMU56_1174278115$ AAAAAPAABAAAADhAA5 _SYSSMU57_178576483$ AAAAAPAABAAAADhAA6 _SYSSMU58_581652980$ AAAAAPAABAAAADhAA7 _SYSSMU59_632849411$ AAAAAPAABAAAADhAAF _SYSSMU5_898567397$ AAAAAPAABAAAADhAA8 _SYSSMU60_2713787583$ AAAAAPAABAAAADhAA9 _SYSSMU61_100489360$ AAAAAPAABAAAADhAA+ _SYSSMU62_2192713506$ AAAAAPAABAAAADhAA/ _SYSSMU63_3457312279$ AAAAAPAABAAAADhABA _SYSSMU64_2868032965$ AAAAAPAABAAAADhABB _SYSSMU65_3548846672$ AAAAAPAABAAAADhABC _SYSSMU66_1454500216$ AAAAAPAABAAAADhABD _SYSSMU67_1652581570$ AAAAAPAABAAAADhABE _SYSSMU68_2815995547$ AAAAAPAABAAAADhABF _SYSSMU69_442032523$ AAAAAPAABAAAADhAAG _SYSSMU6_1263032392$ AAAAAPAABAAAADhABG _SYSSMU70_281987117$ AAAAAPAABAAAADhABH _SYSSMU71_1169965077$ AAAAAPAABAAAADhABI _SYSSMU72_2010299848$ AAAAAPAABAAAADhABJ _SYSSMU73_101365697$ AAAAAPAABAAAADhABK _SYSSMU74_633306031$ AAAAAPAABAAAADhABL _SYSSMU75_2119376231$ AAAAAPAABAAAADhAAH _SYSSMU7_2070203016$ AAAAAPAABAAAADhAAI _SYSSMU8_517538920$ AAAAAPAABAAAADhAAJ _SYSSMU9_1650507775$ 76 rows selected. --//噢明白了执行计划选择了 INDEX FULL SCAN , 使用索引I_UNDO2.按照name排序.这样显示顺序就对了. SCOTT@book> select /*+ full(undo$) */ rowid,name from sys.undo$ ; --//结果不贴出来了. $ diff <(sqlplus -s -l scott/book <<< "set head off feedback  off^Jselect /*+ full(a) */ name from sys.undo$ a;" | sed '1d')  <(. fff.sh 1 225 225 15 ncnnnnnnnnnnnnnnnnncct | awk -F"|" '{print $2}') --//OK,两者比较完全正确,注意里面^J使用ctrl+v,ctrl+J输入. 3.我以前也写过一篇[20140516]取出回滚段信息.txt --//链接：http://blog.itpub.net/267265/viewspace-1162543/ --//嗯,原链接已经不见了.重新贴出. --//http://blog.itpub.net/267265/viewspace-2073723/=>[20160401]取出回滚段信息2.txt --//使用dd+strings，也可以确定，但是不准确。 4.附上fff.sh脚本： $ cat fff.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} # create sed script. a=$5 len=$( echo ${#a}) seq $len | xargs -IQ expr substr $a Q 1 | grep -n '[nt]' | sed  's+:.$+s/ $//+' >| ff.sed echo 's+^\*NULL\*$++g' >> ff.sed # scan1 begin_block to end_block,define Scope. /bin/rm scan1.txt 2>/dev/null while [ $begin_block -le $end_block ] do         v_object_id=$(echo "p /d dba $file_number,$begin_block  ktbbh.ktbbhsid.ktbbhod1" | rlbbed | grep ktbbhod1 |awk '{print $NF}')         if [ -z "$v_object_id" ]         then                 v_object_id=0         fi         if (( $v_object_id == $data_object_id ))         then                 echo $file_number,$begin_block >> scan1.txt         fi         begin_block=$[ begin_block + 1 ] done # scan2 kdbr and display record. cat scan1.txt |while read dba do         #echo set dba $dba         kdbr_size=$(echo map dba $dba | rlbbed | grep "sb2 kdbr" | sed -e "s/^.*\[//;s/].*$//")         #echo $kdbr_size         begin=0         end=$[ kdbr_size -1 ]         #echo $begin $end         while [ $begin -le $end ]         do                 kdbr_off=$(echo p dba $dba offset 0 kdbr | rlbbed | grep "sb2 kdbr\[$begin\]" | awk '{print $NF'})                 #echo $kdbr_off                 #if [ $kdbr_off -gt $kdbr_size ]                 if (( $kdbr_off > $kdbr_size ))                 then                         echo -n "x $ff dba $dba *kdbr[$begin]" | rlbbed  | grep "^col " | cut -c20- | sed -f ff.sed | paste -sd'|'                 fi                 begin=$[ begin + 1 ]         done done