2017-01-12 10:35:04 WARNING OGG-06439 No unique key is defined for table T. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Logging of supplemental redo log data is already enabled for table SCOTT.T.
TRANDATA for instantiation CSN has been added on table 'SCOTT.T'.
#添加schema级别的trandata
添加schema级别的trandata,命令与添加表级别的相同add schematrandata SCHEMA
另外还需要设置参数enable_goldengate_replication为true
点击(此处)折叠或打开
SQL> show parameter enable_goldengate_replication
NAME TYPE VALUE
------------------------------------ ---------------------- ------------------------------
enable_goldengate_replication boolean TRUE
如果enable_goldengate_replication为false会提示以下错误
点击(此处)折叠或打开
GGSCI (node01 as ogg@inds) 20>add schematrandata scott
ERROR: Operation not supported becauseenable_goldengate_replicationis not set to true.
#在这里我们需要设置参数enable_goldengate_replication为true#
GGSCI (node01 as ogg@inds) 21>add schematrandata scott
2017-01-12 10:59:07 INFO OGG-01788 SCHEMATRANDATA has been added on schema scott.
2017-01-12 10:59:07 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema scott.
当然也可以通过delete schematrandata SCHEMA取消。
7.2、添加checkpoint表
在目标端配置复制进程replicat之前,需要在目标数据库中创建一个checkpoint表。这个checkpoint表是基于GoldenGate checkpoint文件的,它记录了所有GoldenGate可以恢复的checkpoint以及sequence。
尽管这个操作不是必须的,但是Oracle强烈建议使用它,因为它可以使得checkpoint包含在Replicat的事物中,保证了可以从各类失败场景中恢复。
添加的步骤为在目标机器上编辑GLOBALS文件,添加
checkpointtable ogg.checkpoint
然后在ggsci中登陆ogg,使用add checkpoint添加
点击(此处)折叠或打开
GGSCI (node01 as ogg@inds) 9>edit params ./GLOBALS
GGSCI (node01 as ogg@inds) 10>view params ./GLOBALS
checkpointtable ogg.checkpoint
GGSCI (node01 as ogg@inds) 12>add checkpointtable ogg.checkpoint
Successfully created checkpoint table ogg.checkpoint.
GGSCI (node01 as ogg@inds) 13>info checkpointtable ogg.checkpoint
Checkpoint table ogg.checkpoint created 2017-01-12 10:34:26.
阐述一下GLOBALS文件,它是一个全局文件,文件中的参数对全局起作用,其中的参数有mgrservname、checkpointtable、ggschema、ddltable、markertable、outputfiletable。文件名称必须大写。
7.3、配置抽取extract进程
抽取进程在源端运行,负责抓取需要传输的数据。
GGSCI (node01 as ogg@inds) 14>edit params extnd
GGSCI (node01 as ogg@inds) 15>view params extnd
SETENV用于设置操作系统环境变量
GGSCI (node01 as ogg@inds) 16> add extract extnd,tranlog,begin now
EXTRACT added.
表示创建一个extract进程,名称为extnd。注意:这个名称要与edit params extnd里面的名称一致。
#add extract extnd,tranlog,begin now,threads 2#
GGSCI (node01 as ogg@inds) 17>add exttrail ./dirdat/et,extract extnd
EXTTRAIL added.
命令add exttrail创建本地文件./dirdat/et,进程extract进程抽取的源数据转换为数据写入该文件中,进程pump负责读取该文件。
7.4、数据传输pump进程
Pump进程又称为secondly extract进程。如果没有pump进程,则extract进程负责将把抽取来的数据库传递给目标端,配置pump进程还有个好处就是当网络故障时,可以把数据无差错的传递给目标端。
创建parameter pump_sos文件
GGSCI (node01 as ogg@inds) 12> edit params pump_sos
添加params pump_sos进程到ggsci队列
点击(此处)折叠或打开
GGSCI (node01 as ogg@inds) 31> add extract pump_sos,exttrailsource ./dirdat/et
EXTRACT added.
GGSCI (node01 as ogg@inds) 32> add rmttrail ./dirdat/pt,extract pump_sos
RMTTRAIL added.
GGSCI (node01 as ogg@inds) 33> start pump_sos
Sending START request to MANAGER ...
EXTRACT PUMP_SOS starting
GGSCI (node01 as ogg@inds) 34> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTND 00:00:00 00:00:05
EXTRACT RUNNING PUMP_SOS 00:00:00 00:00:07
8、配置目标端
8.1、配置mgr
依照配置源端的mgr参数,配置目标端的mgr,步骤如下
GGSCI (Idota as ogg@mynewdb) 3> edit params mgr
配置完成后,重启mgr进程
8.2、添加replicat复制进程Replicat进程运行在目标端,负责读取源端抽取进程抽取的文件,然后把文件中的变化数据应用目标端,形成数据同步
创建replicat复制进程
在GGSCI中添加replicat进程管理
点击(此处)折叠或打开
GGSCI (Idota as ogg@mynewdb) 12> add replicat repl,exttrail ./dirdat/pt
REPLICAT added.
GGSCI (Idota as ogg@mynewdb) 22> start repl
Sending START request to MANAGER ...
REPLICAT REPL starting
GGSCI (Idota as ogg@mynewdb) 23> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPL 00:00:00 00:00:06
9、DML测试验证同步
检查源端和目标端的scott.dept数据
·源端
·目标端
·源端做DML操作
·目标端数据检查是否同步
OGG很完美的完成基于DML操作的数据同步,但是无法完成基于DDL的操作。如何完成DDL操作同步呢,在ogg 12版本中还需要我们执行几个脚本:
marker_setup.sql、ddl_setup.sql、role_setup.sql、ddl_enable.sql、以及性能优化工具
@?/rdbms/admin/dbmspool.sql
ddl_pin.sql ogg
10、配置DDL复制