goldengate v11.2 for mysql to oracle 单向复制

备注:本文参考网上文档,部分内容及排错有自己的整理

ogg(goldengate) mysql to oracle 同步配置


Mysql test库的test2表dml复制(mysql ->oracle )

192.168.1.105 ( source: mysql) —>192.168.1.103 ( targetracle)

Os version          Mysql version          Oracle version          ogg for mysql         ogg for oracle
redhat 5.5          5.1.73                     11.2.0.3.0                  11.2.1.0.1                 11.2.1.0.1
数据库软件安装包:
MySQL-server-5.1.73-1.glibc23.x86_64.rpm
MySQL-client-5.1.73-1.glibc23.x86_64.rpm


※Mysql to Oracle注意事项※

1. Supported data types for mysql

CHAR         DOUBLE         TINYTEXT
VARCHAR         DATE         MEDIUMTEXT
INT         TIME         LONGTEXT
TINYINT         YEAR         BLOB
SMALL INT         DATETIME         TINYBLOB
MEDIUM INT         TIMESTAMP         MEDIUMBLOB
BIG INT         BINARY         LONGBLOB
DECIMAL         VARBINARY         ENUM
FLOAT         TEXT         BIT(M)
2.Oracle GoldenGate supports InnoDB storage engine for a source MySQL database
goldengate对mysql只支持innodb引擎
所以,在创建mysql端的表的时候,要指定表为innodb引擎。
create table mysql (name char(10)) engine=innodb;

3.【版本问题】当前for mysql版本的ogg11.2.1.0.1,暂不支持mysql 5.0以下,以及mysql 5.6以上。

4.【配置问题】mysql跟oracle的区别是,mysql是靠各个数据库来划分表,而oracle是靠用户来划分表,所以在配置以mysql为源端的时候,
mysql端 [table test.test2] test指的是数据库的名字,但是如果是oracle做源端的时候,test指的就是用户的名字了,这里有所区分。

一.源端配置【Mysql】
1.创建mysql用户
*********************************************************************************************
注:创建mysql用户的目的是要把goldengate安装到mysql用户下,
否则当ogg读取mysql二进制索引文件
[log-bin-index=/var/lib/mysql/binary-log.index]
的时候没有相关权限。
***********************************************************************************************
创建mysql用户:
groupadd -g 1000 mysql
useradd -u 1000 -g mysql mysql
passwd mysql
密码:mysql

root 用户上传MYSQL客户机服务器端程序:
程序包 MySQL-client-advanced-5.6.18-1.rhel5.x86_64.rpm
             MySQL-server-advanced-5.6.18-1.rhel5.x86_64.rpm



2.安装mysql数据库(root)
安装mysql服务端:
[root@mysql2 mysql]# rpm -ivh MySQL-server-5.5.33-1.rhel5.i386.rpm
Preparing…                ########################################### [100%]
   1:MySQL-server           ########################################### [100%]

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password ‘new-password’
/usr/bin/mysqladmin -u root -h mysql2 password ‘new-password’

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default.  This is
strongly recommended for production servers.

See the manual for more instructions.

Please report any problems with the /usr/bin/mysqlbug script!

安装mysql客户端(root):
[root@mysql2 mysql]# rpm -ivh MySQL-client-5.5.33-1.rhel5.i386.rpm
Preparing…                ########################################### [100%]
   1:MySQL-client           ########################################### [100%]

