ogg目标端添加时间字段记录同步时间

--源端 192.168.56.101, linux, Oracle 19.11, OGG 19.1.0.0.4

create table T4
(
  employee_id    NUMBER(6) not null,
  first_name     VARCHAR2(20),
  last_name      VARCHAR2(25) not null,
  email          VARCHAR2(25) not null,
  phone_number   VARCHAR2(20),
  hire_date      DATE not null,
  job_id         VARCHAR2(10) not null,
  salary         NUMBER(8,2),
  commission_pct NUMBER(2,2),
  manager_id     NUMBER(6),
  department_id  NUMBER(4)
) tablespace USERS;

alter table T4
  add constraint PK_T4 primary key (EMPLOYEE_ID)
  using index
  tablespace USERS;

--目标端 192.168.56.102, linux, Oracle 19.11, OGG 19.1.0.0.4

create table T4
(
  employee_id    NUMBER(6) not null,
  first_name     VARCHAR2(20),
  last_name      VARCHAR2(25) not null,
  email          VARCHAR2(25) not null,
  phone_number   VARCHAR2(20),
  hire_date      DATE not null,
  job_id         VARCHAR2(10) not null,
  salary         NUMBER(8,2),
  commission_pct NUMBER(2,2),
  manager_id     NUMBER(6),
  department_id  NUMBER(4),
  deal_date      TIMESTAMP(6)
) tablespace USERS;

alter table T4
  add constraint PK_T4 primary key (EMPLOYEE_ID)
  using index
  tablespace USERS;

--源端mgr

GGSCI (s01) 50> view params mgr

PORT 6800
autostart extract *
autorestart extract *,retries 3,waitminutes 3
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPHOURS 24
CHECKMINUTES 60

-- 源端extract抽取进程

GGSCI (s01) 51> view params ext1

extract ext1
userid c##oggadmin,password oggadmin
exttrail /u01/app/oracle/ogg/dirdat/et
WARNLONGTRANS 20M
BR BRINTERVAL 20M, BRDIR BR
SOURCECATALOG pdb1
table hr.t1;
table hr.t2;
table hr.t3;
table hr.t4;
--table hr.t4,TOKENS(DEAL_DATE = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'));

-- 源端extract投递进程

GGSCI (s01) 52> view params pump1

extract pump1
userid c##oggadmin,password oggadmin
rmthost 192.168.56.102,mgrport 6800
rmttrail /u01/app/oracle/ogg/dirdat/rt
SOURCECATALOG pdb1
table hr.t1;
table hr.t2;
table hr.t3;
table hr.t4,TOKENS (TKN-COMMIT-TS = @GETENV ('GGHEADER', 'COMMITTIMESTAMP'));

-- 源端定义defgen

GGSCI (s01) 53> edit params defgen

DEFSFILE dirdef/source.def, PURGE
userid hr@pdb1,password hr
table hr.t4;

-- 源端生成defgen文件,将生成的文件，copy到目标端的dirdef目录下

defgen paramfile dirprm/defgen.prm
scp defgen.prm 192.168.56.102:/u01/app/oracle/ogg/dirprm

--目标端配置mgr

GGSCI (s02) 1> view params mgr

PORT 6800
autostart replicat *
autorestart replicat *,retries 3,waitminutes 3

--目标端配置replicate进程

GGSCI (s02) 2> view params rep1

replicat rep1
userid oggadmin@pdb1,password oggadmin
--ASSUMETARGETDEFS
--HANDLECOLLISIONS
DBOPTIONS ENABLE_INSTANTIATION_FILTERING
DISCARDFILE /u01/app/oracle/ogg/rep1_discard.txt,append,megabytes 10
DDLERROR DEFAULT IGNORE RETRYOP
SOURCECATALOG pdb1
map hr.t1,target hr.t1;
map hr.t2,target hr.t2;
map hr.t3,target hr.t3;
map hr.t4,target hr.t4, colmap(usedefaults,DEAL_DATE=@token('TKN-COMMIT-TS'));

-- 测试数据，源端插入数据，数据同步到了目标端，并且目标端update字段会更新

insert into T4 (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, manager_id, department_id) values (777, 'Leonard', 'Zhou', 'LEOZ', '15770077777', to_date('01-12-2023', 'dd-mm-yyyy'), 'DBA', 10000, null, 205, 110);

update t4 set salary=salary+10000 where employee_id=777;

delete from t4 where employee_id=777;

参考文档

ogg测试数据同步，并记录同步时间_ogg数据同步_文档搬运工的博客-CSDN博客

https://www.cnblogs.com/eastsea/p/4232303.html