[实施目的]

1、OGG双向复制配置

[项目环境]

source system(gc5)

操作系统	RedHat 5.4
主机名	GC5
数据库版本	Oracle 10.2.0.1.0
字符集	ZHS16GBK
生产库实例名	EMREP
监听	LISTENER/1521

target system(gc1)

操作系统	RedHat 5.4
主机名	GC1
数据库版本	Oracle 10.2.0.1.0
字符集	ZHS16GBK
生产库实例名	PROD
监听	LISTENER/1521

[实施步骤]

添加补充日志和数据库强记日志source system并授权

SQL> alter database add supplemental log data;

Database altered.

SQL> alter system switch logfile;

SQL> alter database force logging;

Database altered.

SQL> grant alter any table to ogg;

为要传送的表，添加日志跟踪

[oracle@gc5 ogg]$ ./ggsci

GGSCI (gc5) 1> DBLOGIN USERID ogg, PASSWORD ogg

Successfully logged into database.

GGSCI (gc5) 2> ADD TRANDATA scott.EMP_OGG

Logging of supplemental redo data enabled for table SCOTT.EMP_OGG.

GGSCI (gc5) 3> ADD TRANDATA scott.DEPT_OGG

Logging of supplemental redo data enabled for table SCOTT.DEPT_OGG.

GGSCI (gc5) 4> INFO TRANDATA scott.*

Logging of supplemental redo log data is disabled for table SCOTT.BONUS.

Logging of supplemental redo log data is disabled for table SCOTT.DEPT.

Logging of supplemental redo log data is enabled for table SCOTT.DEPT_OGG

Logging of supplemental redo log data is disabled for table SCOTT.EMP.

Logging of supplemental redo log data is enabled for table SCOTT.EMP_OGG

Logging of supplemental redo log data is disabled for table SCOTT.SALGRADE.

配置EXTRACT进程

GGSCI (gc5) 5> EDIT PARAMS EORA_1

EXTRACT EORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

TRANLOGOPTIONS EXCLUDEUSER ogg

EXTTRAIL ./dirdat/aa

TABLE scott.emp_ogg;

TABLE scott.dept_ogg;

GGSCI (gc5) 6> ADD EXTRACT EORA_1, TRANLOG, BEGIN NOW

EXTRACT added.

GGSCI (gc5) 7> ADD EXTTRAIL ./dirdat/aa, EXTRACT EORA_1, MEGABYTES 5

EXTTRAIL added.

GGSCI (gc5) 8> START EXTRACT EORA_1

Sending START request to MANAGER ...

EXTRACT EORA_1 starting

GGSCI (gc5) 9> INFO EXTRACT EORA_1

EXTRACT EORA_1 Last Started 2014-08-12 14:58 Status RUNNING

Checkpoint Lag 00:00:28 (updated 00:00:09 ago)

Log Read Checkpoint Oracle Redo Logs

2014-08-12 14:58:24 Seqno 7, RBA 58384

配置pump进程

GGSCI (gc5) 10> EDIT PARAMS PORA_1

EXTRACT PORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

PASSTHRU

RMTHOST gc1, MGRPORT 7809

RMTTRAIL ./dirdat/pa

TABLE scott.emp_ogg;

TABLE scott.dept_ogg;

GGSCI (gc5) 11> ADD EXTRACT PORA_1, EXTTRAILSOURCE ./dirdat/aa

EXTRACT added.

GGSCI (gc5) 12> ADD RMTTRAIL ./dirdat/pa, EXTRACT PORA_1, MEGABYTES 5

RMTTRAIL added.

GGSCI (gc5) 13> START EXTRACT PORA_1

Sending START request to MANAGER ...

EXTRACT PORA_1 starting

GGSCI (gc5) 14> info EXTRACT PORA_1

EXTRACT PORA_1 Last Started 2014-08-12 15:01 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:00 ago)

Log Read Checkpoint File ./dirdat/aa000000

First Record RBA 895

target system(gc1)

配置checkpoint

[oracle@gc1 ~]$ cd /u01/app/ogg

[oracle@gc1 ogg]$ ./ggsci

GGSCI (gc1) 1> EDIT PARAMS ./GLOBALS

