[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