OGG for Oracle to MySQL异构同步实施笔记

一、Oracle源端OGG安装

1、源端Oracle用户添加环境变量

vi .bash_profile

export LD_LIBRARY_PATH=/software/ggs:$ORACLE_HOME/lib:$LD_LIBRARY_PATH

 

2、源端创建ogg相关目录

[oracle@rac1 ggs]$ pwd    注:一定要进入ogg软件目录进行下列操作

/software/ggs 

 

[oracle@rac1 ggs]$ ggsci            注:在oracle用户的环境变量中添加ogg目录

 

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

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

 

GGSCI (rac1) 1> create subdirs

 

Creating subdirectories under current directory /software/ggs

Parameter files                /software/ggs/dirprm: already exists

Report files                   /software/ggs/dirrpt: created

Checkpoint files             /software/ggs/dirchk: created

Process status files          /software/ggs/dirpcs: created

SQL script files              /software/ggs/dirsql: created

Database definitions files     /software/ggs/dirdef: created

Extract data files             /software/ggs/dirdat: created

Temporary files                /software/ggs/dirtmp: created

Stdout files                  /software/ggs/dirout: created

GGSCI (rac1) 2>

 

:因为主库集群通过nfs来实现共享所以只需要在一个节点执行。

 

 

3、源端创建GLOBALS和mgr

创建GLOBALS

在主库集群的一个节点上进行下列操作:

在goldengate安装目录下执行./ggsci进入OGG控制台然后执行edit params ./GLOBALS来创建全局参数

[oracle@rac1 ggs]$ ggsci

 

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

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

 

GGSCI (rac1) 1>  edit params ./GLOBALS 

进入文件编辑界面

添加以下一行后保存退出

GGSCHEMA ggs

 

:ggs为数据库中的OGG用户

 

创建mgr文件

在主库集群的一个节点上进行下列操作:

在OGG控制台中执行以下命令来创建manager进程的参数文件

GGSCI (rac1) 1> edit params mgr

 

进入文件编辑界面后添加以下内容后保存退出:

源库rac集群添加以下参数:

PORT 7840

DYNAMICPORTLIST 7840-7939

--AUTOSTART ER myext01

--AUTOSTART ER myext02

--AUTOSTART ER mypump01

--AUTOSTART ER mypump02

--AUTORESTART ER myext01,RETRIES 5,WAITMINUTES 3

--AUTORESTART ER myext02,RETRIES 5,WAITMINUTES 3

--AUTORESTART ER mypump01,RETRIES 5,WAITMINUTES 3

--AUTORESTART ER mypump02,RETRIES 5,WAITMINUTES 3

