GoldenGate DDL双向复制

  

  继续上一篇的实验。

  节点说明： 
  dd1(源库)--->>kf2(目标库)
  dd1(目标库)<<---kf2(源库)

 

  在配置反向复制过程中，可暂时把源库和目标库调换位置，配置基本上雷同。
 
  但在官网上有说明要注意的一个地方：
  Do either of the following to  specify the Replicat database us er. All transactions generated
  by this user will be excluded from being captured. This information is available to Extract
  in the transaction record.
  ● Identify the Replicat database user by name with the following parameter statement
  in the Extract parameter file.
  TRANLOGOPTIONS EXCLUDEUSER <user name>
 
  该参数加到主端和备端的extract 参数文件中，否则会出现死循环复制。

 

1. 在dd1上配置全局参数

 

  GGSCI (dd1) 1> view params ./GLOBALS
 
  ggschema ogguser
 
 
  GGSCI (dd1) 2> edit params ./GLOBALS
 
 
  ggschema ogguser
  checkpointtable ogguser.checkpoint
 
 
  GGSCI (dd1) 3> dblogin userid ogguser@test1,password ogguser         
  Successfully logged into database.
 
  添加checkpoint表：
  GGSCI (dd1) 4> add checkpointtable
  ERROR: Missing checkpoint table specification.
 
  GGSCI (dd1) 5> add checkpointtable ogguser.checkpoint
 
  Successfully created checkpoint table OGGUSER.CHECKPOINT.
 

 

2. 在kf2上确认全局参数，开启附加日志，强制日志，归档

 

  GGSCI (kf2.calvin) 4> view params ./GLOBALS
 
  GGSCHEMA ogguser
  CHECKPOINTTABLE ogguser.checkpoint
 
 
  SQL> alter database add supplemental log data;
 
  Database altered.
 
  SQL> alter system switch logfile;
 
  System altered.
 
  SQL> alter database force logging;
 
  Database altered.
 
  SQL> alter system set recyclebin=off scope=spfile;
   
  System altered.
 
  SQL> select log_mode,supplemental_log_data_min,force_logging from v$database;
 
  LOG_MODE     SUPPLEME FOR
  ------------ -------- ---
  ARCHIVELOG   YES      YES
 

3. 在kf2上配置ddl支持
 
  SQL> @marker_setup.sql
 
  SQL> @ddl_setup.sql
 
  SQL> @role_setup.sql
 
  SQL> grant GGS_GGSUSER_ROLE to ogguser;
 
  SQL> @ddl_enable.sql
 
  SQL> @?/rdbms/admin/dbmspool.sql
 
  SQL> @ddl_pin.sql ogguser

 

4. 在kf2上添加extract，exttrail，并配置参数

 

  GGSCI (kf2.calvin) 5> dblogin userid ogguser@test2,password ogguser
  Successfully logged into database.
 
  GGSCI (kf2.calvin) 6> add extract ext1,tranlog,begin now
  EXTRACT added.
 
 
  GGSCI (kf2.calvin) 7> add exttrail /data/oracle/ogg11/dirdat/lt,extract ext1
  EXTTRAIL added.
 
  GGSCI (kf2.calvin) 8> edit params ext1
  extract ext1
  userid ogguser@test2, password ogguser
  tranlogoptions excludeuser ogguser   ##避免死循环复制，同样的，dd1上的extract test1参数也要设置
  rmthost 192.168.130.168, mgrport 7809
  rmttrail /data/oracle/ogg11/dirdat/lt
  ddl include mapped objname stat.*;
  table stat.*;
 
 
  GGSCI (kf2.calvin) 4> info all
 
  Program     Status      Group       Lag           Time Since Chkpt
 
  MANAGER     RUNNING                                          
  EXTRACT     RUNNING     EXT1        00:00:00      00:10:24   
  REPLICAT    RUNNING     TEST2       00:00:00      00:00:07
 

 

5. 在dd1上 添加replicat，extrtrail，并配置参数

 

  GGSCI (dd1) 4> add replicat ext2,exttrail /data/oracle/ogg11/dirdat/lt,checkpointtable ogguser.checkpoint
  REPLICAT added.
 
  GGSCI (dd1) 5> edit params ext2
  replicat ext2
  ASSUMETARGETDEFS
  userid ogguser@test1,password ogguser
  discardfile /data/oracle/ogg11/dirdat/test1_discard.txt,append, megabytes 10
  DDL INCLUDE MAPPED
  DDLERROR DEFAULT IGNORE RETRYOP
  map stat.*, target stat.*;
 
 
  GGSCI (dd1) 6> start replicat ext2
 
  Sending START request to MANAGER ...
  REPLICAT EXT2 starting
 
 
  GGSCI (dd1) 7> info all
 
  Program     Status      Group       Lag           Time Since Chkpt
 
  MANAGER     RUNNING                                          
  EXTRACT     RUNNING     TEST1       00:00:00      00:00:06   
  REPLICAT    RUNNING     EXT2        00:00:00      00:00:01
  
 

6. 测试数据双向同步

 

  kf2:
  SQL> create table tp_test2(name varchar2(10));
 
  Table created.
 
  SQL> insert into tp_test2 values('calvin'); ##在kf2上插入数据
 
  1 row created.
 
  SQL> commit;
 
  Commit complete.
 
  SQL> select * from tp_test2;
 
  NAME
  ----------
  calvin
 
  1 row selected
 
 
  dd1:
  SQL> select * from tp_test2;
 
  NAME
  ----------
  calvin          ##数据已经同步
 
  SQL> insert into tp_test2 values('fish'); ##在dd1上插入数据
 
  1 row created.
 
  SQL> commit;
 
  Commit complete.
 
  SQL> select * from tp_test2;
 
  NAME
  ----------
  calvin
  fish
 
  kf2:
  SQL> select * from tp_test2;
 
  NAME
  ----------
  calvin
  fish           ##数据已经同步