启动mysql:
[root@mysql2 mysql]# service mysql start
Starting MySQL..[确定]
启动MYSQL
[root@dg1 rpm]# mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
报错了,可以使用下面的命令启动:
service mysql start或/etc/init.d/mysql start或/etc/rc.d/init.d/mysql start
[root@dg1 rpm]# service mysql start
Starting MySQL......                                       [  OK  ]
[root@dg1 rpm]#
5.1.4 登录MYSQL
[root@dg1 rpm]# mysql
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
[root@dg1 rpm]#
该错误提示不用密码是无法访问的,注意此时用mysql -u root -p的方式也是无法登录的,因为此时没有正确的root密码,必须先强制修改root密码,解决方法如下:
[root@dg1 rpm]# /etc/init.d/mysql stop
Shutting down MySQL..                                      [  OK  ]
[root@dg1 rpm]# /usr/bin/mysqld_safe --skip-grant-tables
130831 14:32:39 mysqld_safe Logging to '/var/lib/mysql/dg1.err'.
130831 14:32:39 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
另外再开一个SSH后执行:
[root@dg1 ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.13 MySQL Community Server (GPL)
Copyright (c) 2000, 2013, 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> use mysql
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> update user set password=password("123456") where user="root";
Query OK, 4 rows affected (0.01 sec)
Rows matched: 4  Changed: 4  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
直接关闭新开的SSH,或者执行pkill -KILL -t pts/0 可将pts为0的**用户(之前运行mysqld_safe的用户窗口)强制踢出
然后在原来的SSH,执行Ctrl+Z退出skip-grant-tables模式
[1]+  Stopped                 /usr/bin/mysqld_safe --skip-grant-tables
[root@dg1 rpm]#
正常启动 MySQL:/etc/init.d/mysql start   (service mysqld start)
此时再使用密码方式登录:
[root@dg1 rpm]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.13
Copyright (c) 2000, 2013, 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>

操作数据库
mysql> show databases;
ERROR 1820 (HY000): You must SET PASSWORD before executing this statement
之前已经用安全模式给MYSQL重新修改了密码,也用密码登陆了,但是仍然提示需要密码
查阅官方文档:http://dev.mysql.com/doc/refman/5.6/en/alter-user.html
原来还要重新执行一个操作,再设置一下密码:
mysql> set password=password('123456');
Query OK, 0 rows affected (0.03 sec)
mysql> show databases;


修改root用户密码:
[mysql@mysql2 ~]$  mysqladmin -u root password 123456

设置开机自启动:
[root@mysql2 ~]# chkconfig –list mysql
mysql           0:关闭  1:关闭  2:启用  3:启用  4:启用  5:启用  6:关闭

拷贝初始参数文件/etc下:
[root@mysql2 ~]# cp /usr/share/mysql/my-medium.cnf /etc/my.cnf


配置参数文件,并开启二进制日志,日志模式为row:
[root@mysql2 ~]#  vi /etc/my.cnf
# Replication Master Server (default)
# binary logging is required for replication
#log-bin=mysql-bin
log-bin=/var/lib/mysql/binary-log
log-bin-index=/var/lib/mysql/binary-log.index
# binary logging format – mixed recommended
binlog_format=row

查看设置的参数是否生效:
mysql> show variables like ‘log_bin’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| log_bin       | ON    |
+—————+——-+
1 row in set (0.01 sec)

mysql>  show master status;
+——————-+———-+————–+——————+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+——————-+———-+————–+——————+
| binary-log.000001 |      107 |              |                  |
+——————-+———-+————–+——————+
1 row in set (0.00 sec)

mysql>  show variables like ‘%log_bin%’;
+———————————+——-+
| Variable_name                   | Value |
+———————————+——-+
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
| sql_log_bin                     | ON    |
+———————————+——-+
3 rows in set (0.00 sec)

mysql> show variables like ‘%binlog%’;
+—————————————–+———————-+
| Variable_name                           | Value                |
+—————————————–+———————-+
| binlog_cache_size                       | 32768                |
| binlog_direct_non_transactional_updates | OFF                  |
| binlog_format                           | ROW                  |
| binlog_stmt_cache_size                  | 32768                |
| innodb_locks_unsafe_for_binlog          | OFF                  |
| max_binlog_cache_size                   | 18446744073709547520 |
| max_binlog_size                         | 1073741824           |
| max_binlog_stmt_cache_size              | 18446744073709547520 |
| sync_binlog                             | 0                    |
+—————————————–+———————-+
9 rows in set (0.00 sec)

创建即将同步的表:
mysql>  use test
Database changed
mysql> create table test2 (name char(10)) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

查看test2表的属性:
mysql> show table status from test like ‘test2′;
+——-+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———————+————-+————+——————-+———-+—————-+———+
| Name  | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation         | Checksum | Create_options | Comment |
+——-+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———————+————-+————+——————-+———-+—————-+———+
| test2 | InnoDB |      10 | Compact    |    0 |              0 |       16384 |               0 |            0 |  10485760 |           NULL | 2013-08-15 04:38:04 | NULL        | NULL       | latin1_swedish_ci |     NULL |                |         |
+——-+——–+———+————+——+—————-+————-+—————–+————–+———–+—————-+———————+————-+————+——————-+———-+—————-+———+
1 row in set (0.00 sec)

3.安装mysql版本的goldengate
用mysql用户安装gg:
设置环境变量:
vi .bash_profile
export PATH=/home/mysql/ggsPATH
export LD_LIBRARY_PATH=/home/mysql/ggsLD_LIBRARY_PATH

创建安装目录
su - mysql
cd /home/mysql
mkdir ggs


安装ogg:
mysql upload software to /home/mysql/ggs:

[mysql@mysql2 ggs]$ tar -xvf ggs_Linux_x86_MySQL_32bit.tar
[mysql@mysql2 ggs]$ ./ggsci

Oracle GoldenGate Command Interpreter for MySQL
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
Linux, x86, 32bit (optimized), MySQL Enterprise on Apr 23 2012 04:29:30

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

GGSCI (mysql2) 1> create subdirs

Creating subdirectories under current directory /home/mysql/ggs

Parameter files                /home/mysql/ggs/dirprm: already exists
Report files                   /home/mysql/ggs/dirrpt: created
Checkpoint files               /home/mysql/ggs/dirchk: created
Process status files           /home/mysql/ggs/dirpcs: created
SQL script files               /home/mysql/ggs/dirsql: created
Database definitions files     /home/mysql/ggs/dirdef: created
Extract data files             /home/mysql/ggs/dirdat: created
Temporary files                /home/mysql/ggs/dirtmp: created
Stdout files                   /home/mysql/ggs/dirout: created


++++++++++++++++++++++++++
如果报错:
GGSCI (mysql2) 2>  dblogin sourcedb test@localhost:3306, userid root,password 123456

2013-08-14 19:53:41  WARNING OGG-00769  MySQL Login failed: . SQL error (2002). Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2).
ERROR: Failed to connect to MySQL database engine for HOST localhost, DATABASE test, USER root, PORT 3306.

