Oracle To Mysql (Use Goldengate)

最新推荐文章于 2023-08-21 23:15:00 发布

zbdba

最新推荐文章于 2023-08-21 23:15:00 发布

阅读量1.7k

点赞数 1

分类专栏： GoldenGate 文章标签： oracle mysql glodengate

本文链接：https://blog.csdn.net/zbdba/article/details/44034675

版权

GoldenGate 专栏收录该内容

29 篇文章 0 订阅

订阅专栏

安装环境：

Source:
OS:redhat 6.3
IP:192.168.56.60
DB:Oracle 11.2.0.4

Target:
OS:redhat 6.3
IP:192.168.56.13
DB:Mysql 5.6.14

Goldengate for mysql：
https://edelivery.oracle.com/EPD/Download/get_form?egroup_aru_number=14841438

安装配置source端Goldengate：

useradd ogg -g oinstall

Add Environment Varible
vi /home/oracle/.bash_profile
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib:/ogg

Configure Database
alter database archivelog;
alter database force logging;
alter database add supplemental log data;

select SUPPLEMENTAL_LOG_DATA_MIN, SUPPLEMENTAL_LOG_DATA_PK,
SUPPLEMENTAL_LOG_DATA_UI from v$database;

ALTER SYSTEM SET recyclebin = OFF scope=spfile; for Oracle 11g ,need to be restart


Create OGG User:
sqlplus / as sysdba
create user ogg identified by ogg;
grant dba to ogg;


GGSCI (zbdba1) 1> edit params ./GLOBAL
GGSCHEMA ogg
CHECKPOINTTABLE ogg.checkpoint

注意goldengate for mysql 不支持ddl

MANAGER
GGSCI (zbdba1) 1>  create subdirs

GGSCI (zbdba1) 22> view param mgr

