本文详细介绍了如何使用OracleGoldenGate配置抽取进程和应用进程，以实现对HIS表的逻辑同步配置改为仅处理删除操作。在源端停止相关进程后，清除HIS表中的非DELETE操作数据，然后重新启动进程并验证同步效果，确保只有删除操作被记录在HIS表中。
文章目录
前言
这里是后续！！！前一章写的太多，编辑器卡死了，只能再写一篇文章补充啦！！！！
前一篇文章地址：【大数据实时数据同步】OGG多路映射同步原表&审计表&只存删除数据表实现方案(一)
博主所在单位目前使用Oracle GoldenGate将各个业务生产库汇聚到一起做数仓实时ODS平台，源端库可能涉及Oracle、Mysql、达梦、Guassdb库。为了做增量etl以及记录每条数据的变更历史：
- 我们采用异构同步，即源端同步过来的表在ODS新增了一个etltime字段，用来记录当前数据变更时间。
- 为了记录数据的事务变更历史记录，我们将数据的变更记录映射同步到一张tab_name_audit表中。
- 为了防止源端业务库误删数据，我们将被删除的数据映射同步到一张tab_name_his表中。
- 原表映射到ods后还是正常的映射同步dml操作。
至于以上方式是怎么实现的，我这里给大家在本地电脑做一次模拟。至于GoldenGate如何安装，请看博主其他相关文章，这里就不赘述了！！！
十一、将SCOTT下所有已同步的HIS表逻辑同步配置改为DEL表操作
1、首先来看一下抽取进程和应用进程我们要修改成什么样的配置
--------------抽取进程：
GGSCI (11g) 4> edit params e_sc
extract e_sc
userid ogg,password ogg
setenv(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
setenv(ORACLE_SID="orcl")
reportcount every 10 minutes,rate
numfiles 5000
discardfile ./dirrpt/e_sc.dsc,append,megabytes 1000
warnlongtrans 2h,checkinterval 3m
exttrail ./dirdat/sc
threadoptions maxcommitpropagationdelay 60000
dboptions allowunusedcolumn
tranlogoptions archivedlogonly
tranlogoptions altarchivelogdest primary /u01/archivelog
TRANLOGOPTIONS ALTARCHIVEDLOGFORMAT %t_%s_%r.dbf
dynamicresolution
ddl include mapped
ddloptions addtrandata,report
notcpsourcetimer
nocompressupdates
--fetchoptions nousesnapshot
fetchoptions USEROWID
--GETUPDATEBEFORES
NOCOMPRESSDELETES
----------scott.EMP
table SCOTT.EMP,tokens(
TKN-CSN = @GETENV("TRANSACTION", "CSN"),
TKN-COMMIT-TS = @GETENV ("GGHEADER", "COMMITTIMESTAMP"),
TKN-OP-TYPE = @GETENV ("GGHEADER", "OPTYPE")
);
----------SCOTT.DEPT
table SCOTT.DEPT,tokens(
TKN-CSN = @GETENV("TRANSACTION", "CSN"),
TKN-COMMIT-TS = @GETENV ("GGHEADER", "COMMITTIMESTAMP"),
TKN-OP-TYPE = @GETENV ("GGHEADER", "OPTYPE")
);
----------SCOTT.DEL
table SCOTT.DEL,tokens(
TKN-CSN = @GETENV("TRANSACTION", "CSN"),
TKN-COMMIT-TS = @GETENV ("GGHEADER", "COMMITTIMESTAMP"),
TKN-OP-TYPE = @GETENV ("GGHEADER", "OPTYPE")
);
----------SCOTT.nopk_tab
table SCOTT.NOPK_TAB,keycols(EMPNO,ENAME),tokens(
TKN-CSN = @GETENV("TRANSACTION", "CSN"),
TKN-COMMIT-TS = @GETENV ("GGHEADER", "COMMITTIMESTAMP"),
TKN-OP-TYPE = @GETENV ("GGHEADER", "OPTYPE")
);
----------------------应用进程配置
----R_SC2进程
GGSCI (OGG) 25> edit params r_sc2
replicat r_sc2
setenv(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg,password ogg
reportcount every 30 minutes,rate
reperror default,abend
--reperror default,DISCARD
numfiles 50000
checkpointsecs 40
assumetargetdefs
discardfile ./dirrpt/r_sc2.dsc,append,megabytes 1000
allownoopupdates
ddl &
include mapped &
exclude objname scott.*_audit &
exclude optype create &
objtype 'table' &
exclude optype drop &
objtype 'table' &
exclude objtype 'index' &
objname SCOTT.*_his &
exclude instr 'constraint' &
---exclude instr 'null' &
exclude instr 'trigger' &
exclude instr 'rename to' &
exclude instr 'grant' &
exclude instr 'revoke' &
exclude instr 'analyze'
DDLOPTIONS MAPSESSIONSCHEMA XUM target ogg
ddloptions report
allowduptargetmap
----------DEPT
getinserts
getupdates
getdeletes
noupdatedeletes
NOINSERTDELETES
map SCOTT.DEPT,target SCOTT.DEPT;
updatedeletes
getinserts
ignoreupdates
ignoredeletes
map SCOTT.DEPT,target SCOTT.DEPT_AUDIT,keycols(DEPTNO),colmap(
usedefaults,
csn=@token("TKN-CSN"),
optime=@token("TKN-COMMIT-TS"),
optype=@token("TKN-OP-TYPE"),
inserttime=@token("TKN-COMMIT-TS"),
curdate=@DATENOW()
);
ignoreinserts
getupdates
getdeletes
map SCOTT.DEPT,target SCOTT.DEPT_AUDIT,keycols(DEPTNO),colmap(
usedefaults,
csn=@token("TKN-CSN"),
optime=@token("TKN-COMMIT-TS"),
optype=@token("TKN-OP-TYPE"),
curdate=@DATENOW()
),filter(@strfind(@token("TKN-OP-TYPE"),"PK UPDATE") =0);
ignoreinserts
getupdates
getdeletes
map SCOTT.DEPT,target SCOTT.DEPT_AUDIT,keycols(DEPTNO),colmap(
usedefaults,
csn=@token("TKN-CSN"),
optime=@token("TKN-COMMIT-TS"),
optype=@case(@token("TKN-OP-TYPE"),"PK UPDATE","INSERT"),
inserttime=@token("TKN-COMMIT-TS"),
curdate=@DATENOW()
),filter(@strfind(@token("TKN-OP-TYPE"),"PK UPDATE") >0),insertallrecords;
ignoreinserts
getupdates
getdeletes
map SCOTT.DEPT,target SCOTT.DEPT_AUDIT,keycols(DEPTNO),colmap(
DEPTNO=before.DEPTNO,
csn=@token("TKN-CSN"),
optime=@token("TKN-COMMIT-TS"),
optype=@case(@token("TKN-OP-TYPE"),"PK UPDATE","DELETE"),
curdate=@DATENOW()
),filter(@strfind(@token("TKN-OP-TYPE"),"PK UPDATE") >0);
ignoreinserts
ignoreupdates
getdeletes
noupdatedeletes
INSERTDELETES
MAP SCOTT.DEPT, TARGET SCOTT.DEPT_DEL,keycols(DEPTNO),colmap(
usedefaults
BEFORE_AFTER=@getenv("GGHEADER", "BEFOREAFTERINDICATOR"),
DEL_DATE=@getenv ("GGHEADER", "COMMITTIMESTAMP"),
OP_FLAG=@getenv ("GGHEADER", "OPTYPE")
);
map SCOTT.DEPT,target SCOTT.DEPT_HIS,keycols(DEPTNO);
----------NOPK_TAB
getinserts
getupdates
getdeletes
noupdatedeletes
NOINSERTDELETES
map SCOTT.NOPK_TAB,target SCOTT.NOPK_TAB,keycols(EMPNO,ENAME);
updatedeletes
getinserts
getupdates
getdeletes
map SCOTT.NOPK_TAB,target SCOTT.NOPK_TAB_AUDIT,keycols(EMPNO,ENAME),colmap(
EMPNO=@if(@strfind(@token("tkn-op-type"),"PK UPDATE")>0,before.EMPNO,EMPNO),
ENAME=@if(@strfind(@token("tkn-op-type"),"PK UPDATE")>0,before.ENAME,ENAME)
csn=@token("tkn-csn"),
optime=@token("tkn-commit-ts"),
optype=@if(@strfind(@token("tkn-op-type"),"PK UPDATE")>0,"DELETE",@token("tkn-op-type")),
inserttime=@eval(@strfind(@token("tkn-op-type"),"INSERT")>0,@token("tkn-commit-ts")),
curdate=@DATENOW()
);
ignoreinserts
getupdates
ignoredeletes
map SCOTT.NOPK_TAB,target SCOTT.NOPK_TAB_AUDIT,keycols(EMPNO,ENAME),colmap(
usedefaults,
csn=@token("tkn-csn"),
optime=@token("tkn-commit-ts"),
optype=@case(@token("tkn-op-type"),"PK UPDATE","INSERT"),
inserttime=@token("tkn-commit-ts"),
curdate=@DATENOW()
),filter(@strfind(@token("tkn-op-type"),"PK UPDATE") >0),insertallrecords;
ignoreinserts
ignoreupdates
getdeletes
noupdatedeletes
INSERTDELETES
MAP SCOTT.NOPK_TAB, TARGET SCOTT.NOPK_TAB_DEL,keycols(EMPNO,ENAME),colmap(
usedefaults,
BEFORE_AFTER=@getenv("GGHEADER", "BEFOREAFTERINDICATOR"),
DEL_DATE=@getenv ("GGHEADER", "COMMITTIMESTAMP"),
OP_FLAG=@getenv ("GGHEADER", "OPTYPE")
);
map SCOTT.NOPK_TAB,target SCOTT.NOPK_TAB_HIS,keycols(EMPNO,ENAME);
----R_SC进程：
GGSCI (OGG) 24> edit params r_sc
replicat r_sc
setenv(NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg,password ogg
reportcount every 30 minutes,rate
reperror default,abend
numfiles 50000
checkpointsecs 40
assumetargetdefs
discardfile ./dirrpt/r_sc.dsc,append,megabytes 1000
allownoopupdates
ddl &
include mapped &
exclude objname scott.*_audit &
exclude optype create &
objtype 'table' &
exclude optype drop &
objtype 'table' &
exclude objtype 'index' &
objname SCOTT.*_his &
exclude instr 'constraint' &
---exclude instr 'null' &
exclude instr 'trigger' &
exclude instr 'rename to' &
exclude instr 'grant' &
exclude instr 'revoke' &
exclude instr 'analyze'
DDLOPTIONS MAPSESSIONSCHEMA XUM target ogg
ddloptions report
allowduptargetmap
--INSERTALLRECORDS
----------EMP
getinserts
getupdates
getdeletes
noupdatedeletes
noinsertdeletes
map SCOTT.EMP,target SCOTT.EMP;
updatedeletes
--map SCOTT.EMP,target SCOTT.EMP_HIS,keycols(EMPNO);
getinserts
ignoreupdates
ignoredeletes
map SCOTT.EMP,target SCOTT.EMP_AUDIT,keycols(EMPNO),colmap(
usedefaults,
csn=@token("TKN-CSN"),
optime=@token("TKN-COMMIT-TS"),
optype=@token("TKN-OP-TYPE"),
inserttime=@token("TKN-COMMIT-TS"),
curdate=@DATENOW()
);
ignoreinserts
getupdates
getdeletes
map SCOTT.EMP,target SCOTT.EMP_AUDIT,keycols(EMPNO),colmap(
usedefaults,
csn=@token("TKN-CSN"),
optime=@token("TKN-COMMIT-TS"),
optype=@token("TKN-OP-TYPE"),
curdate=@DATENOW()
),filter(@strfind(@token("TKN-OP-TYPE"),"PK UPDATE") =0);
ignoreinserts
getupdates
getdeletes
map SCOTT.EMP,target SCOTT.EMP_AUDIT,keycols(EMPNO),colmap(
usedefaults,
csn=@token("TKN-CSN"),
optime=@token("TKN-COMMIT-TS"),
optype=@case(@token("TKN-OP-TYPE"),"PK UPDATE","INSERT"),
inserttime=@token("TKN-COMMIT-TS"),
curdate=@DATENOW()
),filter(@strfind(@token("TKN-OP-TYPE"),"PK UPDATE") >0),insertallrecords;
ignoreinserts
getupdates
getdeletes
map SCOTT.EMP,target SCOTT.EMP_AUDIT,keycols(EMPNO),colmap(
EMPNO=before.EMPNO,
csn=@token("TKN-CSN"),
optime=@token("TKN-COMMIT-TS"),
optype=@case(@token("TKN-OP-TYPE"),"PK UPDATE","DELETE"),
curdate=@DATENOW()
),filter(@strfind(@token("TKN-OP-TYPE"),"PK UPDATE") >0);
ignoreinserts
ignoreupdates
getdeletes
noupdatedeletes
INSERTDELETES
map SCOTT.EMP,target SCOTT.EMP_HIS,keycols(EMPNO);
2、开始前先停止源端的抽取进程
--------------停止抽取进程
stop e_sc
查看抽取进程结束位置，查看抽取进程“Log Read Checkpoint”的“Seqno”和“RBA”,查看抽取进程“Target Extract Trails”的“Seqno”和“RBA”
GGSCI (11g) 7> info e_Sc detail
EXTRACT E_SC Last Started 2018-02-23 16:22 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:00:31 ago)
Log Read Checkpoint Oracle Redo Logs
2018-02-26 09:19:12 Seqno 559, RBA 52736
SCN 0.1684462 (1684462)
Target Extract Trails:
Remote Trail Name Seqno RBA Max MB
./dirdat/sc 79 18167 500
--------------停止投递进程
停止投递进程前检查，查看投递进程的“Log Read Checkpoint”的“Seqno”和“RBA”
info D_SC detail
GGSCI (11g) 8> info D_SC detail
EXTRACT D_SC Last Started 2018-02-12 14:20 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:06 ago)
Log Read Checkpoint File ./dirdat/sc000079
2018-02-26 09:19:12.000000 RBA 18167
Target Extract Trails:
Remote Trail Name Seqno RBA Max MB
./dirdat/sc 56 20937 500
--------------如果数值和抽取进程“Target Extract Trails”的“Seqno”和“RBA”一致，才可以停止投递进程
stop D_SC
--------------停止应用进程
停止应用进程前检查，查看应用进程“Log Read Checkpoint”的“Seqno”和“RBA”
info r_sc detail
GGSCI (OGG) 26> info r_sc detail
REPLICAT R_SC Last Started 2018-02-26 10:08 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:10 ago)
Log Read Checkpoint File ./dirdat/sc000056
First Record RBA 20937
..................
info r_sc2 detail
GGSCI (OGG) 27> info r_sc2 detail
REPLICAT R_SC2 Last Started 2018-02-23 15:55 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:16 ago)
Log Read Checkpoint File ./dirdat/sc000056
2018-02-26 09:19:12.014722 RBA 20937
.................
如果上面两个应用进程“Seqno”和“RBA”数值和前面记录下来的投递进程的“Target Extract Trails”的“Seqno”和“RBA”一致，才可以停止应用进程.
现在看来是一致的了，seq都是sc000056，rba都是20937，停止应用进程。
stop r_sc
stop r_sc2
GGSCI (OGG) 29> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED R_SC 00:00:00 00:00:01
REPLICAT STOPPED R_SC2 00:00:00 00:00:01
3、清除原来HIS表非DELETE操作数据
现在将最上面的抽取和应用进程的配置替换现在的抽取和应用进程。接下来的操作就是把已同步的三张表在应用端映射的HIS中保存的原来的所有增删改的记录
只保留被delete的记录，其余保留的插入和修改的历史记录全部删除，这就需要写一个脚本来实现了，写脚本使用ogg端ogg_tables_info表
这张表存的是当前ogg环境中所有已同步表对应的信息，此表的表结构和信息：
create table ogg_tables_info
(
USER_NAME VARCHAR2(50) ,
TABLE_NAME VARCHAR2(50) ,
OGG_USER_NAME VARCHAR2(50) ,
OGG_TABLE_NAME VARCHAR2(50) ,
TABLE_COMMENT VARCHAR2(1000),
TABLE_TYPE VARCHAR2(50) ,
PK_COLUMN VARCHAR2(400) ,
PK_COLUMN_TYPE VARCHAR2(100) ,
HIS_TABLENAME VARCHAR2(100) ,
AUDIT_TABLENAME VARCHAR2(100) ,
FLAG_MON VARCHAR2(1)
)
insert into ogg_tables_info(user_name,table_name,ogg_user_name,ogg_table_name,pk_column,his_tablename,audit_tablename,flag_mon)
values('SCOTT','DEPT','SCOTT','DEPT','DEPTNO','DEPT_HIS','DEPT_AUDIT',1);
insert into ogg_tables_info(user_name,table_name,ogg_user_name,ogg_table_name,pk_column,his_tablename,audit_tablename,flag_mon)
values('SCOTT','EMP','SCOTT','EMP','EMPNO','EMP_HIS','EMP_AUDIT',1);
insert into ogg_tables_info(user_name,table_name,ogg_user_name,ogg_table_name,pk_column,his_tablename,audit_tablename,flag_mon)
values('SCOTT','NOPK_TAB','SCOTT','NOPK_TAB','DEPTNO','NOPK_TAB_HIS','NOPK_TAB_AUDIT',1);
接下来写脚本：
--set serveroutput on;
declare
v_error_pos varchar2 (50); --错误位置
v_error_code varchar2 (8); --错误代码
v_error_msg varchar2 (500); --错误信息
exec_error exception; --自定义异常
cursor audittab1 is
select OGG_USER_NAME,OGG_TABLE_NAME,HIS_TABLENAME,AUDIT_TABLENAME,PK_COLUMN
from
ogg_tables_info
where flag_mon='1' and PK_COLUMN not like '%,%';
cursor audittab2 is
select OGG_USER_NAME,OGG_TABLE_NAME,HIS_TABLENAME,AUDIT_TABLENAME,PK_COLUMN
from
ogg_tables_info
where flag_mon='1' and PK_COLUMN like '%,%';
v_sql varchar2(2000);
begin
for tab in audittab1
loop
v_sql:='delete from '||tab.HIS_TABLENAME||' where not exists (select null from (select '||tab.PK_COLUMN||' as pkcol from '||tab.OGG_USER_NAME||'.'||tab.AUDIT_TABLENAME||' where optype=''DELETE'')'||'t where t.pkcol='||tab.PK_COLUMN||')';
execute immediate v_sql;
end loop;
for tab2 in audittab2
loop
v_sql:='delete from '||tab2.HIS_TABLENAME||' where not exists (select null from (select '||replace(tab2.PK_COLUMN,',','||')||' as pkcol2 from '||tab2.OGG_USER_NAME||'.'||tab2.AUDIT_TABLENAME||' where optype=''DELETE'')'||'t where t.pkcol2='||replace(tab2.PK_COLUMN,',','||')||')';
execute immediate v_sql;
end loop;
commit;
exception
when others
then
v_error_code := sqlcode;
v_error_msg := substr (sqlerrm,1,500);
rollback;
dbms_output.put_line('v_error_code= '||v_error_code||CHR(10)||'v_error_msg= '||v_error_msg);
end;
脚本执行完成后，现在这三张表对应的HIS中保存的都是在审计表中记录的做过删除操作的记录，因为之前的历史表配置，只是不执行delete操作，如果记录之前执行了insert和普通的update操作，那么在源端执行delete操作时，目的端不会执行删除操作，从而使得被删除的记录得以保存。
4、启动抽取进程和应用进程验证
----------源端启动抽取/投递进程
start er *
GGSCI (11g) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING D_SC 00:00:00 00:00:01
EXTRACT RUNNING E_SC 00:00:00 00:00:03
-------------目的端启动应用进程
start er *
GGSCI (OGG) 24> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING R_SC 00:00:00 00:00:36
REPLICAT RUNNING R_SC2 00:00:00 00:00:36
接下来在源端进行下面一系列操作：
insert into dept values(51,'a','b');
insert into dept values(52,'a','b');
commit;
alter system switch logfile;
在目的端查看：
发现正常应用过来了，然后再再源端进行下面操作：
update dept set DNAME ='bbb' where deptno=52;
update dept set deptno=25,DNAME ='bbb' where deptno=52;
delete from dept where deptno=51;
commit;
alter system switch logfile;
再目的端查看：
贴上trail内容：
[oracle@OGG ogg11]$ ./logdump
Oracle GoldenGate Log File Dump Utility for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258
Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Logdump 314 >obey loginit_sc
cd ./dirdat
open sc000057
Current LogTrail is /u01/app/oracle/ogg11/dirdat/sc000057
ghdr on
detail on
detail data
usertoken on
usertoken detail
ggstoken on
ggstoken detail
headertoken on
headertoken detail
reclen 0
Reclen set to 0
pos last
Reading forward from RBA 3017
pos rev
Reading in reverse from RBA 3017
logtrail
Current LogTrail is /u01/app/oracle/ogg11/dirdat/sc000057
pos
Current position is RBA 3017 Reverse
Logdump 315 >pos last
Reading in reverse from RBA 3017
Logdump 316 >pos rev
Reading in reverse from RBA 3017
Logdump 317 >n
TokenID x47 'G' Record Header Info x01 Length 201
TokenID x48 'H' GHDR Info x00 Length 45
4504 0042 0020 03ff 02f2 8c4c eb2f 7a97 0000 0000 | E..B. .....L./z.....
0002 85e4 0000 0231 0252 0000 0001 5343 4f54 542e | .......1.R....SCOTT.
4445 5054 00 | DEPT.
TokenID x44 'D' Data Info x00 Length 32
TokenID x54 'T' GGS Tokens Info x00 Length 24
TokenID x55 'U' User Tokens Info x00 Length 76
TokenID x5a 'Z' Record Trailer Info x01 Length 201
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: B (x42)
RecLength : 32 (x0020) IO Time : 2018/02/26 16:40:30.995.095
IOType : 3 (x03) OrigNode : 255 (xff)
TransInd : . (x02) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 561 AuditPos : 165348
Continued : N (x00) RecCount : 1 (x01)
2018/02/26 16:40:30.995.095 Delete Len 32 RBA 2816
Name: SCOTT.DEPT
Before Image: Partition 4 GU e
0000 000a 0000 0000 0000 0000 0033 0001 0005 0000 | .............3......
0001 6100 0200 0500 0000 0162 | ..a........b
Column 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 0033 | .........3
Column 1 (x0001), Len 5 (x0005)
0000 0001 61 | ....a
Column 2 (x0002), Len 5 (x0005)
0000 0001 62 | ....b
User tokens: 76 bytes
TKN-CSN : 1703311
TKN-COMMIT-TS : 2018-02-26 16:40:31.000000
TKN-OP-TYPE : DELETE
GGS tokens:
TokenID x52 'R' ORAROWID Info x00 Length 20
4141 4156 5243 4141 4541 4141 4143 4841 4142 0001 | AAAVRCAAEAAAACHAAB..
Logdump 318 >n
TokenID x47 'G' Record Header Info x01 Length 213
TokenID x48 'H' GHDR Info x00 Length 45
4504 0041 0029 73ff 02f2 8c4c eb2f 7a97 0000 0000 | E..A.)s....L./z.....
0000 6c10 0000 0000 0152 0000 0001 5343 4f54 542e | ..l......R....SCOTT.
4445 5054 00 | DEPT.
TokenID x44 'D' Data Info x00 Length 41
TokenID x54 'T' GGS Tokens Info x00 Length 24
TokenID x55 'U' User Tokens Info x00 Length 79
TokenID x5a 'Z' Record Trailer Info x01 Length 213
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 41 (x0029) IO Time : 2018/02/26 16:40:30.995.095
IOType : 115 (x73) OrigNode : 255 (xff)
TransInd : . (x01) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 0 AuditPos : 27664
Continued : N (x00) RecCount : 1 (x01)
2018/02/26 16:40:30.995.095 GGSPKUpdate Len 41 RBA 2603
Name: SCOTT.DEPT
After Image: Partition 4 GU m
000e 0000 000a 0000 0000 0000 0000 0034 0000 000a | ...............4....
0000 0000 0000 0000 0019 0001 0007 0000 0003 6262 | ..................bb
62 | b
Before Image Len 16 (x00000010)
KeyLen 14 (x0000000e)
KeyCol 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 0034 | .........4
After Image Len 25 (x00000019)
Column 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 0019 | ..........
Column 1 (x0001), Len 7 (x0007)
0000 0003 6262 62 | ....bbb
User tokens: 79 bytes
TKN-CSN : 1703311
TKN-COMMIT-TS : 2018-02-26 16:40:31.000000
TKN-OP-TYPE : PK UPDATE
GGS tokens:
TokenID x52 'R' ORAROWID Info x00 Length 20
4141 4156 5243 4141 4541 4141 4143 4841 4143 0001 | AAAVRCAAEAAAACHAAC..
Logdump 319 >n
TokenID x47 'G' Record Header Info x01 Length 226
TokenID x48 'H' GHDR Info x00 Length 45
4504 0041 0019 0fff 02f2 8c4c eb2f 7a97 0000 0000 | E..A.......L./z.....
0000 6810 0000 0231 0052 0000 0001 5343 4f54 542e | ..h....1.R....SCOTT.
4445 5054 00 | DEPT.
TokenID x44 'D' Data Info x00 Length 25
TokenID x54 'T' GGS Tokens Info x00 Length 48
TokenID x55 'U' User Tokens Info x00 Length 84
TokenID x5a 'Z' Record Trailer Info x01 Length 226
___________________________________________________________________
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 25 (x0019) IO Time : 2018/02/26 16:40:30.995.095
IOType : 15 (x0f) OrigNode : 255 (xff)
TransInd : . (x00) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 561 AuditPos : 26640
Continued : N (x00) RecCount : 1 (x01)
2018/02/26 16:40:30.995.095 FieldComp Len 25 RBA 2377
Name: SCOTT.DEPT
After Image: Partition 4 GU b
0000 000a 0000 0000 0000 0000 0034 0001 0007 0000 | .............4......
0003 6262 62 | ..bbb
Column 0 (x0000), Len 10 (x000a)
0000 0000 0000 0000 0034 | .........4
Column 1 (x0001), Len 7 (x0007)
0000 0003 6262 62 | ....bbb
User tokens: 84 bytes
TKN-CSN : 1703311
TKN-COMMIT-TS : 2018-02-26 16:40:31.000000
TKN-OP-TYPE : SQL COMPUPDATE
GGS tokens:
TokenID x52 'R' ORAROWID Info x00 Length 20
4141 4156 5243 4141 4541 4141 4143 4841 4143 0001 | AAAVRCAAEAAAACHAAC..
TokenID x4c 'L' LOGCSN Info x00 Length 7
3137 3033 3331 31 | 1703311
TokenID x36 '6' TRANID Info x00 Length 9
392e 3331 2e31 3434 32 | 9.31.1442
从挖掘的trail内容可以看到，源端执行的update ,pkupdate,delete操作的确都正常传递并应用了。再一步证实是源端的正常操作，之所以这样挖掘再次验证，
是因为笔者层曾遇到过pkupdate操作被莫名解析成update的情况。
再来测一下多次插入删除同一记录，HIS表是否会叠加保存：
-------源端连续插入删除
insert into dept values(56,‘a’,‘b’);
delete from dept where deptno=56;
insert into dept values(56,‘a’,‘b’);
delete from dept where deptno=56;
commit;
alter system switch logfile;
-----------目的端查看：
接下来验证一下nopk_tab表，源端进行下面操作：
insert into nopk_tab values('1222','dongdong','111',10);
insert into nopk_tab values('1233','dongdong','1113',10);
update nopk_tab set sal=1000 where empno=1222 and ename='dongdong';
update nopk_tab set empno=3321,ename='donge',sal=333 where empno=1233 and ename='dongdong';
delete from nopk_tab where empno=1111;
insert into nopk_tab values('1233','dongdong','1113',10);
delete from nopk_tab where empno=1233;
insert into nopk_tab values('1233','dongdong','1113',10);
delete from nopk_tab where empno=1233;
commit;
alter system switch logfile;
在目的端查看结果：
接下来在用dept表做一下增删列的操作在检查：
---------源端操作
alter table dept add test number;
insert into dept values(57,'a','b',33);
delete from dept where deptno=57;
insert into dept values(57,'a','b',33);
delete from dept where deptno=57;
commit;
alter system switch logfile;
------------目的端查看
-------------源端删除列
alter table dept drop column test ;
insert into dept values(58,'a','b');
delete from dept where deptno=58;
insert into dept values(58,'a','b');
delete from dept where deptno=58;
commit;
alter system switch logfile;
----------目的端查看
也是正常的。
总结
上面是完整的OGG异构多路映射同步原表&审计表&只存删除数据表实现方案，是博主将博主和另一位Oracle OCM大牛多年的总结模拟出来的实验，在各大央企国企单位中均有使用！！！
扫一扫
936
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
