ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 利用goldengate进行同步操作测试

利用goldengate进行同步操作测试

原创 Linux操作系统 作者:lirenquan 时间:2011-03-10 11:33:11 0 删除 编辑

1、确认各进程组的状态:
GGSCI (ora10g) 54> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EMP_EXT1    00:00:00      00:00:00   
REPLICAT    RUNNING     EMP_REP1    00:00:00      00:00:05

2、测试最简单的DDL和DML操作
1)测试创建表并插入记录同步
源端:
SQL> select instance_name from v$instance;
INSTANCE_NAME
--------------------------------
orcl
SQL> show user      
USER is "SYS"
SQL> conn test/test
Connected.
SQL> create table tb_rep_ddl3(id int primary key,name varchar2(30));

Table created.

SQL> insert into tb_rep_ddl3 values(1,'lirq');

1 row created.

SQL> insert into tb_rep_ddl3 values(2,'liyx');

1 row created.

SQL> insert into tb_rep_ddl3 values(3,'yaocb');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tb_rep_ddl3;

        ID NAME
---------- ------------------------------------------------------------
         1 lirq
         2 liyx
         3 yaocb
目标端:
SQL> select instance_name from v$instance;

INSTANCE_NAME
--------------------------------
ggtarge

SQL> show user
USER is "TEST"
SQL> select * from tb_rep_ddl3;
select * from tb_rep_ddl3
              *
ERROR at line 1:
ORA-00942: table or view does not exist

分析:
奇怪,怎么没有复制成功。
看看进程状态:

GGSCI (ora10g) 59> info all       

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EMP_EXT1    00:00:00      00:00:02   
REPLICAT    ABENDED     EMP_REP1    00:32:29      00:03:26

靠,replicat组中止了,再查看日志:
GGSCI (ora10g) 60> view ggsevt
...
2010-12-08 08:23:09  ERROR   OGG-00519  Oracle GoldenGate Delivery for Oracle, emp_rep1.prm:  Fatal error executing DDL replication:
 error [Error code [1756], ORA-01756: quoted string not properly terminated, SQL ALTER SESSION SET NLS_CURRENCY=' ' ], no error hand
ler present.
2010-12-08 08:23:09  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, emp_rep1.prm:  PROCESS ABENDING.

最后发现了错误,初步确认是在操作DDL语句时,进行隐含DDL语句alter session造成的错误。这个是DDL错误
查了半天,尝试了N多方法,包括调整ASSUMETARGETDEFS、HANDLECOLLISIONS和REPERROR参数,但发现还是不奏效。甚至将replicat组重建,依然是没能解决这个。差不多绝望到要重装数据库了。
最后天可见怜,通过关键字匹配,找到了如下网页:
http://gavinsoorma.com/2010/07/goldengate-ddl-synchronization-some-more-examples/comment-page-1/
这位国外的大牛,终于帮我解决了问题,简单地来说,就是在replicat组的参数中添加如下内容
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP
以忽略掉一些DDL语句错误带来的
修改内容如下:
GGSCI (ora10g) 61> edit params emp_rep1
REPLICAT emp_rep1
USERID gguser@192.168.0.88:1521/ggtarge, PASSWORD gguser
REPERROR 1756,DISCARD
REPERROR DEFAULT,DISCARD
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP
discardfile /u01/ora10g/discard/rep1.log,append,megabytes 10
ASSUMETARGETDEFS
HANDLECOLLISIONS
MAP test.*, TARGET test.*;

再次调起replicat组,查看状态:
GGSCI (ora10g) 62> start replicat emp_rep1

Sending START request to MANAGER ...
REPLICAT EMP_REP1 starting


GGSCI (ora10g) 63> info all

Program     Status      Group       Lag           Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EMP_EXT1    00:00:00      00:00:06   
REPLICAT    RUNNING     EMP_REP1    00:00:00      00:00:00 

gold,终于回到了久违的RUNNING状态。
去目标端查看结果:
SQL> select * from tb_rep_ddl3;
        ID NAME
---------- ------------------------------------------------------------
         1 lirq
         2 liyx
         3 yaocb