CHECKPOINTTABLE ogg.ggschkpt

GGSCI (gc1) 1> DBLOGIN USERID ogg, PASSWORD ogg

Successfully logged into database.

GGSCI (gc1) 2> ADD CHECKPOINTTABLE

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

Successfully created checkpoint table OGG.GGSCHKPT.

配置replicate

GGSCI (gc1) 3> ADD REPLICAT RORA_1, EXTTRAIL ./dirdat/pa

REPLICAT added.

GGSCI (gc1) 4> EDIT PARAM RORA_1

REPLICAT RORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE ./dirrpt/RORA_aa.DSC, PURGE

MAP scott.*, TARGET scott.*;

GGSCI (gc1) 5> START REPLICAT RORA_1

Sending START request to MANAGER ...

REPLICAT RORA_1 starting

GGSCI (gc1) 6> INFO REPLICAT RORA_1

REPLICAT RORA_1 Last Started 2014-08-12 15:07 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:00 ago)

Log Read Checkpoint File ./dirdat/pa000000

First Record RBA 0

配置支持DDL复制

在两个节点执行执行DDL同步脚本命令

先进入goldengate软件安装目录，以SYSDBA身份登录oracle执行以下脚本，执行脚本过程中，需要输入的用户全部是ogg，安装模式为INITIALSETUP，如果数据字典或者某些内部的包有错误，则需要运行catalog.sql和catproc.sql脚本。

[oracle@gc5 ogg]$ !sql

sqlplus '/as sysdba'

SQL> alter system set recyclebin=off scope=spfile; --关闭回收站

SQL> shutdown immediate

SQL> startup

SQL>@marker_setup

SQL>@ddl_setup

SQL>@role_setup

SQL>grant GGS_GGSUSER_ROLE to ogg;

SQL>@ddl_enable

如果某项脚本执行错误，需要重新执行时，先要执行清除的脚本：ddl_remove.sql和marker_remove.sql

先关闭eora_1进程、PORA_1进程、RORA_1进程

source system

GGSCI (gc5) 2> stop extract eora_1

GGSCI (gc5) 3> stop extract PORA_1

GGSCI (gc5) 4> stop REPLICAT rora_1

target system

GGSCI (gc1) 4> stop extract eora_1

GGSCI (gc1) 5> stop extract PORA_1

GGSCI (gc1) 6> stop REPLICAT rora_1

配置两个节点的extract，在eora_1配置文件中添加以下一行：

DDL INCLUDE OBJNAME "scott.*"

最终的内容如下：

GGSCI (gc1) 3> edit param eora_1

-- Change Capture parameter file to capture

-- EMP_OGG and DEPT_OGG changes

EXTRACT EORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

EXTTRAIL ./dirdat/aa

DDL INCLUDE OBJNAME "scott.*"

TABLE scott.EMP_OGG;

TABLE scott.DEPT_OGG;

GGSCI (gc5) 1> edit param eora_1

EXTRACT EORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

TRANLOGOPTIONS EXCLUDEUSER ogg

EXTTRAIL ./dirdat/aa

DDL INCLUDE OBJNAME "scott.*"

TABLE scott.emp_ogg;

TABLE scott.dept_ogg;

配置两个节点的replicat

source system

GGSCI (gc5) 5> DBLOGIN USERID ogg, PASSWORD ogg

Successfully logged into database.

GGSCI (gc5) 6> add checkpointtable ogg.checkpoint

Successfully created checkpoint table OGG.CHECKPOINT.

Target system

GGSCI (gc1) 7> DBLOGIN USERID ogg, PASSWORD ogg

Successfully logged into database.

GGSCI (gc1) 8> add checkpointtable ogg.checkpoint

Successfully created checkpoint table OGG.CHECKPOINT.

如果之前已经添加过，则无需操作。

配置REPLICAT进程参数文件，添加以下几行到rora_1配置文件中

DDL INCLUDE ALL

DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5

DDLERROR DEFAULT DISCARD

DDLERROR DEFAULT IGNORE RETRYOP

最终的内容如下：

GGSCI (gc5) 7> edit param rora_1

