hex格式redo log解析

显示全部楼层 · 2015-6-12 15:43:00

不知道现在还有没有人关注这个~

千问 · 2015-6-12 15:43:00

想过写一个自己的LogMiner，
Oracle底层技术已经提供相关的支持，貌似意义不大。

千问 · 2015-6-12 15:43:00

wqkabc 发表于 2015-6-12 19:46
想过写一个自己的LogMiner，
Oracle底层技术已经提供相关的支持，貌似意义不大。
stream,DG,CDC,LogMiner 都能用，想实现多点数据双向同步，就是不好用，开发上也有很多限制

千问 · 2015-6-12 15:43:00

自己尝试找资料做了些实验，都是些零零散散的：
1、文件头（first 80 B）（前两个块）第一个块（file header）：文件块大小(21B)和块的总数(25B)，第二个块（redo log header）：顺序号，块号，文件版本号，兼容版本号，数据库ID号，数据库名，控制顺序号，文件大小，文件序号，文件块大小(21B)，文件类型等redo log文件基本信息；
2、文件体

从第三个块开始属于文件体，一条redo可能跨越多个块，一个块可能包含多个redo

块头：redo log顺序号（sequence），块号（blocknumber），块更新时间（write time），第一个redo记录的位置（offset），校验和（checksum）

01 22 00 00 01 00 00 00 40 00 00 00 00 80 69 E7

----- ----------- ----------- -------

signature：0122，所有blcokheader都以0x0122开始

block number：01 00 00 00

sequence：40 00 00 00

offset：00

checksum：69 E7
块数据：=redo记录=事物信息+物理操作信息；由记录头、change项（change头+change索引表[索引表长度+change项长度]+change数据体）组成；

A8 01 00 00 0D

----- --

redo记录字节数0x01A8

VLD:0X0D OPCODE位置在0x44; objid在 22 bytes past opcode;字节数（可以推算出字段个数12bytes=3(字段个数)*2+2*2（固定两个）+2（本身））: 2bytes from objid;op位置 2bytes from 字段 ; ver位置: 3bytes from 字段 ;

op : 1->72 2->64 17->120

数字存储：1~99 are considered as units，stored as 2 bytes（first：is an indicator C1;second:数值本身+1）如数字1对应0xC102

100~9999 indicator：C2,stored as 2/3bytes;100=0xC202 101=0xC20202

10000~999999 indicator:C3, ;10000=0xC302,10001=0xC3020102

1000000~99999999 indicator:C4;

千问 · 2015-6-12 15:43:00

后来找到一个比较详细的介绍，结合dessecting-the-redo-logs：

