简单配置GoldenGate双向复制
在完成ogg的单向复制配置后,自然会想着向前推进一层,实现双向复制;在实际应用中,双向复制面临着许多问题,主要有如下几点:
1. 如果两个库同时更新同一条记录 如何处理?
2. 如果网络出现失败如何处理?
3. 如果数据不同步后如何修复?
本文介绍如何在前文的基础上简单实现ogg的双向复制!双向复制一般用于双业务中心环境下,目前笔者的生产环境中未使用到ogg,ogg系列的文章只是从技术上提前做一个准备,因而许多问题的细节未能理清,后续将继续学习研究!在开始之前,请先配置好db1-db2的单向复制(include ddl replicat)
一:配置db1,添加checkpoint表(本文db1和db2互为source和target,因而直接采用db1和db2来标识两台数据库服务器)
1. GGSCI (db1) 3> view params ./GLOBALS
2. ggschema ogg
3. checkpointtable ogg.ggschkpt
4.
5. GGSCI (db1) 4> exit
6.
7. [oracle@db1 ogg]$ ggsci
8. GGSCI (db1) 2> dblogin userid ogg,password ogg
9. Successfully logged into database.
10.
11. GGSCI (db1) 3> add checkpointtable
12. No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)...
13. Successfully created checkpoint table ogg.ggschkpt.
二:配置db2,运行相关的脚本,支持DDL的复制
1. [oracle@db2 ogg]$ sqlplus /nolog
2. SQL> conn /as sysdba
3. Connected.
4. SQL> grant execute on utl_file to ogg;
5. Grant succeeded.
6.
7. [oracle@db2 ogg]$ ggsci
8. GGSCI (db2) 1> view params ./GLOBALS
9. ggschema ogg
10. checkpointtable ogg.ggschkpt
11.
12. [oracle@db2 ogg]$ sqlplus /nolog
13. SQL> conn /as sysdba
14. Connected.
15. SQL> @marker_setup.sql
16. SQL> @ddl_setup.sql
17. SQL> @role_setup.sql
18. SQL> grant ggs_ggsuser_role to ogg;
19. SQL> @ddl_enable.sql
20. SQL> @?/rdbms/admin/dbmspool.sql
21. SQL> @ddl_pin.sql ogg
三:db2上配置extract和pump进程
1. [oracle@db2 ogg]$ ggsci
2. GGSCI (db2) 1> dblogin userid ogg,password ogg
3. Successfully logged into database.
4.
5. GGSCI (db2) 2> add trandata hr.*
6. GGSCI (db2) 5> view params eora_t2
7. extract eora_t2
8. setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
9. userid ogg,password ogg
10. tranlogoptions excludeuser ogg //避免出现死循环复制,db1上的extract进程也需要进行此项设置
11. exttrail ./dirdat/ab
12. table hr.*;
13.
14. GGSCI (db2) 6> add extract eora_t2,tranlog,begin now
15. EXTRACT added.
16.
17. GGSCI (db2) 7> add exttrail ./dirdat/ab,extract eora_t2,megabytes 100
18. EXTTRAIL added.
19.
20. GGSCI (db2) 8> start extract eora_t2
21. Sending START request to MANAGER ...
22. EXTRACT EORA_T2 starting
23.
24. GGSCI (db2) 13> view params pora_t2
25. extract pora_t2
26. setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
27. passthru
28. rmthost 192.168.123.10,mgrport 7809
29. rmttrail ./dirdat/pb
30. table hr.*;
31.
32. GGSCI (db2) 14> add extract pora_t2,exttrailsource ./dirdat/ab
33. EXTRACT added.
34.
35. GGSCI (db2) 15> add rmttrail ./dirdat/pb extract pora_t2,megabytes 100
36. RMTTRAIL added.
37.
38. GGSCI (db2) 19> start extract pora_t2
39. Sending START request to MANAGER ...
40. EXTRACT PORA_T2 starting
41.
42. GGSCI (db2) 20> info all
43. Program Status Group Lag at Chkpt Time Since Chkpt
44.
45. MANAGER RUNNING
46. EXTRACT RUNNING EORA_T2 00:00:00 00:00:04
47. EXTRACT RUNNING PORA_T2 00:00:00 00:01:10
48. REPLICAT RUNNING RORA_T1 00:00:00 00:00:04
四:db1上配置replicat进程
1. GGSCI (db1) 7> view params rora_t2
2. replicat rora_t2
3. setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
4. ddl include all
5. ddlerror default ignore retryop maxretries 3 retrydelay 5
6. userid ogg,password ogg
7. handlecollisions
8. assumetargetdefs
9. discardfile ./dirrpt/rora_t2.dsc,purge
10. map hr.* ,target hr.*;
11.
12. GGSCI (db1) 1> add replicat rora_t2,exttrail ./dirdat/pb
13. REPLICAT added.
14.
15. GGSCI (db1) 2> start replicat rora_t2
16. Sending START request to MANAGER ...
17. REPLICAT RORA_T2 starting
18.
19. GGSCI (db1) 3> info all
20. Program Status Group Lag at Chkpt Time Since Chkpt
21.
22. MANAGER RUNNING
23. EXTRACT RUNNING EORA_T1 00:00:00 00:00:10
24. EXTRACT RUNNING PORA_T1 00:00:00 00:00:06
25. REPLICAT RUNNING RORA_T2 00:00:00 00:00:05
五:测试
1. [oracle@db1 ~]$ sqlplus hr/hr@db1
2. SQL> col location for a20
3. SQL> select * from t2;
4.
5. ID NAME LOCATION
6. ---------- -------------------- --------------------
7. 2 two china
8. 1 one
补充:其他综合 ,