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

2021-11-06 23:47:45

环境：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;

----------

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可以无

SQL> conn sender/sender

Connected.

SQL> select * from test;

----------

此时完成test表的初始化同步，注意：如果非ORACLE-ORACLE方式同步，则只能使用OGG推荐的方式，即配置initial extract来初始化数据，具体可以参考我之前的一篇blog：

OGG配置DML单向复制一例及错误分析

http://blog.csdn.net/aaron8219/article/details/10275431

三、不使用PUMP抽取进程的DML同步参数配置(ORACLE-ORACLE)

3.1 单向复制

3.1.1 创建并配置manager

[ogg@zlm gg11]$ ./ggsci

GGSCI (zlm) 1> info all

GGSCI (zlm) 2> edit params mgr

PORT 7809

ggate (zlm) 3> start manager

Manager started.

3.1.2 配置源端抽取组ext1

GGSCI (zlm) 1> add extract ext1, tranlog, begin now

GGSCI (zlm) 2> add rmttrail ./dirdat/rt, extract ext1

GGSCI (zlm) 3> edit params ext1

extract ext1

userid ogg, password ogg

rmthost zlm2, mgrport 7809

rmttrail ./dirdat/rt

ddl include mapped objname sender.*;

table sender.*;

GGSCI (zlm) 4> info all

3.1.3 配置目标端同步组

3.1.3.1 在目标端添加checkpoint表

[ogg@zlm gg11]$ ./ggsci

GGSCI (zlm2) 1> edit params ./GLOBAL

GGSCHEMA ogg

CHECKPOINTTABLE ogg.ckpt

GGSCI (zlm2) 2> dblogin userid ogg password ogg

GGSCI (zlm2) 3> add checkpointtable ogg.ckpt

3.1.3.2 创建同步组rep1

GGSCI (zlm2) 4> add replicat rep1, exttrail ./dirdat/rt, checkpointtable ogg.ckpt

GGSCI (zlm2) 5> edit params rep1

replicat rep1

ASSUMETARGETDEFS

userid ogg,password ogg

reperror default,discard

discardfile ./dirrpt/rep1.dsc, append, megabytes 5

DDL

map sender.*, target sender.*;

3.1.4 源端开启抽取组ext1，目标端开启同步组rep1

--source

GGSCI (zlm) 1> start mgr

GGSCI (zlm) 2> start extract ext1

GGSCI (zlm) 3> info all

--target

GGSCI (zlm2) 1> start mgr

GGSCI (zlm2) 2> start replicat rep1

GGSCI (zlm2) 3> info all

3.1.5 测试DML同步

3.1.5.1 insert

--source

SQL> select * from test

2 /

----------

SQL> insert into test values(4);

1 row created.

SQL> commit;

Commit complete.

--target

SQL> select * from test;

----------

注意：可以顺利同步，但是查看ext1和rep1的run time messages，都报了如下的错误：

WARNING OGG-00869 No unique key is defined for table 'TEST'. All viable columns will b

e used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

Using the following key columns for source table SENDER.TEST: ID.

该表没有主键，使用KEYCOLS代替，查询官方说明如下：

OGG-00869: {0}
Cause: The specified database error occurred, but can be ignored.
Action: Contact Oracle Support only if a problem persists.

由于这只是个警告，况且对实际的应用也没什么影响，是可以忽略掉的。

3.1.5.2 update

(略)...

3.1.5.3 delete

(略)...

3.1.6 测试DDL同步(可选)

测试DDL需要之前做过1.8步骤的全部配置(绿色部分)，并在相应参数中添加过相应DDL需要使用的参数(绿色部分)

3.1.6.1 create table

--source

SQL> create table test2 as select * from test;

Table created.

--target

SQL> select * from test2;

----------

5 rows selected.

3.1.6.2 alter table

--source

SQL> alter table test2 add (name varchar(10));

Table altered.

--target

SQL> desc test2;

Name Null? Type

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

ID NUMBER

NAME VARCHAR2(10)

3.1.6.3 tuncate table

--source

SQL> truncate table test2;

Table truncated.

--target

SQL> select * from test2;

no rows selected