谢天谢地,终于见到了梦寐以求的结果
2)测试简单的delete操作
源端:
SQL> select * from tb_rep_ddl3;
        ID NAME
---------- ------------------------------------------------------------
         1 lirq
         2 yaocb
         3 liyx
         4 lirq2

SQL> delete from tb_rep_ddl3;

4 rows deleted.
SQL> commit;

Commit complete.
目标端:
SQL> select * from tb_rep_ddl3;

no rows selected

SQL>

简单的delete操作完全没问题

3)测试简单的update操作
源端:
SQL> select * from tb_rep_ddl3;

        ID NAME
---------- ------------------------------------------------------------
         1 lirq

SQL> update tb_rep_ddl3 set name='liyx';

1 row updated.

SQL> commit;

Commit complete.

SQL>  select * from tb_rep_ddl3;

        ID NAME
---------- ------------------------------------------------------------
         1 liyx
目标端:
SQL> select * from tb_rep_ddl3;

        ID NAME
---------- ------------------------------------------------------------
         1 lirq
为啥这么简单的update操作都没有同步?
但是有个奇怪的现象是,针对数值型的更新,是可以同步的,如下示意:
源端:
SQL> alter table tb_rep_ddl3 add value number(1) default 3;

Table altered.

SQL> select * from tb_rep_ddl3;

        ID NAME
---------- ------------------------------------------------------------
     VALUE
----------
         1 liyx
         3


SQL> update tb_rep_ddl3 set value=4 where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from tb_rep_ddl3;

        ID NAME
---------- ------------------------------------------------------------
     VALUE
----------
         1 liyx
         4
目标端:

SQL> select * from tb_rep_ddl3;

        ID NAME
---------- ------------------------------------------------------------
     VALUE
----------
         1 lirq
         3


SQL> select * from tb_rep_ddl3;

        ID NAME
---------- ------------------------------------------------------------
     VALUE
----------
         1 lirq
         4
肯定又是哪个参数没加好,经查reference文档,发现需要在replicat参数中添加GETUPDATEBEFORES
修改后如下:
REPLICAT emp_rep1
USERID gguser@192.168.0.88:1521/ggtarge, PASSWORD gguser
REPERROR 1756,DISCARD
REPERROR DEFAULT,DISCARD
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP
discardfile /u01/ora10g/discard/rep1.log,append,megabytes 10
ASSUMETARGETDEFS
HANDLECOLLISIONS
GETUPDATEBEFORES
MAP test.*, TARGET test.*;
再测试:
源端:
SQL>  select * from tb_rep_ddl3;

        ID NAME
---------- ------------------------------------------------------------
     VALUE
----------
         1 liyx
         4


SQL> update tb_rep_ddl3 set name='yaocb' where id=1;

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from tb_rep_ddl3;

        ID NAME
---------- ------------------------------------------------------------
     VALUE
----------
         1 yaocb
         4
目标端:

SQL>  select * from tb_rep_ddl3;

        ID NAME
---------- ------------------------------------------------------------
     VALUE
----------
         1 liyx
         4


SQL> select * from tb_rep_ddl3;

        ID NAME
---------- ------------------------------------------------------------
     VALUE
----------
         1 yaocb
         4

4)测试truncate操作

3、测试ctas操作的同步
源端:
SQL> create table tb_rep_ctas as
  2  select * from dba_objects;

Table created.

SQL> desc tb_rep_ctas
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

SQL> select bytes from user_segments where segment_name='TB_REP_CTAS';

     BYTES
----------
   6291456

SQL> select count(1),count(distinct object_id) from tb_rep_ctas;

  COUNT(1) COUNT(DISTINCTOBJECT_ID)
---------- ------------------------
     50746                    50746

SQL> select count(1),count(distinct object_id) from dba_objects;

  COUNT(1) COUNT(DISTINCTOBJECT_ID)
---------- ------------------------
     50746                    50746
