oracle并行拆分,goldengateogg常用调优方法–并行处理与进程拆分

为了提高性能&＃xff0c;我们分别配置了五个replicat进程来对数据进行插入操作

Oracle数据库的replicat

ADD REPLICAT rep1, EXTTRAIL /home/oracle/ogg/dirdat/ma, BEGIN now

ADD REPLICAT rep2, EXTTRAIL /home/oracle/ogg/dirdat/ma, BEGIN now

ADD REPLICAT rep3, EXTTRAIL /home/oracle/ogg/dirdat/ma, BEGIN now

ADD REPLICAT rep4, EXTTRAIL /home/oracle/ogg/dirdat/ma, BEGIN now

ADD REPLICAT rep5, EXTTRAIL /home/oracle/ogg/dirdat/ma, BEGIN now

REPLICAT rep1

USERID goldengate,PASSWORD goldengate

ASSUMETARGETDEFS

REPERROR (default,abend)

DBOPTIONS LIMITROWS DEFERREFCONST

BATCHSQL BATCHESPERQUEUE 1000, BATCHTRANSOPS 2000 ,OPSPERBATCH 6000 , OPSPERQUEUE 6000

GROUPTRANSOPS 20000

ALLOWNOOPUPDATES

DDL include all

map test.T_BILL_BALANCE_DETAIL_201112_0, target settle.T_BILL_BALANCE_DETAIL_201112_0 , FILTER (&＃64;RANGE (1, 5));

map test.T_BILL_BALANCE_DETAIL_201112_1, target settle.T_BILL_BALANCE_DETAIL_201112_1 , FILTER (&＃64;RANGE (1, 5));

map test.T_BILL_BALANCE_DETAIL_201112_2, target settle.T_BILL_BALANCE_DETAIL_201112_2 , FILTER (&＃64;RANGE (1, 5));

map test.T_BILL_BALANCE_DETAIL_201112_3, target settle.T_BILL_BALANCE_DETAIL_201112_3 , FILTER (&＃64;RANGE (1, 5));

map test.T_BILL_BALANCE_DETAIL_201112_4, target settle.T_BILL_BALANCE_DETAIL_201112_4 , FILTER (&＃64;RANGE (1, 5));

map test.T_ACCOUNT_1, target settle.T_ACCOUNT_1;

map test.T_SERVICE_1, target settle.T_SERVICE_1;

map test.T_CUSTOMER_1, target settle.T_CUSTOMER_1;

MAP TEST.TF01 ,TARGET SETTLE.TF01 , COLMAP (F2&＃61;F2,F4&＃61;F4,F6&＃61;F6,F1&＃61;&＃64;COLSTAT (NULL),F3&＃61;&＃64;COLSTAT (NULL),F5&＃61;&＃64;COLSTAT (NULL),dd&＃61;&＃64;datenow (

));

MAP TEST.*, TARGET SETTLE.*;

REPLICAT rep2

USERID goldengate,PASSWORD goldengate

ASSUMETARGETDEFS

REPERROR (default,abend)

DBOPTIONS LIMITROWS DEFERREFCONST

BATCHSQL BATCHESPERQUEUE 1000, BATCHTRANSOPS 2000 ,OPSPERBATCH 6000 , OPSPERQUEUE 6000

GROUPTRANSOPS 20000

DDL exclude all

map test.T_BILL_BALANCE_DETAIL_201112_0, target settle.T_BILL_BALANCE_DETAIL_201112_0 , FILTER (&＃64;RANGE (2, 5));

map test.T_BILL_BALANCE_DETAIL_201112_1, target settle.T_BILL_BALANCE_DETAIL_201112_1 , FILTER (&＃64;RANGE (2, 5));

map test.T_BILL_BALANCE_DETAIL_201112_2, target settle.T_BILL_BALANCE_DETAIL_201112_2 , FILTER (&＃64;RANGE (2, 5));

map test.T_BILL_BALANCE_DETAIL_201112_3, target settle.T_BILL_BALANCE_DETAIL_201112_3 , FILTER (&＃64;RANGE (2, 5));

map test.T_BILL_BALANCE_DETAIL_201112_4, target settle.T_BILL_BALANCE_DETAIL_201112_4 , FILTER (&＃64;RANGE (2, 5));

REPLICAT rep3

USERID goldengate,PASSWORD goldengate

ASSUMETARGETDEFS

REPERROR (default,abend)

DBOPTIONS LIMITROWS DEFERREFCONST

BATCHSQL BATCHESPERQUEUE 1000, BATCHTRANSOPS 2000 ,OPSPERBATCH 6000 , OPSPERQUEUE 6000

GROUPTRANSOPS 20000

DDL exclude all

map test.T_BILL_BALANCE_DETAIL_201112_0, target settle.T_BILL_BALANCE_DETAIL_201112_0 , FILTER (&＃64;RANGE (3, 5));

map test.T_BILL_BALANCE_DETAIL_201112_1, target settle.T_BILL_BALANCE_DETAIL_201112_1 , FILTER (&＃64;RANGE (3, 5));

map test.T_BILL_BALANCE_DETAIL_201112_2, target settle.T_BILL_BALANCE_DETAIL_201112_2 , FILTER (&＃64;RANGE (3, 5));