3.1.6.4 drop table

--source

SQL> drop table test2;

Table dropped.

--target

SQL> desc test2

ERROR:

ORA-04043: object test2 does not exist

至此，不设置本地trail和pump抽取进程的DML,DDL都可以顺利复制到目标端

3.2 双向复制

双向复制，其实就是再配置一条目标端到源端的复制通道，分别在源端和目的端各配置一个extract和replicat进程就可以了

3.2.1 配置源端抽取组ext1

GGSCI (zlm2) 1> add extract ext1, tranlog, begin now

GGSCI (zlm2) 2> add rmttrail ./dirdat/rt, extract ext1

GGSCI (zlm2) 3> edit params ext1

extract ext1

userid ogg, password ogg

rmthost zlm1, mgrport 7809 --注意主机名要修改一下，别的参数和之前的一致就可以了

rmttrail ./dirdat/rt

ddl include mapped objname sender.*;

table sender.*;

GGSCI (zlm2) 4> info all

3.2.2 配置目标端同步组

3.2.2.1 在目标端添加checkpoint表

[ogg@zlm gg11]$ ./ggsci

GGSCI (zlm) 1> edit params ./GLOBAL

GGSCHEMA ogg --DDL同步必须指定，DML同步不需要

CHECKPOINTTABLE ogg.ckpt

GGSCI (zlm) 2> dblogin userid ogg password ogg

GGSCI (zlm) 3> add checkpointtable ogg.ckpt

3.2.2.2 创建同步组rep1

GGSCI (zlm) 4> add replicat rep1, exttrail ./dirdat/rt, checkpointtable ogg.ckpt

GGSCI (zlm) 5> edit params rep1

replicat rep1

ASSUMETARGETDEFS

userid ogg,password ogg

reperror default,discard

discardfile ./dirrpt/rep1.dsc, append, megabytes 5

DDL

map sender.*, target sender.*;

3.2.3 源端(原目标端)开启抽取组ext1，目标端(原源端)开启同步组rep1

--source

GGSCI (zlm2) 1> start mgr

GGSCI (zlm2) 2> start extract ext1

GGSCI (zlm2) 3> info all

--target

GGSCI (zlm) 1> start mgr

GGSCI (zlm) 2> start replicat rep1

GGSCI (zlm) 3> info all

具体测试内容和之前配置的单向复制一样，步骤略，DML，DDL都顺利复制到目标端，和之前一样，第一次同步的时候会比较慢，之后就快了，可能是第一次同步需要创建文件的缘故。另外，这里要特别注意，此时因为配置了双向复制，当两边各自的ext1和rep1进程都是running状态，如果此时源端和目标端对同一个表进行DML操作，如insert一条记录，那么这条记录会不断地在两端来回复制，永不停歇，如下所示：

SQL> insert into test values(6);

1 row created.

SQL> select *from test;

----------

这条插入id=6的数据就会不断地插入test表，要解决这个问题，就需要在两端的任意一个extract进程ext1里添加一条TRANLOGOPTIONS EXCLUDEUSER ogg就可以了

四、使用PUMP抽取进程的单向复制(ORACLE-ORACLE)

4.1 添加本地trail路径，修改源端extract抽取组ext1参数

GGSCI (zlm) 1> add extract ext1,tranlog, begin now

GGSCI (zlm) 2> add exttrail ./dirdat/lt,extract ext1

GGSCI (zlm) 3> edit params ext1

extract ext1

userid ogg, password ogg

--rmthost zlm2, mgrport 7809

--rmttrail ./dirdat/rt

exttrail ./dirdat/lt

ddl include mapped objname sender.*;

table sender.*;

注释掉2行和远程连接有关的参数，再添加一行本地trail路径即可

4.2 添加pump抽取组epp1

GGSCI (zlm) 1> add extract epp1,exttrailsource ./dirdat/lt

GGSCI (zlm) 2> add rmttrail ./dirdata/rt,extract epp1

GGSCI (zlm) 3> edit params epp1

extract epp1

userid ogg,password ogg

rmthost zlm2, mgrport 7809

rmttrail ./dirdat/rt