目标端:
SQL> desc tb_rep_ctas
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 OWNER                                                                                                                      VARCHAR2(30)
 OBJECT_NAME                                                                                                                VARCHAR2(128)
 SUBOBJECT_NAME                                                                                                             VARCHAR2(30)
 OBJECT_ID                                                                                                                  NUMBER
 DATA_OBJECT_ID                                                                                                             NUMBER
 OBJECT_TYPE                                                                                                                VARCHAR2(19)
 CREATED                                                                                                                    DATE
 LAST_DDL_TIME                                                                                                              DATE
 TIMESTAMP                                                                                                                  VARCHAR2(19)
 STATUS                                                                                                                     VARCHAR2(7)
 TEMPORARY                                                                                                                  VARCHAR2(1)
 GENERATED                                                                                                                  VARCHAR2(1)
 SECONDARY                                                                                                                  VARCHAR2(1)
SQL> select bytes from user_segments where segment_name='TB_REP_CTAS';

     BYTES
----------
   6291456
SQL> select count(1),count(distinct object_id) from tb_rep_ctas;

  COUNT(1) COUNT(DISTINCTOBJECT_ID)
---------- ------------------------
     50083                    50083
SQL> select count(1),count(distinct object_id) from dba_objects;

  COUNT(1) COUNT(DISTINCTOBJECT_ID)
---------- ------------------------
     50083                    50083

两边表结构占用空间大小完全一样,但是数据结果不一样。
分析原因,发现GOLDENGATE在默认情况下在处理DDL语句时,在TRAIL文件中生成的是同步的SQL语句,不是数据内容。对于这种同步的方式,因为两边在操作ctas语句时,对应的源表是各自的dba_objects,因此结果可能会不一样。

4、测试复杂的DML操作
1)insert into ... select操作
源端:
SQL> alter table tb_rep_ctas add constraint pk_object_id primary key(object_id);

Table altered.
SQL> select constraint_name,table_name from user_constraints where table_name='TB_REP_CTAS';

CONSTRAINT_NAME
------------------------------------------------------------
TABLE_NAME
------------------------------------------------------------
PK_OBJECT_ID
TB_REP_CTAS
SQL> insert into TB_REP_CTAS select * from dba_objects;

50747 rows created.

SQL> commit;

Commit complete.

SQL> select count(1),count(distinct object_id) from tb_rep_ctas;

  COUNT(1) COUNT(DISTINCTOBJECT_ID)
---------- ------------------------
     50750                    50750
目标端:
SQL> select count(1),count(distinct object_id) from tb_rep_ctas;

  COUNT(1) COUNT(DISTINCTOBJECT_ID)
---------- ------------------------
     50750                    50750
注:根据插入记录的多少,处理记录会有或多或少的延时。
2)关联更新语句
源端:
SQL> update tb_rep_ctas set object_name='LIRQ'
  2  where object_id in
  3  (select object_id from dba_objects where rownum<=10);

10 rows updated.

SQL> commit;

Commit complete.

SQL> select count(1) from tb_rep_ctas where object_name='LIRQ';

  COUNT(1)
----------
        10

目标端:
SQL> select count(1) from tb_rep_ctas where object_name='LIRQ';

  COUNT(1)
----------
        10
3)rowid进行去重删除
源端:
SQL> delete from tb_rep_ctas t1
  2  where rowid<(select max(rowid) from tb_rep_ctas t2 where t1.object_name=t2.object_name)
  3  ;

20420 rows deleted.

SQL> commit;

Commit complete.

SQL>  select count(1),count(distinct object_id) from tb_rep_ctas;

  COUNT(1) COUNT(DISTINCTOBJECT_ID)
---------- ------------------------
     30330                    30330
目标端:
SQL> select count(1),count(distinct object_id) from tb_rep_ctas;

  COUNT(1) COUNT(DISTINCTOBJECT_ID)
---------- ------------------------
     30330                    30330

强大,以rowid进行的删除,居然都可以实现同步。不过由于删除大量记录,加上虚拟机资源有限,所以整个延时比较明显。

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

请登录后发表评论 登录
全部评论

注册时间:2010-12-24

  • 博文量
    24
  • 访问量
    108878