port 7839
DYNAMICPORTLIST 7840-7914
USERID OGG,PASSWORD ogg
AUTORESTART REPLICAT *, RETRIES 5, WAITMINUTES 3
PURGEOLDEXTRACTS ./DIRDAT/*,MINKEEPDAYS 3
PURGEDDLHISTORY MINKEEPDAYS 7,MAXKEEPDAYS 10,FREQUENCYMINUTES 30
PURGEMARKERHISTORY MINKEEPDAYS 7,MAXKEEPDAYS 10
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
 
EXTRACT
GGSCI (zbdba1) 1> dblogin userid ogg,password ogg
GGSCI (zbdba1) 1> add trandata zbdba.*
add extract ext1,tranlog, begin now
add exttrail /ogg/dirdat/sa  extract ext1
add rmttrail ./dirdat/sa,extract ext1
GGSCI (zbdba1) 9> view param ext1

EXTRACT EXT1
setenv ( NLS_LANG = AMERICAN_AMERICA.ZHS16GBK )
USERID ogg,PASSWORD ogg
DDL &
INCLUDE MAPPED OBJTYPE 'TABLE' &
INCLUDE MAPPED OBJTYPE 'IDNEX' &
INCLUDE MAPPED OBJTYPE 'SEQUENCE' &
INCLUDE MAPPED OBJTYPE 'VIEW' &
INCLUDE MAPPED OBJTYPE 'PROCEDURE' &
INCLUDE MAPPED OBJTYPE 'FUNCTION' &
INCLUDE MAPPED OBJTYPE 'PACKAGE' &
INCLUDE MAPPED OBJTYPE 'MATERIALIZED VIEW' &
EXCLUDE OPTYPE COMMENT
DDLOPTIONS ADDTRANDATA NOCROSSRENAME REPORT
REPORTCOUNT EVERY 1 MINUTES,RATE
DISCARDFILE ./dirrpt/EXTSA.DSC,APPEND,MEGABYTES 1024
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000 IOLATENCY 60000
DBOPTIONS ALLOWUNUSEDCOLUMN
WARNLONGTRANS 2H,CHECKINTERVAL 3M
exttrail ./dirdat/sa
FETCHOPTIONS NOUSESNAPSHOT,FETCHPKUPDATECOLS,MISSINGROW REPORT
table zbdba.*;


datapump
add extract ext2,exttrailsource /ogg/dirdat/sa
ADD EXTTRAIL ./dirdat/sa, EXTRACT EXT2
add rmttrail  /ogg/dirdat/sa, extract ext2



GGSCI (zbdba1) 2>  view param ext2

EXTRACT EXT2
passthru
RMTHOST 192.168.56.61,MGRPORT 7839,COMPRESS
RMTTRAIL ./dirdat/sa
table zbdba.*;

Target端安装配置ogg:

useradd ogg -g mysql

GGSCI (zbdba2) 1>  create subdirs
GGSCI (zbdba2) 52> view param ./GLOBAL

CHECKPOINTTABLE zbdba.checkpoint

GGSCI (zbdba2) 53> view param mgr

port 7839
DYNAMICPORTLIST 7840-7914
AUTORESTART REPLICAT *, RETRIES 5, WAITMINUTES 3
PURGEOLDEXTRACTS ./DIRDAT/*,MINKEEPDAYS 3
PURGEDDLHISTORY MINKEEPDAYS 7,MAXKEEPDAYS 10,FREQUENCYMINUTES 30
PURGEMARKERHISTORY MINKEEPDAYS 7,MAXKEEPDAYS 10
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45


vi /etc/my.cnf
socket =/tmp/mysql.sock
GGSCI (zbdba2) 1> dblogin sourcedb zbdba userid root
GGSCI (zbdba2) 2> add checkpointtable  zbdba.checkpoint

Successfully created checkpoint table ZBDBA.CHECKPOINT.

GGSCI (zbdba2) 2>  add replicat rep1,exttrail /ogg/dirdat/sa, checkpointtable ogg.checkpoint
REPLICAT added.
GGSCI (zbdba2) 2> add checkpointtable  zbdba.checkpoint

Successfully created checkpoint table OGG.CHECKPOINT.

GGSCI (zbdba2) 2>  add replicat rep1,exttrail /ogg/dirdat/sa, checkpointtable ogg.checkpoint
REPLICAT added.

GGSCI (zbdba) 55> view param rep1

REPLICAT rep1
sourcedefs /ogg/dirdef/ext2mysql.def
ASSUMETARGETDEFS
SOURCEDB zbdba,userid root,password mysql
discardfile ./dirdat/rep1_discard.txt,append,megabytes 5
DDL INCLUDE MAPPED
HANDLECOLLISIONS
DDLERROR DEFAULT DISCARD RETRYOP MAXRETRIES 5 RETRYDELAY 20
--grouptransops 1
--maxtransops 1
APPLYNOOPUPDATES
MAP zbdba.*, TARGET zbdba.*;

进行初始化：

Source：
ADD EXTRACT initload, SOURCEISTABLE
view param initload
EXTRACT initload
userid ogg, password ogg
RMTHOST 192.168.56.13, MGRPORT 7839, COMPRESS
RMTTASK REPLICAT, GROUP repload
TABLE ZBDBA.*;

创建数据定义文件：

edit param defgen
defsfile /ogg/dirdef/ext2mysql.def
userid ogg,password ogg
table zbdba.*;

[ogg@zbdba1 ogg]$ ./defgen paramfile dirprm/defgen.prm

***********************************************************************
        Oracle GoldenGate Table Definition Generator for Oracle
      Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
   Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 05:08:19

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.


                    Starting at 2015-03-02 03:21:52
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Wed Jun 13 18:24:36 EDT 2012, Release 2.6.32-279.el6.x86_64
Node: zbdba1
Machine: x86_64
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 3935

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
defsfile /ogg/dirdef/ext2mysql.def
userid ogg,password ***
table zbdba.*;
Expanding wildcard zbdba.*:

Retrieving definition for ZBDBA.ZBDBA

2015-03-02 03:21:56  WARNING OGG-00869  No unique key is defined for table 'ZBDBA'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.



Definitions generated for 1 table in /ogg/dirdef/ext2mysql.def

scp ext2mysql.def 192.168.56.13:/ogg/dirdef/

Target:
创建表，与远端保持一样表结构

mysql> create table ZBDBA (name varchar(20));
Query OK, 0 rows affected (0.08 sec)


ADD REPLICAT repload, SPECIALRUN

GGSCI (zbdba2) 56> view param repload

replicat REPLOAD
sourcedefs /ogg/dirdef/ext2mysql.def
sourcedb zbdba userid root, password mysql
reperror default, discard
discardfile ./dirrpt/ext_in.dsc,APPEND,MEGABYTES 1000
MAP zbdba.*, TARGET zbdba.*;

Source:
start mgr
GGSCI (zbdba2) 8> start initload

Sending START request to MANAGER ...
EXTRACT INITLOAD starting
监控日志：
2015-03-02 02:59:17  INFO    OGG-00993  Oracle GoldenGate Capture for Oracle, initload.prm:  EXTRACT INITLOAD started.
2015-03-02 02:59:37  INFO    OGG-00953  Oracle GoldenGate Manager for Oracle, mgr.prm:  Purging log history from OGG.GGS_DDL_HIST older than 2015-02-20 01:59:37.619129: 0 rows deleted from OGG.GGS_DDL_HIST.
2015-03-02 02:59:37  INFO    OGG-00953  Oracle GoldenGate Manager for Oracle, mgr.prm:  Purging log history from OGG.GGS_DDL_HIST_ALT older than 2015-02-20 01:59:37.634179: 0 rows deleted from OGG.GGS_DDL_HIST_ALT.
2015-03-02 02:59:43  INFO    OGG-00991  Oracle GoldenGate Capture for Oracle, initload.prm:  EXTRACT INITLOAD stopped normally.

Target:
2015-03-02 02:59:41  INFO    OGG-00996  Oracle GoldenGate Delivery for MySQL, repload.prm:  REPLICAT REPLOAD started.
2015-03-02 02:59:41  INFO    OGG-03010  Oracle GoldenGate Delivery for MySQL, repload.prm:  Performing implicit conversion of column data from character set windows-936 to UTF-8.
2015-03-02 02:59:46  INFO    OGG-00994  Oracle GoldenGate Delivery for MySQL, repload.prm:  REPLICAT REPLOAD stopped normally.
2015-03-02 03:00:00  WARNING OGG-00952  Oracle GoldenGate Manager for MySQL, mgr.prm:  Purging log history from GGSUSER.GGS_DDL_HIST older than 2015-02-20 02:00:00.061727:.
2015-03-02 03:00:00  WARNING OGG-00952  Oracle GoldenGate Manager for MySQL, mgr.prm:  Purging log history from GGSUSER.GGS_DDL_HIST_ALT older than 2015-02-20 02:00:00.061727:.

查看目标端数据是否同步过来：

mysql> use zbdba;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+-----------------+
| Tables_in_zbdba |
+-----------------+
| ZBDBA           |
| checkpoint      |
+-----------------+
2 rows in set (0.00 sec)

mysql> select * from ZBDBA;
+-------+
| name  |
+-------+
| zbdba |
+-------+
1 row in set (0.00 sec)

发现数据已经同步
注意mysql表明区分大小写

这时候开启抽取进程和投递进程

Source:
GGSCI (zbdba1) 1> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
EXTRACT     RUNNING     EXT1        00:00:00      00:00:10   
EXTRACT     RUNNING     EXT2        00:00:00      00:00:00   

Target:

GGSCI (zbdba2) 57> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                          
REPLICAT    RUNNING     REP1        00:00:00      00:07:32

zbdba

关注

1
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
Oracle To Mysql (Use Goldengate)

安装环境：Source:OS:redhat 6.3IP:192.168.56.60DB:Oracle 11.2.0.4Target:OS:redhat 6.3IP:192.168.56.13DB:Mysql 5.6.14Goldengate for mysql：https://edelivery.oracle.com/EPD/Download/get_form?egr
复制链接

扫一扫