OGG11g同构(GoldenGate-ORACLE)、异构(GoldenGate-MYSQL)同步配

OGG同构(ORACLE-ORACLE)、异构(ORACLE-MYSQL)同步配置及错误解析

环境：11.2.0.3(已安装数据库实例)+OEL5.7 192.168.1.55 zlm sid:zlm11g 192.168.1.60 zlm2 sid:zlm11g

一、安装软件，配置环境，创建相关用户 1.1 下载介质并安装OGG软件

从官方网址下载最新版OGG FOR ORACLE 11g ON LINUX X86-64软件：

http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html?ssSourceSiteId=ocomen

Oracle GoldenGate V11.2.1.0.1 for Oracle 11g on Linux x86-64 (86 MB) 介质名称：ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

1.2 把OGG软件包复制到源端oracle主目录，创建安装目录gg11后2次解压到gg11 [oracle@zlm ~]$ ls

ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip [oracle@zlm ~]$ cd $OACLE_BASE [oracle@zlm oracle]$ mkdir gg11 [oracle@zlm oracle]$ cd gg11

[oracle@zlm gg11]$ unzip /home/oracle/ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.zip

[oracle@zlm gg11]$ ll total 223764

-rw-rw-r-- 1 oracle oinstall 228556800 Apr 23 2012 fbo_ggs_Linux_x64_ora11g_64bit.tar

-rwxrwxrwx 1 oracle oinstall 220546 May 2 2012 OGG_WinUnix_Rel_Notes_11.2.1.0.1.pdf

-rwxrwxrwx 1 oracle oinstall 93696 May 2 2012 Oracle GoldenGate 11.2.1.0.1 README.doc

-rwxrwxrwx 1 oracle oinstall 24390 May 2 2012 Oracle GoldenGate 11.2.1.0.1 README.txt

[oracle@zlm gg11]$ tar xvof fbo_ggs_Linux_x64_ora11g_64bit.tar 1.3 修改环境变量文件.bash_profile，加入OGG的环境变量 export GGHOME=$ORACLE_BASE/gg11

export PATH=$ORACLE_BASE/gg11:$ORACLE_HOME/bin:/usr/bin/:$PATH [oracle@zlm gg11]$ . ~/.bash_profile [oracle@zlm gg11]$ ggsci

ggsci: error while loading shared libraries: libnnz11.so: cannot open shared object file: No such file or directory

由于没有设置LD_LIBRARY_PATH环境变量，所以无法执行ggsci，添加

export LD_LIBLARY_PATH=$ORACLE_BASE/gg11:$ORACLE_HOME/lib:$ORACLE_HOME/bin

到环境变量.bash_profile并source，或者创建一个link文件：

[oracle@zlm gg11]$ ln -s /u01/app/oracle/product/11.2.0/db_1/lib/libnnz11.so - /u01/app/oracle/product/11.2.0/db_1/lib/libnnz10.so 1.4 创建OGG专用目录subdirs GGSCI (zlm) 1> create subdirs

Creating subdirectories under current directory /u01/app/oracle/gg11 Parameter files /u01/app/oracle/gg11/dirprm: already exists Report files /u01/app/oracle/gg11/dirrpt: created Checkpoint files /u01/app/oracle/gg11/dirchk: created Process status files /u01/app/oracle/gg11/dirpcs: created SQL script files /u01/app/oracle/gg11/dirsql: created Database definitions files /u01/app/oracle/gg11/dirdef: created Extract data files /u01/app/oracle/gg11/dirdat: created Temporary files /u01/app/oracle/gg11/dirtmp: created Stdout files /u01/app/oracle/gg11/dirout: created 1.5 开启归档模式、强制日志、附加日志

查看v$database看这3个参数是否已开启，使用OGG必须是开启状态

SQL> select LOG_MODE, SUPPLEMENTAL_LOG_DATA_MIN,FORCE_LOGGING from v$database;

