版本信息：ORACLE 19.3 to Mysql 5.7 ，ORACLE源端的ogg版本是21.3，MySQL目标端的ogg版本是12.3。（一开始搭建的是ORACLE 19.3 to Mysql 8.0，OGG的版本都是21.3,但是在搭建后期目标端dblogin时总是报错：OGG-00768 Failed to Map server character to ULibCharSet. SQL error (0)， 经过多番咨询老师和查找，感觉应该是触发了bug，但是不想放弃，想着把目标端的版本降下来再次尝试搭建，最后成功同步，以下是我搭建的过程记录)
源端：
oracle用户下编辑.bashrc文件
export TNS_ADMIN=$ORACLE_HOME/network/admin
export OGG_HOME=/opt/ogg
export 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:$LD_LIBRARY_PATH:$ORACLE_HOME/lib:/usr/lib
export PATH=$OGG_HOME:$PATH:$ORACLE_HOME/bin
创建ogg用户
[root@node ~]# useradd ogg -g oinstall
[root@node ~]# passwd ogg
Changing password for user ogg.
New password:
BAD PASSWORD: The password is shorter than 8 characters
Retype new password:
passwd: all authentication tokens updated successfully.
创建对应目录
[root@node opt]# mkdir -p /opt/ogg
[root@node opt]# chown -R ogg /opt/ogg
[root@node opt]# mkdir -p /home/oracle/oradata/OGG/ORCLCDB
[root@node opt]# mkdir -p /home/oracle/oradata/OGG/tablespace
[root@node opt]# chown -R oracle:oinstall /home/oracle/oradata/OGG
上传ogg安装包
[ogg@node ~]$ ls -lrt
total 337864
-rw-r--r-- 1 root root 345971438 Oct 10 23:23 213000_fbo_ggs_Linux_x64_Oracle_shiphome.zip
解压安装包
[ogg@node ~]$ unzip 213000_fbo_ggs_Linux_x64_Oracle_shiphome.zip
进入安装目录安装ogg软件
[ogg@node Disk1]$ cd /home/ogg/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1
[ogg@node Disk1]$ export DISPLAY=192.168.176.1:0.0
[ogg@node Disk1]$ ./runInstaller
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 9608 MB Passed
Checking swap space: must be greater than 150 MB. Actual 1942 MB Passed
Checking monitor: must be configured to display at least 256 colors. Actual 16777216 Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2022-10-10_11-32-12PM. Please wait ...[ogg@node Disk1]$ You can find the log of this install session at:
/oracle/app/oraInventory/logs/installActions2022-10-10_11-32-12PM.log
ogg用户下编辑.bashrc文件
export ORACLE_BASE=/oracle/app/oracle
export ORACLE_HOME=/oracle/19c
export ORACLE_SID=wbq
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/oracm/lib:$ORACLE_HOME/lib
export OGG_HOME=/opt/ogg
export PATH=$OGG_HOME:$PATH:$ORACLE_HOME/bin
export TNS_ADMIN=$ORACLE_HOME/network/admin
[ogg@node ~]$ source ~/.bashrc
切换到oracle用户下 查看归档是否开启
[oracle@node ~]$ sqlplus / as sysdba
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /sdb1/archlog
Oldest online log sequence 51
Next log sequence to archive 53
Current log sequence 53
需要打开强制日志和附加日志模式
SQL> select name,open_mode,force_logging,supplemental_log_data_min from v$database;
NAME OPEN_MODE
------------------ ----------------------------------------
FORCE_LOGGING
------------------------------------------------------------------------------
SUPPLEMENTAL_LOG
----------------
WBQ READ WRITE
YES
YES
创建ogg的表空间
create tablespace oggtbs datafile '/oracle/app/oracle/oradata/WBQ/oggtbs01.dbf' size 1000M autoextend on;
SQL> select tablespace_name from dba_data_files;
TABLESPACE_NAME
------------------------------------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
DATA
DATA
DATA
OGGTBS <<<<<<
SQL> select * from dba_data_files where tablespace_name='OGGTBS';
FILE_NAME
--------------------------------------------------------------------------------
FILE_ID TABLESPACE_NAME
---------- ------------------------------------------------------------
BYTES BLOCKS STATUS RELATIVE_FNO AUTOEX MAXBYTES
---------- ---------- ------------------ ------------ ------ ----------
MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ONLINE_STATUS LOST_WRITE_PRO
---------- ------------ ---------- ----------- -------------- --------------
/oracle/app/oracle/oradata/WBQ/oggtbs01.dbf
9 OGGTBS
1048576000 128000 AVAILABLE 9 YES 3.4360E+10
4194302 1 1047527424 127872 ONLINE OFF
创建用户并授权
SQL> create user gguser identified by gguser default tablespace oggtbs;
User created.
SQL> grant dba to gguser;
Grant succeeded.
验证gguser是否可以连库
查看tnsname.ora
LISTENER =
(DESCRIPTION =
(FAILOVER=ON)
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.176.133)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME=wbq)
)
)
[oracle@node ~]$ sqlplus gguser/gguser@LISTENER
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 11 01:22:56 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Tue Oct 11 2022 01:22:45 +08:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
创建同步的表
SQL> create user test identified by test;
User created.
SQL> grant dba,connect,resource,unlimited tablespace to test;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
[oracle@node ~]$ sqlplus test/test@LISTENER
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Oct 11 22:40:15 2022
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> create table tableone(id int,name varchar(20),primary key(id));
Table created.
切换到ogg用户 进入$OGG_HOME 执行ggsci
GGSCI (node) 1> create subdirs
Creating subdirectories under current directory /opt/ogg
Parameter file /opt/ogg/dirprm: already exists.
Report file /opt/ogg/dirrpt: already exists.
Checkpoint file /opt/ogg/dirchk: already exists.
Process status files /opt/ogg/dirpcs: already exists.
SQL script files /opt/ogg/dirsql: already exists.
Database definitions files /opt/ogg/dirdef: already exists.
Extract data files /opt/ogg/dirdat: already exists.
Temporary files /opt/ogg/dirtmp: already exists.
Credential store files /opt/ogg/dircrd: already exists.
Master encryption key wallet files /opt/ogg/dirwlt: already exists.
Dump files /opt/ogg/dirdmp: already exists.
GGSCI (node) 2> dblogin userid gguser@LISTENER,password gguser
Successfully logged into database.
GGSCI (node as gguser@wbq) 3> edit param ./globals
编辑内容并保存：oggschema gguser
GGSCI (node as gguser@wbq) 4> add trandata test.tableone
2022-10-11 22:38:26 ERROR OGG-15122 No viable tables matched specification test.tableone.
GGSCI (node as gguser@wbq) 5> add trandata test.tableone
2022-10-11 22:41:58 INFO OGG-15132 Logging of supplemental redo data enabled for table TEST.TABLEONE.
2022-10-11 22:41:58 INFO OGG-15133 TRANDATA for scheduling columns has been added on table TEST.TABLEONE.
2022-10-11 22:41:59 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table TEST.TABLEONE.
2022-10-11 22:42:02 INFO OGG-10471 ***** Oracle Goldengate support information on table TEST.TABLEONE *****
Oracle Goldengate support native capture on table TEST.TABLEONE.
Oracle Goldengate marked following column as key columns on table TEST.TABLEONE: ID.
GGSCI (node as gguser@wbq) 6> add trandata test.*
2022-10-11 22:43:01 INFO OGG-15131 Logging of supplemental redo log data is already enabled for table TEST.TABLEONE.
2022-10-11 22:43:01 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table TEST.TABLEONE.
2022-10-11 22:43:01 INFO OGG-10471 ***** Oracle Goldengate support information on table TEST.TABLEONE *****
Oracle Goldengate support native capture on table TEST.TABLEONE.
Oracle Goldengate marked following column as key columns on table TEST.TABLEONE: ID.
GGSCI (node as gguser@wbq) 7> add schematrandata test
2022-10-11 22:43:43 INFO OGG-01788 SCHEMATRANDATA has been added on schema "test".
2022-10-11 22:43:44 INFO OGG-01976 SCHEMATRANDATA for scheduling columns has been added on schema "test".
2022-10-11 22:43:44 INFO OGG-10154 Schema level PREPARECSN set to mode NOWAIT on schema "test".
2022-10-11 22:43:46 INFO OGG-10471 ***** Oracle Goldengate support information on table TEST.TABLEONE *****
Oracle Goldengate support native capture on table TEST.TABLEONE.
Oracle Goldengate marked following column as key columns on table TEST.TABLEONE: ID.
ogg源端配置mgr
GGSCI (node as gguser@wbq) 9> edit param mgr
编辑内容并保存：
PORT 7809
autostart er *
autorestart er *,waitminutes 3,retries 15
purgeoldextracts ./dirdat/**/ ,usecheckpoints,minkeepdays 7
ogg源端配置抽取进程ext1
GGSCI (node as gguser@wbq) 12> edit param ext1
extract ext1
setenv(NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
userid gguser@LISTENER, password gguser
exttrail /opt/ogg/dirdat/to,format release 12.3
table test.tableone;
GGSCI (node as gguser@wbq) 13> add extract ext1,INTEGRATED TRANLOG,BEGIN NOW
Integrated Extract added.
GGSCI (node as gguser@wbq) 14> add exttrail /opt/ogg/dirdat/to,extract ext1
EXTTRAIL added.
ogg源端配置传输进程pus1
GGSCI (node as gguser@wbq) 16> edit param pus1
extract pus1
userid gguser@LISTENER, password gguser
rmthost 192.168.176.127,mgrport 7810
rmttrail /home/oggmysql/dirdat/re,format release 12.3
table test.tableone;
GGSCI (node as gguser@wbq) 17> add extract pus1, exttrailsource /opt/ogg/dirdat/to
Extract added.
GGSCI (node as gguser@wbq) 43> add rmttrail /home/oggmysql/dirdat/re, extract pus1, megabytes 100
RMTTRAIL added.
mapping文件defgen生成：
GGSCI (node) 8> edit param defgen
userid gguser@LISTENER,password gguser
defsfile /opt/ogg/dirdef/source.def FORMAT RELEASE 12.2
table test.*;
ogg@node ogg]$ ./defgen paramfile dirprm/defgen.prm
***********************************************************************
Oracle GoldenGate Table Definition Generator for Oracle
Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047
Oracle Linux 7, x64, 64bit (optimized), Oracle Database 21c and lower supported versions on Jul 28 2021 13:27:11
Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.
Starting at 2022-10-12 19:03:27
***********************************************************************
Operating System Version:
Linux
Version #2 SMP Fri Apr 23 09:05:55 PDT 2021, Release 5.4.17-2102.201.3.el7uek.x86_64
Node: node
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 11279
***********************************************************************
** Running with the following parameters **
***********************************************************************
userid gguser@LISTENER,password ***
2022-10-12 19:03:27 INFO OGG-03541 Oracle Environment Variables:
TNS_ADMIN = /oracle/19c/network/admin
LD_LIBRARY_PATH = :/oracle/19c/lib.
defsfile /opt/ogg/dirdef/source.def,purge
table test.*;
Expanding wildcard table specification test.*:
Retrieving definition for TEST.TABLEONE.
Definitions generated for 1 table in /opt/ogg/dirdef/source.def.
目标端配置：
echo "PATH=$PATH:/usr/local/mysql/bin " >> /etc/profile
创建目标端的ogg
[root@localhost mysql]# useradd ogg2
[root@localhost mysql]# passwd ogg2
创建对应的目录
[root@localhost mysql]# mkdir /opt/ogg
[root@localhost mysql]# chown -R ogg2 /opt/ogg
配置环境变量
[ogg2@localhost ~]$ vim .bashrc
export OGG_HOME=/opt/ogg
export 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/libs
export PATH=$OGG_HOME:$PATH
创建
mysql> create user 'gguser'@'%' identified by 'gguser';
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on *.* to 'gguser'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> create database ogg;
Query OK, 1 row affected (0.03 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| ogg |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> use ogg
Database changed
mysql> create table tableone(id int,name varchar(20),primary key(id));
Query OK, 0 rows affected (0.01 sec)
[ogg2@localhost ~]$ cd $OGG_HOME
[ogg2@localhost ogg]$ ggsci
Oracle GoldenGate Command Interpreter for MySQL
Version 21.3.0.0.0 OGGCORE_21.3.0.0.0_PLATFORMS_210728.1047
Oracle Linux 7, x64, 64bit (optimized), MySQL on Jul 28 2021 18:17:46
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2021, Oracle and/or its affiliates. All rights reserved.
GGSCI (localhost.localdomain) 1> create subdirs
Creating subdirectories under current directory /opt/ogg
Parameter file /opt/ogg/dirprm: created.
Report file /opt/ogg/dirrpt: created.
Checkpoint file /opt/ogg/dirchk: created.
Process status files /opt/ogg/dirpcs: created.
SQL script files /opt/ogg/dirsql: created.
Database definitions files /opt/ogg/dirdef: created.
Extract data files /opt/ogg/dirdat: created.
Temporary files /opt/ogg/dirtmp: created.
Credential store files /opt/ogg/dircrd: created.
Master encryption key wallet files /opt/ogg/dirwlt: created.
Dump files /opt/ogg/dirdmp: created.
GGSCI (localhost.localdomain) 2> edit param mgr
PORT 7810
GGSCI (localhost.localdomain) 5> edit param ./GLOBALS
CHECKPOINTTABLE gguser.checkpoint
回到oracle端将文件传过来：
[ogg@node ogg]$ scp /opt/ogg/dirdef/source.def ogg2@192.168.176.127:/opt/ogg/dirdef
The authenticity of host '192.168.176.127 (192.168.176.127)' can't be established.
ECDSA key fingerprint is SHA256:IM3MEmO2nOwZ2mbzbTOq6oU0BQLfy6yVrb0Kqo+xScw.
ECDSA key fingerprint is MD5:29:f0:48:4b:04:6d:8d:60:89:75:7c:c6:22:56:bd:f2.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.176.127' (ECDSA) to the list of known hosts.
ogg2@192.168.176.127's password:
source.def 100% 1185 62.7KB/s 00:00
进入ggsci
GGSCI (localhost.localdomain) 5> dblogin sourcedb ogg@192.168.176.127:3306 userid gguser password gguser
Successfully logged into database.
GGSCI (localhost.localdomain DBLOGIN as gguser) 7> edit param ./GLOBALS
CHECKPOINTTABLE ogg.checkpoint
GGSCI (localhost.localdomain DBLOGIN as gguser) 9> add checkpointtable ogg.ggs_checkpoint
Successfully created checkpoint table ogg.ggs_checkpoint.
GGSCI (localhost.localdomain DBLOGIN as gguser) 12> edit params r_ext1
replicat r_ext1
targetdb ogg@192.168.176.127:3306,userid gguser,password gguser
sourcedefs /home/oggmysql/dirdef/source.def
HANDLECOLLISIONS
MAP test.tableone,target ogg.tableone;
GGSCI (localhost.localdomain DBLOGIN as gguser) 21> add replicat r_ext1,exttrail /home/oggmysql/dirdat/re,checkpointtable ogg.ggs_checkpoint
REPLICAT added.
因为源端需要修改配置文件，指定版本 format 12.3
GGSCI (node) 17> alter extract ext1, etrollover
2022-10-20 15:53:05 INFO OGG-01520 Rollover performed. For each affected output trail of Version 10 or higher format, after starting the source extract, issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file; it will not happen automatically.
Extract altered.
GGSCI (node) 18> alter extract pus1, etrollover
2022-10-20 15:53:41 INFO OGG-01520 Rollover performed. For each affected output trail of Version 10 or higher format, after starting the source extract, issue ALTER EXTSEQNO for that trail's reader (either pump EXTRACT or REPLICAT) to move the reader's scan to the new trail file; it will not happen automatically.
Extract altered.
GGSCI (node) 2> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:08
EXTRACT RUNNING PUS1 00:00:00 00:00:06
但是目标端的进程起不来：
GGSCI (localhost.localdomain DBLOGIN as gguser) 64> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED R_EXT1 00:00:00 16:11:08
R_EXT1进程起不来，发现日志中报错如下：
2022-10-21T11:57:02.046+0800 INFO OGG-00987 Oracle GoldenGate Command Interpreter for MySQL: GGSCI command (ogg1): start R_EXT1.
2022-10-21T11:57:02.134+0800 INFO OGG-00963 Oracle GoldenGate Manager for MySQL, mgr.prm: Command received from GGSCI on host [::1]:36874 (START REPLICAT R_EXT1 ).
2022-10-21T11:57:02.152+0800 INFO OGG-00975 Oracle GoldenGate Manager for MySQL, mgr.prm: REPLICAT R_EXT1 starting.
2022-10-21T11:57:05.482+0800 INFO OGG-00995 Oracle GoldenGate Delivery for MySQL, r_ext1.prm: REPLICAT R_EXT1 starting.
2022-10-21T11:57:05.482+0800 INFO OGG-03059 Oracle GoldenGate Delivery for MySQL, r_ext1.prm: Operating system character set identified as UTF-8.
2022-10-21T11:57:05.482+0800 INFO OGG-02695 Oracle GoldenGate Delivery for MySQL, r_ext1.prm: ANSI SQL parameter syntax is used for parameter parsing.
2022-10-21T11:57:09.227+0800 ERROR OGG-02247 Oracle GoldenGate Delivery for MySQL, r_ext1.prm: Failed to read SOURCEDEFS file /home/oggmysql/dirdef/source.def: Invalid version found in DEFGEN file: 6.0.
2022-10-21T11:57:09.227+0800 ERROR OGG-01668 Oracle GoldenGate Delivery for MySQL, r_ext1.prm: PROCESS ABENDING.
注：因为当时的defgen的配置的文件中指定的版本是12.3,遇到报错后经过咨询老师和在网上搜索资料，建议是把defgen配置的文件中的FORMAT RELEASE 12.3改为FORMAT RELEASE 12.2。停到源端和目标端的相关进程，重新配置，重新生成source.def，并将新生成的文件传到目标端，再起两端的进程，发现可以成功启动，不再报错。
GGSCI (localhost.localdomain DBLOGIN as gguser) 73> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING R_EXT1 00:00:00 00:00:02
验证是否可以数据同步
源端：
SQL> insert into test.tableone values(77,'aaa');
1 row created.
SQL> commit;
Commit complete.
目标端：
mysql> select * from tableone;
Empty set (0.00 sec)
mysql> select * from tableone;
+----+------+
| id | name |
+----+------+
| 77 | aaa |
+----+------+
1 row in set (0.00 sec)
