本文介绍了如何在GBase 8s中通过逻辑日志恢复数据，特别是针对drop table操作。通过onstat -l找到相关逻辑日志，使用onlog查看详细内容，分析关键字HDELETE确定删除操作，使用oncheck获取表名，并根据日志上下文确定操作时间点，从而实现精确的数据恢复。
GBase 8s如何通过逻辑日志恢复数据
背景
GBase 8s 支持基于时间点的恢复。当由于操作人员失误，或其它人员的恶意操作，执行了破坏数据的操作，可以通过onbar的基于时间点的恢复，抢救出被删除的数据。
以下内容为分析用户执行drop table破坏数据，我们需要分析出用户在何时执行了drop table的操作，以便进行基于精确时间点的恢复，减少数据损失。
其它破坏数据的操作的时间点分析类似。
分析过程
查找当前逻辑日志
使用onstat -l命令，查看flags中包含C标志的文件的uniqid。如下面信息中的uniqid为18。
这个操作是为了减少日志分析的数量，假设用户不小心做了误删除，需要及时确定用户执行操作的精确时间，以方便后续的基于时间点的数据恢复。
说明：如果删除表操作时间不是当前时间，可以根据逻辑日志的备份时间，尽量定位大概的逻辑日志，并进行分析。
[gbasedbt@train ~]$ onstat -l
Your evaluation license will expire on 2022-06-09 12:00:00
On-Line -- Up 09:19:58 -- 1135464 Kbytes
Physical Logging
Buffer bufused bufsize numpages numwrits pages/io
P-2 0 1024 1010 53 19.06
phybegin physize phypos phyused %used
3:53 99400 13971 0 0.00
Logical Logging
Buffer bufused bufsize numrecs numpages numwrits recs/pages pages/io
L-3 0 512 391 121 116 3.2 1.0
Subsystem numrecs Log Space used
OLDRSAM 346 30620
HA 44 1936
DDL 1 348
address number flags uniqid begin size used %used
4713df88 4 F------ 0 2:53 5000 0 0.00
48445970 5 F------ 0 2:5053 5000 0 0.00
484459d8 6 U-B---- 9 2:10053 5000 1262 25.24
48445a40 7 U-B---- 10 2:15053 5000 11 0.22
48445aa8 8 U-B---- 11 2:20053 5000 5 0.10
48445b10 9 U-B---- 12 2:25053 5000 28 0.56
48445b78 10 U-B---- 13 2:30053 5000 5 0.10
48445be0 11 U-B---- 14 2:35053 5000 14 0.28
48445c48 12 U-B---- 15 2:40053 5000 5 0.10
48445cb0 13 U-B---- 16 2:45053 5000 14 0.28
48445d18 14 U------ 17 2:50053 5000 68 1.36
48445d80 15 U---C-L 18 2:55053 5000 53 1.06
48445de8 16 F------ 0 2:60053 5000 0 0.00
48445e50 17 F------ 0 2:65053 5000 0 0.00
48445eb8 18 F------ 0 2:70053 5000 0 0.00
48445f20 19 F------ 0 2:75053 5000 0 0.00
48445f88 20 F------ 0 2:80053 5000 0 0.00
46f1cf30 21 F------ 0 2:85053 5000 0 0.00
46f1cf98 22 F------ 0 2:90053 5000 0 0.00
46febf30 23 F------ 0 2:95053 5000 0 0.00
20 active, 20 total
[gbasedbt@train ~]$
使用onlog命令，查看当前日志的详细内容
onlog -l -n 18 > onlog-18
此时，onlog-18文件中的内容，即为要分析的逻辑日志文件对应的内容。
分析drop table数据
对于drop table操作，数据库对应的行为：
删除表的记录操作
删除表定义（删除系统表的记录操作）
因此在日志中搜索关键字HDELETE
[gbasedbt@train ~]$ cat onlog-18 | grep HDELETE
addr len type xid id link
16050 80 HDELETE 41 0 16018 100193 101 13
160a0 80 HDELETE 41 0 16050 100193 102 14
160f0 80 HDELETE 41 0 160a0 100193 103 13
16140 80 HDELETE 41 0 160f0 100193 104 15
2d050 196 HDELETE 41 0 2d018 80004b 34f 129
2d1b8 108 HDELETE 41 0 2d178 80004c 547 42
2d2a8 108 HDELETE 41 0 2d268 80004c 548 44
2d398 144 HDELETE 41 0 2d358 80004e 353 77
使用oncheck确定删除操作对应的表名称
说明：上面数据为十六进制，在用oncheck分析时，需要加上0x。
根据上面的结果，确定4个表的名称。
oncheck -pt 0x100193
oncheck -pt 0x80004b
oncheck -pt 0x80004c
oncheck -pt 0x80004e
分析发现，日志中存在systables表的删除记录操作，说明存在drop table操作。
systables 对应的为 0x80004b
以下操作为如何分析表名称
[gbasedbt@train ~]$ oncheck -pt 0x80004b
Your evaluation license will expire on 2022-06-09 12:00:00
TBLspace Report for mydb:gbasedbt.systables
Physical Address 8:2320
Creation date 07/08/2021 18:31:01
TBLspace Flags 906 Row Locking
System Catalogue
TBLspace contains VARCHARS
TBLspace use 4 bit bit-maps
Maximum row size 505
Number of special columns 3
Number of keys 2
Number of extents 1
Current serial value 102
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 16
First extent size 4
Next extent size 4
Number of pages allocated 4
Number of pages used 4
Number of data pages 1
Number of rows 78
Partition partnum 8388683
Partition lockid 8388683
Extents
Logical Page Physical Page Size Physical Pages
0 8:4792 4 32
[gbasedbt@train ~]$
[gbasedbt@train ~]$ oncheck -pt 0x80004c
Your evaluation license will expire on 2022-06-09 12:00:00
TBLspace Report for mydb:gbasedbt.syscolumns
Physical Address 8:2328
Creation date 07/08/2021 18:31:01
TBLspace Flags 906 Row Locking
System Catalogue
TBLspace contains VARCHARS
TBLspace use 4 bit bit-maps
Maximum row size 162
Number of special columns 1
Number of keys 2
Number of extents 1
Current serial value 1
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 16
First extent size 4
Next extent size 8
Number of pages allocated 8
Number of pages used 6
Number of data pages 3
Number of rows 580
Partition partnum 8388684
Partition lockid 8388684
Extents
Logical Page Physical Page Size Physical Pages
0 8:4824 8 64
[gbasedbt@train ~]$
[gbasedbt@train ~]$ oncheck -pt 0x80004e
Your evaluation license will expire on 2022-06-09 12:00:00
TBLspace Report for mydb:gbasedbt.systabauth
Physical Address 8:2344
Creation date 07/08/2021 18:31:01
TBLspace Flags 806 Row Locking
System Catalogue
TBLspace use 4 bit bit-maps
Maximum row size 77
Number of special columns 0
Number of keys 2
Number of extents 1
Current serial value 1
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 16
First extent size 4
Next extent size 4
Number of pages allocated 4
Number of pages used 4
Number of data pages 1
Number of rows 81
Partition partnum 8388686
Partition lockid 8388686
Extents
Logical Page Physical Page Size Physical Pages
0 8:4952 4 32
[gbasedbt@train ~]$
[gbasedbt@train ~]$ oncheck -pt 0x100193
Your evaluation license will expire on 2022-06-09 12:00:00
TBLspace Report for sysmaster:gbasedbt.t_city
Physical Address 1:50624
Creation date 07/09/2021 14:49:07
TBLspace Flags 902 Row Locking
TBLspace contains VARCHARS
TBLspace use 4 bit bit-maps
Maximum row size 26
Number of special columns 1
Number of keys 0
Number of extents 1
Current serial value 1
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 1
Pagesize (k) 2
First extent size 8
Next extent size 8
Number of pages allocated 8
Number of pages used 2
Number of data pages 1
Number of rows 4
Partition partnum 1048979
Partition lockid 1048979
Extents
Logical Page Physical Page Size Physical Pages
0 1:263 8 8
[gbasedbt@train ~]$
分析删除表的名称
提取分析数据。
本例中，用户删除了t_user表。需要查找80004b和HDELETE附近中是否有包含t_user的信息。
[gbasedbt@train ~]$ grep -A 13 80004b onlog-18
6050 48 UNIQID 41 0 6018 80004b 102
30000000 00001100 10000000 00000000 0....... ........
00000000 00000000 29000000 18600000 ........ )....`..
98240600 4b008000 4b008000 66000000 .$..K... K...f...
6080 392 BLDCL 41 0 6050 800090 8 32 26 0 t_user
88010000 00002000 10000000 00000000 ...... . ........
00000000 00000000 29000000 50600000 ........ )...P`..
9f240600 90008000 90008000 08000000 .$...... ........
20000000 00400000 1a000000 02000000 ....@.. ........
6d796462 00000000 00000000 00000000 mydb.... ........
00000000 00000000 00000000 00000000 ........ ........
00000000 00000000 00000000 00000000 ........ ........
00000000 00000000 00000000 00000000 ........ ........
00000000 00000000 00000000 00000000 ........ ........
--
6424 196 HINSERT 41 0 63e4 80004b 34f 129
c4000000 00002800 12010000 00000000 ......(. ........
00000000 00000000 29000000 e4630000 ........ )....c..
b1240600 4b008000 4b008000 4f030000 .$..K... K...O...
81000000 00000000 00000000 80000000 ........ ........
0006745f 75736572 67626173 65646274 ..t_user gbasedbt
20202020 20202020 20202020 20202020
20202020 20202020 00800090 00000065 .......e
0000001a 00020000 00000000 00000000 ........ ........
0000ad61 00650001 54520000 00000000 ...a.e.. TR......
00000000 00100000 00400000 00010000 ........ .@......
01000000 00000000 00000000 40000000 ........ ....@...
00000000 00000000 00000000 00008000 ........ ........
41000000 A...
--
64e8 100 ADDITEM 41 0 6424 80004b 80004b 34f 1 1 40
64000000 00001c00 10000000 00000000 d....... ........
00000000 00000000 29000000 24640000 ........ )...$d..
b2240600 4b008000 4b008000 4b008000 .$..K... K...K...
4f030000 01000000 01002800 0006745f O....... ..(...t_
75736572 67626173 65646274 20202020 usergbas edbt
20202020 20202020 20202020 20202020
20202020
654c 64 ADDITEM 41 0 64e8 80004b 80004b 34f 2 2 4
40000000 00001c00 10000000 00000000 @....... ........
00000000 00000000 29000000 e8640000 ........ )....d..
b4240600 4b008000 4b008000 4b008000 .$..K... K...K...
4f030000 02000000 02000400 80000065 O....... .......e
658c 144 HINSERT 41 0 654c 80004e 353 77
90000000 00002800 12010000 00000000 ......(. ........
00000000 00000000 29000000 4c650000 ........ )...Le..
b7240600 4e008000 4e008000 53030000 .$..N... N...S...
4d000004 00000000 00000000 80000065 M....... .......e
67626173 65646274 20202020 20202020 gbasedbt
20202020 20202020 20202020 20202020
7075626c 69632020 20202020 20202020 public
20202020 20202020 20202020 20202020
--
2d050 196 HDELETE 41 0 2d018 80004b 34f 129
c4000000 00002900 12014000 00000000 ......). ..@.....
00000000 00000000 29000000 18d00200 ........ ).......
79260600 4b008000 4b008000 4f030000 y&..K... K...O...
81000000 00000000 00000000 ffffffff ........ ........
0006745f 75736572 67626173 65646274 ..t_user gbasedbt
20202020 20202020 20202020 20202020
20202020 20202020 00800090 00000065 .......e
0000001a 00020000 00000000 00000000 ........ ........
0000ad61 00650001 54520000 00000000 ...a.e.. TR......
00000000 00100000 00400000 00010000 ........ .@......
01000000 00000000 00000000 40000000 ........ ....@...
00000000 00000000 00000000 00008000 ........ ........
41000000 A...
--
2d114 100 DELITEM 41 0 2d050 80004b 80004b 34f 1 1 40
64000000 00001d00 10000000 00000000 d....... ........
00000000 00000000 29000000 50d00200 ........ )...P...
7d260600 4b008000 4b008000 4b008000 }&..K... K...K...
4f030000 01000000 01002800 0006745f O....... ..(...t_
75736572 67626173 65646274 20202020 usergbas edbt
20202020 20202020 20202020 20202020
20202020
2d178 64 DELITEM 41 0 2d114 80004b 80004b 34f 2 2 4
40000000 00001d00 10000000 00000000 @....... ........
00000000 00000000 29000000 14d10200 ........ ).......
7f260600 4b008000 4b008000 4b008000 .&..K... K...K...
4f030000 02000000 02000400 80000065 O....... .......e
2d1b8 108 HDELETE 41 0 2d178 80004c 547 42
6c000000 00002900 12014000 00000000 l.....). ..@.....
00000000 00000000 29000000 78d10200 ........ )...x...
81260600 4c008000 4c008000 47050000 .&..L... L...G...
2a000000 00000000 00000000 ffffffff *....... ........
0008665f 75736572 69640000 00650001 ..f_user id...e..
00020000 00048000 00008000 00000000 ........ ........
00000000 00000000 00000000 ........ ....
[gbasedbt@train ~]$
查找包含HDELETE和80004b的数据，此数据为对mydb:gbasedbt.systables表进行删除操作的信息。如下面信息中，对表systables的删除操作数据中有t_user信息，可以确定该部分内容为drop table t_user对应的日志数据。根据前面的2d050，在日志中查找该部分的前后文内容。
2d050 196 HDELETE 41 0 2d018 80004b 34f 129
c4000000 00002900 12014000 00000000 ......). ..@.....
00000000 00000000 29000000 18d00200 ........ ).......
79260600 4b008000 4b008000 4f030000 y&..K... K...O...
81000000 00000000 00000000 ffffffff ........ ........
0006745f 75736572 67626173 65646274 ..t_user gbasedbt
20202020 20202020 20202020 20202020
20202020 20202020 00800090 00000065 .......e
0000001a 00020000 00000000 00000000 ........ ........
0000ad61 00650001 54520000 00000000 ...a.e.. TR......
00000000 00100000 00400000 00010000 ........ .@......
01000000 00000000 00000000 40000000 ........ ....@...
00000000 00000000 00000000 00008000 ........ ........
41000000 A...
查找上下文，确定该操作对应事务的开始时间
确定一个事务的开始时间为07/09/2021 16:49:18 ，包含上面分析的数据内容。可以确定在该时间点，用户执行了drop table t_user操作。
addr len type xid id link
2d018 56 BEGIN 41 18 0 07/09/2021 16:49:18 54 gbasedbt
38000000 12000100 00000000 00000000 8....... ........
00000000 00000000 29000000 00000000 ........ ).......
7a260600 00000000 8e0de860 00000000 z&...... ...`....
e9030000 36000000 ....6...
2d050 196 HDELETE 41 0 2d018 80004b 34f 129
c4000000 00002900 12014000 00000000 ......). ..@.....
00000000 00000000 29000000 18d00200 ........ ).......
79260600 4b008000 4b008000 4f030000 y&..K... K...O...
81000000 00000000 00000000 ffffffff ........ ........
0006745f 75736572 67626173 65646274 ..t_user gbasedbt
20202020 20202020 20202020 20202020
20202020 20202020 00800090 00000065 .......e
0000001a 00020000 00000000 00000000 ........ ........
0000ad61 00650001 54520000 00000000 ...a.e.. TR......
00000000 00100000 00400000 00010000 ........ .@......
01000000 00000000 00000000 40000000 ........ ....@...
00000000 00000000 00000000 00008000 ........ ........
41000000 A...
addr len type xid id link
2d114 100 DELITEM 41 0 2d050 80004b 80004b 34f 1 1 40
64000000 00001d00 10000000 00000000 d....... ........
00000000 00000000 29000000 50d00200 ........ )...P...
7d260600 4b008000 4b008000 4b008000 }&..K... K...K...
4f030000 01000000 01002800 0006745f O....... ..(...t_
75736572 67626173 65646274 20202020 usergbas edbt
20202020 20202020 20202020 20202020
20202020
2d178 64 DELITEM 41 0 2d114 80004b 80004b 34f 2 2 4
40000000 00001d00 10000000 00000000 @....... ........
00000000 00000000 29000000 14d10200 ........ ).......
7f260600 4b008000 4b008000 4b008000 .&..K... K...K...
4f030000 02000000 02000400 80000065 O....... .......e
2d1b8 108 HDELETE 41 0 2d178 80004c 547 42
6c000000 00002900 12014000 00000000 l.....). ..@.....
00000000 00000000 29000000 78d10200 ........ )...x...
81260600 4c008000 4c008000 47050000 .&..L... L...G...
2a000000 00000000 00000000 ffffffff *....... ........
0008665f 75736572 69640000 00650001 ..f_user id...e..
00020000 00048000 00008000 00000000 ........ ........
00000000 00000000 00000000 ........ ....
addr len type xid id link
2d224 68 DELITEM 41 0 2d1b8 80004c 80004c 547 1 1 6
44000000 00001d00 10000000 00000000 D....... ........
00000000 00000000 29000000 b8d10200 ........ ).......
85260600 4c008000 4c008000 4c008000 .&..L... L...L...
47050000 01000000 01000600 80000065 G....... .......e
80016572 ..er
2d268 64 DELITEM 41 0 2d224 80004c 80004c 547 2 2 4
40000000 00001d00 10000000 00000000 @....... ........
00000000 00000000 29000000 24d20200 ........ )...$...
87260600 4c008000 4c008000 4c008000 .&..L... L...L...
47050000 02000000 02000400 80000000 G....... ........
2d2a8 108 HDELETE 41 0 2d268 80004c 548 44
6c000000 00002900 12014000 00000000 l.....). ..@.....
00000000 00000000 29000000 68d20200 ........ )...h...
89260600 4c008000 4c008000 48050000 .&..L... L...H...
2c000000 00000000 00000000 ffffffff ,....... ........
000a665f 75737265 6e616d65 00000065 ..f_usre name...e
0002000d 00000014 80000000 80000000 ........ ........
00000000 00000000 00000000 ........ ....
addr len type xid id link
2d314 68 DELITEM 41 0 2d2a8 80004c 80004c 548 1 1 6
44000000 00001d00 10000000 00000000 D....... ........
00000000 00000000 29000000 a8d20200 ........ ).......
89260600 4c008000 4c008000 4c008000 .&..L... L...L...
48050000 01000000 01000600 80000065 H....... .......e
80026572 ..er
2d358 64 DELITEM 41 0 2d314 80004c 80004c 548 2 2 4
40000000 00001d00 10000000 00000000 @....... ........
00000000 00000000 29000000 14d30200 ........ ).......
8a260600 4c008000 4c008000 4c008000 .&..L... L...L...
48050000 02000000 02000400 80000000 H....... ........
2d398 144 HDELETE 41 0 2d358 80004e 353 77
90000000 00002900 12014000 00000000 ......). ..@.....
00000000 00000000 29000000 58d30200 ........ )...X...
8c260600 4e008000 4e008000 53030000 .&..N... N...S...
4d000000 00000000 00000000 ffffffff M....... ........
67626173 65646274 20202020 20202020 gbasedbt
20202020 20202020 20202020 20202020
7075626c 69632020 20202020 20202020 public
20202020 20202020 20202020 20202020
00000065 73752d69 64782d2d 2d000000 ...esu-i dx---...
addr len type xid id link
2d428 128 DELITEM 41 0 2d398 80004e 80004e 353 1 1 68
80000000 00001d00 10000000 00000000 ........ ........
00000000 00000000 29000000 98d30200 ........ ).......
90260600 4e008000 4e008000 4e008000 .&..N... N...N...
53030000 01000000 01004400 80000065 S....... ..D....e
67626173 65646274 20202020 20202020 gbasedbt
20202020 20202020 20202020 20202020
7075626c 69632020 20202020 20202020 public
20202020 20202020 20202020 20202020
2d4a8 96 DELITEM 41 0 2d428 80004e 80004e 353 2 2 36
60000000 00001d00 10000000 00000000 `....... ........
00000000 00000000 29000000 28d40200 ........ )...(...
92260600 4e008000 4e008000 4e008000 .&..N... N...N...
53030000 02000000 02002400 80000065 S....... ..$....e
7075626c 69632020 20202020 20202020 public
20202020 20202020 20202020 20202020
2d508 44 PERASE 41 0 2d4a8 800090
2c000000 00003700 10000000 00000000 ,.....7. ........
00000000 00000000 29000000 a8d40200 ........ ).......
93260600 90008000 90008000 .&...... ....
addr len type xid id link
2d534 56 BEGCOM 41 0 2d508
38000000 00000400 10000000 00000000 8....... ........
00000000 00000000 29000000 08d50200 ........ ).......
93260600 00000000 8e0de860 4e008000 .&...... ...`N...
53030000 02000000 S.......
2e018 44 ERASE 41 0 2d534 800090
2c000000 00000d00 10000000 00000000 ,....... ........
00000000 00000000 29000000 34d50200 ........ )...4...
96260600 90008000 90008000 .&...... ....
2e044 56 COMMIT 41 0 2e018 07/09/2021 16:49:18
38000000 00000200 10000000 00000000 8....... ........
00000000 00000000 29000000 18e00200 ........ ).......
9b260600 00000000 8e0de860 4e008000 .&...... ...`N...
8e0de860 00000000 ...`....
分析结束。
日志详情
下面内容是当前日志的详细信息
GBase Database Server Logical Log display
Software Serial Number AAA#B000000
Copyright General Data Corporation 2014, 2016 All rights reserved.
log uniqid: 18.
addr len type xid id link
18 44 CKPOINT 1 18 0 0
2c000000 12004200 10000000 00000000 ,.....B. ........
00000000 00000000 01000000 00000000 ........ ........
66240600 00000000 00000000 f$...... ....
1018 44 HA 1 0 18 CKPTEND 1322
2c000000 00000200 16000000 06000000 ,....... ........
00000000 00000000 01000000 18000000 ........ ........
6f240600 00000000 2a050000 o$...... *...
1044 40 SYNC 1 0 1018
28000000 00000000 16000000 00000000 (....... ........
00000000 00000000 01000000 18100000 ........ ........
6f240600 00000000 o$......
2018 44 CKPOINT 1 18 0 0
2c000000 12004200 10000000 00000000 ,.....B. ........
00000000 00000000 01000000 00000000 ........ ........
74240600 00000000 00000000 t$...... ....
3018 44 HA 1 0 2018 CKPTEND 1323
2c000000 00000200 16000000 06000000 ,....... ........
00000000 00000000 01000000 18200000 ........ ..... ..
7b240600 00000000 2b050000 {
$...... +...
addr len type xid id link
3044 40 SYNC 1 0 3018
28000000 00000000 16000000 00000000 (....... ........
00000000 00000000 01000000 18300000 ........ .....0..
7b240600 00000000 {
$......
4018 44 CKPOINT 1 18 0 0
2c000000 12004200 10000000 00000000 ,.....B. ........
00000000 00000000 01000000 00000000 ........ ........
8d240600 00000000 00000000 .$...... ....
5018 44 HA 1 0 4018 CKPTEND 1324
2c000000 00000200 16000000 06000000 ,....... ........
00000000 00000000 01000000 18400000 ........ .....@..
94240600 00000000 2c050000 .$...... ,...
5044 40 SYNC 1 0 5018
28000000 00000000 16000000 00000000 (....... ........
00000000 00000000 01000000 18500000 ........ .....P..
94240600 00000000 .$......
6018 56 BEGIN 41 18 0 07/09/2021 14:14:25 46 gbasedbt
38000000 12000100 00000000 00000000 8....... ........
00000000 00000000 29000000 00000000 ........ ).......
98240600 00000000 41e9e760 00000000 .$...... A
最低0.47元/天 解锁文章
1177
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