LOG_MODE SUPPLEME FOR ------------ -------- --- ARCHIVELOG YES YES 各参数开启方法： --archivelog

SQL> shutdown immediate SQL> startup mount

SQL> alter database archivelog; SQL> alter database open; --force logging

SQL> alter database force logging; --supplemental log data

SQL> alter database add supplemental log data; 1.6 关闭数据库的recyclebin(10gDDL必须，11gDDL可选)

SQL> alter system set recyclebin=off scope=spfile; --同步DDL要求关闭10g中的回收站特性

1.7 创建复制用户ogg并授予权限

SQL> create user ogg identified by ogg default tablespace users temporary tablespace temp;

SQL> grant connect,resource,unlimited tablespace to ogg; 1.8 创建测试用户sender并授予权限

SQL> create user sender identified by sender default tablespace users temporary tablespace temp;

SQL> grant connect,resource,unlimited tablespace to sender; 1.9 配置复制的DDL支持(必须SYSDBA登录执行) SQL> grant execute on utl_file to ogg;

SQL> @$GGHOME/marker_setup.sql; --建立一个DDL标记表

SQL> @$GGHOME/ddl_setup.sql; --INITIALSETUP选项运行ddl_setup.sql 将在数据库中创建捕获DDL语句的Trigger等必要组件(注意，执行时必须断开GGSCI连接，否则报错) SQL> @$GGHOME/role_setup.sql; --建立GGS_GGSUSER_ROLE角色

SQL> grant GGS_GGSUSER_ROLE to ogg; --授予给extract group参数中定义的userid用户 SQL> @$GGHOME/ddl_enable.sql; --enable ddl捕获触发器 注意：下面2个SQL脚本只是为了提高DDL复制性能，不是必须的 SQL> @?/rdbms/admin/dbmspool --创建DBMS_SHARED_POOL包

SQL> @ddl_pin --通过dbms_shared_pool.keep存储过程将DDLReplication相关的对象keep在共享池中，以保证这些对象不要reload，提升性能

1.10 目标端重复配置以上1.1-1.9全部内容，至此，ORACLE-ORACLE环境搭建完毕 二、用EXPDP/IMPDP初始化测试数据(仅限ORACLE-ORACLE) 2.1 创建EXPDP/IMPDP使用的directory及其对应的本地目录 SQL> set lin 200 pages 999 SQL> col owner for a5

SQL> col directory_name for a25 SQL> col directory_path for a75 SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH ----- ------------------------- ----------------------------------

SYS GGS_DDL_TRACE /u01/app/oracle/diag/rdbms/zlm11g/zlm11g/trace SYS SUBDIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/order_entry//2002/Sep

SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/order_entry/

SYS BACKUP /u01/backup

SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/log/

SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/product_media/

SYS XMLDIR /u01/app/oracle/product/11.2.0/db_1/rdbms/xml

SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/sales_history/

SYS DATA_PUMP_DIR /u01/app/oracle/admin/zlm11g/dpdump/

SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/db_1/ccr/state SQL> create directory expdump as '/u01/expdp'; SQL> select * from dba_directories;

OWNER DIRECTORY_NAME DIRECTORY_PATH ----- ------------------------- ----------------------------------

SYS GGS_DDL_TRACE /u01/app/oracle/diag/rdbms/zlm11g/zlm11g/trace SYS EXPDUMP /u01/expdp

SYS SUBDIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/order_entry//2002/Sep

SYS SS_OE_XMLDIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/order_entry/

SYS BACKUP /u01/backup

SYS LOG_FILE_DIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/log/ SYS MEDIA_DIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/product_media/

SYS XMLDIR /u01/app/oracle/product/11.2.0/db_1/rdbms/xml

SYS DATA_FILE_DIR /u01/app/oracle/product/11.2.0/db_1/demo/schema/sales_history/