解决办法:
root and mysql user add follow line into .bash_profile
export MYSQL_UNIX_PORT=/var/lib/mysql/mysql.sock
source .bash_profile


在more /etc/my.cnf这里找到这个连接
/var/lib/mysql/mysql.sock
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++=

GGSCI (mysql2) 1> dblogin sourcedb test@localhost:3306, userid root,password 123456   或者(dblogin sourcedb test userid root,password 123456)
Successfully logged into database.
【注:test为mysql中的database名字】

4.配置源端goldengate
配置manager:
GGSCI (mysql2) 2>  edit params mgr
port 7809

配置抽取进程:
GGSCI (mysql2) 10> edit params ext_te2

extract ext_te2
setenv (MYSQL_HOME=”/var/lib/mysql”)
tranlogoptions altlogdest /var/lib/mysql/binary-log.index
sourcedb test@localhost:3306,userid root,password 123456
exttrail /home/mysql/ggs/dirdat/e2
dynamicresolution
gettruncates
table test.test2;   –注:test为mysql中数据库的名字

GGSCI (mysql2) 12> add extract ext_te2,tranlog,begin now
EXTRACT added.

GGSCI (mysql2) 13> add exttrail /home/mysql/ggs/dirdat/e2,extract ext_te2
EXTTRAIL added.

配置推送进程:
GGSCI (mysql2) 14> edit params pump_te2

extract pump_te2
rmthost 192.168.1.103,mgrport 7809
rmttrail /home/oracle/ggs/dirdat/e2
passthru
gettruncates
table test.test2;  –注:test为mysql中数据库的名字

