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?????????? ##數據已經同步

?

?

?