一 源端和目标端配置OGG检查点
二 源端和目标端配置MGR
三 源端：配置抽取进程ext_01
四 源端：配置投递进程pump_01
五 目标端：配置应用进程rep_0l
六 启动进程
七 DML(insert,update,delete)操作同步测试
源端：补充日志
GGSCI (cjcos01) 1> dblogin userid c##ogg@cjcpdb password oracle
Successfully logged into database CJCPDB.
GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 2> add trandata cjc.*
一 源端和目标端配置OGG检查点
源端：
GGSCI (cjcos01) 1> dblogin userid c##ogg password oracle
Successfully logged into database CDB$ROOT.
GGSCI (cjcos01) 1> EDIT PARAMS ./GLOBALS
--加入以下信息
CHECKPOINTTABLE CDB$ROOT.c##ogg.ggschkpt
退出 OGG，重新登录，并使用 OGG 用户登录数据库
GGSCI (cjcos01 as c##ogg@cjcdb/CDB$ROOT) 3> exit
[oracle@cjcos01 ogg]$ ggsci
GGSCI (cjcos01) 1> dblogin userid c##ogg,password oracle
GGSCI (cjcos01 as c##ogg@cjcdb/CDB$ROOT) 8> ADD CHECKPOINTTABLE CDB$ROOT.c##ogg.ggschkpt
This operation will modify an object at the root level of a consolidated database, continue? (Y/N): Y
Successfully created checkpoint table CDB$ROOT.c##ogg.ggschkpt.
---查看数据库表
[ogg@dbdream ogg]$ sqlplus c##ogg/oracle@cjcdb
SQL> select tname from tab where tname like 'GGSCHKPT%';
TNAME
--------------------------------------------------------------------------------
GGSCHKPT
GGSCHKPT_LOX
目标端：
GGSCI (cjcos02) 1> dblogin userid ogg@chenpdb password oracle
GGSCI (cjcos02 as ogg@chendb/CHENPDB) 2> EDIT PARAMS ./GLOBALS
--加入以下信息
CHECKPOINTTABLE chenpdb.ogg.ggschkpt
退出 OGG，重新登录，并使用 OGG 用户登录数据库
GGSCI (cjcos02 as ogg@chendb/CHENPDB) 3> exit
[oracle@cjcos02 ogg]$ ggsci
GGSCI (cjcos02) 1> dblogin userid ogg@chenpdb password oracle
GGSCI (cjcos02 as ogg@chendb/CHENPDB) 2> ADD CHECKPOINTTABLE
No checkpoint table specified. Using GLOBALS specification chenpdb.ogg.ggschkpt...
Successfully created checkpoint table chenpdb.ogg.ggschkpt.
---查看数据库表
SQL> conn ogg/oracle@chenpdb
SQL> select tname from tab where tname like 'GGSCHKPT%';
TNAME
--------------------------------------------------------------------------------
GGSCHKPT
GGSCHKPT_LOX
二 源端和目标端配置MGR
源端：
GGSCI (cjcos01) 1> dblogin userid c##ogg password oracle
---配置MGR
GGSCI (cjcos01 as c##ogg@cjcdb/CDB$ROOT) 2> edit param mgr
PORT 7809
DYNAMICPORTLIST 7809-8000
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/,usecheckpoints, minkeepdays 3
目标端：
GGSCI (cjcos01) 1> dblogin userid ogg password oracle
---配置mgr
GGSCI (cjcos02 as chen@chendb/CHENPDB) 43> view param mgr
PORT 7809
DYNAMICPORTLIST 7810-8000
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/,usecheckpoints, minkeepdays 3
ACCESSRULE, PROG *, IPADDR 192.168.31.*, ALLOW
三 源端：配置抽取进程ext_01
GGSCI (cjcos01 as c##ogg@cjcdb/CDB$ROOT) 2> edit param ext_01
extract ext_01
userid c##ogg@cjcdb,password oracle
GETUPDATEBEFORES
GETTRUNCATES
BR BRINTERVAL 2H
CACHEMGR CACHESIZE 500MB
WARNLONGTRANS 2H,CHECKINTERVAL 5M
NUMFILES 4000
EOFDELAYCSECS 10
LOGALLSUPCOLS
TRANLOGOPTIONS INTEGRATEDPARAMS (max_sga_size 200, parallelism 2)
discardfile ./dirrpt/jcms.dsc,append, megabytes 200
exttrail ./dirdat/ex
TABLE cjcpdb.cjc.emp;
TABLE cjcpdb.cjc.dept;
TABLE cjcpdb.cjc.bonus;
TABLE cjcpdb.cjc.salgrade;
TABLE cjcpdb.cjc.dummy;
添加抽取进程
ADD EXTRACT ext_01,INTEGRATED TRANLOG,BEGIN NOW
---注册
register extract ext_01 database container (cjcpdb)
2020-04-09 13:03:22 INFO OGG-02003 Extract EXT_01 successfully registered with database at SCN 3466632.
添加本地 trail 文件
add exttrail ./dirdat/ex,extract ext_01
四 源端：配置投递进程pump_01
GGSCI (cjcos01 as c##ogg@cjcdb/CDB$ROOT) 2> edit param pump_01
extract pump_01
dynamicresolution
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid c##ogg@cjcdb,password oracle
rmthost 192.168.31.100,mgrport 7809,compress
rmttrail ./dirdat/dp
TABLE cjcpdb.cjc.emp;
TABLE cjcpdb.cjc.dept;
TABLE cjcpdb.cjc.bonus;
TABLE cjcpdb.cjc.salgrade;
TABLE cjcpdb.cjc.dummy;
添加 pump 进程
add extract pump_01,exttrailsource ./dirdat/ex
添加远程 trail 文件
add rmttrail ./dirdat/dp,extract pump_01
说明 : 指定远程 trail 文件
五 目标端：配置应用进程rep_01
GGSCI (cjcos02 as chen@chendb/CHENPDB) 44> view param rep_0l
replicat rep_01
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg@chenpdb,password oracle
assumetargetdefs
reperror default,discard
discardfile ./dirrpt/replzl.dsc,append,megabytes 50
MAP cjcpdb.cjc.emp, TARGET chenpdb.chen.emp;
MAP cjcpdb.cjc.dept, TARGET chenpdb.chen.dept;
MAP cjcpdb.cjc.bonus, TARGET chenpdb.chen.bonus;
MAP cjcpdb.cjc.salgrade, TARGET chenpdb.chen.salgrade;
MAP cjcpdb.cjc.dummy, TARGET chenpdb.chen.dummy;
添加应用进程
add replicat rep_01 integrated,exttrail ./dirdat/dp
add replicat rep_01,exttrail ./dirdat/dp,checkpointtable chenpdb.ogg.ggschkpt
六 启动进程
源端：
start mgr
start extract ext_01
start extract pump_01
查看进程状态：
GGSCI (cjcos01 as c##ogg@cjcdb/CDB$ROOT) 47> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT_01 00:01:59 00:00:37
EXTRACT RUNNING PUMP_01 00:00:00 00:00:04
目标端：
start mgr
start replicat rep_01
查看进程状态：
GGSCI (cjcos02 as ogg@chendb/CHENPDB) 40> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP_01 00:00:00 00:00:04
七 DML(insert,update,delete)操作同步测试
源端：
SQL> insert into dept values(50,'DBA','DUNHUA');
SQL> insert into dept values(60,'DBA','DAPUCHAI');
commit;
SQL> update dept set dname='CJC' where deptno=60;
1 row updated.
SQL> commit;
Commit complete.
SQL> delete dept where deptno=50;
1 row deleted.
SQL> commit;
Commit complete.
目标端：数据已同步
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
60 CJC DAPUCHAI
查看插入时对应的ggserr.log日志
源端：
[oracle@cjcos01 ogg]$ tail -f ggserr.log
......
2020-04-09T18:04:15.194+0800 INFO OGG-06507 Oracle GoldenGate Capture for Oracle, ext_01.prm: MAP (TABLE) resolved (entry cjcpdb.cjc.dept): TABLE "CJCPDB"."CJC"."DEPT".
2020-04-09T18:04:15.470+0800 WARNING OGG-02180 Oracle GoldenGate Capture for Oracle, ext_01.prm: Table CJCPDB.CJC.DEPT will use legacy trail format to support parameter LOGALLSUPCOLS.
2020-04-09T18:04:15.670+0800 WARNING OGG-06439 Oracle GoldenGate Capture for Oracle, ext_01.prm: No unique key is defined for table DEPT. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2020-04-09T18:04:15.670+0800 INFO OGG-06509 Oracle GoldenGate Capture for Oracle, ext_01.prm: Using the following key columns for source table CJCPDB.CJC.DEPT: DEPTNO, DNAME, LOC.
2020-04-09T18:04:15.709+0800 INFO OGG-06507 Oracle GoldenGate Capture for Oracle, ext_01.prm: MAP (TABLE) resolved (entry cjcpdb.cjc.dept): TABLE "CJCPDB"."CJC"."DEPT".
2020-04-09T18:04:15.709+0800 WARNING OGG-02180 Oracle GoldenGate Capture for Oracle, ext_01.prm: Table CJCPDB.CJC.DEPT will use legacy trail format to support parameter LOGALLSUPCOLS.
2020-04-09T18:04:15.709+0800 WARNING OGG-06439 Oracle GoldenGate Capture for Oracle, ext_01.prm: No unique key is defined for table DEPT. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2020-04-09T18:04:15.709+0800 INFO OGG-06509 Oracle GoldenGate Capture for Oracle, ext_01.prm: Using the following key columns for source table CJCPDB.CJC.DEPT: DEPTNO, DNAME, LOC.
2020-04-09T18:04:17.286+0800 INFO OGG-02263 Oracle GoldenGate Capture for Oracle, pump_01.prm: Passthru MAP (TABLE) resolved (entry cjcpdb.cjc.dept): TABLE "CJCPDB"."CJC"."DEPT".
2020-04-09T18:04:17.286+0800 INFO OGG-01890 Oracle GoldenGate Capture for Oracle, pump_01.prm: Compression level is set to 1.
目标端：
[oracle@cjcos01 ogg]$ tail -f ggserr.log
2020-04-09T18:04:18.593+0800 WARNING OGG-02760 Oracle GoldenGate Delivery for Oracle, rep_01.prm: ASSUMETARGETDEFS is ignored because trail file /ogg/dirdat/dp000000000 contains table definitions.
2020-04-09T18:04:20.657+0800 INFO OGG-03506 Oracle GoldenGate Delivery for Oracle, rep_01.prm: The source database character set, as determined from the trail file, is UTF-8.
2020-04-09T18:04:20.738+0800 INFO OGG-06505 Oracle GoldenGate Delivery for Oracle, rep_01.prm: MAP resolved (entry cjcpdb.cjc.dept): MAP "CJCPDB"."CJC"."DEPT", TARGET chenpdb.chen.dept.
2020-04-09T18:04:34.942+0800 WARNING OGG-06439 Oracle GoldenGate Delivery for Oracle, rep_01.prm: No unique key is defined for table DEPT. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2020-04-09T18:04:34.942+0800 INFO OGG-02756 Oracle GoldenGate Delivery for Oracle, rep_01.prm: The definition for table CJCPDB.CJC.DEPT is obtained from the trail file.
2020-04-09T18:04:34.947+0800 INFO OGG-06511 Oracle GoldenGate Delivery for Oracle, rep_01.prm: Using following columns in default map by name: DEPTNO, DNAME, LOC.
2020-04-09T18:04:34.947+0800 INFO OGG-06510 Oracle GoldenGate Delivery for Oracle, rep_01.prm: Using the following key columns for target table CHENPDB.CHEN.DEPT: DEPTNO, DNAME, LOC.
查看进程信息
源端：
查看ext_01进程信息
GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 116> stats ext_01
Sending STATS request to EXTRACT EXT_01 ...
Start of Statistics at 2020-04-09 18:22:29.
Output to ./dirdat/ex:
Extracting from CJCPDB.CJC.DEPT to CJCPDB.CJC.DEPT:
*** Total statistics since 2020-04-09 18:04:15 ***
Total inserts 2.00
Total updates 1.00
Total befores 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
*** Daily statistics since 2020-04-09 18:04:15 ***
Total inserts 2.00
Total updates 1.00
Total befores 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
*** Hourly statistics since 2020-04-09 18:04:15 ***
Total inserts 2.00
Total updates 1.00
Total befores 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
*** Latest statistics since 2020-04-09 18:04:15 ***
Total inserts 2.00
Total updates 1.00
Total befores 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
End of Statistics.
查看pump_01进程信息
GGSCI (cjcos01 as c##ogg@cjcdb/CJCPDB) 117> stats pump_01
Sending STATS request to EXTRACT PUMP_01 ...
Start of Statistics at 2020-04-09 18:25:55.
Output to ./dirdat/dp:
Extracting from CJCPDB.CJC.DEPT to CJCPDB.CJC.DEPT:
*** Total statistics since 2020-04-09 18:04:17 ***
Total inserts 2.00
Total updates 1.00
Total befores 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
*** Daily statistics since 2020-04-09 18:04:17 ***
Total inserts 2.00
Total updates 1.00
Total befores 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
*** Hourly statistics since 2020-04-09 18:04:17 ***
Total inserts 2.00
Total updates 1.00
Total befores 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
*** Latest statistics since 2020-04-09 18:04:17 ***
Total inserts 2.00
Total updates 1.00
Total befores 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
End of Statistics.
目标端：
查看rep_01进程信息
GGSCI (cjcos02 as ogg@chendb/CHENPDB) 77> stats rep_01
Sending STATS request to REPLICAT REP_01 ...
Start of Statistics at 2020-04-09 18:28:34.
Integrated Replicat Statistics:
Total transactions 4.00
Redirected 0.00
Replicated procedures 0.00
DDL operations 0.00
Stored procedures 0.00
Datatype functionality 0.00
Operation type functionality 0.00
Event actions 0.00
Direct transactions ratio 0.00%
Replicating from CJCPDB.CJC.DEPT to CHENPDB.CHEN.DEPT:
*** Total statistics since 2020-04-09 18:04:34 ***
Total inserts 2.00
Total updates 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
*** Daily statistics since 2020-04-09 18:04:34 ***
Total inserts 2.00
Total updates 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
*** Hourly statistics since 2020-04-09 18:04:34 ***
Total inserts 2.00
Total updates 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
*** Latest statistics since 2020-04-09 18:04:34 ***
Total inserts 2.00
Total updates 1.00
Total deletes 1.00
Total upserts 0.00
Total discards 0.00
Total operations 4.00
End of Statistics.
更多数据库相关学习资料，可以查看我的ITPUB博客，网名chenoracle：
http://blog.itpub.net/29785807/