File Header
00 22 00 00 00 00 C0 FF 00 00 00 00 00 00 00 00
67 C8 00 00 00 02 00 00 00 90 01 00 7D 7C 7B 7A
A0 81 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 22:文件类型，日志文件
00 02：块大小，0x0200=512B
00 90 01 00：块数，0x00019000=2304+4096=6400
7D 7C 7B 7A：MAGIC,用于快速鉴别是否是oracle file
Block Header
01 22 00 00 01 00 00 00 66 00 00 00 00 80 4C AA
01 22：signature，每个块头都是以0122开始
01000000：块号，这里表示第一个块
66000000：sequence，0x66=102，即v$log中的sequence#字段
00：offset
4C AA:checksum，校验块是否有问题
Redo log header
01 22 00 00 01 00 00 00 66 00 00 00 00 80 4C AA
00 00 00 00 00 05 20 0A F4 5C 89 0E 53 54 52 4D
34 00 00 00 25 97 00 00 00 90 01 00 00 02 00 00
03 00 02 00 F4 78 89 0E 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 54 68 72 65
61 64 20 30 30 30 31 2C 20 53 65 71 23 20 30 30
30 30 30 30 30 31 30 32 2C 20 53 43 4E 20 30 78
30 30 30 30 30 30 35 33 35 65 32 61 2D 30 78 66
66 66 66 66 66 66 66 66 66 66 66 00 FF FF FF FF
76 D4 23 34 A9 EC 05 00 00 00 00 00 01 00 00 00
01 00 00 00 2A 5E 53 00 00 00 00 00 59 0A 93 34
FF FF FF FF FF FF 00 00 00 00 00 00 01 00 00 00
A9 EC 05 00 00 00 00 00 76 D4 23 34 2A 5E 53 00
00 00 00 00 59 0A 93 34 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 02 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 01 00 00 00
00 00 00 00 38 92 B9 2A 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 05 20 0A: 数据库版本，
F4 5C 89 0E：数据库ID,
0x0E895CF4=4+15*16+12*16+5*16*16+9*16*16*16+8*16*16*16*16+14*16*16*16*16*16
=243883252
53 54 52 4D 34：SID,=STRM4
25 97: Control sequence, 控制文件序列号
03 00：file number
02 00：file type
F4 78 89 0E：Activation ID
【54 68 72 65
61 64 20 30 30 30 31 2C 20 53 65 71 23 20 30 30
30 30 30 30 30 31 30 32 2C 20 53 43 4E 20 30 78
30 30 30 30 30 30 35 33 35 65 32 61 2D 30 78 66
66 66 66 66 66 66 66 66 66 66 66】：描述
=Thread 0001 , SCN 0x000000535e2a-0xfffffffffff
FF FF FF FF：NAB
01:HWS
2A 5E 53 00:low SCN，0x535E2A= 5463594
59 0A 93 34:LOW SCN TIMESTAMP,0x34930A59=882051673
FF FF FF FF:NEXT SCN
00 00 00 00:NEXT SCN TIMESTAMP
A9 EC 05 00:ENABLED SCN
76 D4 23 34:ENABLE SCN TIMESTAMP
2A 5E 53 00:THREAD CLOSED SCN
59 0A 93 34:THREAD CLOSED SCN TIMESTAMP
INSERT ENTRY
01 22 00 00 F0 11 01 00 66 00 00 00 10 80 71 49
44 02 00 00 05 06 00 00 F7 DF 53 00 01 00 04 C3
00 00 00 00 80 03 C2 05 00 00 01 00 02 00 00 00
02 00 00 00 00 03 C2 05 F7 DF 53 00 00 00 80 01
80 01 80 01 80 01 80 01 80 01 80 01 80 01 80 04
D1 C0 93 34 05 02 1B 00 02 00 FF FF 59 00 80 00
BC DF 53 00 00 00 5F BD 01 00 FF FF 04 00 20 00
05 00 00 00 6B 07 00 00 86 09 80 00 19 04 1D 00
12 00 84 00 00 00 03 00 00 00 00 00 00 00 00 00
05 01 1C 00 02 00 FF FF 86 09 80 00 BB DF 53 00
00 00 00 00 02 00 FF FF 0C 00 14 00 48 00 08 00
14 00 14 00 84 00 CA 0A 12 00 00 00 06 00 05 00
6B 07 00 00 19 04 1D 00 C2 D0 00 00 C2 D0 00 00
04 00 00 00 00 00 00 00 0B 01 05 00 08 04 01 00
86 09 80 00 19 04 1B 00 BF D8 53 00 00 00 00 00
D7 D8 53 00 00 00 00 00 78 D8 FF B2 FF FF FF FF
FF FF 00 00 7F 09 80 00 00 00 00 00 39 00 00 00
03 01 00 00 00 00 00 00 3F 00 00 01 3B 00 00 01
FA 12 23 01 01 00 00 00 00 00 00 00 02 1C 00 00
02 00 00 00 01 00 00 00 00 00 00 00 00 00 00 00
0B 02 01 00 04 00 00 00 3F 00 00 01 06 EA 24 00
00 00 00 00 01 00 C2 D0 0A 00 18 00 31 00 02 00
05 00 00 00 01 01 00 00 00 00 00 00 06 00 05 00
6B 07 00 00 86 09 80 00 19 04 1D 00 3F 00 00 01
3B 00 00 01 FA 12 02 01 01 00 00 00 2C 01 02 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 0C 00 00 00 00 00 00 00 00 01 80 05
C1 06 19 1D 54 65 73 74 33 03 01 80 05 13 00 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 06 FF 7F 1C 00 04 00 05 00 05 00 00 00 0A 00
14 00 0A 00 09 00 0C 00 00 00 02 00 04 00 04 00
78 00 99 24 53 43 4F 54 54 01 80 01 53 43 4F 54
01 22 00 00 F1 11 01 00 66 00 00 00 64 80 18 19
54 00 00 00 7A 68 75 74 69 61 6E 6A 69 61 80 01
46 4F 43 55 53 54 45 43 48 5C 5A 48 55 54 49 41
4E 4A 49 41 5A 48 55 54 49 41 4E 4A 49 41 03 C2
31 32 36 30 3A 35 32 36 38 02 C1 02 70 6C 73 71
6C 64 65 76 2E 65 78 65 00 00 00 00 00 05 20 0A
92 8C 01 00 18 01 00 00 01 06 00 00 F7 DF 53 00
02 00 53 00 00 00 00 00 00 00 00 00 05 01 1C 00
02 00 FF FF 86 09 80 00 F7 DF 53 00 00 00 00 00
01 00 FF FF 0C 00 14 00 18 00 20 00 14 00 03 00
5C 00 44 0A 22 00 00 00 06 00 05 00 6B 07 00 00
19 04 1E 00 C3 D0 00 00 C3 D0 00 00 04 00 00 00
00 00 00 00 0A 16 05 1D 00 00 6B 07 04 01 00 00
00 00 00 00 08 00 2C 00 DB 06 00 00 57 05 80 00
4F 04 1A 00 00 80 00 00 94 F7 50 00 03 02 01 00
43 00 00 01 44 00 00 01 00 00 00 00 00 00 00 00
02 C1 06 01 0A 02 01 00 04 00 00 00 44 00 00 01
94 F7 50 00 00 00 A8 1B 01 02 C3 D0 0A 00 40 00
06 00 03 00 06 00 FF B2 11 01 00 00 00 00 00 00
06 00 05 00 6B 07 00 00 86 09 80 00 19 04 1E 00
00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
00 00 00 00 02 01 01 00 F7 DF 53 00 00 00 00 00
02 02 00 00 94 F7 50 00 02 00 04 00 0D 00 80 01
02 C1 06 01 01 00 00 3F 00 00 03 C2 60 00 00 00
01 2C 00 00 F8 DF 53 00 01 00 03 04 00 00 00 00
04 C3 06 3A 05 04 1B 00 02 00 FF FF 59 00 80 00
F7 DF 53 00 00 00 D0 05 01 00 FF FF 08 00 14 00
10 00 04 00 05 00 00 00 6B 07 00 00 00 00 00 00
09 00 00 00 02 00 00 00 86 09 80 00 19 04 1E 00
02 00 E6 09 00 00 00 00 4F FE 78 55 00 00 00 00
80 00 03 00 44 00 00 00 01 00 00 00 71 87 53 00
02 00 01 00 00 00 00 00 01 00 14 00 0D 16 08 00

