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 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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12350275/viewspace-688931/