SYS DATA_PUMP_DIR /u01/app/oracle/admin/zlm11g/dpdump/

SYS ORACLE_OCM_CONFIG_DIR /u01/app/oracle/product/11.2.0/db_1/ccr/state [oracle@zlm gg11]$ cd /u01/ [oracle@zlm u01]$ ls app backup

[oracle@zlm u01]$ mkdir /u01/expdp [oracle@zlm u01]$ ll total 16

drwxr-xr-x 4 oracle oinstall 4096 Jul 13 03:12 app drwxrwxr-x 2 oracle oinstall 4096 Jul 21 20:26 backup drwxr-xr-x 2 oracle oinstall 4096 Aug 28 22:11 expdp 2.2 登录sender用户并创建测试表test SQL> conn sender/sender Connected.

SQL> begin 2 for i in 1..3 loop 3 insert into test values(i); 4 end loop; 5 end; 6 /

PL/SQL procedure successfully completed. SQL> select * from test1; ID ---------- 1 2 3

2.3 赋予sender读写directory的权限，执行expdp导出测试表test SQL> grant read,write on directory expdump to sender; SQL> !

[oracle@zlm ~]$ expdp sender/sender directory=expdump dumpfile=test.dmp logfile=test.log tables=test

[oracle@zlm ~]$ cd /u01/expdp [oracle@zlm expdp]$ ls test.dmp test.log

2.4 在目标端创建directory及相应的本地路径，复制dump文件到目标端 SQL> create directory impdump as ‘/u01/impdp’; --target

[oracle@zlm2 gg11]$ mkdir /u01/impdp [oracle@zlm2 gg11]$ ls /u01 app backup impdp --source

[oracle@zlm expdp]$ scp test.* zlm2:/u01/impdp oracle@zlm2's password:

test.dmp 100% 92KB 92.0KB/s 00:00 test.log 100% 1011 1.0KB/s 00:00 2.5 目标端赋予recerver用户权限，执行impdp导入测试表test SQL> grant read,write on directory impdump to sender; SQL> !

[oracle@zlm2 ~]$ impdp sender/sender directory=impdump dumpfile=test.dmp tables=test

注意：expdp/impdp必须是在同一个schema下的object，否则不能执行成功，logfile可以无

2013-08-31 13:16:23 4486 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2013-08-31 13:16:23 4486 [Note] InnoDB: Compressed tables use zlib 1.2.3 2013-08-31 13:16:23 4486 [Note] InnoDB: Using Linux native AIO

2013-08-31 13:16:23 4486 [Note] InnoDB: Not using CPU crc32 instructions 2013-08-31 13:16:23 4486 [Note] InnoDB: Initializing buffer pool, size = 128.0M 2013-08-31 13:16:23 4486 [Note] InnoDB: Completed initialization of buffer pool 2013-08-31 13:16:23 4486 [Note] InnoDB: Highest supported file format is Barracuda. 2013-08-31 13:16:23 4486 [Note] InnoDB: 128 rollback segment(s) are active. 2013-08-31 13:16:23 4486 [Note] InnoDB: Waiting for purge to start

2013-08-31 13:16:23 4486 [Note] InnoDB: 5.6.13 started; log sequence number 1625977

2013-08-31 13:16:24 4486 [Note] Binlog end

2013-08-31 13:16:24 4486 [Note] InnoDB: FTS optimize thread exiting. 2013-08-31 13:16:24 4486 [Note] InnoDB: Starting shutdown...

2013-08-31 13:16:25 4486 [Note] InnoDB: Shutdown completed; log sequence number 1625987

A RANDOM PASSWORD HAS BEEN SET FOR THE MySQL root USER ! You will find that password in '/root/.mysql_secret'. You must change that password on your first connect, no other statement but 'SET PASSWORD' will be accepted. See the manual for the semantics of the 'password expired' flag. Also, the account for the anonymous user has been removed. In addition, you can run:

/usr/bin/mysql_secure_installation

which will also give you the option of removing the test database. This is strongly recommended for production servers. See the manual for more instructions.

Please report any problems with the /usr/bin/mysqlbug script! The latest information about MySQL is available on the web at http://www.mysql.com

Support MySQL by buying support/licenses at http://shop.mysql.com New default config file was created as /usr/my.cnf and will be used by default by the server when you start it. You may edit this file to change server settings [root@dg1 rpm]#

[root@dg1 ~]# rpm -qa|grep -i mysql MySQL-server-5.6.13-1.rhel5

这次安装很顺利地就完成了，之前安装失败的主机和这台ASM单实例主机用的是OEL5.7的系统，所以排除了系统问题，肯定是之前的软件兼容性问题，继续安装client软件 [root@dg1 rpm]# rpm -ivh MySQL-client-5.6.13-1.rhel5.x86_64.rpm

Preparing... ########################################### [100%]

1:MySQL-client ########################################### [100%]

至此，MYSQL顺利安装完毕！ 5.1.3 启动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(\ 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安装完以后默认密码为空，也就是说，如果不进行强制修改密码方式，直接执行#mysql -u root -p也是可以进入系统的，当提示输入密码时直接回车就可以，这个我还没有验证过，如果是真的话，那我就绕圈子了，呵呵 如果已经登陆进系统，想修改某用户的密码，可以执行如下命令： [root@dg1 ~]# /usr/bin/mysqladmin -u root password 123456 5.1.5 操作数据库 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; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> use test Database changed mysql> show tables; Empty set (0.00 sec) 5.1.6 设置开机自启动(可选)

可以在/etc/rc.local文件中添加以下MYSQL自启动命令： /etc/init.d/mysql start

另外也可以使用chkconfig命令配置自启动： # chkconfig --list | grep mysql # chkconfig --add mysql

设置完重启系统后，使用netstat-nat命令可以看到MYSQL的3306端口 #netstat-nat|grep 3306 5.1.7 MYSQL几个重要目录说明 （1）数据库目录 /var/lib/mysql/ （2）配置文件

/usr/share/mysql（mysql.server命令及配置文件） （3）相关命令

/usr/bin(mysqladmin mysqldump等命令) （4）启动脚本

/etc/rc.d/init.d/（启动脚本文件mysql的目录）

若想查看MySQL安装到哪个目录，可使用“whereis mysql”命令查看 官方说明：

Table 2.9. MySQL Installation Layout for Linux RPM Packages Directory /usr/bin /usr/sbin /var/lib/mysql

/usr/share/info /usr/share/man /usr/include/mysql /usr/lib/mysql mysql /usr/share/sql-bench

Benchmarks

5.2 源端创建defgen文件并配置

异构数据库之间同步数据必须利用结构转换文件 GGSCI (zlm) 1> edit params defgen defsfile ./dirdef/oratomy.def,purge userid ogg, password ogg table sender.*;

[oracle@zlm gg11] $ ./defgen paramfile ./dirprm/defgen.prm 执行前必须先启动oracle数据库，否则会报：

ERROR OGG-00664 OCI Error beginning session (status = 1034-ORA-01034: ORACLE not available

ORA-27101: shared memory realm does not exist Linux-x86_64 Error: 2: No such file or directory). ERROR OGG-01668 PROCESS ABENDING. 再执行一次也会报错：

ERROR OGG-00037 DEFSFILE file ./dirdef/oratomy.def already exists. ERROR OGG-01668 PROCESS ABENDING.

此时应该先删除原先的那个def文件，再执行一次，直到出现： Definitions generated for 2 tables in ./dirdef/oratomy.def

这里这条命令的作用就是生成oratomy.def这个异构数据库转换文件

Libraries

mple configuration files, SQL for database installation

/usr/share/Miscellaneous support files, including error messages, character set files, sa

Include (header) files Unix manual pages Manual in Info format Log files, databases Contents of Directory Client programs and scripts The mysqld server

将生成的该文件拷贝到目标端相同位置：

[root@zlm ~]# scp /u01/app/oracle/gg11/dirdef/oratomy.def dg1:/ggmysql/dirdef The authenticity of host 'dg1 (192.168.1.99)' can't be established. RSA key fingerprint is e4:1f:12:e6:63:13:3a:2a:ad:52:e3:4a:68:b6:33:e0. Are you sure you want to continue connecting (yes/no)? yes

Warning: Permanently added 'dg1,192.168.1.99' (RSA) to the list of known hosts. root@dg1's password:

oratomy.def 100% 1079 1.1KB/s 00:00 [root@zlm ~]#

5.3 一致性抽取源端数据到目标端

由于是异构数据库之间复制数据，初始化不能采用rman、dexpdp/impdp、传输表空间等oracle的方式来完成，这里选用Goldengate推荐的的初始化方式完成 5.3.1 源端增加用户对象的trandata日志

GGSCI (zlm) 1> dblogin userid ogg,password ogg GGSCI (zlm) 2> add trandata sender.* 5.3.2 源端配置初始化抽取组eini

GGSCI (zlm) 1> add extract eini,sourceistable GGSCI (zlm) 2> edit params eini extract eini

userid ogg,password ogg rmthost dg1,mgrport 7809 rmttask replicat,group rini table sender.*;

5.3.3 目标端配置复制组rini

GGSCI (dg1) 1> add replicat rini,specialrun GGSCI (dg1) 2> edit params rini replicat rini

sourcedb test userid root,password 123456 sourcedefs ./dirdef/oratomy.def

discardfile ./dirrpt/rini.dsc,append,megabytes 5 map sender.*, target test.*;

5.3.4 运行源端manager和extract抽取组进程 GGSCI (zlm) 1> start mgr GGSCI (zlm) 2> start eini 5.3.5 运行目标端manager进程 GGSCI (zlm2) 1> start mgr

注意：当使用OGG推荐方式初始化数据时，目标端replicat复制组进程会自动运行，不用手动启动，就可以完成一次性抽取

当启动进程后，源端ggserr.log里提示有错误：

2013-08-31 17:43:13 WARNING OGG-01194 Oracle GoldenGate Capture for Oracle, eini.prm: EXTRACT task RINI abended : Problem at line 27. Expecting file, table, or record definition.

2013-08-31 17:43:13 ERROR OGG-01203 Oracle GoldenGate Capture for Oracle, eini.prm: EXTRACT abending.

2013-08-31 17:43:13 ERROR OGG-01668 Oracle GoldenGate Capture for Oracle, eini.prm: PROCESS ABENDING.

目标端的ggserr.log里也有同样的提示：

2013-08-31 17:43:12 INFO OGG-00995 Oracle GoldenGate Delivery for MySQL, rini.prm: REPLICAT RINI starting.

2013-08-31 17:43:12 ERROR OGG-00303 Oracle GoldenGate Delivery for MySQL, rini.prm: Problem at line 27. Expecting file, table, or record definition.

2013-08-31 17:43:17 ERROR OGG-01668 Oracle GoldenGate Delivery for MySQL, rini.prm: PROCESS ABENDING

从该提示看来，应该是def文件的问题，但是之前已经创建并复制到目标库了，应该没有什么问题啊，查看了MOS上的文章才知道，原来这是由于目标端的OGG软件版本比源端版本低的缘故：

Replicat abend with ERROR OGG-00303 Problem at line xx. Expecting file, table, or record definition. (Doc ID 1455370.1)

In OGG 11.2, there is a new parameter NOEXTATTR. This is used in DEFGEN. When the OGG version in a target site is lower than the source site, defgen needs to use parameter NOEXTATTR to generate a sourcedef file which target site can read . If using a sourcedef file generated without NOEXTATTR, a replicat will abend with error 00303.

So there are two ways to get around the issue:

1. Use a defgen paramfile with NOEXTATTR option, or

2. Generate definition file with the same OGG version as the OGG target site version. 此处采用办法1，添加NOEXTATTR参数选项，重新生成def文件，并复制到目标端，注意先把原来的def文件删除再添加。在用了新的def文件之后，之前的问题已经解决 重启两端mgr和源端的eini后，源端eini的report没有任何错误提示，但目标端rini的report报了一个错误：

ERROR OGG-00770 Failed to connect to MySQL database engine for HOST localhost, DATABASE test, USER root, PORT 3306. ERROR OGG-01668 PROCESS ABENDING. 查看源端eini的report继续报错：

WARNING OGG-01194 Oracle GoldenGate Capture for Oracle, eini.prm: EXTRACT task RINI abended : Failed to connect to MySQL database engine for HOST localhost, DATABASE test, USER root, PORT 3306.

查看目标端并没有生成rini的report，因为在eini先abending了 GGSCI (dg1) 5> view report rini ERROR: REPORT file RINI does not exist. 直接登录也是报错：

GGSCI (dg1) 9> dblogin sourcedb test,userid root,password 123456

WARNING OGG-00769 MySQL Login failed: . SQL error (2002). Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2). 解决办法：

