OGG12实时同步oracle到mysql基础配置

                                                        OGG12实时同步oracle到mysql基础配置
            
系统环境         
1.源库Oracle
Redhat 6.4         
Oracle 11.2.0.4 RAC
IP：192.168.118.119
Ogg: Version 12.1.2.1.0
2.目标库Mysql
CentOS 6.5         
Percona Server 5.7.17
IP：192.168.106.91 
Ogg: Version 12.2.0.1.170221
------------------------------------------------------------------------------------------
Oracle源库配置

1.配置提取进程参数
GGSCI (cktsdb1) 8> view params ex3
extract ex3
tranlogoptions dblogreader
dynamicresolution
userid oggadmin,password oggadmin123
exttrail dirdat/xs
table test.t1;


GGSCI (cktsdb1) 9> add extract ex3,tranlog,begin now,threads 2
EXTRACT added.
GGSCI (cktsdb1) 10> add exttrail dirdat/xs,extract ex3 megabytes 5
EXTTRAIL added.


2.配置投递进程参数


GGSCI (cktsdb1) 13> view params dp3


extract dp3
passthru
dynamicresolution
userid oggadmin,password oggadmin123
rmthost 192.168.106.91,mgrport 7809
rmttrail dirdat/xs
table test.t1;


GGSCI (cktsdb1) 15> add extract dp3 exttrailsource dirdat/xs
EXTRACT added.
GGSCI (cktsdb1) 16> add rmttrail dirdat/xs,extract dp3
RMTTRAIL added.


3.配置define文件
因为是异构，所以define作为2个数据库这间表的关系映射
GGSCI (cktsdb1) 1> edit params t1
defsfile dirdef/t1.prm
userid oggadmin,password oggadmin123
table test.t1；


生成define文件
[oracle@cktsdb1 ogg]$ ./defgen paramfile dirprm/t1.prm


***********************************************************************
        Oracle GoldenGate Table Definition Generator for Oracle
     Version 12.1.2.1.0 OGGCORE_12.1.2.1.0_PLATFORMS_140727.2135.1
   Linux, x64, 64bit (optimized), Oracle 11g on Aug  7 2014 05:45:39
 
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.




                    Starting at 2017-05-22 16:59:23
***********************************************************************


Operating System Version:
Linux
Version #1 SMP Tue Jan 29 11:47:41 EST 2013, Release 2.6.32-358.el6.x86_64
Node: cktsdb1
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: 32025


***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
defsfile dirdef/t1.prm
userid oggadmin,password ***********
table test.t1;
Retrieving definition for TEST.T1.
Definitions generated for 1 table in dirdef/t1.prm.


4.复制define文件到Mysql数据库主机
[root@cktsdb1 ogg]# scp dirdef/t1.prm 192.168.106.91:/home/mysql/dirdef
root@192.168.106.91's password: 
t1.prm                                                                                                          0%    0     0.0KB/s   --:-- ETA
t1.prm                                                                                                        100% 1182     1.2KB/s   00:00    
------------------------------------------------------------------------------------------
Mysql目标库配置


1.创建源库对应的表t1
mysql> create table t1(id int,name varchar(10),primary key(id));
Query OK, 0 rows affected (0.03 sec)
show tables
+----------------+
| Tables_in_test |
+----------------+
| dep            |
| emp            |
| t1             |
+----------------+


2.创建目录create subdirs
[mysql@cqgs_ywjk ~]$ unzip GOLDENGATE\ V12.2.0.1.170221\ FOR\ MySQL_Linux-x86-64.zip 
[mysql@cqgs_ywjk ~]$tar -xvf ggs_Linux_x64_MySQL_64bit.tar
[mysql@cqgs_ywjk ~]$ ./ggsci
Oracle GoldenGate Command Interpreter for MySQL
Version 12.2.0.1.170221 OGGCORE_12.2.0.1.0OGGBP_PLATFORMS_170123.1033
Linux, x64, 64bit (optimized), MySQL Enterprise on Jan 23 2017 13:00:10
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2017, Oracle and/or its affiliates. All rights reserved.
GGSCI (cqgs_ywjk) 1> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     STOPPED                                           