-- Change Delivery parameter file to apply

-- EMP_OGG and DEPT_OGG Changes

REPLICAT RORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE ./dirrpt/RORA_aa.DSC, PURGE

DDL INCLUDE ALL

DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5

DDLERROR DEFAULT DISCARD

DDLERROR DEFAULT IGNORE RETRYOP

MAP scott.emp_ogg, TARGET scott.emp_ogg;

MAP scott.dept_ogg, TARGET scott.dept_ogg;

GGSCI (gc1) 9> edit param rora_1

REPLICAT RORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

USERID ogg, PASSWORD ogg

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE ./dirrpt/RORA_aa.DSC, PURGE

DDL INCLUDE ALL

DDLERROR DEFAULT IGNORE RETRYOP MAXRETRIES 3 RETRYDELAY 5

DDLERROR DEFAULT DISCARD

DDLERROR DEFAULT IGNORE RETRYOP

MAP scott.emp_ogg, TARGET scott.emp_ogg;

MAP scott.dept_ogg, TARGET scott.dept_ogg;

开启进程并验证

开启进程

source system

GGSCI (gc5) 2> start extract eora_1

GGSCI (gc5) 3> start extract PORA_1

GGSCI (gc5) 4> start REPLICAT rora_1

GGSCI (gc5) 12> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EORA_1 00:00:00 00:00:05

EXTRACT RUNNING PORA_1 00:00:00 00:00:02

REPLICAT RUNNING RORA_1 00:00:00 00:00:04

target system

GGSCI (gc1) 4> start extract eora_1

GGSCI (gc1) 5> start extract PORA_1

GGSCI (gc1) 6> start REPLICAT rora_1

GGSCI (gc1) 13> info all

Program Status Group Lag Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EORA_1 00:00:00 00:00:06

EXTRACT RUNNING PORA_1 07:02:41 00:00:04

REPLICAT RUNNING RORA_1 00:00:00 00:00:02

验证

source system 插入数据

[oracle@gc5 ogg]$ !sql

SQL> conn scott/tiger

SQL> select * from dept_ogg;

DEPTNO DNAME LOC

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

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> INSERT INTO dept_ogg VALUES(10,'aa','bb');

SQL> commit;

Commit complete.

SQL> select * from dept_ogg;

DEPTNO DNAME LOC

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

30 SALES CHICAGO

40 OPERATIONS BOSTON

10 aa bb

target system 查询数据，发现数据已经过来了

[oracle@gc1 ogg]$ !sql

SQL> conn scott/tiger

SQL> select * from dept_ogg;

DEPTNO DNAME LOC

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

30 SALES CHICAGO

40 OPERATIONS BOSTON

10 aa bb

target system 再删掉数据

SQL> delete from dept_ogg where deptno=10;

SQL> commit;

SQL> select * from dept_ogg;

DEPTNO DNAME LOC

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

30 SALES CHICAGO

40 OPERATIONS BOSTON

source system 查看数据，发现数据已应用

SQL> select * from dept_ogg;

DEPTNO DNAME LOC

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

30 SALES CHICAGO

40 OPERATIONS BOSTON

补充：

如果新建的表进行同步，update可能不成功，需要进行一下操作，这样子ogg才会去捕

捉new_tab的日志信息：

ADD TRANDATA scott.new_tab；

注意：先关闭rora_1进程，再添加，然后重新启动。

如果是序列，无需关注，因为oracle是取得序列的值进行insert的。
如果源端建表的时候是基于子查询，如果子查询中访问的表在目标端没有，则无法实现

ddl同步。或者子查询中的表如果数据不一样，则同步的表数据也不一样，根据各自数据库的子查询中的表来定。

如果是insert操作，数据基于子查询，如果子查询访问的表目标端没有，却不受影响，

能够同步。

update某行时，如果目标数据库没有符合条件的行，ogg会insert一条新行。

解决办法：在最新的GG11.2版本中针对此种情况设置了专门的冲突解决机制，在11.2以前配置解决这个冲突很复杂

(11:16:58 AM) louise.liang@oracle.com: 在initial load 中有一个参数可以避免这个冲突，但是在正常复制时一般不建议用