修改/etc/my.cnf文件，将server和client的socket修改为：/tmp/mysql.sock 重启mysql：/etc/init.d/mysql stop/start

GGSCI (dg1) 3> dblogin sourcedb test,userid root,password 123456 Successfully logged into database.

GGSCI (dg1) 9> dblogin sourcedb test@localhost.localdomain,userid root,password 123456

Successfully logged into database. 这两种都可以登录方式登录都成功

但是这样改了以后，又碰到一个问题，就是无法登录mysql了 [root@dg1 ggmysql]# service mysql start

Starting MySQL [ OK ] [root@dg1 ggmysql]# mysql -uroot -p123456

Warning: Using a password on the command line interface can be insecure.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

于是只好再改回socket为注释状态，似乎碰到了一个无解的状态 MYSQL的SOCKET问题还真是麻烦，把socket文件重新链接一下： [root@dg1 ~]# locate mysql.sock /var/lib/mysql/mysql.sock

[root@dg1 ~]# ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock

或者定义一下用户的MYSQL_UNIX_PORT变量，指向MySQL数据库文件的目录即可，即： export MYSQL_UNIX_PORT=/var/lib/mysql/mysql.sock

GGSCI (dg1) 1> dblogin sourcedb test userid root,password 123456 Successfully logged into database.

