[20160831]關於資料塊Checksum.txt

--以前我學習bbed時做過一些測試,將'AAAA'替換成'BBBB',你可以發現資料塊的Checksum並沒有發生變化,當時並沒有仔細探究,
--現在想起來計算Checksum演算法應該相對簡單,就是做異或操作.

--比如上面的字元'AAAA'如果2個字元按位做異或操作,變成00000000,這個正好巧合,如果修改成'CCCC',做相同的異或操作結果
--也是00000000.
--如果按照這個推測修改為'CDCD',這樣做異或操作的結果也是00000000. 還是透過測試來說明問題:

1.環境
SCOTT@book> @ &r/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

2.建立測試環境:

create table tx (id number,name varchar2(20));
insert into tx values (1,'AAAA');
commit;

SCOTT@book> select rowid , tx.* from tx;
ROWID ID NAME
------------------ ---------- --------------------
AAAVq1AAEAAAAeMAAA 1 AAAA

SCOTT@book> @ &r/rowid AAAVq1AAEAAAAeMAAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     88757          4       1932          0 0x100078C           4,1932               alter system dump datafile 4 block 1932
                                                                                      ;
SCOTT@book> @ &r/bbvi 4 1932
BVI_COMMAND
------------------------------------------------------
bvi -b 15826944 -s 8192 /mnt/ramdisk/book/users01.dbf

SCOTT@book> alter system checkpoint;
System altered.

3.使用bvi修改'AAAA'=>'CCCC'看看.
--//注:我個人喜歡使用bvi修改,這樣修改快一些.再使用bbed計算checksum.如果你喜歡也可以使用bbed操作.
--//再修改前看看checksum.

BBED> set dba 4,1932
DBA 0x0100078c (16779148 4,1932)

BBED> p kcbh.chkval_kcbh
ub2 chkval_kcbh @16 0x9b53

--//修改'AAAA'=>'CCCC',注意bbed檢視最好退出再進入.

BBED> set dba 4,1932
DBA 0x0100078c (16779148 4,1932)

BBED> sum
Check value for File 4, Block 1932:
current = 0x9b53, required = 0x9b53

BBED> p kcbh.chkval_kcbh
ub2 chkval_kcbh @16 0x9b53

--//以發現checksum=0x9b53
--//使用bvi修改'CCCC'=>'CDCD'看看.注意bbed檢視最好退出再進入.

BBED> set dba 4,1932
DBA 0x0100078c (16779148 4,1932)

BBED> sum
Check value for File 4, Block 1932:
current = 0x9b53, required = 0x9b53

BBED> p kcbh.chkval_kcbh
ub2 chkval_kcbh @16 0x9b53

--//如果修改為'CCDD'應該檢查和就不一樣了。

BBED> set dba 4,1932
DBA 0x0100078c (16779148 4,1932)

BBED> sum
Check value for File 4, Block 1932:
current = 0x9b53, required = 0x9c54

--//可以發現這樣修改出現了不一致。因為CC與DD做異或，CD與CD做異或兩者結果不同。
--//修改回來'AAAA'.

4.由此我們可以"製造"出定制的檢查和。比如我想實現檢查和0x0000.只要在freedata區域找0x0000，換成0x9b53就可以實現檢查和為0x0000。
--//使用bvi在freedata區域找0x0000，換成0x9b53。
BBED> set dba 4,1932
DBA 0x0100078c (16779148 4,1932)

BBED> sum
Check value for File 4, Block 1932:
current = 0x9b53, required = 0x0000

--//可以發現,如果我應用sum apply，就可以現實checksum=0x0000.
BBED> sum apply ;
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
Check value for File 4, Block 1932:
current = 0x0000, required = 0x0000

BBED> p kcbh.chkval_kcbh
ub2 chkval_kcbh @16 0x0000

SCOTT@book> alter system flush buffer_cache;
System altered.

