OGG 安装 ORACLE11.2.0.4同步数据到MYSQL5.7 服务器配置信息

1.软件安装

1.1 ORACLE环境安装OGG

 # useradd -g oinstall ogg# id ogg# mkdir /u01/app/OGG# chown -R ogg:oinstall /u01/app/OGG# unzip 122022_fbo_ggs_Linux_x64_shiphome.zip# chown -R ogg:oinstall fbo_ggs_Linux_x64_shiphome                                             

1.2 Mysql 环境安装OGG

# grouadd ogg

# useradd -g oinstall ogg

# mkdir OGG

# cd OGG/

# tar -xvf /u01/soft/ggs_Linux_x64_MySQL_64bit.tar

# chown -R ogg:oinstall ../OGG

2. 修改操作系统用户变量

2.1 ORACLE环境修改变量

ORACLE端

export PATH

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export OGG_HOME=/u01/app/OGG

export PATH=$OGG_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH:$OGG_HOME

export PATH=$ORACLE_HOME/bin:$PATH

export ORACLE_SID=ORCL 

2.2 登录测试

ORACLE端   MYSQL端  

2.3 测试表建立

ORACLE端

SQL> create tablespace ORCL datafile '/u01/app/oracle/oradata/ORCL/orcl01.dbf' size 200m;

SQL> create user JF identified by manager default tablespace orcl;

SQL> GRANT source,connect TO JF;

SQL> conn jf/manager

SQL> create table TEST (ID int,NAME varchar2(200));

SQL> insert into TEST values (1,'zhansan');

SQL> insert into TEST VALUES (2,'lisi');

SQL> commit;

SQL> col id format 9,999

SQL> col name format a20

SQL> select * from TEST; MYSQL端

mysql> create database oggmysql;

mysql> create database TEST;

mysql > create user 'oggmysql'@'%' identified by 'oggmysql';

mysql> GRANT ALL ON *.* TO 'oggmysql'@'%'; 

# mysql -uJF -p

mysql> use TEST

mysql> show tables;

mysql> create table TEST (id int,name varchar(200)); 

3.配置OGG

3.1 建立OGG数据库用户

ORACLE端

SQL> create tablespace ogg datafile '/u01/app/oracle/oradata/ORCL/ogg01.dbf' size 200M;

SQL> create user oggorcl identified by oggorcl default tablespace ogg;

SQL> grant CONNECT, RESOURCE to oggorcl;

SQL> grant SELECT ANY DICTIONARY, SELECT ANY TABLE to oggorcl;

SQL> grant ALTER ANY TABLE to oggorcl;

SQL> grant FLASHBACK ANY TABLE to oggorcl;

SQL> grant EXECUTE on DBMS_FLASHBACK to oggorcl;

SQL> grant insert any table to oggorcl;

SQL> grant update any table to oggorcl;

SQL> grant delete any table to oggorcl;

SQL> ALTER SYSTEM SET ENABLE_GOLDENGATE_REPLICATION = TRUE;

SQL> grant execute on sys.DBMS_CAPTURE_ADM to oggorcl;

SQL> select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SQL> alter database force logging;

SQL> alter database add SUPPLEMENTAL log data;

SQL> select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;

SQL> archive log list;

SQL> shutdown immediate

SQL> startup mount;

SQL> alter database archivelog;

3.2 运行OGG支持DDL脚本

ORACLE端进入OGG的家目录下执行：SQL> @marker_setup.sql Enter Oracle GoldenGate schema name:oggorclSQL> @ddl_setup.sql Enter Oracle GoldenGate schema name:oggorclSQL> @role_setup.sql Enter Oracle GoldenGate schema name:oggorclSQL> @ddl_enable.sql  

3.3 创建OGG管理目录

ORACLE端[ogg@oracle11c OGG]$ ./ggsciGGSCI (oracle11c.vastdata.com) 1> create subdirs MYSQL端[ogg@mysql OGG]$ ./ggsciGGSCI (mysql.vastdata.com.cn) 1> create subdirs  注：

3.4 源端添加表级TRANDATA

添加表级的trandata可以理解为需要将哪些用户的哪些表和目标库同步，其实也是添加表级的supplemental log。  ORACLE端> dblogin USERID oggorcl,PASSWORD oggorcl> add trandata JF.test> INFO TRANDATA JF.* 

3.5 配置MGR管理进程

ORACLE端> EDIT PARAMS MGR添加内容如下

PORT 7809

PURGEOLDEXTRACTS /u01/app/OGG/dirdat, USECHECKPOINTS> view params MGR  MYSQL端> EDIT PARAMS MGR添加内容如下

PORT 7809

PURGEOLDEXTRACTS /u01/OGG/dirdat, USECHECKPOINTS

ACCESSRULE, PROG *, IPADDR 192.168.31.167, ALLOW> view params MGR> start mgr        > info mgr  注：

PORT:7809                         OGG管理进程的监控端口

PURGEOLDEXTRACTS         清理不需要的TRAIL文件

../dirdat                              trail文件存放位置

USECHECKPOINTS              使用检查点队列 

3.6 配置初始化数据进程

