本文分享自華為雲社區《淺析KV存儲之長尾時延問題，華為雲 GeminiDB Redis 探尋行業更優解決方案！》，作者：華為雲資料庫GaussDB NoSQL團隊。 目前，KV存儲的廣泛使用極大程度上源於快速訪問的業務需求，而這種業務通常對時延敏感度高，在較好的平均性能下，還需要解決特定場景下的性能 ...
[20231103]rename IDL_UB1$後使用bbed的恢復3.txt
--//上午解決rename IDL_UB1$後使用bbed的恢復問題，就是涉及到的5個索引4個需要修改，其中一個因為NULL值的緣故，不需要修改。
--//主要原因是rename是delete再insert obj$,反過來思考，如果修改時長度等長,我僅僅需要name等於原來的字元串就可以，當然有
--//一個前提，rename後在幾個索引的位置不能發生變動。不然這樣的改動量也是很大的，這樣修複索引僅僅是修複i_obj2,i_obj5,
--//name等於原來的字元串就ok了，這樣改動僅僅涉及3個塊，自己還是測試驗證看看。
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
$ strings -t d /mnt/ramdisk/book/system01.dbf | grep 'IDL_UB1\$'| awk '{printf "%6d %6d %20s\n",int($1/8192), $1-int($1/8192)*8192, $2}'
243 6325 IDL_UB1$
351 5692 IDL_UB1$
375 5692 IDL_UB1$
17086 4802 IDL_UB1$
34025 5594 SELECT
34029 5278 COMMIT"SELECT
36154 5278 COMMIT"SELECT
95512 3371 IDL_UB1$l
--//註：顯示的第一個欄位對應數據塊號，第二個欄位對應相應數據塊的偏移.
--//前面的測試我已經探察對應數據塊，主要修改數據塊243,351,375對應對象是obj$以及對應索引i_obj2,i_obj5.
--//註:前面的恢複測試說明我的疏漏,實際上rename是先delete,在insert,這樣涉及到幾個索引都要修改,因為rowid已經發生了變化,並
--//且記錄在索引裡面.參考前面的測試：[20231103]rename IDL_UB1$後使用bbed的恢復的後遺症.txt"
2.開始測試:
SYS@book> rename IDL_UB1$ to IDL_UB1Y;
Table renamed.
--//註：修改前後長度一樣,我僅僅將$ -> Y,這樣在兩個索引段的位置即使變化，也在同一個數據塊中，前後位置的差別。
SYS@book> alter system checkpoint;
System altered.
$ strings -t d /mnt/ramdisk/book/system01.dbf | grep 'IDL_UB1Y'| awk '{printf "%6d %6d %20s\n",int($1/8192), $1-int($1/8192)*8192, $2}'
351 3983 IDL_UB1Y
375 3983 IDL_UB1Y
86182 4750 IDL_UB1Y
--//僅僅涉及3個數據塊.我僅僅修改裡面的Y=>$,這樣等於索引改動還在同一塊中，修改的工作量也大大減少.
--//我僅僅需要修改數據塊86182以及索引塊(351,375)指向的行IDL_UB1Y => IDL_UB1$就ok了。
--//也就是 Y (ascii 0x59) 變成 $ (ascii 0x24).
3.使用bbed觀察與修複:
--//dba = 1,86182
BBED> x /rnnncncntttnccnxnnncct dba 1,86182 *kdbr[18]
rowdata[0] @4736
----------
flag@4736: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@4737: 0x02
cols@4738: 18
col 0[3] @4739: 225
col 1[3] @4743: 225
col 2[1] @4747: 0
col 3[8] @4749: IDL_UB1Y
col 4[2] @4758: 1
col 5[0] @4761: *NULL*
col 6[2] @4762: 2
col 7[7] @4765: 2013-08-24 11:37:39
col 8[7] @4773: 2023-11-07 08:45:45
col 9[7] @4781: 2023-11-07 08:45:45
col 10[2] @4789: 1
col 11[0] @4792: *NULL*
col 12[0] @4793: *NULL*
col 13[1] @4794: 0
col 14[0] @4796: *NULL*
col 15[1] @4797: 0
col 16[2] @4799: 2
~~~~~~~~~~~~~~~~~~~
col 17[1] @4802: 0
SYS@book> @bbvi 1 86182
BVI_COMMAND
----------------------------------------------------------------------------------------------------
bvi -b 706002944 -s 8192 /mnt/ramdisk/book/system01.dbf
xxd -c16 -g 2 -s 706002944 -l 8192 /mnt/ramdisk/book/system01.dbf
dd if=/mnt/ramdisk/book/system01.dbf bs=8192 skip=86182 count=1 of=1_86182.dd conv=notrunc 2>/dev/null
od -j 706002944 -N 8192 -t x1 -v /mnt/ramdisk/book/system01.dbf
hexdump -s 706002944 -n 8192 -C -v /mnt/ramdisk/book/system01.dbf
alter system dump datafile '/mnt/ramdisk/book/system01.dbf' block 86182;
6 rows selected.
--//使用bvi修改比較簡單.
--//dba = 1,351
BBED> x /rncncccnnn dba 1,351 *kd_off[37]
rowdata[4] @3972
----------
flag@3972: 0x00 (NONE)
lock@3973: 0x02
keydata[6]: 0x00 0x41 0x50 0xa6 0x00 0x12
data key:
col 0[1] @3981: 0
col 1[8] @3983: IDL_UB1Y
col 2[2] @3992: 1
col 3[0] @3995: *NULL*
col 4[0] @3996: *NULL*
col 5[0] @3997: *NULL*
col 6[2] @3998: 2
col 7[1] @4001: 0
col 8[3] @4003: 225
SYS@book> @bbvi 1 351
BVI_COMMAND
-----------------------------------------------------
bvi -b 2875392 -s 8192 /mnt/ramdisk/book/system01.dbf
--//dba = 1,375
BBED> x /rncnnnnccn dba 1,375 *kd_off[37]
rowdata[4] @3972
----------
flag@3972: 0x00 (NONE)
lock@3973: 0x02
keydata[6]: 0x00 0x41 0x50 0xa6 0x00 0x12
data key:
col 0[1] @3981: 0
col 1[8] @3983: IDL_UB1Y
col 2[2] @3992: 1
col 3[2] @3995: 2
col 4[1] @3998: 0
col 5[0] @4000: *NULL*
col 6[0] @4001: *NULL*
col 7[0] @4002: *NULL*
col 8[3] @4003: 225
SYS@book> @bbvi 1 375
BVI_COMMAND
------------------------------------------------------
bvi -b 3072000 -s 8192 /mnt/ramdisk/book/system01.dbf
BBED> sum apply dba 1,86182
Check value for File 1, Block 86182:
current = 0x3af3, required = 0x3af3
BBED> sum apply dba 1,351
Check value for File 1, Block 351:
current = 0x9706, required = 0x9706
BBED> sum apply dba 1,375
Check value for File 1, Block 375:
current = 0x2f2d, required = 0x2f2d
--//使用完成後，使用x命令檢查過程略.
verify dba 1,86182
verify dba 1,351
verify dba 1,375
4.驗證是否正確:
SYS@book> alter system flush buffer_cache;
System altered.
SYS@book> alter system flush shared_pool;
System altered.
SYS@book> select rowid from sys.obj$ where name='IDL_UB1$';
ROWID
------------------
AAAAASAABAAAVCmAAS
SYS@book> @ rowid AAAAASAABAAAVCmAAS
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
18 1 86182 18 0x4150A6 1,86182 alter system dump datafile 1 block 86182
--//註：在測試過程遇到1個小問題,alter system flush buffer_cache; dba=1,86182塊被重寫還原(不知代為什麼).後面我關閉資料庫修改OK.
SYS@book> analyze table idl_ub1$ validate structure cascade;
Table analyzed.
SYS@book> analyze table obj$ validate structure cascade;
analyze table obj$ validate structure cascade
*
ERROR at line 1:
ORA-08100: index is not valid - see trace file for diagnostics
$ oerr ora 8100
08100, 00000, "index is not valid - see trace file for diagnostics"
// *Cause: Validate Index detected an inconsistency in its argument index
// *Action: Send trace file to your customer support representative
--//還是不行!!
--//最後我發現連刪除的索引也要按照順序排序.
BBED> x /rncncccnnn dba 1,351 *kd_off[36]
rowdata[1713] @5681
-------------
flag@5681: 0x01 (KDXRDEL)
lock@5682: 0x02
keydata[6]: 0x00 0x40 0x00 0xf3 0x00 0x17
data key:
col 0[1] @5690: 0
col 1[8] @5692: IDL_UB1$
col 2[2] @5701: 1
col 3[0] @5704: *NULL*
col 4[0] @5705: *NULL*
col 5[0] @5706: *NULL*
col 6[2] @5707: 2
col 7[1] @5710: 0
col 8[3] @5712: 225
BBED> x /rncncccnnn dba 1,351 *kd_off[37]
rowdata[4] @3972
----------
flag@3972: 0x00 (NONE)
lock@3973: 0x02
keydata[6]: 0x00 0x41 0x50 0xa6 0x00 0x12
data key:
col 0[1] @3981: 0
col 1[8] @3983: IDL_UB1$
col 2[2] @3992: 1
col 3[0] @3995: *NULL*
col 4[0] @3996: *NULL*
col 5[0] @3997: *NULL*
col 6[2] @3998: 2
col 7[1] @4001: 0
col 8[3] @4003: 225
--//kd_off[36]指向的刪除的記錄,kd_off[37]指向正常的記錄.兩個除了flag,keydata[6]不同外,其它一樣.
--//$ ascii=24,ascii 23 對應字元 #.
--//我必須把指向kd_off[36]的記錄的col 1[8] @5692: IDL_UB1$修改為col 1[8] @5692: IDL_UB1#.這樣保證順序一致.
--//修改後的樣子如下：
BBED> x /rncncccnnn dba 1,351 *kd_off[36]
rowdata[1713] @5681
-------------
flag@5681: 0x01 (KDXRDEL)
lock@5682: 0x02
keydata[6]: 0x00 0x40 0x00 0xf3 0x00 0x17
data key:
col 0[1] @5690: 0
col 1[8] @5692: IDL_UB1#
col 2[2] @5701: 1
col 3[0] @5704: *NULL*
col 4[0] @5705: *NULL*
col 5[0] @5706: *NULL*
col 6[2] @5707: 2
col 7[1] @5710: 0
col 8[3] @5712: 225
--//dba = 1,375 也是一樣,不然 analyze table obj$ validate structure cascade;還會報錯.
BBED> x /rncnnnnccn dba 1,375 *kd_off[36]
rowdata[1713] @5681
-------------
flag@5681: 0x01 (KDXRDEL)
lock@5682: 0x02
keydata[6]: 0x00 0x40 0x00 0xf3 0x00 0x17
data key:
col 0[1] @5690: 0
col 1[8] @5692: IDL_UB1#
col 2[2] @5701: 1
col 3[2] @5704: 2
col 4[1] @5707: 0
col 5[0] @5709: *NULL*
col 6[0] @5710: *NULL*
col 7[0] @5711: *NULL*
col 8[3] @5712: 225
SYS@book> alter system flush shared_pool;
System altered.
SYS@book> alter system flush buffer_cache;
System altered.
SYS@book> analyze table obj$ validate structure cascade;
Table analyzed.
$ dbv file=/mnt/ramdisk/book/system01.dbf
DBVERIFY: Release 11.2.0.4.0 - Production on Tue Nov 7 09:48:34 2023
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) : 64260
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 13456
Total Pages Failing (Index): 0
Total Pages Processed (Other): 4206
Total Pages Processed (Seg) : 1
Total Pages Failing (Seg) : 0
Total Pages Empty : 15358
Total Pages Marked Corrupt : 0
Total Pages Influx : 0
Total Pages Encrypted : 0
Highest block SCN : 392040706 (3.392040706)
--//這樣算下來工作量也不小.
總結:
--//1.修改對應數據塊(86182))以及索引塊(351,375)的name=IDL_UB1$.
--//2.修改刪除索引對應數據塊原來的IDL_UB1$->IDL_UB1#,保證順序一致.這樣analyze table obj$ validate structure cascade;ok.
--//3.我覺得第2步可以不做,這樣就無法通過analyze table obj$ validate structure cascade;罷了,不會影響使用.
--//4.不建議採用這樣的方式恢復，直接修改數據欄位是最高效的方法。
