简单配置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)

oracleogg双向复制GoldenGate