GGSCI (cqgs_ywjk) 3> create subdirs
Creating subdirectories under current directory /home/mysql
Parameter files                /home/mysql/dirprm: created
Report files                   /home/mysql/dirrpt: created
Checkpoint files               /home/mysql/dirchk: created
Process status files           /home/mysql/dirpcs: created
SQL script files               /home/mysql/dirsql: created
Database definitions files     /home/mysql/dirdef: created
Extract data files             /home/mysql/dirdat: created
Temporary files                /home/mysql/dirtmp: created
Credential store files         /home/mysql/dircrd: created
Masterkey wallet files         /home/mysql/dirwlt: created
Dump files                     /home/mysql/dirdmp: created


3.配置启动mgr
GGSCI (cqgs_ywjk) 5> edit params mgr
PORT 7809
dynamicportlist 7840-7914
autorestart extract *,retries 5,waitminutes 7
purgeoldextracts ./dirdat/*,usecheckpoints,minkeepdays 10
lagreporthours 1
laginfominutes 30
lagcriticalminutes 45
accessrule,prog server,allow


GGSCI (cqgs_ywjk) 7> start mgr
Manager started.


GGSCI (cqgs_ywjk) 8> info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING       


4.配置checkpoint table
GGSCI (cqgs_ywjk DBLOGIN as root) 14> edit params ./globals
CHECKPOINTTABLE test.checkpoint
登陆
GGSCI (cqgs_ywjk) 13> dblogin SOURCEDB test,userid root
Password: 
Successfully logged into database.
由于Ogg默认是找/tmp/mysql.sock
ln -s ln -s /mysql/Percona/data/mysql.sock /tmp/mysql.sock /tmp/mysql.sock


GGSCI (cqgs_ywjk DBLOGIN as root) 16> add checkpointtable test.checkpointtab
Successfully created checkpoint table test.checkpointtab.
GGSCI (cqgs_ywjk DBLOGIN as root) 16> info checkpointtable test.checkpointtab
查看Mysql可以看到checkpointtab等表被添加
mysql> use test
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_test    |
+-------------------+
| checkpointtab     |
| checkpointtab_lox |
| dep               |
| emp               |
| t1                |
+-------------------+


5.配置复制进程
GSCI (cqgs_ywjk DBLOGIN as root) 43> edit params rep1
replicat rep1
sourcedefs dirdef/t1.prm
TRAGETDB test,userid root,password root1234
reperror default,discard
discardfile dirrpt/rep4.dsc,append,megabytes 50
map test.t1, target test.t1;


GGSCI (cqgs_ywjk DBLOGIN as root) 28> add replicat rep1,exttrail dirdat/xs,checkpointtable test.checkpointtab


6.修改从源数据库复制过来的define文件权限
[root@cqgs_ywjk dirdef]# chown -R mysql:mysql t1.prm 
                                                                             
数据测试


1.初始化数据
a.两边数据清空。
b.文本导入导出
c.使用ogg direct load方式


2.启动复制进程
GGSCI (cqgs_ywjk DBLOGIN as root) 44> start rep1


Sending START request to MANAGER ...
REPLICAT REP1 starting




GGSCI (cqgs_ywjk DBLOGIN as root) 45> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


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


3.启动抽取和投递进程
GGSCI (cktsdb1) 8> start ex3


Sending START request to MANAGER ...
EXTRACT EX3 starting




GGSCI (cktsdb1) 9> start dp3


Sending START request to MANAGER ...
EXTRACT DP3 starting


GGSCI (cktsdb1) 16> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           
EXTRACT     RUNNING     DP1         00:00:00      00:00:06    
EXTRACT     RUNNING     DP2         00:00:00      00:00:09    
EXTRACT     RUNNING     DP3         00:00:00      00:00:08    
EXTRACT     RUNNING     EX1         00:00:04      00:00:04    
EXTRACT     RUNNING     EX2         00:00:01      00:00:01    
EXTRACT     RUNNING     EX3         01:21:29      00:00:00