GGSCI (mysql2) 16> add extract pump_te2,exttrailsource /home/mysql/ggs/dirdat/e2
EXTRACT added.

GGSCI (mysql2) 17> add rmttrail /home/oracle/ggs/dirdat/e2,extract pump_te2
RMTTRAIL added.

配置defgen (异构数据库需要):
GGSCI (mysql2) 26> edit params defgen

defsfile /home/mysql/ggs/dirdef/defgen.prm
sourcedb test@localhost:3306, userid root,password 123456
table test.test2;   –test为database名字 test2为table名字


++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
报错:
[oracle@mysql2 ggs]$ defgen paramfile dirprm/defgen.prm

***********************************************************************
        Oracle GoldenGate Table Definition Generator for MySQL
      Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
Linux, x86, 32bit (optimized), MySQL Enterprise on Apr 23 2012 04:59:19

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


                    Starting at 2013-08-14 22:56:00
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Wed Dec 17 11:42:39 EST 2008, Release 2.6.18-128.el5
Node: mysql2
Machine: i686
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 18596

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
defsfile /home/oracle/ggs/dirdef/defgen.prm
–userid test2,password *****
sourcedb test@localhost:3306, userid root,password ******

2013-08-14 22:56:00  WARNING OGG-00769  MySQL Login failed: . SQL error (2002). Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2).

Source Context :
  SourceModule            : [ggdb.mysql.dbx]
  SourceID                : [/net/ap1109nap-vlan900/vol/ifarm_ports/ifarm_views/aime_oggcore_210874/oggcore/OpenSys/src/gglib/ggdbmysql/dbxmysql.c]
  SourceFunction          : [open_data_source]
  SourceLine              : [1080]
  ThreaDBAcktrace         : [10] elements
                          : [/home/oracle/ggs/libgglog.so(CMessageContext::AddThreadContext()+0x26) [0x1c81a6]]
                          : [/home/oracle/ggs/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x3c5) [0x1c0bf5]]
                          : [/home/oracle/ggs/libgglog.so(_MSG_ERR_MYSQL_CONNECT_FAILED(CSourceContext*, char const*, ggs::gglib::ggapp::CDBObjName<(DBObjType)8> const&,
                          ggs::gglib::ggapp::CDBObjName<(DBObjType)11> const&, int, CMessageFactory::MessageDisposition)+0x69) [0x1a4059]]
                          : [defgen [0x80ee55a]]
                          : [defgen [0x80ee864]]
                          : [defgen(gl_odbc_param(char const*, char const*, char*)+0x62) [0x80ef412]]
                          : [defgen(get_infile_params()+0x20f) [0x8092d4f]]
                          : [defgen(main+0xc6) [0x8092fe6]]
                          : [/lib/libc.so.6(__libc_start_main+0xdc) [0xbfae8c]]
                          : [defgen(__gxx_personality_v0+0x109) [0x8090701]]

2013-08-14 22:56:00  ERROR   OGG-00770  Failed to connect to MySQL database engine for HOST localhost, DATABASE test, USER root, PORT 3306.

2013-08-14 22:56:00  ERROR   OGG-01668  PROCESS ABENDING.
[oracle@mysql2 ggs]$ rm /home/oracle/ggs/dirdef/defgen.prm
解决办法:
[oracle@mysql2 ggs]$ export MYSQL_UNIX_PORT=/var/lib/mysql/mysql.sock
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++



生成defgen.prm文件:
[mysql@mysql2 ggs]$ defgen paramfile dirprm/defgen.prm

***********************************************************************
        Oracle GoldenGate Table Definition Generator for MySQL
      Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
Linux, x86, 32bit (optimized), MySQL Enterprise on Apr 23 2012 04:59:19

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


                    Starting at 2013-08-14 19:59:53
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Wed Dec 17 11:42:39 EST 2008, Release 2.6.18-128.el5
Node: mysql2
Machine: i686
                         soft limit   hard limit
Address Space Size   :    unlimited    unlimited
Heap Size            :    unlimited    unlimited
File Size            :    unlimited    unlimited
CPU Time             :    unlimited    unlimited

