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.
进入文件编辑界面 添加以下一行后保存退出 GGSCHEMA ggs
注:ggs为数据库中的OGG用户 |
创建mgr文件
在主库集群的一个节点上进行下列操作:
在OGG控制台中执行以下命令来创建manager进程的参数文件
进入文件编辑界面后添加以下内容后保存退出: 源库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
Manager started.
Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING
Manager is running (IP port rac1.7840) 这里manager状态为running是正常状态。
注:如果mgr启动失败,请通过view ggsevt或者退出ggsci控制台,查看安装目录下的ggserr.log错误日志 |
5、源端配置数据库
/software/ggs
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
User created.
Grant succeeded.
注:这里的ggs用户可以自行定义,需要与OGG 参数中设置的对象一致,但不能是数据库保留的SCHEMA;赋予ggs用户的权限需要有对目标数据进行操作的权限,一般直接授予DBA权限,具体情况请与用户协商决定;ggs用户需要单独干净的表空间。 |
5.1、源端执行sequence脚本
在源端执行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
Database altered.
Database altered.
Database altered.
Database altered.
System altered.
Database altered.
注:需要在源端添加supplemental日志来保证数据能正常同步。 |
5.3、源端授权给ggs用户
Grant succeeded. |
5.4、源端执行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>
no rows selected
System altered.
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ buffer_pool_recycle string db_recycle_cache_size big integer 0 recyclebin string OFF
注:如果回收站中存在信息,请确保其中的信息没有用之后再执行接下来的操作。 |
执行ddl_setup.sql脚本的时候如果报错,把这个脚本cp到home/oracle目录下,在登录sqlplus下执行该脚本,执行的时候加上目录。即
Grant succeeded.
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.
Grant succeeded.
注:这个脚本创建了一个role,执行完脚本后需要把创建的role grant给创建的OGG用户(ggs)。 |
5.7、源端执行ddl_enable.sql脚本
Trigger altered. |
5.8、源端执行ddl_pin脚本
SQL> @?/rdbms/admin/dbmspool.sql
Package created. Grant succeeded. View created. Package body created.
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. SQL> 注:这里需要在要执行的脚本后直接加上创建的OGG用户(即ggs)。 |
6、源端配置抽取进程
进入ogg安装目录/goldengate
注:在源端确认能够正常登录到数据库
/software/ggs
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.
Successfully logged into database. GGSCI (rac1) 2>
|
6.1、创建extract初始化抽取进程
在源端ggsci控制台中执行以下命令,创建初始化extract进程
EXTRACT added. GGSCI (rac1) 3>
注:sourceistable代表直接从表中读取数据,初始化抽取进程没有投递进程。
源端初始化抽取进程参数配置: 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进程
EXTRACT added. GGSCI (rac1) 3>
注:这里threads 后的2为rac节点的数量,test01为源端的extract进程名,具体值请根据实际情况更改,确保添加成功。
指定用于存放同步化extract提取内容的trail文件 在源端ggsci控制台中执行以下命令创建extract进程使用的trail文件
EXTTRAIL added. GGSCI (rac1) 4>
注:这里的/software/ggs/dirdat/et中的dirdat为创建的工作区中的一个目录,et为定义的文件名(只能用两个小写字母),该命令中指定了使用该文件的是test01进程,megabytes指定的是文件的大小,具体实施请根据实际情况调整
编辑同步化extract进程的参数文件 在源端ggsci控制台中执行以下命令来创建extract进程的参数
进入文件编辑界面将以下内容添加到文件中后保存退出 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:
# 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:
# 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进程:
EXTRACT added. 注:pump进程也属于extract进程,所以这里创建的时候也是ADD EXTRACT,pump01为进程名,EXTTRAILSOURCE后面为要投递的trail文件,请根据实际情况进行更改。
添加mypump传输的目录文件 在源端ggsci控制台中执行以下命令来指定pump进程的传输目录
RMTTRAIL added.
注:这里的/software/ggs/dirdat/te是目标端的文件,因为这里采用的是将trail文件投递到目标端再到目标库进行实施。
修改pump的参数文件 在源端ggsci控制台中执行以下命令来创建并且修改pump进程的参数文件
进入编辑界面后将以下内容添加到文件中并保存: 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进程:
Defgen文件参数配置: userid ggs,password ggs defsfile /software/ggs/dirdef/oracle.def table orachen.T1; table orachen.T2;
使用defgen工具生成异构映射文件
拷贝源端生成的.def文件到目标端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进程的参数文件
进入文件编辑界面后添加以下内容后保存退出: 目标端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
Manager started.
Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING
Manager is running (IP port rac1.7840) 这里manager状态为running是正常状态。
注:如果mgr启动失败,请通过view ggsevt或者退出ggsci控制台,查看安装目录下的ggserr.log错误日志 |
4、目标端配置数据库
创建同步表结构
/software/ggs
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.
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
|
5、目标端mysql登录ogg,创建检查点
通过以下方式来启动和查看manager
|
6、目标端配置应用进程
6.1、创建replicat初始化应用进程
在目标端ggsci控制台中执行以下命令,创建replicat初始化应用进程
REPLICAT added.
注:specialrun代表只运行一次。
目标端初始化应用进程参数配置:
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同步化应用进程
REPLICAT added.
编辑同步化应用进程
目标端同步化应用进程参数配置:
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 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.
|
2、源库启动初始化抽取进程
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.
注:持续观察目标端ggserr.log日志 |
3、目标端启用同步化应用进程
通过以下方式来启动和查看manager
|
本文来自博客园,作者:Eddie小陈,转载请注明原文链接:https://www.cnblogs.com/orachen/p/15876040.html