ogg getupdatebefores参数对于dml的增删改有哪些影响。
首先部署ogg要开启最小补充日志
SQL> select supplemental_log_data_min from v$database;
SUPPLEME
--------
YES
查看数据库是否开启全列补充日志
SQL> select supplemental_log_data_all from v$database;
SUP
---
NO
ogg搭建这里不做解释(抽取的参数文件里加getupdatebefores)。新建一个表
SQL> create table jianpan as select object_id,object_name from dba_objects where rownum=0;
Table created.
没有开启表级全列补充日志
SQL> break on name on table_name on always on generated
SQL> col name format a20
SQL> col table_name for a19
SQL> col column_name for a11
SQL> select
2 g.log_group_name name,
3 g.owner||','||g.table_name table_name,
4 g.always,
5 g.generated,
6 c.column_name
7 from dba_log_groups g,dba_log_group_columns c
8 where
9 g.owner=c.owner(+) and g.log_group_name=c.log_group_name(+) and
10 g.table_name=c.table_name(+) and
11 g.table_name='JIANPAN' and g.owner='JINLIAN'
12 order by 1,2,3,4
13 /
NAME TABLE_NAME ALWAYS GENERATED COLUMN_NAME
-------------------- ------------------- ----------- -------------- -----------
GGS_88676 JINLIAN,JIANPAN ALWAYS USER NAME OBJECT_NAME
OBJECT_ID
表级如果开启标识关键字的补充日志generated 列显示GENERATED NAME,上面显示未开启。
查询此时数据文件
[oracle@og1 dirdat]$ ls -l
total 12
-rw-r----- 1 oracle oinstall 1082 Jan 9 05:52 t1000013
-rw-r----- 1 oracle oinstall 1022 Jan 9 06:46 t1000014
让抽取强制产生新的trial文件
GGSCI (og1) 37> stop extts
Sending STOP request to EXTRACT EXTTS ...
Request processed.
GGSCI (og1) 38> alter extts etrollover
2018-01-09 07:09:33 INFO OGG-01520 Rollover performed. For each affected output trail of Version 10 or higher format, after starting the source extract, issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file; it will not happen automatically.
EXTRACT altered.
GGSCI (og1) 39> start extts
Sending START request to MANAGER ...
EXTRACT EXTTS starting
插入数据测试
SQL> insert into jianpan select object_id,object_name from dba_objects where object_id<1000;
995 rows created.
SQL> commit;
Commit complete.
[oracle@og1 dirdat]$ ls -l
total 136
-rw-r----- 1 oracle oinstall 1082 Jan 9 05:52 t1000013
-rw-r----- 1 oracle oinstall 129092 Jan 9 06:54 t1000014
使用logdump看只有after image
Logdump 56 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 24 (x0018) IO Time : 2018/01/09 06:53:57.000.000
IOType : 5 (x05) OrigNode : 255 (xff)
TransInd : . (x01) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 102 AuditPos : 7528976
Continued : N (x00) RecCount : 1 (x01)
2018/01/09 06:53:57.000.000 Insert Len 24 RBA 5445
Name: JINLIAN.JIANPAN
After Image: Partition 4 G m
0000 0006 0000 0002 3339 0001 000a 0000 0006 495f | ........39........I_
4f42 4a34 | OBJ4
Column 0 (x0000), Len 6 (x0006)
0000 0002 3339 | ....39
Column 1 (x0001), Len 10 (x000a)
0000 0006 495f 4f42 4a34 | ....I_OBJ4
停止抽取,强制产生新的trail文件,删除几行后使用logdump查看,删除是会取前映像
2018/01/09 07:03:38.000.000 Delete Len 30 RBA 1655
Name: JINLIAN.JIANPAN
Before Image: Partition 4 G m
0000 0008 0000 0004 3132 3937 0001 000e 0000 000a | ........1297........
5359 535f 4642 415f 4641 | SYS_FBA_FA
Column 0 (x0000), Len 8 (x0008)
0000 0004 3132 3937 | ....1297
Column 1 (x0001), Len 14 (x000e)
0000 000a 5359 535f 4642 415f 4641 | ....SYS_FBA_FA
停止抽取,强制产生新的trail文件,更新几行后使用logdump查看,更新也是会取前映像
2018/01/09 07:09:59.000.000 GGSPKUpdate Len 63 RBA 1179
Name: JINLIAN.JIANPAN
After Image: Partition 4 G m
0027 0000 0007 0000 0003 3333 3200 0100 1800 0000 | .'........332.......
1449 5f44 4952 2451 5549 4553 4345 5f53 5441 5455 | .I_DIR$QUIESCE_STATU
5300 0000 0700 0000 0333 3332 0001 0007 0000 0003 | S........332........
5858 58 | XXX
Before Image Len 41 (x00000029)
KeyLen 39 (x00000027)
KeyCol 0 (x0000), Len 7 (x0007)
0000 0003 3333 32 | ....332
KeyCol 1 (x0001), Len 24 (x0018)
0000 0014 495f 4449 5224 5155 4945 5343 455f 5354 | ....I_DIR$QUIESCE_ST
4154 5553 | ATUS
由于我这个表是视图dba_objects复制来的,所以表上没有主键、外键、唯一键、非空等任何约束,而我在update只更新一个字段而before image将所有字段都取出来,是因为add trandata时由于没有主键,所有列作为主键列的原因。而如果表指定某列做主键列,那更新主键列ogg只会抽取主键列更新前的旧值,其他列不会抽取;更新非主键列,那ogg会抽取更新列和主键列的前映像。如果使用fetchcols(*)参数,trail文件的before image里只有更新的列和主键列但after image会包含所有列。(上述都不考虑外键等其他约束)