map test.T_BILL_BALANCE_DETAIL_201112_3, target settle.T_BILL_BALANCE_DETAIL_201112_3 , FILTER (&＃64;RANGE (3, 5));

map test.T_BILL_BALANCE_DETAIL_201112_4, target settle.T_BILL_BALANCE_DETAIL_201112_4 , FILTER (&＃64;RANGE (3, 5));

REPLICAT rep4

USERID goldengate,PASSWORD goldengate

ASSUMETARGETDEFS

REPERROR (default,abend)

DBOPTIONS LIMITROWS DEFERREFCONST

BATCHSQL BATCHESPERQUEUE 1000, BATCHTRANSOPS 2000 ,OPSPERBATCH 6000 , OPSPERQUEUE 6000

GROUPTRANSOPS 20000

DDL exclude all

map test.T_BILL_BALANCE_DETAIL_201112_0, target settle.T_BILL_BALANCE_DETAIL_201112_0 , FILTER (&＃64;RANGE (4, 5));

map test.T_BILL_BALANCE_DETAIL_201112_1, target settle.T_BILL_BALANCE_DETAIL_201112_1 , FILTER (&＃64;RANGE (4, 5));

map test.T_BILL_BALANCE_DETAIL_201112_2, target settle.T_BILL_BALANCE_DETAIL_201112_2 , FILTER (&＃64;RANGE (4, 5));

map test.T_BILL_BALANCE_DETAIL_201112_3, target settle.T_BILL_BALANCE_DETAIL_201112_3 , FILTER (&＃64;RANGE (4, 5));

map test.T_BILL_BALANCE_DETAIL_201112_4, target settle.T_BILL_BALANCE_DETAIL_201112_4 , FILTER (&＃64;RANGE (4, 5));

REPLICAT rep5

USERID goldengate,PASSWORD goldengate

ASSUMETARGETDEFS

REPERROR (default,abend)

DBOPTIONS LIMITROWS DEFERREFCONST

BATCHSQL BATCHESPERQUEUE 1000, BATCHTRANSOPS 2000 ,OPSPERBATCH 6000 , OPSPERQUEUE 6000

GROUPTRANSOPS 20000

DDL exclude all

map test.T_BILL_BALANCE_DETAIL_201112_0, target settle.T_BILL_BALANCE_DETAIL_201112_0 , FILTER (&＃64;RANGE (5, 5));

map test.T_BILL_BALANCE_DETAIL_201112_1, target settle.T_BILL_BALANCE_DETAIL_201112_1 , FILTER (&＃64;RANGE (5, 5));

map test.T_BILL_BALANCE_DETAIL_201112_2, target settle.T_BILL_BALANCE_DETAIL_201112_2 , FILTER (&＃64;RANGE (5, 5));

map test.T_BILL_BALANCE_DETAIL_201112_3, target settle.T_BILL_BALANCE_DETAIL_201112_3 , FILTER (&＃64;RANGE (5, 5));

map test.T_BILL_BALANCE_DETAIL_201112_4, target settle.T_BILL_BALANCE_DETAIL_201112_4 , FILTER (&＃64;RANGE (5, 5));

mysql 数据库的replicat

replicat rep1

DBOPTIONS HOST 127.0.0.1, CONNECTIONPORT 3306

TARGETDB ogg,userid goldengate,password goldengate

SOURCEDEFS /home/oracle/ogg/mysql/dirdef/record.def

reperror (default,abend)

MAP TEST.TESTSQL, TARGET OGG.TESTSQL;

MAP TEST.T_BILL_BALANCE_DETAIL_201112_9, TARGET OGG.T_BILL_BALANCE_DETAIL_201112_9 , FILTER (&＃64;RANGE (1, 5));

replicat rep2

DBOPTIONS HOST 127.0.0.1, CONNECTIONPORT 3306

TARGETDB ogg,userid goldengate,password goldengate

SOURCEDEFS /home/oracle/ogg/mysql/dirdef/record.def

reperror (default,abend)

MAP TEST.T_BILL_BALANCE_DETAIL_201112_9, TARGET OGG.T_BILL_BALANCE_DETAIL_201112_9 , FILTER (&＃64;RANGE (2, 5));

replicat rep3

DBOPTIONS HOST 127.0.0.1, CONNECTIONPORT 3306

TARGETDB ogg,userid goldengate,password goldengate

SOURCEDEFS /home/oracle/ogg/mysql/dirdef/record.def

reperror (default,abend)

MAP TEST.T_BILL_BALANCE_DETAIL_201112_9, TARGET OGG.T_BILL_BALANCE_DETAIL_201112_9 , FILTER (&＃64;RANGE (3, 5));

replicat rep4

DBOPTIONS HOST 127.0.0.1, CONNECTIONPORT 3306

TARGETDB ogg,userid goldengate,password goldengate

SOURCEDEFS /home/oracle/ogg/mysql/dirdef/record.def

reperror (default,abend)

MAP TEST.T_BILL_BALANCE_DETAIL_201112_9, TARGET OGG.T_BILL_BALANCE_DETAIL_201112_9 , FILTER (&＃64;RANGE (4, 5));