Process id: 19276

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
defsfile /home/mysql/ggs/dirdef/defgen.prm
–userid test2,password *****
sourcedb test@localhost:3306, userid root,password ******
table test.test2;
Retrieving definition for test.test2

Definitions generated for 1 table in /home/mysql/ggs/dirdef/defgen.prm


拷贝defgen.prm到目标端:
[mysql@mysql2 ggs]$ scp /home/mysql/ggs/dirdef/defgen.prm oracle@192.168.1.24:/home/oracle/ggs/dirdef/



二.目标端配置【Oracle】

数据库配置:
SQL> create user ggs identified by ggs account unlock;
SQL> grant dba to ggs
【如果对权限有要求,如没要求,一般就给dba权限,详细权限为:
SQL> GRANT create table to ggs;
SQL> GRANT CONNECT TO ggs;
SQL> GRANT ALTER ANY TABLE TO ggs;
SQL> GRANT ALTER SESSION TO ggs;
SQL> GRANT CREATE SESSION TO ggs;
SQL> GRANT FLASHBACK ANY TABLE TO ggs;
SQL> GRANT SELECT ANY DICTIONARY TO ggs;
SQL> GRANT SELECT ANY TABLE TO ggs;
SQL> GRANT RESOURCE” TO ggs;
SQL> GRANT DELETE ANY TABLE TO ggs;
SQL> GRANT INSERT ANY TABLE TO ggs;
SQL> GRANT UPDATE ANY TABLE TO ggs;
SQL> GRANT RESTRICTED SESSION TO ggs; 】
将数据库设置为归档模式:
SQL> alter database archivelog(在mount 状态下执行)
SQL> archive log list(查看归档状态)
检查源端数据库的附加日志是否打开:
SQL> select supplemental_log_data_min from v$database;
将附加日志打开:
SQL> alter database add supplemental log data;
切换日志使附加日志生效:
SQL> alter system archive log current;
关闭回收站:
SQL> alter system set recyclebin=off scope=spfile;

重启数据库,使调整生效:

目标库创建测试用户:

[root@gg2 ~]# su - oracle
[oracle@gg2 ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.3.0 Production on Tue Apr 29 03:36:17 2014

Copyright (c) 1982, 2011, Oracle.  All rights reserved.

SQL> connect /as sysdba
Connected.
SQL> create user test2 identified by test2 account unlock;

User created.

SQL> grant dba to test2;

Grant succeeded.

SQL> connect test2/test2
Connected.
SQL> select * from tab;

no rows selected

SQL> create table test2 (name char(10));

Table created.

SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
TEST2                          TABLE

SQL>


oracle 用户上传goldengate软件:

[root@gg2 ~]# su - oracle
[oracle@gg2 ~]$ ls
database  fbo_ggs_Linux_x64_ora11g_64bit.tar
[oracle@gg2 ~]$ pwd
/home/oracle
[oracle@gg2 ~]$ ls -l | grep fbo_ggs_Linux_x64_ora11g_64bit.tar
-rw-r--r-- 1 oracle oinstall 239042560 Aug 23  2012 fbo_ggs_Linux_x64_ora11g_64bit.tar

oracle 用户创建goldengate 安装目录:
cd /home/oracle
mkdir ggs


查看当前oracle 用户 .bash_profile文件:

[oracle@gg2 ~]$ more .bash_profile
# .bash_profile

# Get the aliases and functions
if [ -f ~/.bashrc ]; then
        . ~/.bashrc
fi

# User specific environment and startup programs

#PATH=$PATHHOME/bin

#export PATH
PATH=$PATHHOME/bin
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=orcl
export PATH=.{PATH}HOME/binORACLE_HOME/bin
export PATH=${PATH}:/usr/bin:/bin:/usr/bin/X11:/usr/local/bin
export PATH=${PATH}ORACLE_BASE/common/oracle/bin
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export ORA_NLS10=$ORACLE_HOME/nls/data
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}ORACLE_HOME/oracm/lib
export LD_LIBRARY_PATH=${LD_LIBRARY_PATH}:/lib:/usr/lib:/usr/local/lib
export CLASSPATH=$ORACLE_HOME/JRE
export CLASSPATH=${CLASSPATH}ORACLE_HOME/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=${CLASSPATH}:$ORACLE_HOME/network/jlib
export THREADS_FLAG=native
export TEMP=/tmp
export TMPDIR=/tmp