10:OFFSET，第一个redo record开始位置位于块的第16个字节
【record header】
44 02 00 00：record长度，0x0244=580，这个record超过一个块大小，截止到上图紫色部分前，紫色部分是上一个record的长度;占4B,偏移量0
05:VLD
00 00：record scn（part 1）;占2B,偏移量6B
F7 DF 53 00：record scn（part 2）,0x53DFF7;占4B,偏移量8B
01 00：sub scn；占2B，偏移量12B
80 01 80 04：record timestamp，每个record header都以timestamp结尾；0x04800180=75497856
(((((75497856-10)/60-5)/60-20)/24+1-15)/31+1-3)/12+1988
01:BLOCK CLASS，占2B，偏移量2B
Block ClassDescription
1Data block
2Sort block
3Save undo block
4Segment header
5Save undo header
6Free list
7Extent map
81st level bitmap block
92nd level bitmap block
103rd level bitmap block
11Bitmap block
12Bitmap index block
13File header block
14Unused
15System undo block
16System undo block
17Undo header
18Undo block
04：AFN，查询试图，这里对应的文件是users01.dbf;占2B，偏移量4B
3F 00 00 01

BA,0x0100003F,占4B，偏移量8B
06 EA 24 00:CHANGE SCN PART2;占4B，偏移量12B
00 00:CHANGE SCN PART1;占2B，偏移量16B
01:CHANGE SEQ;占1B，偏移量20B
00：CHANGE TYPE,占1B，偏移量21B
C2 D0:OBJID,0xD0C2=53442,对应T1表；占2B，偏移量22B
(未完待续。。。)

千问 · 2015-6-12 15:43:00

牛MAN牛发表于 2015-6-15 12:02
stream,DG,CDC,LogMiner 都能用，想实现多点数据双向同步，就是不好用，开发上也有很多限制
各种opcode估计不少于150种吧，你考虑过它的复杂度吗？而且在资料有限的情况下。
可以把你的需求场景和你的做法发过来，交流下呗[email protected]

千问 · 2015-6-12 15:43:00

wqkabc 发表于 2015-6-15 12:29
各种opcode估计不少于150种吧，你考虑过它的复杂度吗？而且在资料有限的情况下。
可以把你的需求场景和 ...
目前还只是个想法而已，我觉得opcode这种可以慢慢来，问题不在这

千问 · 2015-6-12 15:43:00

支持redo log解析研究