ddl include mapped objname sender.*;

table sender.*;

相当于把原来配置在ext1里的远程配置参数搬过来了，因为现在是通过pump与远端连接，

replicat复制组rep1参数不用修改，直接使用就可以

4.2 DML和DDL测试(参考3.1.5和3.1.6的步骤，方法一致)

(略)…

注意：测试同步的时候要多开启一个pump进程，即源端运行ext1，epp1

五、异构数据库之间的单向复制(ORACLE-MYSQL)

5.1下载并安装介质

OGG FOR MYSQL:

官网链接：https://edelivery.oracle.com/EPD/Download/get_form

介质名称：Oracle GoldenGate V11.1.1.1.1 for MySQL 5.x on Linux x86-64

MYSQL DATABASE:

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

介质名称：MySQL Database 5.6.13 RPM for Oracle Linux / RHEL 5 x86 (64bit)

5.1.1目标端安装OGG软件并配置环境

因为MYSQL默认是以root用户登录并使用的，所以只要配置root的环境变量就可以了

在root用户的.bash_profile文件中添加以下内容：

export GGHOME=/ggmysql

export PATH=$PATH:$GGHOME

拷贝OGG FOR MYSQL介质到/rpm目录下并解压缩，然后再一次解压缩到/ggmysql

[root@zlm2 ~]# cd /rpm

[root@zlm2 rpm]# ls

V27808-01.zip

[root@zlm2 rpm]# unzip V27808-01.zip

Archive: V27808-01.zip

inflating: ggs_Linux_x64_MySQL_64bit.tar

inflating: OGG_WinUnix_Rel_Notes_11.1.1.1.1.pdf

inflating: Oracle_GoldenGate_11.1.1.1_README.txt

[root@zlm2 rpm]# ls

ggs_Linux_x64_MySQL_64bit.tar Oracle_GoldenGate_11.1.1.1_README.txt

OGG_WinUnix_Rel_Notes_11.1.1.1.1.pdf V27808-01.zip

[root@zlm2 rpm]# mkdir /ggmysql

[root@zlm2 rpm]# cd /ggmysql

[root@zlm2 ggmysql]# tar -xvf /rpm/ggs_Linux_x86_MySQL_32bit.tar

(略)...

进入OGG界面，添加OGG目录

[root@zlm2 ggmysql]# ./ggsci

GGSCI (zlm2) 1> create subdirs

5.1.2 目标端安装配置MYSQL数据库软件

把从官网下载的MYSQL数据库软件也拷贝到/rpm下并解压

[root@zlm2 rpm]# ls

ggs_Linux_x64_MySQL_64bit.tar V27808-01.zip

OGG_WinUnix_Rel_Notes_11.1.1.1.1.pdf V39097-01.zip

Oracle_GoldenGate_11.1.1.1_README.txt

[root@zlm2 rpm]# unzip V39097-01.zip

Archive: V39097-01.zip

extracting: MySQL-shared-compat-advanced-5.6.13-1.rhel5.x86_64.rpm

extracting: MySQL-devel-advanced-5.6.13-1.rhel5.x86_64.rpm

extracting: MySQL-embedded-advanced-5.6.13-1.rhel5.x86_64.rpm

extracting: MySQL-shared-advanced-5.6.13-1.rhel5.x86_64.rpm

extracting: MySQL-server-advanced-5.6.13-1.rhel5.x86_64.rpm

extracting: MySQL-test-advanced-5.6.13-1.rhel5.x86_64.rpm

extracting: MySQL-client-advanced-5.6.13-1.rhel5.x86_64.rpm

extracting: README.txt

MYSQL数据库软件是一个rpm包，直接以root执行rpm命令进行安装

如果提示libaio.so.1依赖包没有安装的错误，就要先安装依赖包libaio

配置本地yum并执行：yum install libaio，或者直接用rpm包安装

[root@zlm2 rpm]# rpm -ivh MySQL-server-advanced-5.6.13-1.rhel5.x86_64.rpm

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

1:MySQL-server-advanced ########################################### [100%]

2013-08-30 22:46:21 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2013-08-30 22:46:21 5633 [Note] InnoDB: The InnoDB memory heap is disabled

