Oracle GoldenGate Oracle to MySQL配置

1. 数据库环境介绍：

源数据库

操作系统：Red Hat 6.4

数据库：Oracle 11gR2

IP：172.29.9.76

HOSTNAME：szdb1

安装目录：/u01/app/oracle

ORACLE_HOME：/u01/app/oracle/product/11.2.0/dbhome_1

目标数据库

操作系统：Red Hat 6.4

数据库：MySQL 5.6

IP：172.29.9.45

HOSTNAME：szdb2

安装目录：/var/lib/mysql

2.源库服务器(szdb1)添加环境变量vi /etc/profile

SOFTWARE_LOCATION=/u01/app/oracle/ogg

DATABASE_LOCATION=/u01/app/oracle/product/11.2.0/dbhome_1

LD_LIBRARY_PATH=$ORACLE_BASE/ogg:$ORACLE_HOME/lib

export SOFTWARE_LOCATION DATABASE_LOCATION LD_LIBRARY_PATH

注：使环境变量立即生效：source /etc/profile

3.源库服务器(szdb1)创建安装目录

# su - oracle

$ mkdir /u01/app/oracle/ogg

4.源库服务器(szdb1)上传安装包并解压，然后进行安装

$ pwd

/home/oracle

$ ll

total 5948592

-rw-r--r-- 1 root root 475611228 Sep 19 16:12 fbo_ggs_Linux_x_shiphome.zip $ unzip fbo_ggs_Linux_x_shiphome.zip

$ chown -R oracle:oinstall fbo_ggs_Linux_x_shiphome

5.使用VNC登录源库服务器(szdb1)进行安装

# xhost +

access control disabled, clients can connect from any host

# su - oracle

Last login: Mon Sep 26 15:27:28 CST 2016 on pts/0

$ cd /home/oracle/fbo_ggs_Linux_x_shiphome/Disk1/

$ ./runInstaller

6.目标库服务器(szdb2)创建安装目录

# mkdir ogg

7.目标库服务器(szdb2)上传安装包到/ogg并解压安装

# unzip ggs_Linux_x_MySQL_bit.zip

# tar -xvf ggs_Linux_x_MySQL_bit.tar

# ./ggsci

GGSCI (szdb2) 1> create subdirs

Creating subdirectories under current directory /ogg

Parameter files /ogg/dirprm: created

Report files /ogg/dirrpt: created

Checkpoint files /ogg/dirchk: created

Process status files /ogg/dirpcs: created

SQL script files /ogg/dirsql: created

Database definitions files /ogg/dirdef: created

Extract data files /ogg/dirdat: created

Temporary files /ogg/dirtmp: created

Credential store files /ogg/dircrd: created

Masterkey wallet files /ogg/dirwlt: created

Dump files /ogg/dirdmp: created8.在源库给OGG分配用户，并执行OGG的配置脚本

1)表空间、用户的创建，用户权限的授予

SQL> create tablespace ts_ogg

datafile '/u01/app/oracle/oradata/orcl/ts_ogg.dbf'

size 500M; 2 3

Tablespace created.

SQL> create user oggadmin identified by "oggadmin"

default tablespace ts_ogg

quota unlimited on ts_ogg; 2 3

User created.

SQL> grant connect,resource to oggadmin;

Grant succeeded.

SQL> grant execute on utl_file to oggadmin;

Grant succeeded.

2)执行OGG的配置脚本

SQL> host pwd

/u01/app/oracle/ogg

SQL> show user

USER is "SYS"

SQL> @ marker_setup.sql

SQL> @ ddl_setup.sql

SQL> @ role_setup.sql

SQL> grant GGS_GGSUSER_ROLE to oggadmin;

注：执行上述的脚本，在出现输入提示的时候，输入oggadmin用户。

9.在源库上启用归档，强制日志与补充日志数据

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 751595520 bytes

Fixed Size 2257032 bytes

Variable Size 461377400 bytes

Database Buffers 281018368 bytesRedo Buffers 6942720 bytes

Database mounted.

SQL> alter database archivelog

2 /

Database altered.

SQL> alter database force logging

2 /

Database altered.

SQL> alter database add supplemental log data

2 /

Database altered.

SQL> alter database add supplemental log data (primary key) columns

2 /

Database altered.

SQL> alter database open

2 /

Database altered.

SQL> select log_mode,force_logging,supplemental_log_data_min from v$database;