接着再启动源端、目标端进程，又出现错误：

WARNING OGG-01223 TCP/IP error 111 (Connection refused). WARNING OGG-01223 TCP/IP error 104 (Connection reset by peer).

WARNING OGG-01223 TCP/IP error 107 (Transport endpoint is not connected). 原因是必须等Target 端的GG进程(mrg进程)启动以后才能启动 之前的问题都已经逐步解决掉了，现在又碰到一个报错： ERROR OGG-01389 File header failed to parse tokens. File INITIALDATALOAD, last offset 815, data: Google到一篇老外写的关于这个问题的解决方法：

http://gavinsoorma.com/2012/06/using-the-format-release-parameter-to-handle-goldengate-version-differences/

解决方法与MOS上的文章差不多：

OGG v11.1.1.0.0 JMS Adapter reading OGG v11.2.1.x trails (文档 ID 1477046.1) Cause:

JMS EXTRACT v11.1.1.0.0 user-exit is compatible with v11.2.x EXTRACT but there are certain configuration steps you must follow for backwards compatibility. Solution:

1. Generate the sourcedefs with the NOEXTATTR option to create a backwards compatible sourcedefs file:

os> DEFGEN paramfile …., NOEXTATTR

2. Use FORMAT RELEASE 9.5 in the source EXTRACT and any EXTRACT PUMP to write trails that are in v9.5 format

