[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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2789246/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- bbed修改undo段狀態
- [20231008]bbed探究lob段.txt
- [20230224]bbed設定偏移技巧.txt
- [20211021]關於undo段頭事務表.txt
- [20210311]如何建立bbed安裝包.txt
- [20190630]如何確定直方圖型別.txt直方圖型別
- [20181031]如何確定db_link的程式號.txt
- 28、undo_1_2（undo引數、undo段、事務）
- [20210920]bbed的assign命令.txt
- [20220223]bbed ktbbh.ktbbhict.txt
- [20210304]bbed的assign命令.txt
- [20181204]bbed修改問題.txt
- [20180619]bbed verify問題.txt
- 【譯】如何通過 INUIAddVoiceShortcutButtonDelegate 正確地使用 INUIAddVoiceShortcutButtonUI
- [20210906]bbed讀取資料塊(bbed-wrap.sh).txt
- [20210901]cygwin下使用bbed.txt
- [20210223]bbed itl ktbitflg 2.txt
- [20210303]bbed使用小問題.txt
- [20190104]bbed手工插入資料.txt
- [20181227]bbed的使用問題.txt
- [20210318]bbed讀取資料塊.txt
- [20190104]bbed手動修改資料.txt
- [20180628]顯示bbed x命令格式.txt
- [重慶思莊每日技術分享]-在自動UNDO管理情況下，如何手工增加undo段
- [20231021]生成bbed的執行指令碼.txt指令碼
- [20230427]bbed sum apply問題2.txtAPP
- [20180627]測試bbed是否支援管道命令.txt
- [20191118]確定linux伺服器cpu數量.txtLinux伺服器
- [20191206]確定sys.file$相關資訊.txt
- IT職場：如何確定哪些過程需要進行PFMEA分析？
- 【UNDO】Oracle系統回滾段說明Oracle
- [20231109]bbed p命令dba引數問題.txt
- [20210831]bbed讀取資料塊6.txt
- [20210930]bbed恢復刪除的資料.txt
- [20210323]bbed讀取資料塊5.txt
- [20210318]bbed讀取資料塊2.txt
- [20210319]bbed讀取資料塊3.txt
- [20180604]在記憶體修改資料(bbed).txt記憶體