PURGEOLDEXTRACTS /software/ggs/dirdat/*,usecheckpoints,minkeepdays 7

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

 

 

目标库ogg_racsrv添加以下参数:

PORT 7840

DYNAMICPORTLIST 7840-7939

--AUTOSTART ER myext01

--AUTOSTART ER myext02

--AUTOSTART ER mypump01

--AUTOSTART ER mypump02

--AUTORESTART ER myext01,RETRIES 5,WAITMINUTES 3

--AUTORESTART ER myext02,RETRIES 5,WAITMINUTES 3

--AUTORESTART ER mypump01,RETRIES 5,WAITMINUTES 3

--AUTORESTART ER mypump02,RETRIES 5,WAITMINUTES 3

PURGEOLDEXTRACTS /software/ggs/dirdat/*,usecheckpoints,minkeepdays 7

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

 

ACCESSRULE, PROG *, IPADDR 192.168.*.*, ALLOW

 

1、一定不要让rept进程随mgr进程自动启动!!!

2、源库、目标库mgr进程的port必须一致

3、12.2.0.2版本及以上版本ogg需添加ACCESSRULE访问规则限制,否则无法实现mgr进程间通信

 

 

其中PORT 后的是默认启用的端口号

    AUTOSTART是启动manager进程后要自动启动的进程

    AUTORESTART是manager启动后自动重启相关的设置

    在源库添加的PURGEOLDEXTRACTS参数是关于源端trail文件保存删除的参数,这里保留七天,这个参数需要根据客户空间实际情况,以及客户需求来进行设置

    LAG开头的参数是设置lag预警的,将来可以在日志中看到相应的info、waring、error

   这里只给出最基本的参数,具体参数内容需要根据实际情况进行对应的调整。

 

 

4、源端启动mgr

源端编辑完参数后需要执行以下操作

通过以下方式来启动和查看manager

GGSCI (rac1) 1> start mgr

 

Manager started.

 

GGSCI (rac1) 2> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

 

GGSCI (rac1) 3> info mgr

 

Manager is running (IP port rac1.7840)

这里manager状态为running是正常状态。

 

如果mgr启动失败,请通过view ggsevt或者退出ggsci控制台,查看安装目录下的ggserr.log错误日志

 

 

5、源端配置数据库

[oracle@rac1 ggs]$ pwd

/software/ggs

[oracle@rac1 ggs]$ sqlplus / as sysdba

 

SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 19 09:01:51 2015

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

Data Mining and Real Application Testing options

 

SQL> CREATE USER ggs IDENTIFIED BY ggs default tablespace ggs;   - -创建OGG用户

 

User created.      

 

SQL> GRANT CONNECT,RESOURCE,DBA TO ggs;  - -给OGG用户授权

 

Grant succeeded.

 

:这里的ggs用户可以自行定义,需要与OGG 参数中设置的对象一致,但不能是数据库保留的SCHEMA;赋予ggs用户的权限需要有对目标数据进行操作的权限,一般直接授予DBA权限,具体情况请与用户协商决定;ggs用户需要单独干净的表空间。

 

5.1、源端执行sequence脚本

在源端执行sequence.sql脚本,注意是否有报错。

SQL> @sequence.sql

Please enter the name of a schema for the GoldenGate database objects:

ggs

Setting schema name to GGS

UPDATE_SEQUENCE STATUS:

Line/pos

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

Error

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

No errors

No errors

GETSEQFLUSH

Line/pos

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

Error

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

No errors

No errors

SEQTRACE

Line/pos

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

Error

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

No errors

No errors

REPLICATE_SEQUENCE STATUS:

Line/pos

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

Error

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

No errors

No errors

STATUS OF SEQUENCE SUPPORT

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

SUCCESSFUL installation of Oracle Sequence Replication support

SQL>

 

5.2、源端添加supplemental log
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

 

Database altered.

 

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

 

Database altered.

 

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

 

Database altered.

 

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

 

Database altered.

 

SQL> ALTER SYSTEM SWITCH LOGFILE;

 

System altered.

 

SQL> ALTER DATABASE FORCE LOGGING;

 

Database altered.

 

:需要在源端添加supplemental日志来保证数据能正常同步。

5.3、源端授权给ggs用户
SQL> GRANT EXECUTE ON utl_file TO ggs;

 

Grant succeeded.

5.4、源端执行marker_setup.sql脚本
SQL> @marker_setup.sql

 

Marker setup script

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ggs

Marker setup table script complete, running verification script...

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GGS

MARKER TABLE

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

OK

MARKER SEQUENCE

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

OK

Script complete.

SQL>

 

5.5、源端执行ddl_setup.sql脚本

:10g执行该脚本前需要关闭数据库的回收站,并确保回收站中没有数据

11g不需要关闭数据库的回收站

SQL> show parameter recyc

 

NAME                                 TYPE

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

VALUE

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

buffer_pool_recycle                  string

db_recycle_cache_size                big integer

0

recyclebin                           string

on

SQL>

SQL>  select * from recyclebin;        - -查看回收站中是否存在数据

 

no rows selected

 

SQL> alter system set recyclebin=off;            - -关闭回收站

 

System altered.

 

SQL> show parameter recyc                 - -查看回收站状态

 

NAME                                 TYPE        VALUE

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

buffer_pool_recycle                  string

db_recycle_cache_size                big integer         0

recyclebin                           string           OFF

 

:如果回收站中存在信息,请确保其中的信息没有用之后再执行接下来的操作。

 

执行ddl_setup.sql脚本的时候如果报错,把这个脚本cp到home/oracle目录下,在登录sqlplus下执行该脚本,执行的时候加上目录。即

SQL> grant create table,CREATE SEQUENCE TO ggs;

 

Grant succeeded.

 

SQL> @ddl_setup.sql

 

Oracle GoldenGate DDL Replication setup script

Verifying that current user has privileges to install DDL Replication...

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.

NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

Enter Oracle GoldenGate schema name:ggs

Working, please wait ...

Spooling to file ddl_setup_spool.txt

Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ...

Check complete.

Using GGS as a Oracle GoldenGate schema name.

Working, please wait ...

DDL replication setup script complete, running verification script...

Please enter the name of a schema for the GoldenGate database objects:

Setting schema name to GGS

CLEAR_TRACE STATUS:

Line/pos

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

Error

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

No errors

No errors

CREATE_TRACE STATUS:

Line/pos

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

Error

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

No errors

No errors

TRACE_PUT_LINE STATUS:

Line/pos

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

Error

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

No errors

No errors

INITIAL_SETUP STATUS:

Line/pos

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

Error

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

No errors

No errors

DDLVERSIONSPECIFIC PACKAGE STATUS:

Line/pos

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

Error

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

No errors

No errors

DDLREPLICATION PACKAGE STATUS:

Line/pos

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

Error

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

No errors

No errors

DDLREPLICATION PACKAGE BODY STATUS:

Line/pos

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

Error

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

No errors

No errors

DDL IGNORE TABLE

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

OK

DDL IGNORE LOG TABLE

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

OK

DDLAUX  PACKAGE STATUS:

Line/pos

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

Error

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

No errors

No errors

DDLAUX PACKAGE BODY STATUS:

Line/pos

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

Error

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

No errors

No errors

SYS.DDLCTXINFO  PACKAGE STATUS:

Line/pos

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

Error

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

No errors

No errors

SYS.DDLCTXINFO  PACKAGE BODY STATUS:

Line/pos

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

Error

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

No errors

No errors

DDL HISTORY TABLE

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

OK

DDL HISTORY TABLE(1)

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

OK

DDL DUMP TABLES

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

OK

DDL DUMP COLUMNS

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

OK

DDL DUMP LOG GROUPS

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

OK

DDL DUMP PARTITIONS

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

OK

DDL DUMP PRIMARY KEYS

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

OK

DDL SEQUENCE

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

OK

GGS_TEMP_COLS

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

OK

GGS_TEMP_UK

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

OK

DDL TRIGGER CODE STATUS:

Line/pos

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

Error

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

No errors

No errors

DDL TRIGGER INSTALL STATUS

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

OK

DDL TRIGGER RUNNING STATUS

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

ENABLED

STAYMETADATA IN TRIGGER

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

OFF

DDL TRIGGER SQL TRACING

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

0

DDL TRIGGER TRACE LEVEL

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

0

LOCATION OF DDL TRACE FILE

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

/u01/app/oracle/diag/rdbms/racsrv/racsrv3/trace/ggs_ddl_trace.log

Analyzing installation status...

STATUS OF DDL REPLICATION

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

SUCCESSFUL installation of DDL Replication software components

Script complete.

SQL>

请注意查看执行过程中是否存在报错如果存在报错则需要解决后再继续接下来的步骤

 

5.6、源端执行role_setup.sql脚本
SQL> @role_setup.sql

 

GGS Role setup script

This script will drop and recreate the role GGS_GGSUSER_ROLE

To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.)

You will be prompted for the name of a schema for the GoldenGate database objects.

NOTE: The schema must be created prior to running this script.

NOTE: Stop all DDL replication before starting this installation.

Enter GoldenGate schema name:ggs

Wrote file role_setup_set.txt

PL/SQL procedure successfully completed.

Role setup script complete

Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO <loggedUser>

where <loggedUser> is the user assigned to the GoldenGate processes.

 

SQL> GRANT GGS_GGSUSER_ROLE TO ggs;

 

Grant succeeded.

 

这个脚本创建了一个role执行完脚本后需要把创建的role grant给创建的OGG用户(ggs)。

5.7、源端执行ddl_enable.sql脚本
SQL> @ddl_enable.sql

 

Trigger altered.

5.8、源端执行ddl_pin脚本
SQL> @?/rdbms/admin/dbmspool.sql

 

Package created.

Grant succeeded.

View created.

Package body created.

 

 

 

SQL> @ddl_pin ggs

 

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL>

:这里需要在要执行的脚本后直接加上创建的OGG用户(即ggs)。

 

 

6、源端配置抽取进程

进入ogg安装目录/goldengate

:在源端确认能够正常登录到数据库

[oracle@rac1 ~]$ cd /software/ggs

[oracle@rac1 ggs]$ pwd

/software/ggs

[oracle@rac1 ggs]$ ggsci

 

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

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

 

GGSCI (rac1) 1> DBLOGIN USERID ggs, PASSWORD ggs

 

Successfully logged into database.

GGSCI (rac1) 2>

 

6.1、创建extract初始化抽取进程

 

源端ggsci控制台中执行以下命令,创建初始化extract进程

GGSCI (rac1) 2> add extract  ext_1,sourceistable

EXTRACT added.

GGSCI (rac1) 3>

 

:sourceistable代表直接从表中读取数据,初始化抽取进程没有投递进程。

 

edit params ext_1

 

源端初始化抽取进程参数配置:

extract ext_1

SETENV (ORACLE_HOME ="/u01/app/oracle/product/11.2.0/db_1")

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

userid ggs@orcl , password ggs

rmthost 192.168.10.211,mgrport 7840

rmttask replicat,group rept_1

table orachen.T1;

table orachen.T2;

 

 

 

6.2、创建extract同步化抽取进程

源端ggsci控制台中执行以下命令,创建同步化extract进程

GGSCI (rac1) 2> ADD EXTRACT test01, TRANLOG, BEGIN NOW, THREADS 2

EXTRACT added.

GGSCI (rac1) 3>

 

:这里threads 后的2为rac节点的数量,test01为源端的extract进程名,具体值请根据实际情况更改,确保添加成功。

 

 

 

 

 

 

 

 

指定用于存放同步化extract提取内容的trail文件

源端ggsci控制台中执行以下命令创建extract进程使用的trail文件

GGSCI (rac1) 3> ADD EXTTRAIL /software/ggs/dirdat/et, EXTRACT test01,megabytes 100

EXTTRAIL added.

GGSCI (rac1) 4>

 

:这里的/software/ggs/dirdat/et中的dirdat为创建的工作区中的一个目录et为定义的文件名只能用两个小写字母),该命令中指定了使用该文件的是test01进程megabytes指定的是文件的大小具体实施请根据实际情况调整

 

 

 

 

 

 

 

 

 

 

编辑同步化extract进程的参数文件

源端ggsci控制台中执行以下命令来创建extract进程的参数

GGSCI (rac1) 4> EDIT PARAMS test01

进入文件编辑界面将以下内容添加到文件中后保存退出

EXTRACT test01

SETENV (ORACLE_HOME ="/u01/app/oracle/product/11.2.0/db_1")

SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")

USERID ggs@orcl, PASSWORD ggs

THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 3000

TRANLOGOPTIONS ASMUSER sys@ASM, ASMPASSWORD oracle

EXTTRAIL /software/ggs/dirdat/et

DISCARDFILE /software/ggs/dirrpt/test01.dsc APPEND,MEGABYTES 100

WARNLONGTRANS 2h,CHECKINTERVAL 3m

DBOPTIONS ALLOWUNUSEDCOLUMN

CHECKPOINTSECS 3

EOFDELAY 3

FLUSHSECS 3

TABLE orachen.T1;

TABLE orachen.T2;

 

:这里需要注意 ORACLE_HOME需要确保正确

USERID 后为ggs数据库中的用户@service name,PASSWORD为该用户登录数据库pssword

请确保其中填写目录的地方目录的正确性

TABLE orachen.T1为要同步的数据范围

一定要确保有NLS_LANG环境变量

由于MYSQL严格区分大小写,而Oracle大小写不敏感,Oracle会自动统一为大写,因此OGG进程中涉及到的Oracle的表、MYSQL对应的表、DATABASE等全部为大写!!!

具体参数不做解说,需要根据实际情况进行详细的配置。

 

:这里修改了rac集群的tnsnames.ora文件 内容如下:

节点1:

[oracle@slrac1 ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# tnsnames.ora.xxrac1 Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora.xxrac1

# Generated by Oracle configuration tools.

 

racsrv =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = xxrac-scan)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = racsrv)

    )

  )

 

STANDBY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.209.148)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = standby)

    )

  )

 

ASM =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.209.140)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME=+ASM)

      (INSTANCE_NAME = +ASM1)

      (UR=A)

    )

  )

 

[oracle@xxrac1 ~]$

 

节点2:

[oracle@xxrac2 ~]$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

# tnsnames.ora.xxrac2 Network Configuration File: /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora.xxrac2

# Generated by Oracle configuration tools.

 

racsrv =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = xxrac-scan)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = racsrv)

    )

  )

 

STANDBY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.209.148)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = standby)

    )

  )

 

ASM =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.209.142)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME=+ASM)

      (INSTANCE_NAME = +ASM2)

      (UR=A)

    )

  )

 

[oracle@xxrac2 ~]$

 

 

 

 

7、源端配置投递进程

创建pump进程

 

源端ggsci控制台中执行以下命令来创建pump进程:

GGSCI(rac1)5> ADD EXTRACT pump01, EXTTRAILSOURCE /software/ggs/dirdat/et,BEGIN now

EXTRACT added. 

:pump进程也属于extract进程,所以这里创建的时候也是ADD EXTRACT,pump01为进程名,EXTTRAILSOURCE后面为要投递的trail文件,请根据实际情况进行更改。

 

 

 

 

 

 

 

添加mypump传输的目录文件

源端ggsci控制台中执行以下命令来指定pump进程的传输目录

GGSCI (rac1) 6> ADD RMTTRAIL /software/ggs/dirdat/te, EXTRACT PUMP01

RMTTRAIL added.

 

:这里的/software/ggs/dirdat/te是目标端的文件因为这里采用的是将trail文件投递到目标端再到目标库进行实施

 

 

 

 

 

 

 

 

 

修改pump的参数文件

源端ggsci控制台中执行以下命令来创建并且修改pump进程的参数文件

GGSCI (rac1) 7> EDIT PARAMS pump01

进入编辑界面后将以下内容添加到文件中并保存:

EXTRACT pump01

RMTHOST mysql, MGRPORT 7840,TCPBUFSIZE 100000,TCPFLUSHBYTES 300000

EOFDELAY 3

FLUSHSECS 5

PASSTHRU

RMTTRAIL /software/ggs/dirdat/te

TABLE orachen.T1;

TABLE orachen.T2;

 

:这里mysql为目标库主机名,/software/ggs/dirdat/te为目标库文件名

传输的表为orachen用户下所有表,具体参数请根据实际情况设定。

 

 

 

 

 

 

 

 

 

 

 

8、源端配置异构对象映射文件

源端ggsci控制台中执行以下命令来创建pump进程:

GGSCI (rac1) 7> view params defgen

 

Defgen文件参数配置:

userid ggs,password ggs

defsfile /software/ggs/dirdef/oracle.def

table orachen.T1;

table orachen.T2;

 

 

 

使用defgen工具生成异构映射文件

[oracle@rac1 ggs]$ ./defgen paramfile /software/ggs/dirprm/defgen.prm

 

 

拷贝源端生成的.def文件到目标端dirdef目录位置

[oracle@rac1 ggs]$ scp oracle.def root@192.168.10.211:/software/ggs/dirdef

 

 

 

 

 

 

 

 

二、Mysql目标端OGG安装

1、目标端创建ogg相关目录

[root@mysql ggs]#  pwd    注:一定要进入ogg软件目录进行下列操作

/software/ggs 

 

[root@mysql ggs]# ggsci            注:在mysql用户的环境变量中添加ogg目录

 

Oracle GoldenGate Command Interpreter for MySQL

Version 12.3.0.1.2 OGGCORE_12.3.0.1.0_PLATFORMS_171208.0005

Linux, x64, 64bit (optimized), MySQL Enterprise on Dec  8 2017 11:42:23

Operating system character set identified as UTF-8.

 

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

GGSCI (mysql) 1> create subdirs

 

Creating subdirectories under current directory /software/ggs

Parameter files                /software/ggs/dirprm: already exists

Report files                   /software/ggs/dirrpt: created

Checkpoint files               /software/ggs/dirchk: created

Process status files           /software/ggs/dirpcs: created

SQL script files               /software/ggs/dirsql: created

Database definitions files     /software/ggs/dirdef: created

Extract data files             /software/ggs/dirdat: created

Temporary files                /software/ggs/dirtmp: created

Stdout files                   /software/ggs/dirout: created

GGSCI (rac1) 2>

 

 

 

2、目标端创建mgr

在备库进行下列操作:

在OGG控制台中执行以下命令来创建manager进程的参数文件

GGSCI (mysql) 1> edit params mgr

 

进入文件编辑界面后添加以下内容后保存退出:

目标端mgr添加以下参数:

PORT 7840

DYNAMICPORTLIST 7840-7939

--AUTOSTART ER myext01

--AUTOSTART ER myext02

--AUTOSTART ER mypump01

--AUTOSTART ER mypump02

--AUTORESTART ER myext01,RETRIES 5,WAITMINUTES 3

--AUTORESTART ER myext02,RETRIES 5,WAITMINUTES 3

--AUTORESTART ER mypump01,RETRIES 5,WAITMINUTES 3

--AUTORESTART ER mypump02,RETRIES 5,WAITMINUTES 3

PURGEOLDEXTRACTS /software/ggs/dirdat/*,usecheckpoints,minkeepdays 7

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

 

ACCESSRULE, PROG *, IPADDR 192.168.*.*, ALLOW 

 

1、一定不要让rept进程随mgr进程自动启动!!!

2、源库、目标库mgr进程的port必须一致

3、12.2.0.2版本及以上版本ogg需添加ACCESSRULE访问规则限制,否则无法实现mgr进程间通信

 

 

其中PORT 后的是默认启用的端口号

    AUTOSTART是启动manager进程后要自动启动的进程

    AUTORESTART是manager启动后自动重启相关的设置

    在源库添加的PURGEOLDEXTRACTS参数是关于源端trail文件保存删除的参数,这里保留七天,这个参数需要根据客户空间实际情况,以及客户需求来进行设置

    LAG开头的参数是设置lag预警的,将来可以在日志中看到相应的info、waring、error

   这里只给出最基本的参数,具体参数内容需要根据实际情况进行对应的调整。

 

 

3、目标端启动mgr

目标端编辑完参数后需要执行以下操作

通过以下方式来启动和查看manager

GGSCI (mysql) 1> start mgr

 

Manager started.

 

GGSCI (mysql) 2> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

 

GGSCI (mysql) 3> info mgr

 

Manager is running (IP port rac1.7840)

这里manager状态为running是正常状态。

 

如果mgr启动失败,请通过view ggsevt或者退出ggsci控制台,查看安装目录下的ggserr.log错误日志

 

 

4、目标端配置数据库

创建同步表结构

[root@mysql ggs]# pwd

/software/ggs

[root@mysql ggs]# mysql –p123

 

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

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 38

Server version: 5.7.21-log MySQL Community Server (GPL)

 

Copyright (c) 2000, 2018, 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 GGS

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>create table T1(id bigint,name varchar(20));




mysql> create table T2

(

  owner                     VARCHAR(30) not null,

  table_name                VARCHAR(30) not null,

  tablespace_name           VARCHAR(30),

  cluster_name              VARCHAR(30),

  iot_name                  VARCHAR(30),

  status                    VARCHAR(8),

  pct_free                  bigint,

  pct_used                  bigint,

  ini_trans                 bigint,

  max_trans                 bigint,

  initial_extent            bigint,

  next_extent               bigint,

  min_extents               bigint,

  max_extents               bigint,

  pct_increase              bigint,

  freelists                 bigint,

  freelist_groups           bigint,

  logging                   VARCHAR(3),

  backed_up                 VARCHAR(1),

  num_rows                  bigint,

  blocks                    bigint,

  empty_blocks              bigint,

  avg_space                 bigint,

  chain_cnt                 bigint,

  avg_row_len               bigint,

  avg_space_freelist_blocks bigint,

  num_freelist_blocks       bigint,

  degree                    VARCHAR(20),

  instances                 VARCHAR(20),

  cache                     VARCHAR(10),

  table_lock                VARCHAR(8),

  sample_size               bigint,

  last_analyzed             DATE,

  partitioned               VARCHAR(3),

  iot_type                  VARCHAR(12),

  temporary                 VARCHAR(1),

  secondary                 VARCHAR(1),

  nested                    VARCHAR(3),

  buffer_pool               VARCHAR(7),

  flash_cache               VARCHAR(7),

  cell_flash_cache          VARCHAR(7),

  row_movement              VARCHAR(8),

  global_stats              VARCHAR(3),

  user_stats                VARCHAR(3),

  duration                  VARCHAR(15),

  skip_corrupt              VARCHAR(8),

  monitoring                VARCHAR(3),

  cluster_owner             VARCHAR(30),

  dependencies              VARCHAR(8),

  compression               VARCHAR(8),

  compress_for              VARCHAR(12),

  dropped                   VARCHAR(3),

  read_only                 VARCHAR(3),

  segment_created           VARCHAR(3),

  result_cache              VARCHAR(7)

);

 

 

 

 

 

5、目标端mysql登录ogg,创建检查点

 

通过以下方式来启动和查看manager

GGSCI (mysql) 1> dblogin sourcedb GGS@localhost:3306,userid root,password 123

GGSCI (mysql) 2> add checkpointtable GGS.CHECKPOINT

 

 

6、目标端配置应用进程

6.1、创建replicat初始化应用进程

 

目标端ggsci控制台中执行以下命令,创建replicat初始化应用进程

GGSCI (mysql) 2> add replicat rept_1,specialrun

REPLICAT added.

 

 

:specialrun代表只运行一次。

 

edit params rept_1

 

目标端初始化应用进程参数配置:

 

replicat rept_1

sourcecharset ZHS16GBK

setenv (NLS_LANG=AMERICAN_AMERICA.zhs16gbk)

sourcedefs ./dirdef/oracle.def

targetdb GGS,userid root,password 123

discardfile ./dirrpt/rept_1.dsc,purge

map orachen.T1, target GGS.T1 REPERROR (1403, discard);

map orachen.T2, target GGS.T2 REPERROR (1403, discard);

 

:目标端必须和源端的NLS_LANG一致,用户名、表名、库名必须大小写一致。

 

 

 

6.2、创建replicat同步化应用进程

 

目标端ggsci控制台中执行以下命令,创建replicat同步化应用进程

GGSCI (mysql) 2> add replicat rept01,exttrail /software/ggs/dirdat/te,checkpointtable GGS.CHECKPOINT

REPLICAT added.

 

 

编辑同步化应用进程

GGSCI (mysql) 2> edit params rept01

 

目标端同步化应用进程参数配置:

 

replicat rept01

SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")

dboptions host localhost,connectionport 3306

targetdb GGS,userid root,password 123

sourcedefs /software/ggs/dirdef/oracle.def

handlecollisions

--assumetargetdefs

discardfile /software/ggs/dirrpt/rept01.dsc,append,megabytes 50

map orachen.T1,target GGS.T1;

map orachen.T2,target GGS.T2;

 

:目标端必须和源端的NLS_LANG一致,用户名、表名、库名必须大小写一致。

 

 

 

 

 

 

三、Oracle to Mysql异构数据初始化、同步

1、源库启动同步化抽取进程、投递进程
[oracle@rac1 ggs]$ ggsci

 

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

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

 

GGSCI (rac1) 1>  start test01




GGSCI (rac1) 2>  start pump01

 

 

 

2、源库启动初始化抽取进程
[oracle@rac1 ggs]$ ggsci

 

Oracle GoldenGate Command Interpreter for Oracle

Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO

Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14

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

 

GGSCI (rac1) 1>  start ext_1




GGSCI (rac1) 2>  info ext_1

 

GGSCI (rac1) 3>  view report ext_1

 

 

:持续观察目标端ggserr.log日志

 

 

3、目标端启用同步化应用进程

 

通过以下方式来启动和查看manager

GGSCI (mysql) 1> start rept01

 

 

 

posted @ 2022-02-09 17:42  Eddie小陈  阅读(380)  评论(0编辑  收藏  举报