|
OGG ,部署在同一台机器上。IP地址是192.168.2.100?
源端：orcl
目标端： test?
ogg版本?
[oracle@redhat762100 ogg19c]$ ./ggsci -v
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
[oracle@redhat762100 ogg19c]$
将bb.t 同步到bb.t
配置测试内容：
1 将bb.t 同步到bb.t ，两个表是相同的schema，相同的结构
2 将bb.t1同步到zbb.t1,两个表属于不同的schema，结构相同，但是目标端表，多了一个update_time字段，该字段记录同步时间?
----- 配置测试1 ?
-- 源端配置
view params mgr -- 配置mgr进程
PORT 7809
DYNAMICPORTLIST 7800-8000
AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 7
LAGREPORTHOURS 1
PURGEOLDEXTRACTS ./dirdat/ext1/*, USECHECKPOINTS, MINKEEPDAYS 3
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
-- 源端加入要同步的表?
add trandata bb.t
-- 配置抽取进程
view params ext1?
extract ext1
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
SETENV (ORACLE_HOME = "/u01/app/oracle")
SETENV (ORACLE_SID = "orcl")
userid ogg, password oracle
warnlongtrans 4h, checkinterval 10m
dynamicresolution
exttrail ./dirdat/ext1/ee
table bb.t ;
add extract ext1,tranlog,begin now
add exttrail ./dirdat/ext1/ee,extract ext1,megabytes 200 -- 注意这里不要使用绝对路径，否则报错
-- 配置投递进程? ?view params pump1?
extract PUMP1
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
SETENV (ORACLE_HOME = "/u01/app/oracle")
SETENV (ORACLE_SID = "orcl")
rmthost 192.168.2.100, mgrport 7809
rmttrail ./dirdat/pump1/pp
userid ogg, password oracle
table bb.t ;
add extract pump1, exttrailsource ./dirdat/ext1/ee, begin now
ADD RMTTRAIL ./dirdat/pump1/pp, EXTRACT pump1, MEGABYTES 200
-- 目标端配置复制进程??view params rep1?
replicat rep1
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
SETENV (ORACLE_HOME = "/u01/app/oracle")
SETENV (ORACLE_SID = "test")
userid ogg, password oracle
assumetargetdefs
discardfile ./dirdat/rep_l_discard,append
--discardfile ./dirrpt/repdb1_repaix.dsc,purge
REPORTROLLOVER AT 05:30 ON Friday
map bb.t,target bb.t;
add replicat rep1,exttrail ./dirdat/pump1/pp, checkpointtable ogg.checkpoint
start replicat repdb1
-- 查看进程状态
GGSCI (redhat762100 as ogg@orcl) 52> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:01
EXTRACT RUNNING PUMP1 00:00:00 00:00:06
REPLICAT RUNNING REP1 00:00:00 00:00:02
REPLICAT STOPPED REPTAB1 00:00:00 03:11:37
REPLICAT STOPPED REPTAB2 00:00:00 2708:59:59
----------------- 配置测试2 ?，新增加同步进程，使表结构不一样
源端：orcl
目标端： test?
将bb.t1 同步到zbb.t1?
-- 源端加入要同步的表?
add trandata bb.t1
源端表
BB@orcl>desc bb.t1
Name Null? Type
----------------------------------------------------------------- -------- --------------------------------------------
ID NUMBER
NAME VARCHAR2(10)
BB@orcl>
目标端表 ，和源端的表字段相同，但是多了一个update_date字段
SYS@test>desc zbb.t1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(30)
SYS@test>alter table zbb.t1 add update_date date default sysdate;
Table altered.
SYS@test>desc zbb.t1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(30)
UPDATE_DATE DATE
SYS@test>
-- 源端配置抽取进程 ext2?
先add ?trandata bb.t1?
view params ext2?
extract ext2
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
SETENV (ORACLE_HOME = "/u01/app/oracle")
SETENV (ORACLE_SID = "orcl")
userid ogg, password oracle
warnlongtrans 4h, checkinterval 10m
dynamicresolution
exttrail ./dirdat/ext2/ee
table bb.t1 ,TOKENS (UPDATE_DATE = @GETENV ('GGHEADER', 'COMMITTIMESTAMP')) ; -- 注意要单引号
add extract ext2,tranlog,begin now
add exttrail ./dirdat/ext2/ee,extract ext2,megabytes 200 -- 注意这里不要使用绝对路径，否则报错
-- 源端配置投递进程? ?view params pump2?
extract PUMP2
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
SETENV (ORACLE_HOME = "/u01/app/oracle")
SETENV (ORACLE_SID = "orcl")
rmthost 192.168.2.100, mgrport 7809
rmttrail ./dirdat/pump2/pp
userid ogg, password oracle
table bb.t1 ,TOKENS (UPDATE_DATE = @GETENV ('GGHEADER', 'COMMITTIMESTAMP')) ; -- 这里也要单引号
add extract pump2, exttrailsource ./dirdat/ext2/ee, begin now
ADD RMTTRAIL ./dirdat/pump2/pp, EXTRACT pump2, MEGABYTES 200
-- 源端定义defgen ?
view params defgen_t1 ?-- 这个文件在dirprm文件夹中 ，就是defgen_t1.prm?
defsfile ./dirdat/defen_t1/defgen_t1.def -- 写这个，说明是生成的defsfile文件的路径
userid ogg,password oracle
table bb.t1;
-- 源端生成defgen文件,将生成的文件，copy到目标端的dirdef目录下(拷贝过程略)?
defgen paramfile /u01/ogg19c/dirprm/defgen_t1.prm
[oracle@redhat762100 ogg19c]$ ./defgen paramfile /u01/ogg19c/dirprm/defgen_t1.prm
***********************************************************************
Oracle GoldenGate Table Definition Generator for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054
Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 15:32:20
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
Starting at 2022-06-25 15:03:16
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Thu Oct 4 20:48:51 UTC 2018, Release 3.10.0-957.el7.x86_64
Node: redhat762100
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 37169
***********************************************************************
** Running with the following parameters **
***********************************************************************
defsfile ./dirdat/defen_t1/defgen_t1.def
userid ogg,password ***
table bb.t1;
Retrieving definition for BB.T1.
2022-06-25 15:03:20 WARNING OGG-06439 No unique key is defined for table T1. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Definitions generated for 1 table in ./dirdat/defen_t1/defgen_t1.def.
[oracle@redhat762100 ogg19c]$
-- 目标端配置复制进程
replicat rep2
Sourcedefs /u01/ogg19c/dirdef/defgen_t1.def -- 使用到上面生成的文件 （将上面生成的文件，copy到合适的目录）
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
SETENV (ORACLE_HOME = "/u01/app/oracle")
SETENV (ORACLE_SID = "test")
userid ogg, password oracle
--assumetargetdefs
discardfile ./dirdat/rep_2_discard,append
--discardfile ./dirrpt/repdb1_repaix.dsc,purge
REPORTROLLOVER AT 05:30 ON Friday
map bb.t1,target zbb.t COLMAP (USEDEFAULTS, UPDATE_DATE= @TOKEN ('TKN-COMMITTIME'));
add replicat rep2,exttrail ./dirdat/pump2/pp, checkpointtable ogg.checkpoint
start replicat rep2
-- 查看进程，各个进程正常。
GGSCI (redhat762100 as ogg@test) 24> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:09
EXTRACT RUNNING EXT2 00:00:00 00:00:09
EXTRACT RUNNING PUMP1 00:00:00 00:00:09
EXTRACT RUNNING PUMP2 00:00:00 00:00:08
REPLICAT RUNNING REP1 00:00:00 00:00:05
REPLICAT RUNNING REP2 00:00:00 00:00:05
REPLICAT STOPPED REPTAB1 00:00:00 04:32:38
REPLICAT STOPPED REPTAB2 00:00:00 2710:20:59
GGSCI (redhat762100 as ogg@test) 25>
过程中碰到的问题及原因：
问题1 ：
OGG-00041 ?Oracle GoldenGate Capture for Oracle, pump1.prm: ?Data source not specified.
OGG-00041 ? ?Data source not specified.
原因：
edit params 参数里面不全 ，没有指定extract的名称
问题2?
OGG-01298 ?Oracle GoldenGate Capture for Oracle, ext2.prm: ?Column function diagnostic message: could not find column "GGHEADER".
原因：
单引号和双引号的问题 ，ogg11g和ogg19c，注意使用的是单引号还是双引号
问题3?
GGSCI (redhat762100 as ogg@orcl) 102> info all
Program ? ? Status ? ? ?Group ? ? ? Lag at Chkpt ?Time Since Chkpt
MANAGER ? ? RUNNING ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ? ??
EXTRACT ? ? RUNNING ? ? EXT1 ? ? ? ?00:00:00 ? ? ?00:00:05 ? ?
EXTRACT ? ? RUNNING ? ? EXT2 ? ? ? ?00:00:00 ? ? ?00:00:05 ? ?
EXTRACT ? ? RUNNING ? ? PUMP1 ? ? ? 00:00:00 ? ? ?00:00:04 ? ?
EXTRACT ? ? RUNNING ? ? PUMP2 ? ? ? 00:00:00 ? ? ?00:00:04 ? ?
REPLICAT ? ?RUNNING ? ? REP1 ? ? ? ?00:00:00 ? ? ?00:00:00 ? ?
REPLICAT ? ?ABENDED ? ? REP2 ? ? ? ?00:00:00 ? ? ?00:01:50 ? ?
REPLICAT ? ?STOPPED ? ? REPTAB1 ? ? 00:00:00 ? ? ?04:16:21 ? ?
REPLICAT ? ?STOPPED ? ? REPTAB2 ? ? 00:00:00 ? ? ?2710:04:42 ?
原因，表zbb.t1有主键（not null约束，还和zbb.t2有外键约束），和zbb.t2有外键约束，
alter table zbb.t1 disable constraint T1_pk;
alter table zbb.t2 disable constraint T2_FK_CASCADE;
?
END?
?
|