2013-08-30 22:46:21 5633 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2013-08-30 22:46:21 5633 [Note] InnoDB: Compressed tables use zlib 1.2.3

2013-08-30 22:46:21 5633 [Note] InnoDB: Using Linux native AIO

2013-08-30 22:46:21 5633 [Note] InnoDB: Not using CPU crc32 instructions

/usr/sbin/mysqld: Can't create/write to file '/tmp/ibgnuRhJ' (Errcode: 13 - Permission denied)

2013-08-30 22:46:21 7fa1cc42c6f0 InnoDB: Error: unable to create temporary file; errno: 13

2013-08-30 22:46:21 5633 [ERROR] Plugin 'InnoDB' init function returned error.

2013-08-30 22:46:21 5633 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.

2013-08-30 22:46:21 5633 [ERROR] Unknown/unsupported storage engine: InnoDB

2013-08-30 22:46:21 5633 [ERROR] Aborting

2013-08-30 22:46:21 5633 [Note] Binlog end

2013-08-30 22:46:21 5633 [Note] /usr/sbin/mysqld: Shutdown complete

显示完以上信息后就停住了，跳回了root的shell界面，查看了上面提示的内容，

出现几个报错，第一次装MYSQL，不太明白是什么意思，网上查了一下说是需要配置my.cnf参数，直接在my.cnf里的mysqld下加一行explicit_defaults_for_timestamp

http://bbs.csdn.net/topics/390369836?page=1#post-393710642

但是我觉得应该不是这个问题，现在软件肯定是还没有装好，查看了一下，相关的目录和配置文件都没有创建，可能是官网这个最新版server-advanced-5.6.13包兼容性有点问题，网上查了一下，也说最好别下官网最新版的MYSQL数据库安装包

于是去MYSQL官网下载MySQL Community Server最新版

官方链接：http://dev.mysql.com/downloads/mirror.php?id=414018

介质名称：MySQL-server-5.6.13-1.rhel5.x86_64.rpm

官方链接：http://dev.mysql.com/downloads/mirror.php?id=413959

介质名称：MySQL-client-5.6.13-1.rhel5.x86_64.rpm

把新下载的RPM拷贝到/rpm目录下，重新安装MYSQL，但是此时又发现一个问题

[root@zlm2 rpm]# rpm -ivh MySQL-server-5.6.13-1.rhel5.x86_64.rpm

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

file /usr/bin/innochecksum from install of MySQL-server-5.6.13-1.rhel5.x86_64 conflicts with file from package MySQL-server-advanced-5.6.13-1.rhel5.x86_64

file /usr/bin/my_print_defaults from install of MySQL-server-5.6.13-1.rhel5.x86_64 conflicts with file from package MySQL-server-advanced-5.6.13-1.rhel5.x86_64

(略)...

由于之前的server-advacned包没有完全正确安装，现在再安装这个包提示和之前的包有冲突，无法继续安装，于是想到先卸载一下之前安装的包：

[root@zlm2 rpm]# rpm -ev MySQL-server-advanced-5.6.13-1.rhel5.x86_64.rpm

error: package MySQL-server-advanced-5.6.13-1.rhel5.x86_64.rpm is not installed

提示之前的server-advanced包并没有安装，也无法卸载，现在不知道如何清除之前rpm的安装残留信息，所以换了一台之前装的Oracle ASM单实例数据库的主机，进行安装：

SQL-server-5.6.13-1.rhel5.x86_64.rpm

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

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

2013-08-31 13:16:17 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2013-08-31 13:16:17 4462 [Note] InnoDB: The InnoDB memory heap is disabled

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

2013-08-31 13:16:17 4462 [Note] InnoDB: Compressed tables use zlib 1.2.3

2013-08-31 13:16:17 4462 [Note] InnoDB: Using Linux native AIO

2013-08-31 13:16:17 4462 [Note] InnoDB: Not using CPU crc32 instructions

2013-08-31 13:16:17 4462 [Note] InnoDB: Initializing buffer pool, size = 128.0M

