夜雨聆风由于业务特定需求，需要将Oracle数据库中的数据同步到MySQL数据库中，方便后续割接迁移到国产化数据库中。本方案介绍了使用ogg软件同步oracle库中的数据到mysql库的全过程！
1、OGG软件介绍
OGG（Oracle GoldenGate）是一个应用程序，提供实时数据集成、数据复制、事务变更数据捕获、数据转换、高可用性解决方案，以及操作和分析企业系统之间的验证。 使用Oracle GoldenGate，您可以通过安全或非安全配置在企业中的多个系统之间移动已提交的事务。它支持广泛的数据库和数据源，提供相同类型之间或异构数据库之间的复制。例如，您可以在Oracle自治数据库实例和Oracle数据库实例之间进行复制，或者在作为源和目标的两个Oracle数据库实例之间进行复制，或者在MySQL数据库和Oracle数据库实例之间进行双向复制。此外，您还可以将数据复制到Java Messaging Queues、flat files以及结合Oracle GoldenGate for Big Data复制到Big Data。 官方文档：https://docs.oracle.com/en/middleware/goldengate/index.html
2、OGG工作原理图
图片来源于网络：https://www.cnblogs.com/yaoyangding/p/14918938.html，如有侵权，删
3、OGG软件下载
Ogg下载地址： https://www.oracle.com/downloads/
4、使用OGG将oracle库的数据同步到mysql数据库中
下面主要描述使用OGG（Oracle GoldenGate）软件将oracle库的数据同步到mysql数据库中的实施过程。
4.1 环境说明
4.2 OGG软件安装和数据同步配置
对于部署实时同步的场景，Oracle GoldenGate软件要求在生产库以及目标库分别安装软件。对于最新版的OGG软件则只需要在目标库主机部署ogg软件即可，使用前请查看ogg相关说明文档。 OGG软件安装包为： 源端： ogg12.3 V975837-01.zip 目标端：p36473485_2114000OGGRU_Linux-x86-64.zip，ogg for mysql，版本21.14
4.2.1 部署前OGG的配置
在部署GoldenGate前，需要如下配置：
确认已开启源端oracle库归档目录； 源端创建/goldengate目录，授权oracle:oinstall； 目标端划分1T存储磁盘空间； 目标端创建/goldengate目录，授权mysql:mysql
4.2.2 oracle源端安装OGG-12.3.0.1.4
上传ogg软件并解压su - oraclecd /u01/softwareunzip V975837-01.zip 配置环境变量su - oraclevi .bash_profile加入export OGG_HOME=/goldengateexport PATH=$PATH:$ORACLE_HOME/bin:$OGG_HOMEexport TNS_ADMIN=$ORACLE_HOME/network/adminexport LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/rdbms/lib:/lib:/usr/libexport LD_LIBRARY_PATH=$OGG_HOME:$LD_LIBRARY_PATHalias ggsci='cd $OGG_HOME;ggsci'静默安装vi /u01/software/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp修改下面两项：INSTALL_OPTION=ORA11gSOFTWARE_LOCATION=/goldengate[oracle@11rac01 Disk1]$ /u01/software/fbo_ggs_Linux_x64_shiphome/Disk1/runInstaller -silent -responseFile /u01/software/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rspggsci:[oracle@11rac01 goldengate]$ ggsciOracle GoldenGate Command Interpreter for OracleVersion 12.3.0.1.4 OGGCORE_12.3.0.1.0_PLATFORMS_180415.0359_FBOLinux, x64, 64bit (optimized), Oracle 11g on Apr 15 2018 21:16:09Operating system character set identified as UTF-8.Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.GGSCI (11rac01) 1> 4.2.3 oracle源端配置ogg
4.2.3.1 开启归档日志
SQL> archive log list;Database log mode Archive ModeAutomatic archival EnabledArchive destination /u01/archOldest online log sequence 115Next log sequence to archive 117Current log sequence 117SQL> SQL> show parameter log_archive_dest_1;NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_1 string location=/u01/arch VALID_FOR=( ALL_LOGFILES,ALL_ROLES) DB_UNI QUE_NAME=orcl4.2.3.2 打开数据库的最小附加日志
SQL> select force_logging from v$database;#检查是否开启强制日志模式SQL> alter database force logging;#开启强制日志SQL> select supplemental_log_data_min from v$database;#检查是否开启附加日志SQL> alter database add supplemental log data;#开启附加日志SQL> alter system archive log current; #切换日志4.2.3.3 设置enable_goldengate_replication参数
SQL> alter system set enable_goldengate_replication=true scope=both;System altered.4.2.3.4 检查参数是否都已修改好
SQL> select name,log_mode,db_unique_name,supplemental_log_data_min,force_logging from v$database;NAME LOG_MODE DB_UNIQUE_NAME SUPPLEME FOR--------- ------------ ------------------------------ -------- ---ORCL ARCHIVELOG orcl YES YES SQL> show parameter enable_goldengate_replication;NAME TYPE VALUE------------------------------------ ----------- ------------------------------enable_goldengate_replication boolean TRUE4.2.3.5 创建GoldenGate管理用户
创建表空间：alter tablespace users add datafile '/u01/data/orcl/users02.dbf' size 500m;并在上面创建ogg用户：SQL>create user ogg identified by ogg default tablespace users;赋予权限：SQL>grant dba ,connect, resource, unlimited tablespace to ogg;4.2.3.6 创建测试用户及数据
SQL> create user test_ogg identified by ogg ;User created.SQL> grant dba ,connect, resource, unlimited tablespace to test_ogg;Grant succeeded.SQL> conn test_ogg/oggConnected.SQL> create table test_ogg(id int,name varchar(20),primary key(id));Table created.SQL> insert into test_ogg values('1','a');1 row created.SQL> insert into test_ogg values('2','b');1 row created.SQL> commit;Commit complete.SQL> 4.2.3.7 创建子目录及编辑GLOBALS参数文件
[oracle@11rac01 ~]$ ggsciGGSCI (11rac01) 1> create subdirsCreating subdirectories under current directory /goldengateParameter file /goldengate/dirprm: created.Report file /goldengate/dirrpt: created.Checkpoint file /goldengate/dirchk: created.Process status files /goldengate/dirpcs: created.SQL script files /goldengate/dirsql: created.Database definitions files /goldengate/dirdef: created.Extract data files /goldengate/dirdat: created.Temporary files /goldengate/dirtmp: created.Credential store files /goldengate/dircrd: created.Masterkey wallet files /goldengate/dirwlt: created.Dump files /goldengate/dirdmp: created.GGSCI (myoracle) 1> EDIT PARAMS ./GLOBALS添加以下内容：ggschema ogg 4.2.3.8 OGG数据库用户登录测试
GGSCI (11rac01) 1> dblogin userid ogg, password ***Successfully logged into database.#19c数据库需要以下方式登录dblogin userid ogg@192.\*.*.50:1521/orcldb, password *** 4.2.3.9 配置MGR管理进程
GGSCI (11rac01) 1> edit param mgr加入：PORT 7809DYNAMICPORTLIST 7810-7860AUTORESTART ER *, RETRIES 3, WAITMINUTES 5PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 30lagreporthours 1laginfominutes 30lagcriticalminutes 60ACCESSRULE, PROG *, IPADDR 192.*.*.*, PRI 1, ALLOWGGSCI (11rac01) 1> start mgrManager started.GGSCI (11rac01) 2> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING 4.2.3.10 配置CHECKPOINTTABLE
GGSCI (11rac01) 3> dblogin userid ogg,password ***Successfully logged into database.GGSCI (11rac01 as ogg@orcl) 5> edit params ./GLOBALS加入：CHECKPOINTTABLE ogg.ggschkptGGSCI (11rac01 as ogg@orcl) 6> add checkpointtable ogg.ggschkptSuccessfully created checkpoint table ogg.ggschkpt.开启表级附加日志
GGSCI (11rac01 as ogg@orcl) 7> add schematrandata test_ogg2024-09-21 16:52:57 INFO OGG-01788 SCHEMATRANDATA has been added on schema "test_ogg".2024-09-21 16:52:57 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema "test_ogg".GGSCI (11rac01 as ogg@orcl) 8> add trandata test_ogg.*2024-09-21 16:53:10 INFO OGG-15132 Logging of supplemental redo data enabled for table TEST_OGG.TEST_OGG.2024-09-21 16:53:10 INFO OGG-15133 TRANDATA for scheduling columns has been added on table TEST_OGG.TEST_OGG.2024-09-21 16:53:10 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table TEST_OGG.TEST_OGG.GGSCI (11rac01 as ogg@orcl) 9> capture tabledef test_ogg.*Table definitions for TEST_OGG.TEST_OGG:ID NUMBER NOT NULL PKNAME VARCHAR (20)检查表级附加日志是否全部打开SQL> select log_group_name,log_group_type,owner,table_name,always from dba_log_groups;LOG_GROUP_NAME LOG_GROUP_TYPE OWNER TABLE_NAME ALWAYS------------------------------ ---------------------------- ------------------------------ ------------------------------ -----------SEQ$_LOG_GRP USER LOG GROUP SYS SEQ$ ALWAYSENC$_LOG_GRP USER LOG GROUP SYS ENC$ ALWAYSGGS_88756 USER LOG GROUP TEST_OGG TEST_OGG ALWAYSSYS_C0011145 PRIMARY KEY LOGGING TEST_OGG TEST_OGG ALWAYSSYS_C0011146 UNIQUE KEY LOGGING TEST_OGG TEST_OGG CONDITIONALSYS_C0011147 FOREIGN KEY LOGGING TEST_OGG TEST_OGG CONDITIONAL6 rows selected.4.2.3.11 OGG抽取进程配置
GGSCI (myoracle as ogg@cdb19c/PDB19C)9> edit param extorcl添加以下配置extract extorclDDL INCLUDE ALLSETENV (ORACLE_HOME ="/u01/app/oracle/product/11.2.0/db_1")SETENV (ORACLE_SID="orcl")SETENV (NLS_LANG="american_america.AL32UTF8")userid ogg@192.\*.*.130:1521/orcldb, password ***exttrail /goldengate/dirdat/trtable test_ogg.*;GGSCI (11rac01 as ogg@orcl) 24> edit param extorcl #编辑GGSCI (11rac01 as ogg@orcl) 24> delete extract extorcl #删除GGSCI (11rac01 as ogg@orcl) 24>ADD EXTRACT extorcl,INTEGRATED TRANLOG,BEGIN NOW #核心参数GGSCI (11rac01 as ogg@orcl) 24>ADD EXTTRAIL /goldengate/dirdat/tr,EXTRACT extorcl, MEGABYTES 100 #核心参数GGSCI (11rac01 as ogg@orcl) 6> start extorclSending START request to MANAGER ...EXTRACT EXTORCL startingGGSCI (11rac01 as ogg@orcl) 7> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT RUNNING EXTORCL 00:00:00 00:17:54 4.2.3.12 OGG源端传输进程pumysql配置
GGSCI (11rac01 as ogg@orcl) 8> edit param pumysql添加以下配置extract pumysqluserid ogg@192.\*.*.130:1521/orcldb, password ***rmthost 192.\*.*.20,mgrport 7809rmttrail /goldengate/dirdat/rttable test_ogg.*;GGSCI (11rac01 as ogg@orcl) 9> add extract pumysql, exttrailsource /goldengate/dirdat/tr #核心参数EXTRACT added.GGSCI (11rac01 as ogg@orcl) 10> add rmttrail /goldengate/dirdat/rt, extract pumysql, megabytes 100 #核心参数RMTTRAIL added.4.2.3.13 启动EXTORCL、pumysql和mgr进程
GGSCI (11rac01 as ogg@orcl) 11>start mgrGGSCI (11rac01 as ogg@orcl) 12>start extorclGGSCI (11rac01 as ogg@orcl) 13>start pumysqlGGSCI (11rac01 as ogg@orcl) 14>info all应用进程
edit param tab1添加以下内容defsfile /goldengate/dirdef/ogg_test.ogg_testuserid ogg,password ***table test_ogg.test_ogg; 生成表定义文件
利用DEFGEN工具可以为源端和目标端表生成数据定义文件，当源库和目标库类型不一致时，或源端的表和目标端的表结构不一致时，数据定义文件时必须要有的
生成数据定义文件的步骤
Step1. 编辑defgen文件
Step2. 利用defgen工具生成defgen.prm文件
Step3. 将生成好的数据定义文件ftp二进制模式传输到容灾端对应的目录dirdef
[oracle@myoracle goldengate]$./defgen paramfile dirprm/tab1.prm这里需要注意的是要等mysql的OGG服务装完后把/goldengate/dirdef/ogg_test.ogg_test文件SCP到对应的目录下
4.2.4 mysql目标端安装ogg 12.14(root用户安装)
unzip p36473485_2114000OGGRU_Linux-x86-64.zip -d /goldengatecd /goldengate/tar -xf ggs_Linux_x64_MySQL_64bit.tar chown -R root: /goldengate/4.2.5 mysql目标端配置ogg
4.2.5.1 配置环境变量
vi .bash_profileexport OGG_HOME=/goldengateexport LD_LIBRARY_PATH=$JAVA_HOME/jre/lib/amd64:$JAVA_HOME/jre/lib/amd64/server:$JAVA_HOME/jre/lib/amd64/libjsig.so:$JAVA_HOME/jre/lib/amd64/server/libjvm.so:$OGG_HOME/lib:$HOME/libsexport PATH=$OGG_HOME:$PATHalias ggsci='cd $OGG_HOME;ggsci'4.2.5.2 mysql目标端创建对应ogg用户和数据库
mysql> GRANT SYSTEM_USER ON *.* TO 'root'@'%';Query OK, 0 rows affected (0.06 sec)mysql> create user ogg@'%' identified by '******';Query OK, 0 rows affected (0.04 sec)mysql> grant all privileges on *.* to ogg@'%';Query OK, 0 rows affected (0.03 sec)mysql> flush privileges;Query OK, 0 rows affected (0.01 sec)mysql> create database ogg;Query OK, 1 row affected (0.02 sec)4.2.5.3 创建同步表
表多的话，可以使用Navicat的数据传输功能或其它工具直接从Oracle端生成MySQL类型的建表语句。
mysql> create database ogg;Query OK, 1 row affected (0.02 sec)mysql> use ogg;Database changedmysql> create table test_ogg(id int,name varchar(20),primary key(id));Query OK, 0 rows affected (0.05 sec)4.2.5.4 创建目录
[root@node2 ~]# ggsciGGSCI (node2) 2> create subdirsCreating subdirectories under current directory /goldengateParameter file /goldengate/dirprm: created.Report file /goldengate/dirrpt: created.Checkpoint file /goldengate/dirchk: created.Process status files /goldengate/dirpcs: created.SQL script files /goldengate/dirsql: created.Database definitions files /goldengate/dirdef: created.Extract data files /goldengate/dirdat: created.Temporary files /goldengate/dirtmp: created.Credential store files /goldengate/dircrd: created.Master encryption key wallet files /goldengate/dirwlt: created.Dump files /goldengate/dirdmp: created.将源端oracle 19c生成的ogg_test.ogg_test文件传到到mysql目的端。
scp /goldengate/36175509/files/dirdef/ogg_test.ogg_test root@192.\*.*.20:/goldengate/dirdef/ #源端操作配置MGR管理进程
GGSCI (node2) 1> edit param mgr添加以下内容PORT 7809DYNAMICPORTLIST 7810-7820AUTOSTART EXTRACT *AUTORESTART EXTRACT *PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7LAGREPORTHOURS 1LAGINFOMINUTES 30LAGCRITICALMINUTES 45ACCESSRULE, PROG *, IPADDR *, ALLOWGGSCI (node2) 4> start mgrGGSCI (node2) 5> info all dblogin登录数据库
GGSCI (node2) 6> dblogin sourcedb ogg@192.\*.*.20:3306 ,userid root, password ******Successfully logged into database.GGSCI (node2 DBLOGIN as root) 7> add checkpointtable ogg.ggs_checkpoint编辑目的端REPLICAT进程
GGSCI (node2 DBLOGIN as root) 11>edit params r_tab1replicat r_tab1targetdb ogg@192.\*.*.20:3306,userid root, password *****sourcedefs /ogg/dirdef/ogg_test.ogg_testHANDLECOLLISIONSMAP orcldb.test_ogg.*,target ogg.test_ogg;GGSCI (node2 DBLOGIN as root) 11>add replicat r_tab1,exttrail /goldengate/dirdat/rt,checkpointtable ogg.ggs_checkpointGGSCI (node2 DBLOGIN as root) 12>start mgr GGSCI (node2 DBLOGIN as root) 13>start r_tab1GGSCI (node2 DBLOGIN as root) 14>info all4.2.6 查看源端和目标端进程状态
#源端GGSCI (node1) 4> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING EXTRACT RUNNING EXTORCL 00:00:00 00:00:07 EXTRACT RUNNING PUMYSQL 00:00:00 02:15:17 #目标端GGSCI (node2 DBLOGIN as root) 22> info allProgram Status Group Lag at Chkpt Time Since ChkptMANAGER RUNNING REPLICAT RUNNING R_TAB1 00:00:00 00:00:08 4.2.7 同步数据验证
oracle源端和mysql目标端中检查：
查询现有数据：select * from test_ogg;oracle数据库中插入数据后查询：select * from test_ogg;数据一致，同步正常！
5、碰到的问题
1、Software Location /goldengate specified is already an existing Oracle GoldenGate home and does not contain the selected Oracle GoldenGate install type. [oracle@11rac01 goldengate]$ /u01/software/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/runInstaller -silent -responseFile /u01/software/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/response/oggcore.rsp Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 4892 MB Passed Checking swap space: must be greater than 150 MB. Actual 2047 MB Passed Preparing to launch Oracle Universal Installer from /tmp/OraInstall2024-09-21_08-39-59AM. Please wait ...[oracle@11rac01 goldengate]$ [FATAL] [INS-75012] Software Location /goldengate specified is already an existing Oracle GoldenGate home and does not contain the selected Oracle GoldenGate install type. CAUSE: Software Location /goldengate specified is found registered in the central inventory already and does not contain the selected Oracle GoldenGate install type. ACTION: Specify an empty directory or an Oracle GoldenGate home containing the selected Oracle GoldenGate install type.解决办法：
vi /u01/app/oraInventory/ContentsXML/inventory.xml删除这一行：<HOME NAME="OraHome1" LOC="/goldengate" TYPE="O" IDX="2"/>2、ggsci报错：安装ogg21.15和21.3都报错 [oracle@11rac01 goldengate]$ ./ggsci ./ggsci: /lib64/libc.so.6: version GLIBC_2.14' not found (required by /goldengate/instantclient/libnnz21.so) ./ggsci: /lib64/libc.so.6: version GLIBC_2.17' not found (required by /goldengate/instantclient/libclntsh.so.21.1) ./ggsci: /lib64/libc.so.6: version GLIBC_2.14' not found (required by /goldengate/instantclient/libclntsh.so.21.1) ./ggsci: /lib64/libc.so.6: version GLIBC_2.14' not found (required by /goldengate/instantclient/libclntshcore.so.21.1) 原因：glibc版本过低 glibc下载：http://ftp.gnu.org/pub/gnu/glibc/
3、安装ogg报错： [INS-08109] Unexpected error occurred while validating inputs at state 'installOptions' 原因：response文件中ORA11g错写成了ORA11G 4、extract进程无法启动
ggsci:ADD EXTTRAIL /goldengate/dirdat/tr,EXTRACT extorcl, MEGABYTES 100file portion must be two charactersextract进程无法启动，报错如下：2024-09-21 17:21:15 ERROR OGG-02022 Logmining server does not exist on this Oracle database.2024-09-21 17:21:15 ERROR OGG-01668 PROCESS ABENDING.解决办法：
GGSCI (11rac01 as ogg@orcl) 3> REGISTER EXTRACT EXTORCL,database2024-09-21 17:27:17 ERROR OGG-15415 Log archive destination at the source database must be configured with the NOREGISTER attribute for destination id '1'.2024-09-21 17:27:17 ERROR OGG-15415 Log archive destination at the source database must be configured with the NOREGISTER attribute for destination id '1'.oracle19c不需要修改数据库参数：SQL> show parameter log_archive_dest_1;NAME TYPE VALUE------------------------------------ ----------- ------------------------------log_archive_dest_1 string location=/u01/arch VALID_FOR=( ALL_LOGFILES,ALL_ROLES) DB_UNI QUE_NAME=orcl去掉db_unique_name：SQL> alter system set log_archive_dest_1='location=/u01/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES)';GGSCI (11rac01 as ogg@orcl) 4> REGISTER EXTRACT EXTORCL,database2024-09-21 17:29:38 INFO OGG-02003 Extract EXTORCL successfully registered with database at SCN 2614619.6、参考文档
How to Replicate Data Between Oracle and MySQL Database? (文档 ID 1605674.1)
7、总结
将oracle库中的数据同步到mysql库中有多种解决方案，ogg只是其中的一种。在实际生产项目中使用ogg迁移数据的过程中也会碰到不少问题，要多在测试环境中进行验证，选择最适合的方案。
8、关于作者
网名：飞天，墨天轮2024年度、2025年度优秀原创作者，拥有 Oracle 10g OCM 认证、PGCE认证、MySQL 8.0 OCP认证以及OBCA、KCP、KCSM、ACP、YCP、HCIP-openGauss、HCCDP-GaussDB、磐维等众多国产数据库认证证书，目前从事Oracle、Mysql、PostgreSQL、磐维数据库管理运维工作，喜欢结交更多志同道合的朋友，热衷于研究、分享数据库技术。微信公众号：飞天online墨天轮：https://www.modb.pro/u/15197如有任何疑问，欢迎大家留言，共同探讨~~~