ORACLE端> ADD EXTRACT EINI_1, SOURCEISTABLE> INFO EXTRACT *, TASKS> EDIT PARAMS EINI_1添加内容如下

EXTRACT EINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

SETENV (ORACLE_SID=ORCL)

USERID oggorcl, PASSWORD oggorcl

RMTHOST 192.168.31.79, MGRPORT 7809

RMTTASK REPLICAT, GROUP RINI_1

TABLE JF.test;> view PARAMS EINI_1

  注：

EXTRACT EINI_1 :         说明这是个EXTRACT进程，名字是EINI_1

SETENV                       环境变量，一定要设置和数据库字符集一致，否则可能造成乱码

USERID                        数据库OGG用户

PASSWORD                 数据库OGG用户的密码

RMTHOST                   目标地址

MGRPORT                   目标端MGR管理进程监听的端口

RMTTASK REPLICAT     目标端REPLIC应用进程的组合名字

TABLE                          源端要初始化数据的表的名字 

3.7 配置目标端REPLICAT进程

  MYSQL端> EDIT PARAMS RINI_1添加内容如下

REPLICAT RINI_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

DBOPTIONS HOST 192.168.31.79,CONNECTIONPORT 3306

ASSUMETARGETDEFS

TARGETDB TEST ,USERID oggmysql, PASSWORD oggmysql

DISCARDFILE /u01/OGG/dirrpt/RINIaa.dsc, PURGE

MAP JF.*, TARGET TEST.*;> view params RINI_1> add replicat RINI_1,specialrun

 REPLICAT                     RINI_1           说明这是REPLICAT应用进程，名字叫RINI_1SETENT                       语言变量ASSUMETARGETDEFS  告诉OGG目标端和源端需要同步的表的结构完全一致。USERID\PASSOWRD    账号|密码DICCARDFILE               错误信息存放的位置以及命名规则MAP                            源端捕获的表的名字TARGET                       目标端同步标的名字，可以不在一个SCHEMA   

3.8 初始化数据

ORACLE端> START EXTRACT EINI_1> view report EINI_1

  MYSQL端mysql> select * from TEST; 

3.9 配置OGG的检查点

此步骤不是必须的，但是为了让OGG网络中断、服务器宕机、掉电等在突发情况也能正确断点续传，ORACLE建议配置OGG的检查点队列。 ORACLE端> EDIT PARAMS ./GLOBALS添加如下内容

CHECKPOINTTABLE oggorcl.ggschkpt > ADD CHECKPOINTTABLE oggorcl.ggschkpt> info CHECKPOINTTABLE oggmysql.ggschkpt  登录数据库检查  MYSQL端 > dblogin SOURCEDB oggmysql USERID oggmysql,PASSWORD oggmysql> EDIT PARAMS ./GLOBALS添加内容如下：

CHECKPOINTTABLE oggmysql.ggschkpt> ADD CHECKPOINTTABLE oggmysql.ggschkpt  登录数据库检查 

3.10 源端配置捕获进程

现在已经可以配置数据同步了，下面在源端配置捕获进程。  ORACLE端> EDIT PARAMS EORA_1添加内容如下：

EXTRACT EORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

SETENV (ORACLE_SID=ORCL)

USERID oggorcl, PASSWORD oggorcl

EXTTRAIL /u01/app/OGG/dirdat/aa

TABLE JF.test;> ADD EXTRACT EORA_1, TRANLOG, BEGIN NOW> ADD EXTTRAIL /u01/app/OGG/dirdat/aa,EXTRACT EORA_1,MGEABYTES 5 启动源端捕获进程EORA_1 > START EXTRACT EORA_1> INFO EXTRACT EORA_1  

3.11 源端配置PUMP传输进程

> EDIT PARAMS PORA_1> view PARAMS PORA_1添加内容如下：

EXTRACT PORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

PASSTHRU

RMTHOST 192.168.31.79, MGRPORT 7809

RMTTRAIL /u01/OGG/dirdat/pa

TABLE JF.test;  添加PUMP进程PORA_1到OGG,并指定本地TRAIL的路径> ADD EXTRACT PORA_1, EXTTRAILSOURCE /u01/app/OGG/dirdat/aa为PUMP进程PORA_1指定传输到目标端的文件路径及格式> ADD RMTTRAIL /u01/OGG/dirdat/pa, EXTRACT PORA_1, MEGABYTES 5> START EXTRACT PORA_1> INFO EXTRACT PORA_1 

3.12目标端配置同步进程

MYSQL端  > EDIT PARAMS RORA_1添加内容如下：

REPLICAT RORA_1

SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

DBOPTIONS HOST 192.168.31.79,CONNECTIONPORT 3306

TARGETDB TEST ,USERID oggmysql, PASSWORD oggmysql

HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE /u01/OGG/dirrpt/RORA_aa.DSC, PURGE

MAP JF.*, TARGET TEST.*; 添加应用进程> add replicat RORA_1,exttrail /u01/OGG/dirdat/pa, checkpointtable oggmysql.ggschkpt > START REPLICAT RORA_1> INFO REPLICAT RORA_1 

4.验证

4.1 进程状态检查

ORACLE端> info all  MYSQL端> info all 

4.2 数据验证

ORACLE端

  MYSQL端