LOG_MODE FOR SUPPLEME

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

ARCHIVELOG YES YES

1 row selected.

10.在源库创建需要同步的数据

SQL> create user ggtest identified by 123456

default tablespace ts_ogg

quota unlimited on ts_ogg; 2 3

User created.

SQL> grant connect,resource to ggtest;

Grant succeeded.

SQL> conn ggtest/123456

Connected.

SQL> create table tt (id number,name varchar2(20)); Table created.

SQL> insert into tt values(1,'abc');

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> create table gg (dd number,kk date);

Table created.

SQL> insert into gg values(1,sysdate);

1 row created.

SQL> /

1 row created.

SQL> /

1 row created.SQL> /

1 row created.

SQL> commit;

Commit complete.

SQL> select * from tt;

ID NAME

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

1 abc

1 abc

1 abc

1 abc

4 rows selected.

SQL> select * from gg;

DD KK

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

1 12-JAN-17

1 12-JAN-17

1 12-JAN-17

1 12-JAN-17

4 rows selected.

11.源库服务器(szdb1)上OGG的配置

1)配置mgr进程

[oracle@szdb1 ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

Linux, x, bit (optimized), Oracle 11g on Dec 12 2015 00:54:38

Operating system character set identified as UTF-8.

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

GGSCI (szdb1) 1> edit params mgrport 7809

dynamicportlist 7810-7820,7830

autostart er *

autorestart er *,retries 4,waitminutes 4

startupvalidationdelay 5

purgeoldextracts /u01/app/oracle/ogg/dirdat/*,usecheckpoints,minkeephours 2

2)配置extract进程

GGSCI (szdb1) 2> add extract ext1,tranlog,begin now

EXTRACT added.

GGSCI (szdb1) 3> add exttrail /u01/app/oracle/ogg/dirdat/et,extract ext1

EXTTRAIL added.

GGSCI (szdb1) 4> edit params ext1

extract ext1

userid oggadmin,password oggadmin

exttrail /u01/app/oracle/ogg/dirdat/et

table ggtest.*;

3)配置pump进程

GGSCI (szdb1) 5> add extract pump1,exttrailsource /u01/app/oracle/ogg/dirdat/et,begin now

EXTRACT added.

GGSCI (szdb1) 6> add rmttrail /ogg/dirdat/rt,extract pump1

RMTTRAIL added.

GGSCI (bigdb) 7> edit params pump1

extract pump1

userid oggadmin, password oggadmin

rmthost 172.29.9.45, mgrport 9900

rmttrail /ogg/dirdat/rt

passthru

table ggtest.*;

4)创建defgen参数文件

[oracle@szdb1 ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle

Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401_FBO

Linux, x, bit (optimized), Oracle 11g on Dec 12 2015 00:54:38Operating system character set identified as UTF-8.

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

GGSCI (szdb1) 1> edit params defgen

userid oggadmin password oggadmin

defsfile /u01/app/oracle/ogg/dirdef/oracle.def

table ggtest.*;

5)在OGG安装目录下执行以下命令生成数据定义文件oracle.def

[oracle@szdb1 ogg]$ pwd

/u01/app/oracle/ogg

[oracle@szdb1 ogg]$ ./defgen paramfile dirprm/defgen.prm

*********************************************************************** Oracle GoldenGate Table Definition Generator for Oracle

Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401 Linux, x, bit (optimized), Oracle 11g on Dec 11 2015 21:37:21

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

Starting at 2017-01-12 14:42:51

***********************************************************************

Operating System Version:

Linux

Version #1 SMP Tue Jan 29 11:47:41 EST 2013, Release 2.6.32-358.el6.x86_

Node: bigdb

Machine: x86_

soft limit hard limit

Address Space Size : unlimited unlimited

Heap Size : unlimited unlimited

File Size : unlimited unlimited

CPU Time : unlimited unlimited

Process id: 8735

*********************************************************************** ** Running with the following parameters **

*********************************************************************** userid oggadmin password ***defsfile /u01/app/oracle/ogg/dirdef/oracle.def

table ggtest.*;

Expanding wildcard table specification ggtest.*:

Retrieving definition for GGTEST.GG.

2017-01-12 14:43:01 WARNING OGG-039 No unique key is defined for table GG.

All viable columns will be used to represent the key, but may not guarantee uniqueness.

KEYCOLS may be used to define the key.

Retrieving definition for GGTEST.TT.

2017-01-12 14:43:01 WARNING OGG-039 No unique key is defined for table TT.

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 2 tables in /u01/app/oracle/ogg/dirdef/oracle.def.

6)将源库服务器(szdb1)的数据定义文件oracle.def拷贝到目录库服务器

(szdb2)/ogg/dirdef目录下

[oracle@szdb1 dirdef]$ pwd

/u01/app/oracle/ogg/dirdef

[oracle@szdb1 dirdef]$ scp oracle.def ftpuser@172.29.9.45:/ogg/dirdef

The authenticity of host '172.29.9.45 (172.29.9.45)' can't be established.

RSA key fingerprint is dc:3e:43:c6:6a:0f:0a:fa:9c:c7:26:6d::38:09:c9.

Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added '172.29.9.45' (RSA) to the list of known hosts.

ftpuser@172.29.9.45's password:

oracle.def

12.在目标库创建需要同步的表结构

[root@szdb2 /]# mysql -uroot -p

Enter password:

Welcome to the MySQL monitor. Commands end with ; or \\g.

Your MySQL connection id is 4338

Server version: 5.6.35-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.mysql> create database ggtest;

Query OK, 1 row affected (0.00 sec)

mysql> use ggtest;

Database changed

mysql> create table TT (id int,name varchar(20));

Query OK, 0 rows affected (0.11 sec)

mysql> create table GG (dd int,kk datetime);

Query OK, 0 rows affected (0.01 sec)

13.在目标库创建用于OGG使用登录的数据库，用户

mysql> create database ogg;

Query OK, 1 row affected (0.00 sec)

mysql> grant all privileges on *.* to oggadmin@"%" identified by 'oggadmin';

Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

14.目标库服务器(szdb1)的配置

1)mgr进程的配置

[root@szdb2 ogg]# ./ggsci

Oracle GoldenGate Command Interpreter for MySQL

Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401

Linux, x, bit (optimized), MySQL Enterprise on Dec 11 2015 16:23:51

Operating system character set identified as UTF-8.

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

GGSCI (szdb2) 1> edit params mgr

port 9900

dynamicportlist 9901-9920,9930

autostart er *

autorestart er *,retries 4,waitminutes 4

startupvalidationdelay 5

purgeoldextracts /ogg/dirdat/*,usecheckpoints,minkeephours 2

~

GGSCI (szdb2) 2> dblogin sourcedb ogg@172.29.9.45:3306 userid oggadmin password

oggadmin

Successfully logged into database.

GGSCI (szdb2 DBLOGIN as oggadmin) 3> add checkpointtable ogg.checkpoint_table

Successfully created checkpoint table ogg.checkpoint_table.

2)replicat 进程的配置

GGSCI (szdb2) 1> add replicat rep1,exttrail /ogg/dirdat/rt,checkpointtable

ogg.checkpoint_table

REPLICAT added.

GGSCI (szdb2) 2> edit params rep1

replicat rep1

targetdb ogg@172.29.9.45:3306 userid oggadmin password oggadmin

sourcedefs /ogg/dirdef/oracle.def

discardfile /ogg/dirrpt/rep1.dsc,purge

map ggtest.*,target ggtest.*;

15.使用工具把源库对应数据导入到目标库中

16.在目标库服务器(szdb2)上启动mgr进程

GGSCI (szdb2) 11> start mgr

Manager started.

GGSCI (szdb2) 12> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REP1 00:00:00 00:00:01

17.在源库服务器(szdb1)上启动mgr进程

GGSCI (szdb1) 19> start mgr

Manager started.

GGSCI (szdb1) 20> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNINGEXTRACT RUNNING EXT1 00:00:00 00:00:01 EXTRACT RUNNING PUMP1 00:00:00 00:00:07

18.测试

1)源库

SQL> select dd,to_char(kk,'yyyy-mm-dd hh24:mi:ss') kk from gg;

no rows selected

SQL> insert into gg values(1,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select dd,to_char(kk,'yyyy-mm-dd hh24:mi:ss') kk from gg;

DD KK

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

1 2017-01-1

2 17:38:42

2)目标库

mysql> use ggtest;

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> SELECT * FROM GG;

Empty set (0.00 sec)

mysql> SELECT * FROM GG;

+------+---------------------+

| dd | kk |

+------+---------------------+

| 1 | 2017-01-12 17:38:42 |

+------+---------------------+

1 row in set (0.00 sec)