简单配置GoldenGate双向复制

在完成ogg的单向复制配置后，自然会想着向前推进一层，实现双向复制；在实际应用中，双向复制面临着许多问题，主要有如下几点：

1. 如果两个库同时更新同一条记录 如何处理？

2. 如果网络出现失败如何处理？

3. 如果数据不同步后如何修复？

本文介绍如何在前文的基础上简单实现ogg的双向复制！双向复制一般用于双业务中心环境下，目前笔者的生产环境中未使用到ogg，ogg系列的文章只是从技术上提前做一个准备，因而许多问题的细节未能理清，后续将继续学习研究！在开始之前，请先配置好db1-db2的单向复制(include ddl replicat)! 参考：

http://ylw6006.blog.51cto.com/470441/903752

http://ylw6006.blog.51cto.com/470441/904373

一：配置db1，添加checkpoint表(本文db1和db2互为source和target，因而直接采用db1和db2来标识两台数据库服务器)

GGSCI (db1) 3> view params ./GLOBALS  

ggschema ogg  

checkpointtable ogg.ggschkpt  

GGSCI (db1) 4> exit  

[oracle@db1 ogg]$ ggsci   

GGSCI (db1) 2> dblogin userid ogg,password ogg  

Successfully logged into database.  

GGSCI (db1) 3> add checkpointtable  

No checkpoint table specified, using GLOBALS specification (ogg.ggschkpt)...  

Successfully created checkpoint table ogg.ggschkpt. 

二：配置db2,运行相关的脚本，支持DDL的复制

[oracle@db2 ogg]$ sqlplus /nolog  

SQL> conn /as sysdba  

Connected.  

SQL> grant execute on utl_file to ogg;  

Grant succeeded.  

[oracle@db2 ogg]$ ggsci   

GGSCI (db2) 1> view params ./GLOBALS  

ggschema ogg  

checkpointtable ogg.ggschkpt  

[oracle@db2 ogg]$ sqlplus /nolog  

SQL> conn /as sysdba  

Connected.  

SQL> @marker_setup.sql    

SQL> @ddl_setup.sql  

SQL> @role_setup.sql  

SQL> grant ggs_ggsuser_role to ogg;  

SQL> @ddl_enable.sql    

SQL> @?/rdbms/admin/dbmspool.sql     

SQL> @ddl_pin.sql ogg   

三：db2上配置extract和pump进程

[oracle@db2 ogg]$ ggsci   

GGSCI (db2) 1> dblogin userid ogg,password ogg  

Successfully logged into database.  

GGSCI (db2) 2> add trandata hr.*  

GGSCI (db2) 5> view params eora_t2  

extract eora_t2  

setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)   

userid ogg,password ogg  

tranlogoptions excludeuser ogg  //避免出现死循环复制，db1上的extract进程也需要进行此项设置  

exttrail ./dirdat/ab  

table hr.*;  

GGSCI (db2) 6> add extract eora_t2,tranlog,begin now  

EXTRACT added.  

GGSCI (db2) 7> add exttrail ./dirdat/ab,extract eora_t2,megabytes 100  

EXTTRAIL added.  

GGSCI (db2) 8> start extract eora_t2  

Sending START request to MANAGER ...  

EXTRACT EORA_T2 starting  

GGSCI (db2) 13> view params pora_t2  

extract pora_t2  

setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)  

passthru  

rmthost 192.168.123.10,mgrport 7809  

rmttrail ./dirdat/pb  

table hr.*;  

GGSCI (db2) 14> add extract pora_t2,exttrailsource ./dirdat/ab  

EXTRACT added.  

GGSCI (db2) 15> add rmttrail ./dirdat/pb extract pora_t2,megabytes 100  

RMTTRAIL added.  

GGSCI (db2) 19> start extract pora_t2  

Sending START request to MANAGER ...  

EXTRACT PORA_T2 starting  

GGSCI (db2) 20> info all  

Program     Status      Group       Lag at Chkpt  Time Since Chkpt  

MANAGER     RUNNING                                             

EXTRACT     RUNNING     EORA_T2     00:00:00      00:00:04      

EXTRACT     RUNNING     PORA_T2     00:00:00      00:01:10      

REPLICAT    RUNNING     RORA_T1     00:00:00      00:00:04    

四：db1上配置replicat进程

GGSCI (db1) 7> view params rora_t2  

replicat rora_t2  

setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)  

ddl include all  

ddlerror default ignore retryop maxretries 3 retrydelay 5  

userid ogg,password ogg  

handlecollisions  

assumetargetdefs  

discardfile ./dirrpt/rora_t2.dsc,purge  

map hr.* ,target hr.*;   

GGSCI (db1) 1> add replicat rora_t2,exttrail ./dirdat/pb  

REPLICAT added.  

GGSCI (db1) 2> start replicat rora_t2  

Sending START request to MANAGER ...  

REPLICAT RORA_T2 starting  

GGSCI (db1) 3> info all  

Program     Status      Group       Lag at Chkpt  Time Since Chkpt  

MANAGER     RUNNING                                             

EXTRACT     RUNNING     EORA_T1     00:00:00      00:00:10      

EXTRACT     RUNNING     PORA_T1     00:00:00      00:00:06      

REPLICAT    RUNNING     RORA_T2     00:00:00      00:00:05   

五：测试

[oracle@db1 ~]$ sqlplus hr/hr@db1  

SQL> col location for a20  

SQL> select * from t2;  

        ID NAME                 LOCATION  

---------- -------------------- --------------------  

         2 two                  china  

         1 one  

SQL> update t2 set location='america' where id=1;  

1 row updated.  

SQL> commit;  

Commit complete.  

SQL> select * from t2;  

        ID NAME                 LOCATION  

---------- -------------------- --------------------  

         2 two                  china  

         1 one                  america  

SQL> conn hr/hr@db2  

Connected.  

SQL> select * from t2;  

        ID NAME                 LOCATION  

---------- -------------------- --------------------  

         2 two                  china  

         1 one                  american  

SQL> insert into t2 values (3,'three','japan');  

1 row created.  

SQL> commit;  

Commit complete.  

SQL> conn hr/hr@db1  

Connected.  

SQL> select * from t2;  

        ID NAME                 LOCATION  

---------- -------------------- --------------------  

         2 two                  china  

         1 one                  america  

         3 three                japan  

SQL> alter table t2 add sex char(4);  

Table altered.  

SQL> desc t2  

 Name                                      Null?    Type  

 ----------------------------------------- -------- ----------------------------  

 ID                                        NOT NULL NUMBER  

 NAME                                               VARCHAR2(20)  

 LOCATION                                           VARCHAR2(200)  

 SEX                                                CHAR(4)  

SQL> conn hr/hr@db2  

Connected.  

SQL> desc t2  

 Name                                      Null?    Type  

 ----------------------------------------- -------- ----------------------------  

 ID                                        NOT NULL NUMBER  

 NAME                                               VARCHAR2(20)  

 LOCATION                                           VARCHAR2(200)  

 SEX                                                CHAR(4) 

©著作权归作者所有：来自51CTO博客作者ylw6006的原创作品，谢绝转载，否则将追究法律责任

oracleogg双向复制GoldenGate