调整oracle 用户.bash_profile文件内容:

修改环境变量文件.bash_profile,加入OGG的环境变量
export GGHOME=/home/oracle/ggs
export PATH=${PATH}:$GGHOME:$ORACLE_BASE/common/oracle/bin

source .bash_profile 生效

解压goldengate 软件:
[oracle@gg2 ~]$ pwd
/home/oracle
[oracle@gg2 ~]$ ls -l
total 233680
drwxr-xr-x 8 oracle oinstall      4096 Sep 22  2011 database
-rw-r--r-- 1 oracle oinstall 239042560 Aug 23  2012 fbo_ggs_Linux_x64_ora11g_64bit.tar
drwxr-xr-x 2 oracle oinstall      4096 Apr 29 03:29 ggs
[oracle@gg2 ~]$ pwd
/home/oracle
[oracle@gg2 ~]$ whoami
oracle
[oracle@gg2 ~]$ cd ggs
[oracle@gg2 ggs]$ pwd
/home/oracle/ggs
[oracle@gg2 ggs]$ tar -xvof /home/oracle/fbo_ggs_Linux_x64_ora11g_64bit.tar -C $GGHOME

创建相关目录:

[oracle@gg2 ~]$ cd $GGHOME
[oracle@gg2 ggs]$ pwd
/home/oracle/ggs
[oracle@gg2 ggs]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.3 14400833 OGGCORE_11.2.1.0.3_PLATFORMS_120823.1258_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Aug 23 2012 20:20:21

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



GGSCI (gg2) 1> create subdirs

Creating subdirectories under current directory /home/oracle/ggs

Parameter files                /home/oracle/ggs/dirprm: already exists
Report files                   /home/oracle/ggs/dirrpt: created
Checkpoint files               /home/oracle/ggs/dirchk: created
Process status files           /home/oracle/ggs/dirpcs: created
SQL script files               /home/oracle/ggs/dirsql: created
Database definitions files     /home/oracle/ggs/dirdef: created
Extract data files             /home/oracle/ggs/dirdat: created
Temporary files                /home/oracle/ggs/dirtmp: created
Stdout files                   /home/oracle/ggs/dirout: created

编辑MGR:
GGSCI (gg2) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED                                          


GGSCI (gg2) 4> edit params mgr
port 7809

配置GLOBALS:
edit params ./GLOBALS

ggschema test2
checkpointtable test2.chkptab


dblogin  userid ggs,password ggs
Successfully logged into database.

add checkpointtable test2.chkptab
Successfully created checkpoint table test2.chkptab.




GGSCI (ora11g) 55> edit params rep_t2

replicat rep_t2
sourcedefs /home/oracle/ggs/dirdef/defgen.prm
userid ggs,password ggs
reperror default,discard
discardfile /home/oracle/ggs/dirrpt/rep_t2.dsc,append,megabytes 50
dynamicresolution
map test.test2, target test2.test2;


GGSCI (gg2) 10> dblogin userid ggs,password ggs
Successfully logged into database.

GGSCI (ora11g) 56> add replicat rep_t2,exttrail /home/oracle/ggs/dirdat/e2,CHECKPOINTTABLE test2.chkptab
REPLICAT added.



三.进行dml测试
可以分别用不同的用户对源端mysql数据库中 test库下的test2表进行insert、delete、update测试。
查看目标端oracle数据库下的test2用户下的test2表是否同步成功。
测试完毕。





