OGG双向复制配置

  

[实施目的]

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

[实施步骤]

  1. 添加补充日志和数据库强记日志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;

  1. 为要传送的表,添加日志跟踪

[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.

  1. 配置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

  1. 配置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

  1. target system(gc1)
  1. 配置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.

  1. 配置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

  1. 配置支持DDL复制
  1. 在两个节点执行执行DDL同步脚本命令

先进入goldengate软件安装目录,以SYSDBA身份登录oracle执行以下脚本,执行脚本过程中,需要输入的用户全部是ogg,安装模式为INITIALSETUP,如果数据字典或者某些内部的包有错误,则需要运行catalog.sqlcatproc.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.sqlmarker_remove.sql

  1. 先关闭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

  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;

  1. 配置两个节点的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.

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

  1. 配置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;

  1. 开启进程并验证
  1. 开启进程

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

  1. 验证

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

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

new_tab的日志信息:

ADD TRANDATA scott.new_tab

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

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

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

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

能够同步。

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

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

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

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29819001/viewspace-1255288/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/29819001/viewspace-1255288/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值