2013-08-31 13:16:17 4462 [Note] InnoDB: Completed initialization of buffer pool

2013-08-31 13:16:17 4462 [Note] InnoDB: The first specified data file ./ibdata1 did not exist: a new database to be created!

2013-08-31 13:16:17 4462 [Note] InnoDB: Setting file ./ibdata1 size to 12 MB

2013-08-31 13:16:17 4462 [Note] InnoDB: Database physically writes the file full: wait...

2013-08-31 13:16:18 4462 [Note] InnoDB: Setting log file ./ib_logfile101 size to 48 MB

2013-08-31 13:16:20 4462 [Note] InnoDB: Setting log file ./ib_logfile1 size to 48 MB

2013-08-31 13:16:21 4462 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0

2013-08-31 13:16:21 4462 [Warning] InnoDB: New log files created, LSN=45781

2013-08-31 13:16:21 4462 [Note] InnoDB: Doublewrite buffer not found: creating new

2013-08-31 13:16:21 4462 [Note] InnoDB: Doublewrite buffer created

2013-08-31 13:16:21 4462 [Note] InnoDB: 128 rollback segment(s) are active.

2013-08-31 13:16:21 4462 [Warning] InnoDB: Creating foreign key constraint system tables.

2013-08-31 13:16:21 4462 [Note] InnoDB: Foreign key constraint system tables created

2013-08-31 13:16:21 4462 [Note] InnoDB: Creating tablespace and datafile system tables.

2013-08-31 13:16:21 4462 [Note] InnoDB: Tablespace and datafile system tables created.

2013-08-31 13:16:21 4462 [Note] InnoDB: Waiting for purge to start

2013-08-31 13:16:21 4462 [Note] InnoDB: 5.6.13 started; log sequence number 0

A random root password has been set. You will find it in '/root/.mysql_secret'.

2013-08-31 13:16:22 4462 [Note] Binlog end

2013-08-31 13:16:22 4462 [Note] InnoDB: FTS optimize thread exiting.

2013-08-31 13:16:22 4462 [Note] InnoDB: Starting shutdown...

2013-08-31 13:16:23 4462 [Note] InnoDB: Shutdown completed; log sequence number 1625977

2013-08-31 13:16:23 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).

2013-08-31 13:16:23 4486 [Note] InnoDB: The InnoDB memory heap is disabled

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)

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

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 ~]#

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命令可以看到端口

#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	Contents of Directory
/usr/bin	Client programs and scripts
/usr/sbin	The mysqld server
/var/lib/mysql	Log files, databases
/usr/share/info	Manual in Info format
/usr/share/man	Unix manual pages
/usr/include/mysql	Include (header) files
/usr/lib/mysql	Libraries
/usr/share/mysql	Miscellaneous support files, including error messages, character set files, sample configuration files, SQL for database installation
/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这个异构数据库转换文件

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

