客户ogg要实现同步10天之内的删除操作,10天之外的操作复制端不进行delete
CREATE TABLE ogg2.ogg_test6 (id NUMBER PRIMARY KEY,logdat VARCHAR(20)); |
GGSCI (enmotech) 9> add trandata ogg1.ogg_test6 , COLS (id , logdat)
2015-03-12 21:21:45 WARNING OGG-00706 Failed to add supplemental log group on table OGG1.OGG_TEST6 due to ORA-00957: duplicate column name SQL ALTER TABLE "OGG1"."OGG_TEST6" ADD SUPPLEMENTAL LOG GROUP "GGS_23261" ("ID","ID","LOGDAT") ALWAYS /* GOLDENGATE_DDL_REPLICATION */.
--这里是因为表存在主键,所以在add trandata只需要增加额外列就行
GGSCI (enmotech) 10> add trandata ogg1.ogg_test6 , COLS ( logdat)
Logging of supplemental redo data enabled for table OGG1.OGG_TEST6.
GGSCI (enmotech) 11> info trandata ogg1.ogg_test6
Logging of supplemental redo log data is enabled for table OGG1.OGG_TEST6.
Columns supplementally logged for table OGG1.OGG_TEST6: LOGDAT, ID.
EXTRACT
table ogg1.ogg_test6 , keycols (id ,logdat);
REPLICAT
map ogg1.ogg_test6, target ogg2.ogg_test6 , keycols (id ,logdat) , filter( ON DELETE , @DATEDIFF ('DD' , @DATE('YYYY-MM-DD' , 'YYYYMMDD' , logdat ) ,@DATE ( 'YYYY-MM-DD', 'YYYY-MM-DD HH:MI:SS', @DATENOW() ) ) < 10 );
以上添加keycols (id ,logdat) 的原因是,如果表上存在主键,ogg在delete、update 操作时会进行压缩抽取,简单的说就是只抽取主键和更新键列
所以在delete在应用的时候就成了delete from xxx where 主键=xx,没有办法利用到logdat,所以增加keycols ,这样保证了ogg在抽取的时候可以捕获到id,logdat列
logdump 分析
keycols(id )
Logdump 218 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: B (x42)
RecLength : 10 (x000a) IO Time : 2015/03/12 21:16:05.000.000
IOType : 3 (x03) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 792 AuditPos : 1478160
Continued : N (x00) RecCount : 1 (x01)
2015/03/12 21:16:05.000.000 Delete Len 10 RBA 2452
Name: OGG1.OGG_TEST6
Before Image: Partition 4 G s
0000 0006 0000 0002 3137 | ........17
Column 0 (x0000), Len 6 (x0006)
0000 0002 3137 | ....17
keycols(id,logdat )
Logdump 277 >n
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: B (x42)
RecLength : 25 (x0019) IO Time : 2015/03/12 21:33:42.000.000
IOType : 3 (x03) OrigNode : 255 (xff)
TransInd : . (x00) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 792 AuditPos : 1596432
Continued : N (x00) RecCount : 1 (x01)
2015/03/12 21:33:42.000.000 Delete Len 25 RBA 3151
Name: OGG1.OGG_TEST6
Before Image: Partition 4 G b
0000 0005 0000 0001 3100 0100 0c00 0000 0832 3031 | ........1........201
3530 3331 32 | 50312
Column 0 (x0000), Len 5 (x0005)
0000 0001 31 | ....1
Column 1 (x0001), Len 12 (x000c)
0000 0008 3230 3135 3033 3132 | ....20150312 |
原始数据
SQL> SELECT * FROM ogg1.ogg_test6;
ID LOGDAT
---------- --------------------
1 20150312
2 20150311
3 20150310
4 20150309
5 20150308
6 20150307
7 20150306
8 20150305
9 20150304
10 20150303
11 20150302
12 20150301
13 20150301
13 ROWS selected.
SQL> SELECT * FROM ogg2.ogg_test6;
ID LOGDAT DIFF
---------- -------------------- ----------
1 20150312
2 20150311
3 20150310
4 20150309
5 20150308
6 20150307
7 20150306
8 20150305
9 20150304
10 20150303
11 20150302
12 20150301
13 20150301
13 ROWS selected.
id 1,2,3都是10天之内的数据
DELETE FROM ogg1.ogg_test6 WHERE id IN (1,2,3);
SQL> DELETE FROM ogg1.ogg_test6 WHERE id IN (1,2,3);
3 ROWS deleted.
SQL> commit;
Commit complete.
SQL> SELECT * FROM ogg1.ogg_test6;
ID LOGDAT
---------- --------------------
4 20150309
5 20150308
6 20150307
7 20150306
8 20150305
9 20150304
10 20150303
11 20150302
12 20150301
13 20150301
10 ROWS selected.
SQL> SELECT * FROM ogg2.ogg_test6;
ID LOGDAT DIFF
---------- -------------------- ----------
4 20150309
5 20150308
6 20150307
7 20150306
8 20150305
9 20150304
10 20150303
11 20150302
12 20150301
13 20150301
10 ROWS selected.
ID 12,13 都是10天之外的数据
DELETE FROM ogg1.ogg_test6 WHERE id IN (12,13);
SQL> DELETE FROM ogg1.ogg_test6 WHERE id IN (12,13);
2 ROWS deleted.
SQL> commit;
Commit complete.
SQL> SELECT * FROM ogg1.ogg_test6;
ID LOGDAT
---------- --------------------
4 20150309
5 20150308
6 20150307
7 20150306
8 20150305
9 20150304
10 20150303
11 20150302
8 ROWS selected.
SQL> SELECT * FROM ogg2.ogg_test6;
ID LOGDAT DIFF
---------- -------------------- ----------
4 20150309
5 20150308
6 20150307
7 20150306
8 20150305
9 20150304
10 20150303
11 20150302
12 20150301
13 20150301
10 ROWS selected. |