SCOTT@book> select rowid , tx.* from tx;
ROWID ID NAME
------------------ ---------- --------------------
AAAVq1AAEAAAAeMAAA 1 AAAA

--//顯示正常！

5.在由此產生一個問題，就是如果在bbed執行corrupt看看這個時候檢查和是多少。

BBED> set dba 4,1932
DBA 0x0100078c (16779148 4,1932)

BBED> corrupt
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
Block marked media corrupt.

BBED> p kcbh.chkval_kcbh
ub2 chkval_kcbh @16 0x0217

BBED> p seq_kcbh
ub1 seq_kcbh @14 0xff

BBED> p tailchk
ub4 tailchk @8188 0x000006ff

--//執行corrupt僅僅導致seq_kcbh=0xff.
--//修復,正常這樣修復很簡單,就是設定seq_kcbh=0x01,tailchk=0x00000601.

BBED> assign seq_kcbh=0x01
ub1 seq_kcbh @14 0x01

BBED> assign tailchk=0x00000601
ub4 tailchk @8188 0x00000601

BBED> sum ;
Check value for File 4, Block 1932:
current = 0x0217, required = 0x0217

BBED> sum apply;
Check value for File 4, Block 1932:
current = 0x0217, required = 0x0217

--//可以發現這樣修改檢查和也不會變化，實際上我們僅僅修改2處0xff=>0x01,這樣檢查和是不會變化的。
SCOTT@book> alter system flush buffer_cache;
System altered.

SCOTT@book> select rowid , tx.* from tx;
ROWID ID NAME
------------------ ---------- --------------------
AAAVq1AAEAAAAeMAAA 1 AAAA

6.最後探究檢查和的計算。
--使用bvi將這個資料塊儲存為檔案a.txt
$ xxd -c 2 a.txt | cut -c10-13 > a1.txt

--想辦法將上面的結果匯入資料庫的表中。我簡單使用vim的替換功能。轉化成sql語句。
SCOTT@book> create table ty( a varchar2(20));
Table created.

--oracle沒有異或操作，有位與操作。可以透過如下實現。
BITXOR(x,y) = BITOR(x,y) - BITAND(x,y) = (x + y) - BITAND(x, y) * 2;

--透過遞迴寫了一個sql語句，不考慮效率有點慢。感謝kelis2004的指點。
--連結：http://www.itpub.net/thread-2066614-1-1.html

SCOTT@book> alter table ty add (b number);
Table altered.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> update ty set b=TO_NUMBER (a, 'xxxxxxxxxxxxxxx');
4096 rows updated.

SCOTT@book> commit ;
Commit complete.

WITH t AS (SELECT ROWNUM ID, b FROM ty)
    ,prod (lastID, lastprod)
     AS (SELECT id, b
           FROM t
          WHERE id = 1
         UNION ALL
         SELECT ID, (b + lastprod) - BITAND (b, lastprod) * 2
           FROM prod, t
          WHERE t.id = lastID + 1)
SELECT *
FROM prod
WHERE lastid = (SELECT MAX (ID) FROM t);
;

LASTID LASTPROD
---------- ----------
4096 0

--正好是0，說明演算法正常，當然我寫的sql效率不是很高哈哈。

--補充1點：

SCOTT@book> alter table ty add (c number);
Table altered.

SCOTT@book> update ty set c=rownum;
4096 rows updated.

SCOTT@book> commit ;
Commit complete.

SCOTT@book> create unique index pk_ty on ty(c);
Index created.

WITH prod (lastID, lastprod)
     AS (SELECT c, b
           FROM ty
          WHERE c = 1
         UNION ALL
         SELECT c, (b + lastprod) - BITAND (b, lastprod) * 2
           FROM prod, ty
          WHERE ty.c = lastID + 1)
SELECT *
FROM prod
WHERE lastid = (SELECT MAX (c) FROM ty);

LASTID LASTPROD
---------- ----------
4096 0

[20160831]關於資料塊Checksum.txt

相關文章