[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 "gg.brokentrail=true"

这里都提到了在用到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 = "AMERICAN"

NLS_TERRITORY = "AMERICA"

NLS_CHARACTERSET = "AL32UTF8"

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 **

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

MAP resolved (entry SENDER.TEST2):

map SENDER.TEST2, target test.test2;

Using following columns in default map by name:

Using the following key columns for target table test.test2: id.

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

* ** Run Time Statistics ** *

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

Report at 2013-09-01 11:14:01 (activity since 2013-09-01 11:13:56)

From Table SENDER.TEST2 to test.test2:

# inserts: 3

# updates: 0

# deletes: 0

mysql> show tables;

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

| Tables_in_test |

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

| test2 |

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

1 row in set (0.00 sec)

mysql> select * from test2;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

+------+

3 rows in set (0.06 sec)

源端之前建立了一张只有一个字段(id ini)和3条记录的表，现在已经利用OGG初始化方式同步到目标端，初始化测试完毕！

5.4 源端配置extract抽取组eotomy

GGSCI (zlm) 1> add extract eotomy,tranlog,begin now

GGSCI (zlm) 2> add exttrail ./dirdat/lm,extract eotomy

GGSCI (zlm) 3> edit params eotomy

extract eotomy

userid ogg, password ogg

--defsfile ./dirdef/oratomy.def

exttrail ./dirdat/lm

table sender.*;

5.5 源端配置pump抽取组potomy

GGSCI (zlm) 1> add extract potomy,exttrailsource ./dirdat/lm

GGSCI (zlm) 2> add rmttrail ./dirdat/rm,extract potomy

GGSCI (zlm) 3> edit params potomy

extract potomy

userid ogg,password ogg

rmttrail ./dirdat/rm

table sender.*;

5.6 配置./GLOBALS参数并创建checkpointtable

GGSCI (dg1) 1> edit params ./GLOABLS

checkpointtable test.chkpt

GGSCI (dg1) 2> add checkpointtable test.chkpt

验证一下：

mysql> show tables;

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

| Tables_in_test |

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

| chkpt |

| test2 |

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

2 rows in set (0.00 sec)

5.7 目标端配置replicat复制组rmysql

GGSCI (zlm2) 1> add replicat rotomy,exttrail ./dirdat/rm,checkpointtable test.chkpt

GGSCI (zlm2) 2> edit params rotomy

replicat rotomy

sourcedefs ./dirdef/oratomy.def --同构复制就替换成这个参数assumetargetdefs

端口连接MYSQL

sourcedb test userid root,password 123456

reperror default,discard

map SENDER.TEST2, target test.test2;

5.8 开始DML同步测试

首先保证源和目标端的mgr进程已经开启，然后依次启动源端的eotomy、potomy抽取组和目标端的rotomy同步组

5.8.1 INSERT

SQL> select * from test2;

----------

SQL> insert into test2 values(4);

1 row created.

SQL> commit;

Commit complete.

mysql> select * from test2;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 4 |

+------+

4 rows in set (0.00 sec)

5.8.2 UPDATE

SQL> update test2 set id='5' where id='4';

1 row updated.

SQL> commit;

Commit complete.

mysql> select * from test2;

+------+

| id |

+------+

| 1 |

| 2 |

| 3 |

| 5 |

+------+

4 rows in set (0.00 sec)

5.8.3 DELETE

SQL> delete from test2 where id<5;

3 rows deleted.

SQL> commit;

Commit complete.

mysql> select * from test2;

+------+

| id |

+------+

| 5 |

+------+

1 row in set (0.00 sec)

至此，DML操作测试完毕，注意，由于之前配置过了初始化抽取，所以测试表test2的结构信息已经在之前的eini参数中通过指定oratmy.def传递到目标端，之后的eotomy和potomy参数中都不需要再添加defsfile ./dirdef/oratomy.def这句了

关于ORACLE->MYSQL的DDL同步说明：

根据官方文档，由于ogg不支持mysql 的 MyISAM 引擎，所以不能进行DDL的同步

注：goldengate对mysql只支持innodb引擎
create table mysql (name char(10)) engine=innodb;

六、同构和异构数据库的双路单向复制(ORACLE-ORACLE/ORACLE-MYSQL)

6.1 同构数据库的单向复制(ORACLE-ORACLE)

内容与第四步一致，(略)…

6.2 异构数据库的单向复制(ORACLE-MYSQL)

内容与第五步一致，(略)…

不同的只是在源端和目标端同时配置两组复制链路，用于同构和异构数据之间的同步复制，可以在目标端同时安装2个OGG软件，也可以分别在1台机器上装OGG FOR ORACLE，另一台机器上装OGG FOR MYSQL，源端配置好发往2个目标端的相关抽取组就可以了

总结：在配置ORACLE->MYSQL初始化抽取这里花了好多时间，也碰到了好多问题，因为是初次接触MYSQL的缘故，很多东西都不懂，查了好多资料，不过最后都一一解决了，发现错误不可怕，通过自己的努力和坚持再解决以后，成就感油然而生，这是一次宝贵的学习经历，把期间碰到的一些OGG方面的问题拿出来分享，希望对大家有所帮助！

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

By aaron8219 Chinaunix Blog：http://blog.chinaunix.net/uid/24612962.html

原创内容，转载请注明链接，谢谢！

http://blog.csdn.net/aaron8219/article/details/10823547

码农公寓

相关文章