最近项目中需要用到goldengate，于是开始学习，但很多东西都没接触过，于是在学习过程中遇到很多问题，通过这个帖子解决了一部分，觉得不错，于是分享给大家，希望对大家有帮助！！
利用goldengate进行同步操作测试
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 [email=gguser@192.168.0.88:1521/ggtarge]gguser@192.168.0.88:1521/ggtarge[/email], 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 [email=gguser@192.168.0.88:1521/ggtarge]gguser@192.168.0.88:1521/ggtarge[/email], 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操作
源端：
SQL> select * from tb_rep_ddl3;
ID NAME
---------- ------------------------------------------------------------
VALUE
----------
1 yaocb
4
SQL> truncate table tb_rep_ddl3;
Table truncated.
SQL> select * from tb_rep_ddl3;
no rows selected
目标端：
SQL> select * from tb_rep_ddl3;
ID NAME
---------- ------------------------------------------------------------
VALUE
----------
1 yaocb
4
SQL> select * from tb_rep_ddl3;
no rows selected
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进行的删除，居然都可以实现同步。不过由于删除大量记录，加上虚拟机资源有限，所以整个延时比较明显。
2877
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