报错:
2014-04-29 04:13:25  ERROR   OGG-00146  Oracle GoldenGate Capture for MySQL, ext_te2.prm:  VAM function VAMControl re
turned unexpected result: error 600 - VAM Client Report <CAUSE OF FAILURE : Sanity Check Failed for events
WHEN FAILED : While reading log event from binary log
WHERE FAILED : MySQLBinLog Reader Module
CONTEXT OF FAILURE : No Information Available!>.
2014-04-29 04:13:25  ERROR   OGG-01668  Oracle GoldenGate Capture for MySQL, ext_te2.prm:  PROCESS ABENDING.

原因:

版本 Oracle GoldenGate Command Interpreter for MySQL
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230
Linux, x64, 64bit (optimized), MySQL Enterprise on Apr 23 2012 04:34:25

不支持 Server version: 5.6.18-enterprise-commercial-advanced-log MySQL Enterprise Server - Advanced Edition (Commercial)


goldengate 12.1.2 以后支持此版本MYSQL.



OGG-00146 [ CAUSE OF FAILURE : Sanity Check Failed for events ] 解决办法
发表于 2013 年 9 月 6 日
ogg mysql to oracle

源端抽取进程报错:

2013-08-14 20:14:13  ERROR   OGG-00146  Oracle GoldenGate Capture for MySQL, ext_te2.prm:  VAM function VAMRead returned unexpected result: error 600 – VAM Client Report <CAUSE OF FAILURE : Sanity Check Failed for events
WHEN FAILED : While reading log event from binary log
WHERE FAILED : MySQLBinLog Reader Module
CONTEXT OF FAILURE : No Information Available!>.
2013-08-14 20:14:13  ERROR   OGG-01668  Oracle GoldenGate Capture for MySQL, ext_te2.prm:  PROCESS ABENDING.

解决办法:

用的mysql5.6版本一直报此错误,更换了mysql5.5版本后,此错误消失。

经测试目前ogg for mysql 暂不支持mysql5.0以下,mysql5.6以上。


报错:


抽取报错:
2014-04-29 10:25:03  INFO    OGG-00993  Oracle GoldenGate Capture for MySQL, ext_te2.prm:  EXTRACT EXT_TE2 started.
2014-04-29 10:25:03  ERROR   OGG-01496  Oracle GoldenGate Capture for MySQL, ext_te2.prm:  Failed to open target trail file /home/mys
ql/ggs/dirdat/e2000001, at RBA 1011.
2014-04-29 10:25:03  ERROR   OGG-01668  Oracle GoldenGate Capture for MySQL, ext_te2.prm:  PROCESS ABENDING.

复制报错:
2014-04-29 10:27:34  ERROR   OGG-00446  Oracle GoldenGate Delivery for Oracle, rep_t2.prm:  No data selecting position from checkpoin
t table test2.chkptab for group 'REP_T2', key 3018732020 (0xb3ee31f4), SQL <SELECT a.current_dir, a.seqno, a.rba, a.audit_ts, a.log_c
mplt_csn, a.log_cmplt_xids, b.log_cmplt_xids FROM test2.chkptab a LEFT JOIN test2.chkptab_lox b ON a.group_name = b.group_name AND a.
group_key = b.group_key AND a.log_cmplt_csn = b.log_cmplt_csn WHERE a.group_name = 'REP_T2' AND a.group_key  = 3018732020>.
2014-04-29 10:27:34  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rep_t2.prm:  PROCESS ABENDING.

解决:


复制端:

SQL> connect test2/test2
Connected.
SQL> select * from tab;   

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
CHKPTAB                        TABLE
CHKPTAB_LOX                    TABLE
TEST2                          TABLE

SQL> drop table chkptab;

Table dropped.

SQL> drop table chkptab_lox;

Table dropped.


删除源与目标端的 dirchk 目录下的文件,此时两端info all
只有MGR

重新添加抽取,传递,复制,参数不变,只是绑定关系

重新启动各进程,成功。





标签: 暂无标签
dongxujian

写了 86 篇文章,拥有财富 384,被 13 人关注

转播转播 分享分享 分享淘帖
回复

使用道具

P4 | 发表于 2018-4-16 14:10:03
顶一下1111111111111111111111111
回复

使用道具

您需要登录后才可以回帖 登录 | 加入社区

本版积分规则

意见
反馈