OGG简单实验配置【成功运行】

1.源端配置，使用ORACLE系统用户,库用户为goldengate,源端与目标端进行GG实验用户为AIKI

SQL> create user goldengate identified by goldengate_10 
SQL> GRANT resource,connect,dba to goldengate;   

SQL> select SUPPLEMENTAL_LOG_DATA_MIN from v$database;  
SUPPLEME  
--------  
NO  
SQL> alter database add supplemental log data; 

#su –  oracle  
>ggsci  
GGSCI (pmahdb01) 1> create subdirs  
GGSCI (kfctest) 33> edit param mgr 

port 7839
DYNAMICPORTLIST 7840-7845
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

GGSCI (pmahdb01) 1> dblogin userid goldengate, password goldengate_10  

GGSCI (pmahdb01) 3> add trandata aiki.* 

GGSCI (pmahdb01) 6> add extract extyxa, tranlog,  begin now 

GGSCI (pmahdb01) 7> add EXTTRAIL ./dirdat/r1, extract extyxa,MEGABYTES 100 

GGSCI (kfctest) 34>  edit params extyxa   
extract extyxa
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID goldengate,PASSWORD goldengate_10
exttrail ./dirdat/r1,megabytes 100
dynamicresolution
TABLE aiki.*;

GGSCI (pmahdb01) 8>start extyxa  

GGSCI (pmahdb01) 1>add extract dpyxa,exttrailsource  ./dirdat/r1 ,begin now  

GGSCI (pmahdb01) 2>add rmttrail ./dirdat/t1,EXTRACT dpyxa,MEGABYTES 100 

GGSCI (pmahdb01) 3> edit params dpyxa   

GGSCI (kfctest) 35> edit params dpyxa  
extract dpyxa
dynamicresolution
passthru
rmthost 172.16.108.144, mgrport 7839, compress
rmttrail ./dirdat/t1
numfiles 5000
table aiki.*;

接下来是在目标端的配置

SQL> create user goldengate identified by goldengate_10 
SQL> GRANT resource,connect,dba to goldengate;   

#su –  oracle  
>ggsci  
GGSCI (pmahdb01) 1> create subdirs

GGSCI (kfc11g) 31> edit params mgr


port 7839
DYNAMICPORTLIST 7840-7845
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

GGSCI (pmahdb01) 3> start mgr 

连接数据库

dblogin userid goldengate, password goldengate_10 

添加检查点表

add checkpointtable goldengate.checktable 

增加复制进程并与队列地址绑定

add replicat repyxa exttrail ./dirdat/t1, checkpointtable  goldengate.checktable  

GGSCI (kfc11g) 32> edit param repyxa  


REPLICAT repyxa
SETENV (NLS_LANG = "American_America.ZHS16GBK")
SETENV (ORACLE_SID=kfc11g)
USERID goldengate,PASSWORD  goldengate_10
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
numfiles 5000
HANDLECOLLISIONS
assumetargetdefs
DISCARDFILE ./dirrpt/repyxa.dsc, APPEND, MEGABYTES 1000
GETTRUNCATES
ALLOWNOOPUPDATES

MAP aiki.*, TARGET aiki.*;

启动GoldenGate软件

GGSCI> start repyxa

测试验证和错误解决

错误1：

GGSCI (kfctest) 17> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     DPYXA       00:00:00      00:00:00    
EXTRACT     STOPPED     EXTYXA      00:00:00      22:12:55 

这种情况一般在EXTYXA参数文件配置上有误，可以采用最小化参数方法，将不可预料的参数先扔走缩小范围排查；

错误2：源端进行了DML操作，目标端没有同步而是显示

GGSCI (kfc11g) 22> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED                                           
REPLICAT    ABENDED     REPYXA      00:00:00      00:16:30

解决：查看日志GGSCI (kfc11g) 10> view ggsevt，看来是跟目标端的表上没有主键或唯一性索引有关，创建上，重启服务

start repyxa

--------------------------------

2013-01-06 09:19:48  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, repyxa.prm:  No unique key is defined for table 'TT1'.
All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
2013-01-06 09:19:48  WARNING OGG-01431  Oracle GoldenGate Delivery for Oracle, repyxa.prm:  Aborted grouped transaction on 'AIKI.TT1',
 Mapping error.
2013-01-06 09:19:48  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, repyxa.prm:  Repositioning to rba 1023 in seqno 0.
2013-01-06 09:19:48  WARNING OGG-01151  Oracle GoldenGate Delivery for Oracle, repyxa.prm:  Error mapping from AIKI.TT1 to AIKI.TT1.
2013-01-06 09:19:48  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, repyxa.prm:  Repositioning to rba 1023 in seqno 0.
2013-01-06 09:19:48  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, repyxa.prm:  Error mapping from AIKI.TT1 to AIKI.TT1.
2013-01-06 09:19:48  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, repyxa.prm:  PROCESS ABENDING.

3.查看两边状态

GGSCI (kfctest) 37> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
EXTRACT     RUNNING     DPYXA       00:00:00      00:00:08    
EXTRACT     RUNNING     EXTYXA      00:00:00      00:00:09

GGSCI (kfc11g) 33> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     REPYXA      00:00:00      00:00:00

4.操作测试

源端 SQL> select count(*) from tt1;  COUNT(*)----------         0SQL> insert into tt1 select * from dba_objects where rownum<6;5 rows created.SQL> commit;Commit complete.SQL> select count(*) from tt1;  COUNT(*)----------         5目标端  SQL> /  COUNT(*)----------         0SQL> l  1* select count(*) from tt1SQL> /  COUNT(*)----------         5同步成功！

备注：EXTRACT其它参数记录

TRANLOGOPTIONS EXCLUDEUSER goldengate
TRANLOGOPTIONS convertucs2clobs
GETTRUNCATES
REPORTCOUNT EVERY 1 MINUTES, RATE
numfiles 5000
DIYXARDFILE ./dirrpt/extyxa.dsc,APPEND,MEGABYTES 1000
DIYXARDROLLOVER AT 3:00