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

最新推荐文章于 2022-06-25 17:51:40 发布

原创最新推荐文章于 2022-06-25 17:51:40 发布 · 1.9k 阅读

CC 4.0 BY-SA版权

本文详细介绍了一个使用Oracle GoldenGate进行数据库同步的实际案例。从创建用户、配置GoldenGate参数到解决同步过程中遇到的问题，如检查点表的设置、参数配置错误及解决方法等。此外，还展示了如何通过GoldenGate Manager监控同步进程的状态。

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(*)
----------
         0

SQL> 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(*)
----------
         0

SQL> l
  1* select count(*) from tt1
SQL> /

  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