-- 創建表 drop table if exists emp; create table emp( empno int, ename varchar(50), job varchar(50), mgr int, hiredate date, sal decimal(7,2), comm decim ...
[20230427]bbed sum apply問題2.txt
--//使用bbed修改數據塊時,最後總要sum apply改寫校驗和,但是修改redo文件是一個例外,sum apply不會修改.
--//通過例子說明:
1.環境:
SCOTT@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
2.測試環境建立:
SCOTT@book> create table deptx as select * from dept ;
Table created.
SCOTT@book> select rowid,deptx.* from deptx where rownum=1;
ROWID DEPTNO DNAME LOC
------------------ ------------ -------------- -------------
AAAWF/AAEAAAAObAAA 10 ACCOUNTING NEW YORK
SCOTT@book> @ rowid AAAWF/AAEAAAAObAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
------------ ------------ ------------ ------------ -------------------- -------------------- ----------------------------------------
90495 4 923 0 0x100039B 4,923 alter system dump datafile 4 block 923 ;
SCOTT@book> alter system checkpoint;
System altered.
SCOTT@book> alter system archive log current ;
System altered.
SCOTT@book> @ logfile
GROUP# STATUS TYPE MEMBER IS_ GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
------ ---------- ---------- ------------------------------- --- ------ ------- --------- ----------- --------- ------- --- ---------- ------------------ ------------------- ---------------------- -------------------
1 ONLINE /mnt/ramdisk/book/redo01.log NO 1 1 734 52428800 512 1 YES ACTIVE 13277809855 2023-04-27 06:00:16 13277826041 2023-04-27 09:47:54
2 ONLINE /mnt/ramdisk/book/redo02.log NO 2 1 735 52428800 512 1 NO CURRENT 13277826041 2023-04-27 09:47:54 281474976710655
3 ONLINE /mnt/ramdisk/book/redo03.log NO 3 1 733 52428800 512 1 YES INACTIVE 13277772888 2023-04-26 22:00:09 13277809855 2023-04-27 06:00:16
4 STANDBY /mnt/ramdisk/book/redostb01.log NO
5 STANDBY /mnt/ramdisk/book/redostb02.log NO
6 STANDBY /mnt/ramdisk/book/redostb03.log NO
7 STANDBY /mnt/ramdisk/book/redostb04.log NO
7 rows selected.
--//當前線上日誌是 /mnt/ramdisk/book/redo02.log.
3.修改數據塊產生日誌:
SCOTT@book> update deptx set dname = 'zyxabcde' where deptno=10;
1 row updated.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system archive log current ;
System altered.
$ strings -t d /mnt/ramdisk/book/redo02.log | grep zyxabcde
65716 zyxabcde
--//偏移在65716處,redo塊大小512,65716/512= 128.3515625,也就是在128塊處.
--//塊內偏移在65716-128*512 = 180.
--//128*512 = 65536
4.bbed查看:
BBED> info all
File# Name Size(blks)
----- ---- ----------
1 /mnt/ramdisk/book/system01.dbf 0
2 /mnt/ramdisk/book/sysaux01.dbf 0
3 /mnt/ramdisk/book/undotbs01.dbf 0
4 /mnt/ramdisk/book/users01.dbf 0
5 /mnt/ramdisk/book/example01.dbf 0
6 /mnt/ramdisk/book/tea01.dbf 0
7 /mnt/ramdisk/book/mssm01.dbf 0
101 /mnt/ramdisk/book/control01.ctl 0
102 /mnt/ramdisk/book/control02.ctl 0
201 /mnt/ramdisk/book/temp01.dbf 0
501 /mnt/ramdisk/book/redo01.log 0
502 /mnt/ramdisk/book/redo02.log 0
503 /mnt/ramdisk/book/redo03.log 0
504 /mnt/ramdisk/book/redostb01.log 0
BBED> set dba 502,128
DBA 0x7d800080 (2105540736 502,128)
BBED> p chkval_kcbh
ub2 chkval_kcbh @16 0x01f0
--//當前chkval_kcbh=0x01f0.
BBED> dump /v offset 0 count 512
File: /mnt/ramdisk/book/redo02.log (502)
Block: 128 Offsets: 0 to 511 Dba:0x7d800080
-------------------------------------------------------
01220000 80000000 df020000 10805e88 l ."......?....^.
f0010000 0d010300 748c6b17 0100ff01 l ?......t.k.....
~~~~--//checksum.
80018001 80018001 00000100 01000000 l ................
01000000 0a018001 738c6b17 03008001 l ........s.k.....
80018001 802c004e 758c6b17 03000000 l .....,.Nu.k.....
bb76aa43 0b050100 04000100 9b030001 l 籿狢............
988b6b17 03004000 02007f61 0a001800 l [email protected]....
1d000200 08001800 010dd982 00000000 l ..........?....
0a001900 b4530000 fd07c000 22100500 l ....碨..??"...
9b030001 9a030001 fa120501 0200ffff l ........?......
2c020c00 00000301 feff0000 00000000 l ,.......?......
01000000 7a797861 62636465 05022300 l ....zyxabcde..#.
0300ffff 1001c000 e48b6b17 0300c000 l ......??k...?
0100ffff 04002000 19000100 b4530000 l ...... .....碨..
fd07c000 22100500 12009000 00102200 l ??".........".
00000000 00000000 05042300 0300ffff l ..........#.....
1001c000 748c6b17 0300041b 0100ffff l ..?t.k.........
08001400 10000400 19000000 b4530000 l ............碨..
00000000 09000000 02000000 fd07c000 l ............??
22100500 0300441d 00000000 bad44964 l ".....D.....涸Id
05012400 0300ffff fd07c000 e38b6b17 l ..$.....???k.
03000000 0100ffff 0e001400 4c000800 l ............L...
1d000200 0a000000 9000d61d 12000100 l ..........?....
0a001900 b4530000 22100500 7f610100 l ....碨.."....a..
7f610100 04000000 00000000 0b011900 l .a..............
080c0100 00000000 fd07c000 22100400 l ........??"...
9e876b17 03006681 a0876b17 0300c000 l ..k...f.?k...?
4e032900 fb8b6b17 03000000 fa07c000 l N.).?k.....??
00000000 53000000 030d6b17 03000000 l ....S.....k.....
9b030001 9a030001 fa120501 0200ffff l ........?......
2c000c00 00000301 02000000 00000000 l ,...............
01000000 4143434f 554e5449 4e470000 l ....ACCOUNTING..
<16 bytes per line>
BBED> dump /v offset 180 count 8
File: /mnt/ramdisk/book/redo02.log (502)
Block: 128 Offsets: 180 to 187 Dba:0x7d800080
-------------------------------------------------------
7a797861 62636465 l zyxabcde
<16 bytes per line>
BBED> modify /c y offset 180
Warning: contents of previous BIFILE will be lost. Proceed? (Y/N) y
File: /mnt/ramdisk/book/redo02------------------------------------
79797861 62636465
<16 bytes per line>
BBED> dump /v offset 180 count 8
File: /mnt/ramdisk/book/redo02.log (502)
Block: 128 Offsets: 180 to 187 Dba:0x7d800080
-------------------------------------------------------
79797861 62636465 l yyxabcde
<16 bytes per line>
BBED> sum
Check value for File 502, Block 128:
current = 0x01f0, required = 0x01f0
--//你可以發現checksum並沒有發生變化.也是就是通過bbed sum apply無法修改redo文件的checksum.
BBED> sum apply
Check value for File 502, Block 128:
current = 0x01f0, required = 0x01f0
--//sum apply也沒有變化.
$ xxd -c16 -g 2 -s 65536 -l 512 /mnt/ramdisk/book/redo02.log | cut -c10-48 | xor.sh | grep result
xor result: 300
--//不是0,說明修改不正確.
BBED> assign chkval_kcbh=0x0
ub2 chkval_kcbh @16 0x0000
$ xxd -c16 -g 2 -s 65536 -l 512 /mnt/ramdisk/book/redo02.log | cut -c10-48 | xor.sh | grep result
xor result: F301
--//可以看出正確的值是0x01F3(註意大小頭問題).
BBED> assign chkval_kcbh=0x01F3
ub2 chkval_kcbh @16 0x01f3
$ xxd -c16 -g 2 -s 65536 -l 512 /mnt/ramdisk/book/redo02.log | cut -c10-48 | xor.sh | grep result
xor result: 0
--//ok這樣才修改正確.
--//之所以有這個測試,因為http://www.killdb.com/2023/04/17/的測試.
--//理論上如果修改redo文件正確,無法通過鏈接測試模擬出ORA-00312錯誤的.對方的測試修改塊的檢查和存在問題.
--//另外一點通過bbed讀取這類塊要小心,實際上結構不一定正確.也許早期redo文件讀取是正確的.
BBED> map
File: /mnt/ramdisk/book/redo02.log (502)
Block: 128 Dba:0x7d800080
------------------------------------------------------------
Undo Segment Header
struct kcbh, 20 bytes @0
struct ktect, 44 bytes @20
struct ktetb[16778139], 134225112 bytes @64
struct ktuxc, 104 bytes @404
struct ktuxe[0], 0 bytes @508
ub4 tailchk @508
--//bbed把它當作Undo Segment Header.ktetb的結構也不可能這麼大.
BBED> dump /v offset 508 count 4
File: /mnt/ramdisk/book/redo02.log (502)
Block: 128 Offsets: 508 to 511 Dba:0x7d800080
-------------------------------------------------------
4e470000 l NG..
<16 bytes per line>
--//裡面的NG實際上ACCOUNTING的一部分,根本不是普通數據塊裡面的tailchk.
4.後記:
--//但是我的修改無法通過validate.
SCOTT@book> alter system dump logfile '/mnt/ramdisk/book/redo02.log' validate;
alter system dump logfile '/mnt/ramdisk/book/redo02.log' validate
*
ERROR at line 1:
ORA-00399: corrupt change description in redo log
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ORA-00353: log corruption near block 128 change 13277826164 time 04/27/2023 09:49:47
ORA-00334: archived log: '/mnt/ramdisk/book/redo02.log'
--//如果我修改回來,validate沒有任何問題:
BBED> modify /c z offset 180
File: /mnt/ramdisk/book/redo02------------------------------------
7a797861
<16 bytes per line>
BBED> assign chkval_kcbh=0x01f0
ub2 chkval_kcbh @16 0x01f0
SCOTT@book> alter system dump logfile '/mnt/ramdisk/book/redo02.log' validate;
System altered.
5.補充:
--//xor.sh腳本可以從這裡下載: http://blog.itpub.net/267265/viewspace-2134945/
--//我腳本裡面還使用logic.bc,可以wget http://phodd.net/gnu-bc/code/logic.bc
--//http://phodd.net/gnu-bc/index.html#logic