exttrail /u01/app/goldengate/dirdat/ae,format release 9.5 rmttrail /u01/app/goldengate/dirdat/ae, format release 9.5 See KM Doc ID 1395761.1.

3. The EXTRACT used to execute the v11.1.1.0.x Adapter must be OGG v11.1.x. Do not use v11.2 EXTRACT to execute v11.1.1.x Adapter.

You can use FORMAT RELEASE 11.1 if using JMS Adapter v11.1.1.0.10+ with properties value of \

这里都提到了在用到trail文件时，通过添加release version xx.x 来降低源端的trail文件版本，但是对于初始化数据时碰到的这个情况，并没有给出解决方法

由于OGG FOR MYSQL目前最高版本官网只提供到11.1，看来要做初始化的ORACLE->MYSQL只有降低源端OGG版本才能解决了 无奈只能重新去官网下载11.1版本的OGG

官网链接：https://edelivery.oracle.com/EPD/Search/handle_go

介质名称：Oracle GoldenGate V11.1.1.1.2 for Oracle 11g on Linux x86-64 安装过程参考之前11.2版本的步骤，此处略

重新运行目标端、源端相应进程后，查看eini的report信息： Database Language and Character Set:

NLS_LANG environment variable specified has invalid format, default value will be used. NLS_LANG environment variable not set, using default value

AMERICAN_AMERICA.US7ASCII. NLS_LANGUAGE = \NLS_TERRITORY = \NLS_CHARACTERSET = \

Warning: your NLS_LANG setting does not match database server language setting. Please refer to user manual for more information. 提示环境变量字符集与数据库字符集不一致

在源端环境变量中添加：export NLS_LANG=’AMERICAN_AMERICA.AL32UTF8’

WARNING OGG-00869 Oracle GoldenGate Capture for Oracle, eini.prm: No unique key is defined for table TEST2. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

字符集的问题解决了，但是从ggserr.log中的提示看来，测试表还没有唯一键定义，难道这是造成不能同步的原因吗？来测试一下，给测试表加上主键约束： SQL> alter table test2 add constraint pk_test2 primary key(id);

注意：因为增加了主键，表结构已经变化了，要重新生成def文件并复制到目标端 做完以上步骤以后，目标端的rini的report依然报错：No records were replicated. 说明没有主键并不是导致不同步的原因，应该是在ORACLE->MYSQL的时候，表名没有匹配，做了一个默认的转换，因为我在eini的report中发现了这么一条： TABLEWildcard resolved (entry SENDER.*): table SENDER.TEST2;

明明配置的是table setender.*，现在变成经过通配符转换以后，变成大写的了，那目标端的rini参数里是否也要改写成大写才行呢？ 修改目标端rini参数，注意：一定要这么改！！！ map SENDER..TEST2，target test.test2; 关于这个map target转换参数的总结：

1、由于MYSQL的数据库是大小写敏感的，不像ORACLE数据库那样会自动进行转换(创建时用的小写表名，在OGG传递的时候用大写的进行转换)，这里由于在源端的eini进程做了一个转换，所以在目标端的replicat参数中一定要写成一样的，否则就是No recoerds were replicated的提示

2、这里还要注意一个问题，taget和之前的“，”之间必须要写一个空格，否则也会提示No recoerds were replicated，并且会报一个错：

ERROR OGG-00212 Invalid option for MAP: test.test2.

发现这个错误不要误以为是teest.test2名字写错了，只是因为少了一个空格,在目标端rini参数中修改以上2处错误以后，提示已经正常同步了,困扰我1天ORACLE->MYSQL的初始化问题终于得到解决：

*********************************************************************** ** Run Time Messages **

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

本文来源：https://www.bwwdw.com/article/fge6.html