利用GoldenGate实现DB2到PostgreSQL（MogDB）数据同步

本站文章除注明转载外，均为本站原创： 转载自love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

本文链接地址: 利用GoldenGate实现DB2到PostgreSQL（MogDB）数据同步

近期由于某项目需要，需要测试一下GoldenGate是否支持DB2到MogDB（openGauss）的数据同步。我们知道GoldenGate是支持PostgreSQL的，经查支持9.2版本。而MogDB的内核也是基于PostgreSQL 9.2.4进化而来。下面直接开始测试步骤：

1、安装DB2 10.5 for Linux

[root@test server_t]# ./db2_install
Requirement not matched for DB2 database "Server" . Version: "10.5.0.10". 
Summary of prerequisites that are not met on the current system: 
DBT3514W  The db2prereqcheck utility failed to find the following 32-bit library file: "/lib/libpam.so*". 
DBT3514W  The db2prereqcheck utility failed to find the following 32-bit library file: "libstdc++.so.6". 
DBI1324W  Support of the db2_install command is deprecated.
Default directory for installation of products - /opt/ibm/db2/V10.5
***********************************************************
Install into default directory (/opt/ibm/db2/V10.5) ? [yes/no] 
yes
Specify one of the following keywords to install DB2 products.
SERVER 
CONSV 
EXP 
CLIENT 
RTCL 
Enter "help" to redisplay product names.
Enter "quit" to exit.
***********************************************************
SERVER
***********************************************************
Do you want to install the DB2 pureScale Feature? [yes/no] 
no
Requirement not matched for DB2 database "Server" . Version: "10.5.0.10". 
Summary of prerequisites that are not met on the current system: 
DBT3514W  The db2prereqcheck utility failed to find the following 32-bit library file: "/lib/libpam.so*". 
DBT3514W  The db2prereqcheck utility failed to find the following 32-bit library file: "libstdc++.so.6". 
DB2 installation is being initialized.
Total number of tasks to be performed: 49 
Total estimated time for all tasks to be performed: 1972 second(s) 
Task #1 start
Description: Checking license agreement acceptance 
Estimated time 1 second(s) 
Task #1 end 
........

[root@test server_t]# ./db2_install

Requirement not matched for DB2 database "Server" . Version: "10.5.0.10".

Summary of prerequisites that are not met on the current system:

DBT3514W The db2prereqcheck utility failed to find the following 32-bit library file: "/lib/libpam.so*".

DBT3514W The db2prereqcheck utility failed to find the following 32-bit library file: "libstdc++.so.6".

DBI1324W Support of the db2_install command is deprecated.

Default directory for installation of products - /opt/ibm/db2/V10.5

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

Install into default directory (/opt/ibm/db2/V10.5) ? [yes/no]

yes

Specify one of the following keywords to install DB2 products.

SERVER

CONSV

EXP

CLIENT

RTCL

Enter "help" to redisplay product names.

Enter "quit" to exit.

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

SERVER

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

Do you want to install the DB2 pureScale Feature? [yes/no]

Requirement not matched for DB2 database "Server" . Version: "10.5.0.10".

Summary of prerequisites that are not met on the current system:

DBT3514W The db2prereqcheck utility failed to find the following 32-bit library file: "/lib/libpam.so*".

DBT3514W The db2prereqcheck utility failed to find the following 32-bit library file: "libstdc++.so.6".

DB2 installation is being initialized.

Total number of tasks to be performed: 49

Total estimated time for all tasks to be performed: 1972 second(s)

Task #1 start

Description: Checking license agreement acceptance

Estimated time 1 second(s)

Task #1 end

........

2、创建dascrt

[root@test instance]# ./dascrt -u dasusr1
DBI1070I  Program dascrt completed successfully.
[root@test instance]#

[root@test instance]# ./dascrt -u dasusr1

DBI1070I Program dascrt completed successfully.

[root@test instance]#

3、初始化实例

[root@test instance]# ./db2icrt -a server -u db2fenc1 db2inst1
DBI1446I  The db2icrt command is running.
DB2 installation is being initialized.
Total number of tasks to be performed: 4 
Total estimated time for all tasks to be performed: 309 second(s) 
Task #1 start
Description: Setting default global profile registry variables 
Estimated time 1 second(s) 
Task #1 end 
Task #2 start
Description: Initializing instance list 
Estimated time 5 second(s) 
Task #2 end 
Task #3 start
Description: Configuring DB2 instances 
Estimated time 300 second(s) 
Task #3 end 
Task #4 start
Description: Updating global profile registry 
Estimated time 3 second(s) 
Task #4 end 
The execution completed successfully.
For more information see the DB2 installation log at "/tmp/db2icrt.log.63841".
DBI1070I  Program db2icrt completed successfully.

[root@test instance]# ./db2icrt -a server -u db2fenc1 db2inst1

DBI1446I The db2icrt command is running.

DB2 installation is being initialized.

Total number of tasks to be performed: 4

Total estimated time for all tasks to be performed: 309 second(s)

Task #1 start

Description: Setting default global profile registry variables

Estimated time 1 second(s)

Task #1 end

Task #2 start

Description: Initializing instance list

Estimated time 5 second(s)

Task #2 end

Task #3 start

Description: Configuring DB2 instances

Estimated time 300 second(s)

Task #3 end

Task #4 start

Description: Updating global profile registry

Estimated time 3 second(s)

Task #4 end

The execution completed successfully.

For more information see the DB2 installation log at "/tmp/db2icrt.log.63841".

DBI1070I Program db2icrt completed successfully.

4、更新配置

[root@test instance]# su - db2inst1
[db2inst1@test ~]$ db2 update dbm cfg using SVCENAME db2c_db2inst1
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed 
successfully.
[db2inst1@test ~]$ db2set DB2COMM=TCPIP
[db2inst1@test ~]$

[root@test instance]# su - db2inst1

[db2inst1@test ~]$ db2 update dbm cfg using SVCENAME db2c_db2inst1

DB20000I The UPDATE DATABASE MANAGER CONFIGURATION command completed

successfully.

[db2inst1@test ~]$ db2set DB2COMM=TCPIP

[db2inst1@test ~]$

5、启动数据库实例

[db2inst1@test ~]$ db2start
SQL8007W  There are "90" day(s) left in the evaluation period for the product 
"DB2 Advanced Enterprise Server Edition". For evaluation license terms and 
conditions, refer to the License Agreement document located in the license 
directory in the installation path of this product. If you have licensed this 
product, ensure the license key is properly registered. You can register the 
license by using the db2licm command line utility. The license key can be 
obtained from your licensed product CD.
11/29/2021 18:16:14     0   0   SQL5043N  Support for one or more communications protocols specified in the DB2COMM environment variable failed to start successfully. However, core database manager functionality started successfully.
SQL1063N  DB2START processing was successful.
[db2inst1@test ~]$

[db2inst1@test ~]$ db2start

SQL8007W There are "90" day(s) left in the evaluation period for the product

"DB2 Advanced Enterprise Server Edition". For evaluation license terms and

conditions, refer to the License Agreement document located in the license

directory in the installation path of this product. If you have licensed this

product, ensure the license key is properly registered. You can register the

license by using the db2licm command line utility. The license key can be

obtained from your licensed product CD.

11/29/2021 18:16:14 0 0 SQL5043N Support for one or more communications protocols specified in the DB2COMM environment variable failed to start successfully. However, core database manager functionality started successfully.

SQL1063N DB2START processing was successful.

[db2inst1@test ~]$

6、创建测试数据库enmotech并配置归档

[db2inst1@test ~]$ db2 create db enmotech
DB20000I  The CREATE DATABASE command completed successfully.
[db2inst1@test ~]$ 
[db2inst1@test ~]$ 
[db2inst1@test ~]$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.5.10
You can issue database manager commands and SQL statements from the command 
prompt. For example:
db2 => connect to sample
db2 => bind sample.bnd
For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
? CATALOG DATABASE for help on the CATALOG DATABASE command
? CATALOG          for help on all of the CATALOG commands.
To exit db2 interactive mode, type QUIT at the command prompt. Outside 
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.
For more detailed help, refer to the Online Reference Manual.
db2 => connect to enmotech
Database Connection Information
Database server        = DB2/LINUXX8664 10.5.10
SQL authorization ID   = DB2INST1
Local database alias   = ENMOTECH
db2 => create schema roger
DB20000I  The SQL command completed successfully.
db2 => create table roger.test1129(a varchar(20),b int)
DB20000I  The SQL command completed successfully.
db2 => 
注意这里还需要配置归档,
[db2inst1@test ~]$ db2 "UPDATE DB CFG FOR ENMOTECH  USING LOGARCHMETH1  'DISK:/opt/ibm/db2/db2arch'"
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@test ~]$
[db2inst1@test ~]$ db2 backup database  enmotech to /opt/ibm/db2/backup
Backup successful. The timestamp for this backup image is : 20211129200022
[db2inst1@test ~]$ du -sm /opt/ibm/db2/backup/
161     /opt/ibm/db2/backup/
[db2inst1@test ~]$ db2 get db cfg for enmotech |grep arch
Varchar2 compatibility                                  = OFF
First log archive method                 (LOGARCHMETH1) = DISK:/opt/ibm/db2/db2arch/
Archive compression for logarchmeth1    (LOGARCHCOMPR1) = OFF
Options for logarchmeth1                  (LOGARCHOPT1) = 
Second log archive method                (LOGARCHMETH2) = OFF
Archive compression for logarchmeth2    (LOGARCHCOMPR2) = OFF
Options for logarchmeth2                  (LOGARCHOPT2) = 
Failover log archive path                (FAILARCHPATH) = 
Number of log archive retries on error   (NUMARCHRETRY) = 5
Log archive retry Delay (secs)         (ARCHRETRYDELAY) = 20
[db2inst1@test ~]$ du -sm /opt/ibm/db2/backup/           
161     /opt/ibm/db2/backup/
[db2inst1@test ~]$
[db2inst1@test ~]$ db2 "UPDATE DB CFG FOR ENMOTECH  USING LOGARCHMETH1  'DISK:/opt/ibm/db2/db2arch'"
DB20000I  The UPDATE DATABASE CONFIGURATION command completed successfully.
[db2inst1@test ~]$ db2stop
11/29/2021 19:49:48     0   0   SQL1025N  The database manager was not stopped because databases are still active.
SQL1025N  The database manager was not stopped because databases are still active.
[db2inst1@test ~]$ db2start
11/29/2021 19:49:51     0   0   SQL1026N  The database manager is already active.
SQL1026N  The database manager is already active.
[db2inst1@test ~]$

[db2inst1@test ~]$ db2 create db enmotech

DB20000I The CREATE DATABASE command completed successfully.

[db2inst1@test ~]$

[db2inst1@test ~]$ db2

Command Line Processor for DB2 Client 10.5.10

You can issue database manager commands and SQL statements from the command

prompt. For example:

db2 => connect to sample

db2 => bind sample.bnd

For general help, type: ?.

For command help, type: ? command, where command can be

the first few keywords of a database manager command. For example:

? CATALOG DATABASE for help on the CATALOG DATABASE command

? CATALOG for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside

interactive mode, all commands must be prefixed with 'db2'.

To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => connect to enmotech

Database Connection Information

Database server = DB2/LINUXX8664 10.5.10

SQL authorization ID = DB2INST1

Local database alias = ENMOTECH

db2 => create schema roger

DB20000I The SQL command completed successfully.

db2 => create table roger.test1129(a varchar(20),b int)

DB20000I The SQL command completed successfully.

db2 =>

注意这里还需要配置归档,

[db2inst1@test ~]$ db2 "UPDATE DB CFG FOR ENMOTECH USING LOGARCHMETH1 'DISK:/opt/ibm/db2/db2arch'"

DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.

[db2inst1@test ~]$

[db2inst1@test ~]$ db2 backup database enmotech to /opt/ibm/db2/backup

Backup successful. The timestamp for this backup image is : 20211129200022

[db2inst1@test ~]$ du -sm /opt/ibm/db2/backup/

161 /opt/ibm/db2/backup/

[db2inst1@test ~]$ db2 get db cfg for enmotech |grep arch

Varchar2 compatibility = OFF

First log archive method (LOGARCHMETH1) = DISK:/opt/ibm/db2/db2arch/

Archive compression for logarchmeth1 (LOGARCHCOMPR1) = OFF

Options for logarchmeth1 (LOGARCHOPT1) =

Second log archive method (LOGARCHMETH2) = OFF

Archive compression for logarchmeth2 (LOGARCHCOMPR2) = OFF

Options for logarchmeth2 (LOGARCHOPT2) =

Failover log archive path (FAILARCHPATH) =

Number of log archive retries on error (NUMARCHRETRY) = 5

Log archive retry Delay (secs) (ARCHRETRYDELAY) = 20

[db2inst1@test ~]$ du -sm /opt/ibm/db2/backup/

161 /opt/ibm/db2/backup/

[db2inst1@test ~]$

[db2inst1@test ~]$ db2 "UPDATE DB CFG FOR ENMOTECH USING LOGARCHMETH1 'DISK:/opt/ibm/db2/db2arch'"

DB20000I The UPDATE DATABASE CONFIGURATION command completed successfully.

[db2inst1@test ~]$ db2stop

11/29/2021 19:49:48 0 0 SQL1025N The database manager was not stopped because databases are still active.

SQL1025N The database manager was not stopped because databases are still active.

[db2inst1@test ~]$ db2start

11/29/2021 19:49:51 0 0 SQL1026N The database manager is already active.

SQL1026N The database manager is already active.

[db2inst1@test ~]$

配置归档后做一次一次数据库全备份生效参数即可。

7、源端部署GoldenGate（软件安装步骤略，解压即可）

[db2inst1@test ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for DB2
Version 12.1.2.0.1 17597485 OGGCORE_12.1.2.0.T2_PLATFORMS_140316.1644
Linux, x64, 64bit (optimized), DB2 10.5 on Mar 16 2014 22:01:44
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
GGSCI (test) 1> create subdirs
Creating subdirectories under current directory /opt/ogg
Parameter files                /opt/ogg/dirprm: already exists
Report files                   /opt/ogg/dirrpt: created
Checkpoint files               /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
Masterkey wallet files         /opt/ogg/dirwlt: created
Dump files                     /opt/ogg/dirdmp: created
GGSCI (test) 2> 
GGSCI (test) 10> dblogin sourcedb enmotech userid db2inst1,password db2inst1
2021-11-29 19:00:32  INFO    OGG-03036  Database character set identified as UTF-8. Locale: en_US.
2021-11-29 19:00:32  INFO    OGG-03037  Session character set identified as UTF-8.
Successfully logged into database.
GGSCI (test) 11> 
GGSCI (test) 5>  add extract inext,sourceistable
EXTRACT added.
GGSCI (test) 12> edit param defgen
GGSCI (test) 13> view param defgen 
DEFSFILE ./dirdef/source.def,PURGE
SOURCEDB enmotech,USERID db2inst1,PASSWORD db2inst1
TABLE roger.test1129;
GGSCI (test) 14> 
[db2inst1@test ogg]$ ./defgen paramfile dirprm/defgen.prm
***********************************************************************
Oracle GoldenGate Table Definition Generator for DB2
Version 12.1.2.0.1 17597485 OGGCORE_12.1.2.0.T2_PLATFORMS_140316.1644
Linux, x64, 64bit (optimized), DB2 10.5 on Mar 16 2014 22:07:54
Copyright (C) 1995, 2014, Oracle and/or its affiliates. All rights reserved.
Starting at 2021-11-29 19:06:04
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Wed May 19 15:30:27 PDT 2021, Release 4.18.0-305.el8.x86_64
Node: test
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: 78007
***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
DEFSFILE ./dirdef/source.def,PURGE
SOURCEDB enmotech,USERID db2inst1,PASSWORD ********
2021-11-29 19:06:04  INFO    OGG-03036  Database character set identified as UTF-8. Locale: en_US.
2021-11-29 19:06:04  INFO    OGG-03037  Session character set identified as UTF-8.
TABLE roger.test1129;
Retrieving definition for ROGER.TEST1130.
Definitions generated for 1 table in ./dirdef/source.def.
[db2inst1@test ogg]$ scp  ./dirdef/source.def omm@192.168.108.8:/opt/ogg/dirdef/
The authenticity of host '192.168.108.8 (192.168.108.8)' can't be established.
ECDSA key fingerprint is SHA256:PdU+bDMLWsIhyh2W69xWLWEekPuFMtuWrKa9BUa3EH8.
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '192.168.108.8' (ECDSA) to the list of known hosts.
omm@192.168.108.8's password: 
source.def                                                                                                                                                                       100% 1085   861.4KB/s   00:00    
[db2inst1@test ogg]$ 
GGSCI (test) 34> add trandata roger.*
Logging of supplemental log data (include longvar) is enabled for table ROGER.TEST1129
GGSCI (test) 35> view param extdb2
EXTRACT extdb2
dboptions NOCATALOGCONNECT
SOURCEDB db2test, USERID db2inst1,PASSWORD db2inst1
EXTTRAIL /opt/ogg/nd
COMPRESSUPDATES
GETTRUNCATES
WILDCARDRESOLVE DYNAMIC
TABLE roger.*;
GGSCI (test) 36> ADD EXTRACT extdb2,TRANLOG,BEGIN NOW
EXTRACT added.
GGSCI (test) 37> 
GGSCI (test) 40> add extract pudb2,exttrailsource /opt/ogg/nd
EXTRACT added.
GGSCI (test) 41> add RMTTRAIL /opt/ogg/dirdat/nd,EXTRACT pudb2,megabytes 10
RMTTRAIL added.
GGSCI (test) 42> view param pudb2
EXTRACT pudb2
RMTHOST 192.168.108.8, MGRPORT 7809, compress
RMTTRAIL /opt/ogg/dirdat/nd
SOURCEDEFS /opt/ogg/dirdef/source.def
wildcardresolve dynamic
TABLE roger.*;
GGSCI (test) 43>

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

[db2inst1@test ogg]$ ./ggsci

Oracle GoldenGate Command Interpreter for DB2

Version 12.1.2.0.1 17597485 OGGCORE_12.1.2.0.T2_PLATFORMS_140316.1644

Linux, x64, 64bit (optimized), DB2 10.5 on Mar 16 2014 22:01:44

Operating system character set identified as UTF-8.

GGSCI (test) 1> create subdirs

Creating subdirectories under current directory /opt/ogg

Parameter files /opt/ogg/dirprm: already exists

Report files /opt/ogg/dirrpt: created

Checkpoint files /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

Masterkey wallet files /opt/ogg/dirwlt: created

Dump files /opt/ogg/dirdmp: created

GGSCI (test) 2>

GGSCI (test) 10> dblogin sourcedb enmotech userid db2inst1,password db2inst1

2021-11-29 19:00:32 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.

2021-11-29 19:00:32 INFO OGG-03037 Session character set identified as UTF-8.

Successfully logged into database.

GGSCI (test) 11>

GGSCI (test) 5> add extract inext,sourceistable

EXTRACT added.

GGSCI (test) 12> edit param defgen

GGSCI (test) 13> view param defgen

DEFSFILE ./dirdef/source.def,PURGE

SOURCEDB enmotech,USERID db2inst1,PASSWORD db2inst1

TABLE roger.test1129;

GGSCI (test) 14>

[db2inst1@test ogg]$ ./defgen paramfile dirprm/defgen.prm

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

Oracle GoldenGate Table Definition Generator for DB2

Version 12.1.2.0.1 17597485 OGGCORE_12.1.2.0.T2_PLATFORMS_140316.1644

Linux, x64, 64bit (optimized), DB2 10.5 on Mar 16 2014 22:07:54

Starting at 2021-11-29 19:06:04

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

Operating System Version:

Linux

Version #1 SMP Wed May 19 15:30:27 PDT 2021, Release 4.18.0-305.el8.x86_64

Node: test

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: 78007

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

** Running with the following parameters **

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

DEFSFILE ./dirdef/source.def,PURGE

SOURCEDB enmotech,USERID db2inst1,PASSWORD ********

2021-11-29 19:06:04 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.

2021-11-29 19:06:04 INFO OGG-03037 Session character set identified as UTF-8.

TABLE roger.test1129;

Retrieving definition for ROGER.TEST1130.

Definitions generated for 1 table in ./dirdef/source.def.

[db2inst1@test ogg]$ scp ./dirdef/source.def omm@192.168.108.8:/opt/ogg/dirdef/

The authenticity of host '192.168.108.8 (192.168.108.8)' can't be established.

ECDSA key fingerprint is SHA256:PdU+bDMLWsIhyh2W69xWLWEekPuFMtuWrKa9BUa3EH8.

Are you sure you want to continue connecting (yes/no/[fingerprint])? yes

Warning: Permanently added '192.168.108.8' (ECDSA) to the list of known hosts.

omm@192.168.108.8's password:

source.def 100% 1085 861.4KB/s 00:00

[db2inst1@test ogg]$

GGSCI (test) 34> add trandata roger.*

Logging of supplemental log data (include longvar) is enabled for table ROGER.TEST1129

GGSCI (test) 35> view param extdb2

EXTRACT extdb2

dboptions NOCATALOGCONNECT

SOURCEDB db2test, USERID db2inst1,PASSWORD db2inst1

EXTTRAIL /opt/ogg/nd

COMPRESSUPDATES

GETTRUNCATES

WILDCARDRESOLVE DYNAMIC

TABLE roger.*;

GGSCI (test) 36> ADD EXTRACT extdb2,TRANLOG,BEGIN NOW

EXTRACT added.

GGSCI (test) 37>

GGSCI (test) 40> add extract pudb2,exttrailsource /opt/ogg/nd

EXTRACT added.

GGSCI (test) 41> add RMTTRAIL /opt/ogg/dirdat/nd,EXTRACT pudb2,megabytes 10

RMTTRAIL added.

GGSCI (test) 42> view param pudb2

EXTRACT pudb2

RMTHOST 192.168.108.8, MGRPORT 7809, compress

RMTTRAIL /opt/ogg/dirdat/nd

SOURCEDEFS /opt/ogg/dirdef/source.def

wildcardresolve dynamic

TABLE roger.*;

GGSCI (test) 43>

8、DB2端创建测试表并插入测试数据

db2 => create table roger.test1130(age int)
DB20000I The SQL command completed successfully.
db2 =>
db2 => quit
DB20000I The QUIT command completed successfully.
[db2inst1@test ~]$
[db2inst1@test ~]$
[db2inst1@test ~]$ db2 “insert into roger.test1130 values(10)”
DB20000I The SQL command completed successfully.
[db2inst1@test ~]$ db2 “insert into roger.test1130 values(11)”
DB20000I The SQL command completed successfully.
[db2inst1@test ~]$ db2 “insert into roger.test1130 values(12)”
DB20000I The SQL command completed successfully.
[db2inst1@test ~]$

db2 => create table roger.test1130(age int)

DB20000I The SQL command completed successfully.

db2 =>

db2 => quit

DB20000I The QUIT command completed successfully.

[db2inst1@test ~]$

[db2inst1@test ~]$ db2 “insert into roger.test1130 values(10)”

DB20000I The SQL command completed successfully.

[db2inst1@test ~]$ db2 “insert into roger.test1130 values(11)”

DB20000I The SQL command completed successfully.

[db2inst1@test ~]$ db2 “insert into roger.test1130 values(12)”

DB20000I The SQL command completed successfully.

[db2inst1@test ~]$

9、启动源端抽取进程和投递进程

GGSCI (test) 43> info extract extdb2
EXTRACT EXTDB2 Initialized 2021-11-29 19:22 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:04:02 ago)
Log Read Checkpoint DB2 Transaction Log
2021-11-29 19:22:01.174830 LRI 0,-1
GGSCI (test) 45> info rmttrail *
Extract Trail: /opt/ogg/nd
Extract: EXTDB2
Seqno: 0
RBA: 0
File Size: 10M
Extract Trail: /opt/ogg/dirdat/nd
Extract: PUDB2
Seqno: 0
RBA: 0
File Size: 10M
GGSCI (test) 46>
GGSCI (test) 16> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTDB2 00:00:00 00:00:00
EXTRACT RUNNING PUDB2 00:00:00 00:00:08
GGSCI (test) 17>

GGSCI (test) 43> info extract extdb2

EXTRACT EXTDB2 Initialized 2021-11-29 19:22 Status STOPPED

Checkpoint Lag 00:00:00 (updated 00:04:02 ago)

Log Read Checkpoint DB2 Transaction Log

2021-11-29 19:22:01.174830 LRI 0,-1

GGSCI (test) 45> info rmttrail *

Extract Trail: /opt/ogg/nd

Extract: EXTDB2

Seqno: 0

RBA: 0

File Size: 10M

Extract Trail: /opt/ogg/dirdat/nd

Extract: PUDB2

Seqno: 0

RBA: 0

File Size: 10M

GGSCI (test) 46>

GGSCI (test) 16> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXTDB2 00:00:00 00:00:00

EXTRACT RUNNING PUDB2 00:00:00 00:00:08

GGSCI (test) 17>

源端DB2端也需要进行odbc相关编译和配置，步骤省略，参考下面目标端的配置步骤。

这里为了进行验证，我在目标端分别部署了PostgreSQL 9.2和MogDB2.0。

如下是目标端端的相关配置：

1、首先配置ODBC

[omm@mogdb ODBCDataSources]$ cat odbcinst.ini
Example driver definitions
Driver from the postgresql-odbc package
Setup from the unixODBC package
[mogdb]
Description = ODBC for openGauss
Driver = /usr/local/lib/psqlodbcw.so
Driver64 = /usr/local/lib/psqlodbcw.so
Setup = /usr/local/lib/libodbc.so
Setup64 = /usr/local/lib/libodbc.so
#FileUsage = 1
[omm@mogdb ODBCDataSources]$ cat odbc.ini
[ogg_odbc]
Driver=mogdb
Servername=192.168.108.8
Database=enmotech
Port=26000
Username=roger
Password=Roger888
Sslmode=allow
[omm@mogdb ODBCDataSources]$ pwd
/usr/local/etc/ODBCDataSources
[omm@mogdb ODBCDataSources]$

[omm@mogdb ODBCDataSources]$ cat odbcinst.ini

Example driver definitions

Driver from the postgresql-odbc package

Setup from the unixODBC package

[mogdb]

Description = ODBC for openGauss

Driver = /usr/local/lib/psqlodbcw.so

Driver64 = /usr/local/lib/psqlodbcw.so

Setup = /usr/local/lib/libodbc.so

Setup64 = /usr/local/lib/libodbc.so

#FileUsage = 1

[omm@mogdb ODBCDataSources]$ cat odbc.ini

[ogg_odbc]

Driver=mogdb

Servername=192.168.108.8

Database=enmotech

Port=26000

Username=roger

Password=Roger888

Sslmode=allow

[omm@mogdb ODBCDataSources]$ pwd

/usr/local/etc/ODBCDataSources

[omm@mogdb ODBCDataSources]$

最后修改用户.bash_profile增加如下环境变量：

export LD_LIBRARY_PATH=/usr/local/lib/:/data/mogdb/lib:/opt/ogg/lib:$LD_LIBRARY_PATH
#export LD_LIBRARY_PATH=/usr/local/lib/:/opt/opengauss/lib:/opt/ogg/lib:$LD_LIBRARY_PATH
#export ODBCSYSINI=/usr/local/etc
export ODBCSYSINI=/usr/local/etc/ODBCDataSources
export ODBCINI=/usr/local/etc/ODBCDataSources/odbc.ini

export LD_LIBRARY_PATH=/usr/local/lib/:/data/mogdb/lib:/opt/ogg/lib:$LD_LIBRARY_PATH

#export LD_LIBRARY_PATH=/usr/local/lib/:/opt/opengauss/lib:/opt/ogg/lib:$LD_LIBRARY_PATH

#export ODBCSYSINI=/usr/local/etc

export ODBCSYSINI=/usr/local/etc/ODBCDataSources

export ODBCINI=/usr/local/etc/ODBCDataSources/odbc.ini

需要注意的是，这里需要先下载unixODBC-2.3.7pre.tar.gz然后进行编译，步骤略（Opengauss2.0和MogDB2.0 不支持太老的odbc版本）。

odbc配置成功之后，可以通过如下方式来进行验证是否正常；如果提示说明配置正常。

-bash-4.2$ isql ogg_odbc
±--------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
±--------------------------------------+
SQL>

-bash-4.2$ isql ogg_odbc

±--------------------------------------+

| Connected! |

| |

| sql-statement |

| help [tablename] |

| quit |

| |

±--------------------------------------+

SQL>

2、部署目标端goldengate（对于PostgreSQL9.2版本，只能用ogg 11.2版本，其他版本不支持）

[omm@mogdb ~]$ cd /opt/ogg
[omm@mogdb ogg]$ ./ggsci
./ggsci: /usr/local/lib/libodbc.so: no version information available (required by ./ggsci)
Oracle GoldenGate Command Interpreter
Version 11.2.1.0.2 OGGCORE_11.2.1.0.2T3_PLATFORMS_120724.2205
Linux, x64, 64bit (optimized), PostgreSQL on Jul 25 2012 00:32:24
Copyright © 1995, 2012, Oracle and/or its affiliates. All rights reserved.
GGSCI (mogdb) 1> info mgr
Manager is running (IP port mogdb.7809).
GGSCI (mogdb) 2> dblogin sourcedb ogg_odbc userid roger
Password:
2021-11-29 01:34:43 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.
2021-11-29 01:34:43 INFO OGG-03037 Session character set identified as UTF-8.
Successfully logged into database.
GGSCI (mogdb) 3>
GGSCI (mogdb) 4> add replicat inload, specialrun
REPLICAT added.
GGSCI (mogdb) 5>
GGSCI (mogdb) 20> view param inload
REPLICAT INLOAD
USERID ogg,PASSWORD ogg
DISCARDFILE ./dirrpt/rini.dsc, PURGE
SOURCEDEFS /opt/ogg/dirdef/source.def
MAP roger.test1129, TARGET roger.db2mogdb;
GGSCI (mogdb) 14> add replicat repmog,exttrail /opt/ogg/dirdat/nd
ERROR: No checkpoint table specified for ADD REPLICAT.
GGSCI (mogdb) 15> edit params ./GLOBALS
GGSCI (mogdb) 16> view param ./GLOBALS
CheckPointTable roger.ggschkpt
GGSCI (mogdb) 18> add checkpointtable roger.ggschkpt
2021-11-29 03:32:00 WARNING OGG-00552 Database operation failed: SQLExecDirect error: CREATE TABLE roger.ggschkpt ( group_name VARCHAR(8) NOT NULL, group_key INT8 NOT NULL, seqno INT4, rba INT8 NOT NULL, audit_ts VARCHAR(29), create_ts TIMESTAMP NOT NULL, last_update_ts TIMESTAMP NOT NULL, current_dir VARCHAR(255) NOT NULL, PRIMARY KEY (group_name, group_key)). ODBC error: SQLSTATE S0001 native database error 16. Error creating the table;
could not send data to server: Broken pipe.
ERROR: Creating checkpoint table ‘roger.ggschkpt’.
Database error 16 (Error creating the table;
could not send data to server: Broken pipe
).
GGSCI (mogdb) 19> add replicat repmog,exttrail /opt/ogg/dirdat/nd,NODBCHECKPOINT
REPLICAT added.
GGSCI (mogdb) 20>
GGSCI (mogdb) 21> view param repmog
REPLICAT repmog
USERID roger, PASSWORD Roger888
DISCARDFILE /opt/ogg/dirrpt/repnd.dsc, append, MEGABYTES 10
numfiles 1000
SOURCEDEFS /opt/ogg/dirdef/source.def
dynamicresolution
handlecollisions
MAP roger.test1129, TARGET public.db2pg9;
GGSCI (mogdb) 6> info repmog
REPLICAT REPMOG Last Started 2021-12-01 11:55 Status ABENDED
Checkpoint Lag 00:00:00 (updated 14:54:22 ago)
Log Read Checkpoint File /opt/ogg/dirdat/nd000007
First Record RBA 969
GGSCI (mogdb) 9> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT ABENDED REPMOG 00:00:00 14:54:42
GGSCI (mogdb) 10> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y
Sending STOP request to MANAGER …
Request processed.
Manager stopped.
GGSCI (mogdb) 13> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
REPLICAT ABENDED REPMOG 00:00:00 14:55:31
GGSCI (mogdb) 14> start mgr
Manager started.
GGSCI (mogdb) 16> start repmog
Sending START request to MANAGER …
REPLICAT REPMOG starting
GGSCI (mogdb) 17> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPMOG 00:00:00 00:00:01
GGSCI (mogdb) 18> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPMOG 00:00:00 00:00:02
GGSCI (mogdb) 19> stats repmog
Sending STATS request to REPLICAT REPMOG …
Start of Statistics at 2021-12-01 12:33:34.
Replicating from ROGER.TEST1130 to public.db2pg9:
*** Total statistics since 2021-12-01 12:33:28 ***
Total inserts 3.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 3.00
*** Daily statistics since 2021-12-01 12:33:28 ***
Total inserts 3.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 3.00
*** Hourly statistics since 2021-12-01 12:33:28 ***
Total inserts 3.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 3.00
*** Latest statistics since 2021-12-01 12:33:28 ***
Total inserts 3.00
Total updates 0.00
Total deletes 0.00
Total discards 0.00
Total operations 3.00
End of Statistics.
-bash-4.2$ psql -d enmotech -p25000 -Utest -h192.168.108.8 -W
Password for user test:
psql (9.2.24)
Type “help” for help.
enmotech=> select * from db2pg9;
age
10
11
12
(3 rows)
enmotech=> select version();
version
PostgreSQL 9.2.24 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

[omm@mogdb ~]$ cd /opt/ogg

[omm@mogdb ogg]$ ./ggsci

./ggsci: /usr/local/lib/libodbc.so: no version information available (required by ./ggsci)

Oracle GoldenGate Command Interpreter

Version 11.2.1.0.2 OGGCORE_11.2.1.0.2T3_PLATFORMS_120724.2205

Linux, x64, 64bit (optimized), PostgreSQL on Jul 25 2012 00:32:24

GGSCI (mogdb) 1> info mgr

Manager is running (IP port mogdb.7809).

GGSCI (mogdb) 2> dblogin sourcedb ogg_odbc userid roger

Password:

2021-11-29 01:34:43 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.

2021-11-29 01:34:43 INFO OGG-03037 Session character set identified as UTF-8.

Successfully logged into database.

GGSCI (mogdb) 3>

GGSCI (mogdb) 4> add replicat inload, specialrun

REPLICAT added.

GGSCI (mogdb) 5>

GGSCI (mogdb) 20> view param inload

REPLICAT INLOAD

USERID ogg,PASSWORD ogg

DISCARDFILE ./dirrpt/rini.dsc, PURGE

SOURCEDEFS /opt/ogg/dirdef/source.def

MAP roger.test1129, TARGET roger.db2mogdb;

GGSCI (mogdb) 14> add replicat repmog,exttrail /opt/ogg/dirdat/nd

ERROR: No checkpoint table specified for ADD REPLICAT.

GGSCI (mogdb) 15> edit params ./GLOBALS

GGSCI (mogdb) 16> view param ./GLOBALS

CheckPointTable roger.ggschkpt

GGSCI (mogdb) 18> add checkpointtable roger.ggschkpt

2021-11-29 03:32:00 WARNING OGG-00552 Database operation failed: SQLExecDirect error: CREATE TABLE roger.ggschkpt ( group_name VARCHAR(8) NOT NULL, group_key INT8 NOT NULL, seqno INT4, rba INT8 NOT NULL, audit_ts VARCHAR(29), create_ts TIMESTAMP NOT NULL, last_update_ts TIMESTAMP NOT NULL, current_dir VARCHAR(255) NOT NULL, PRIMARY KEY (group_name, group_key)). ODBC error: SQLSTATE S0001 native database error 16. Error creating the table;

could not send data to server: Broken pipe.

ERROR: Creating checkpoint table ‘roger.ggschkpt’.

Database error 16 (Error creating the table;

could not send data to server: Broken pipe

GGSCI (mogdb) 19> add replicat repmog,exttrail /opt/ogg/dirdat/nd,NODBCHECKPOINT

REPLICAT added.

GGSCI (mogdb) 20>

GGSCI (mogdb) 21> view param repmog

REPLICAT repmog

USERID roger, PASSWORD Roger888

DISCARDFILE /opt/ogg/dirrpt/repnd.dsc, append, MEGABYTES 10

numfiles 1000

SOURCEDEFS /opt/ogg/dirdef/source.def

dynamicresolution

handlecollisions

MAP roger.test1129, TARGET public.db2pg9;

GGSCI (mogdb) 6> info repmog

REPLICAT REPMOG Last Started 2021-12-01 11:55 Status ABENDED

Checkpoint Lag 00:00:00 (updated 14:54:22 ago)

Log Read Checkpoint File /opt/ogg/dirdat/nd000007

First Record RBA 969

GGSCI (mogdb) 9> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT ABENDED REPMOG 00:00:00 14:54:42

GGSCI (mogdb) 10> stop mgr

Manager process is required by other GGS processes.

Are you sure you want to stop it (y/n)? y

Sending STOP request to MANAGER …

Request processed.

Manager stopped.

GGSCI (mogdb) 13> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER STOPPED

REPLICAT ABENDED REPMOG 00:00:00 14:55:31

GGSCI (mogdb) 14> start mgr

Manager started.

GGSCI (mogdb) 16> start repmog

Sending START request to MANAGER …

REPLICAT REPMOG starting

GGSCI (mogdb) 17> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REPMOG 00:00:00 00:00:01

GGSCI (mogdb) 18> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REPMOG 00:00:00 00:00:02

GGSCI (mogdb) 19> stats repmog

Sending STATS request to REPLICAT REPMOG …

Start of Statistics at 2021-12-01 12:33:34.

Replicating from ROGER.TEST1130 to public.db2pg9:

*** Total statistics since 2021-12-01 12:33:28 ***

Total inserts 3.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

*** Daily statistics since 2021-12-01 12:33:28 ***

Total inserts 3.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

*** Hourly statistics since 2021-12-01 12:33:28 ***

Total inserts 3.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

*** Latest statistics since 2021-12-01 12:33:28 ***

Total inserts 3.00

Total updates 0.00

Total deletes 0.00

Total discards 0.00

Total operations 3.00

End of Statistics.

-bash-4.2$ psql -d enmotech -p25000 -Utest -h192.168.108.8 -W

Password for user test:

psql (9.2.24)

Type “help” for help.

enmotech=> select * from db2pg9;

age

(3 rows)

enmotech=> select version();

version

PostgreSQL 9.2.24 on x86_64-redhat-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit

(1 row)

可见对PostgreSQL 9.2.24版本支持良好，数据正常同步。由于MogDB基于基于openGauss 内核，而openGauss内核是基于PostgreSQL 9.2.4 演进而来。

对于DB2到PostgreSQL的数据同步，可以参考文档：
How To Replicate Data from Oracle to Postgres Using GoldenGate (Doc ID 1544137.1)

那么Goldengate软件能否支持openGauss或者MogDB2.0呢？经过测试发现，暂时不支持，数据复制时会报如下：

2021-11-30 21:44:51 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
REPLICAT repmog
SOURCEDEFS /opt/ogg/dirdef/source.def
SETENV ( PGCLIENTENCODING = “UTF8” )
Set environment variable (PGCLIENTENCODING=UTF8)
SETENV (ODBCINI="//usr/local/etc/ODBCDataSources/odbc.ini" )
Set environment variable (ODBCINI=//usr/local/etc/ODBCDataSources/odbc.ini)
SETENV (NLS_LANG=“AMERICAN_AMERICA.AL32UTF8”)
Set environment variable (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
TARGETDB ogg_odbc, USERID test, PASSWORD *********
2021-11-30 21:44:51 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.
2021-11-30 21:44:51 INFO OGG-03037 Session character set identified as UTF-8.
DISCARDFILE /opt/ogg/dirrpt/diskg.dsc, purge
map roger.test1130, TARGET test.db2mogdb1, COLMAP (age=mage);
2021-11-30 21:44:51 INFO OGG-01815 Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/opt/ogg/dirtmp.
CACHEMGR virtual memory values (may have been adjusted)
CACHESIZE: 2G
CACHEPAGEOUTSIZE (normal): 8M
PROCESS VM AVAIL FROM OS (min): 4G
CACHESIZEMAX (strict force to disk): 3.41G
Database Version:
PostgreSQL
Version 9.2.4
ODBC Version 03.52
Driver Information:
psqlodbcw.so
Version 10.03.0000
ODBC Version 03.51
** Run Time Messages **
Opened trail file /opt/ogg/dirdat/nd000007 at 2021-11-30 21:44:51
MAP resolved (entry roger.test1130):
map “ROGER”.“TEST1130”, TARGET test.db2mogdb1, COLMAP (age=mage);
Source Context :
SourceModule : [ggdb.odbc.util]
SourceID : [/scratch/aime1/adestore/views/aime1_adc4150256/oggcore/OpenSys/src/gglib/ggdbodbc/odbcutil.c]
SourceFunction : [ODBCUTIL_check_err]
SourceLine : [1414]
ThreadBacktrace : [15] elements
: [/opt/ogg/libgglog.so(CMessageContext::AddThreadContext()+0x26) [0x7f74de95e946]]
: [/opt/ogg/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, …)+0x366) [0x7f74de958636]]
: [/opt/ogg/libgglog.so(_MSG_ERR_ODBC_OPERATION_FAILED(CSourceContext*, char const*, char const*, char const*, int, CMessageFactory::MessageDisposition)+0x63) [0x7f74de92eb73]]
: [/opt/ogg/replicat(ODBCUTIL_check_err(short, void*, void*, void*, short, char const*, …)+0x1cd) [0x68b21d]]
: [/opt/ogg/replicat(get_odbc_table_def(file_def*, short, char*)+0x928) [0x67fe88]]
: [/opt/ogg/replicat(add_target_file(ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, unsigned int&, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, int, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&)+0x2f4) [0x535314]]
: [/opt/ogg/replicat(get_map_entry(ggs::gglib::ggunicode::UString const&, int, wc_def*, int)+0x1af9) [0x53a989]]
: [/opt/ogg/replicat(wc_def::resolve_wc_entry(ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, int, unsigned int*, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1>, int)+0x284) [0x6031c4]]
: [/opt/ogg/replicat(WILDCARD_check_table(ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const, int, unsigned int*, int, unsigned int, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1>, int)+0x19f) [0x60381f]]
: [/opt/ogg/replicat(REP_find_source_file_wc(ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, unsigned int, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1>, int)+0x862) [0x536c32]]
: [/opt/ogg/replicat(source_file_lookup(__std_rec_hdr const*, ULibCharSet)+0x423) [0x5415b3]]
: [/opt/ogg/replicat(process_extract_loop()+0x1fe2) [0x544382]]
: [/opt/ogg/replicat(main+0xbc4) [0x561254]]
: [/lib64/libc.so.6(__libc_start_main+0xf5) [0x7f74da7633d5]]
: [/opt/ogg/replicat(__gxx_personality_v0+0x272) [0x48f2da]]
2021-11-30 21:44:51 ERROR OGG-00551 Database operation failed: Retrieving column information for test.db2mogdb1. ODBC error: SQLSTATE S1000 native database error 8. Unrecognized return value from copy_and_co
nvert_field.

2021-11-30 21:44:51 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.

REPLICAT repmog

SOURCEDEFS /opt/ogg/dirdef/source.def

SETENV ( PGCLIENTENCODING = “UTF8” )

Set environment variable (PGCLIENTENCODING=UTF8)

SETENV (ODBCINI="//usr/local/etc/ODBCDataSources/odbc.ini" )

Set environment variable (ODBCINI=//usr/local/etc/ODBCDataSources/odbc.ini)

SETENV (NLS_LANG=“AMERICAN_AMERICA.AL32UTF8”)

Set environment variable (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

TARGETDB ogg_odbc, USERID test, PASSWORD *********

2021-11-30 21:44:51 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.

2021-11-30 21:44:51 INFO OGG-03037 Session character set identified as UTF-8.

DISCARDFILE /opt/ogg/dirrpt/diskg.dsc, purge

map roger.test1130, TARGET test.db2mogdb1, COLMAP (age=mage);

2021-11-30 21:44:51 INFO OGG-01815 Virtual Memory Facilities for: COM

anon alloc: mmap(MAP_ANON) anon free: munmap

file alloc: mmap(MAP_SHARED) file free: munmap

target directories:

/opt/ogg/dirtmp.

CACHEMGR virtual memory values (may have been adjusted)

CACHESIZE: 2G

CACHEPAGEOUTSIZE (normal): 8M

PROCESS VM AVAIL FROM OS (min): 4G

CACHESIZEMAX (strict force to disk): 3.41G

Database Version:

PostgreSQL

Version 9.2.4

ODBC Version 03.52

Driver Information:

psqlodbcw.so

Version 10.03.0000

ODBC Version 03.51

** Run Time Messages **

Opened trail file /opt/ogg/dirdat/nd000007 at 2021-11-30 21:44:51

MAP resolved (entry roger.test1130):

map “ROGER”.“TEST1130”, TARGET test.db2mogdb1, COLMAP (age=mage);

Source Context :

SourceModule : [ggdb.odbc.util]

SourceID : [/scratch/aime1/adestore/views/aime1_adc4150256/oggcore/OpenSys/src/gglib/ggdbodbc/odbcutil.c]

SourceFunction : [ODBCUTIL_check_err]

SourceLine : [1414]

ThreadBacktrace : [15] elements

: [/opt/ogg/libgglog.so(CMessageContext::AddThreadContext()+0x26) [0x7f74de95e946]]

: [/opt/ogg/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, …)+0x366) [0x7f74de958636]]

: [/opt/ogg/libgglog.so(_MSG_ERR_ODBC_OPERATION_FAILED(CSourceContext*, char const*, char const*, char const*, int, CMessageFactory::MessageDisposition)+0x63) [0x7f74de92eb73]]

: [/opt/ogg/replicat(ODBCUTIL_check_err(short, void*, void*, void*, short, char const*, …)+0x1cd) [0x68b21d]]

: [/opt/ogg/replicat(get_odbc_table_def(file_def*, short, char*)+0x928) [0x67fe88]]

: [/opt/ogg/replicat(add_target_file(ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, unsigned int&, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, int, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&)+0x2f4) [0x535314]]

: [/opt/ogg/replicat(get_map_entry(ggs::gglib::ggunicode::UString const&, int, wc_def*, int)+0x1af9) [0x53a989]]

: [/opt/ogg/replicat(wc_def::resolve_wc_entry(ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, int, unsigned int*, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1>, int)+0x284) [0x6031c4]]

: [/opt/ogg/replicat(WILDCARD_check_table(ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const, int, unsigned int*, int, unsigned int, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1>, int)+0x19f) [0x60381f]]

: [/opt/ogg/replicat(REP_find_source_file_wc(ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, unsigned int, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1>, int)+0x862) [0x536c32]]

: [/opt/ogg/replicat(source_file_lookup(__std_rec_hdr const*, ULibCharSet)+0x423) [0x5415b3]]

: [/opt/ogg/replicat(process_extract_loop()+0x1fe2) [0x544382]]

: [/opt/ogg/replicat(main+0xbc4) [0x561254]]

: [/lib64/libc.so.6(__libc_start_main+0xf5) [0x7f74da7633d5]]

: [/opt/ogg/replicat(__gxx_personality_v0+0x272) [0x48f2da]]

2021-11-30 21:44:51 ERROR OGG-00551 Database operation failed: Retrieving column information for test.db2mogdb1. ODBC error: SQLSTATE S1000 native database error 8. Unrecognized return value from copy_and_co

nvert_field.

在进行相关测试的过程中，还遇到了如下几个方面问题：

1）其他问题

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
2021-11-29 20:51:44  INFO    OGG-03035  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
REPLICAT repmog
SOURCEDEFS /opt/ogg/dirdef/source.def
Source Context :
SourceModule            : [er.init]
SourceID                : [/scratch/aime1/adestore/views/aime1_adc4150256/oggcore/OpenSys/src/app/er/init.cpp]
SourceFunction          : [get_infile_params]
SourceLine              : [1567]
ThreadBacktrace         : [7] elements
: [/opt/ogg/libgglog.so(CMessageContext::AddThreadContext()+0x26) [0x7fb5dc3d2946]]
: [/opt/ogg/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x366) [0x7fb5dc3cc636]]
: [/opt/ogg/libgglog.so(_MSG_ERR_STARTUP_PARAMERROR_ERRORTEXT(CSourceContext*, char const*, CMessageFactory::MessageDisposition)+0x42) [0x7fb5dc3a7312]]
: [/opt/ogg/replicat(get_infile_params(time_elt_def*, time_elt_def*, char**)+0x5429) [0x52fd79]]
: [/opt/ogg/replicat(main+0x115) [0x5607a5]]
: [/lib64/libc.so.6(__libc_start_main+0xf5) [0x7fb5d81d73d5]]
: [/opt/ogg/replicat(__gxx_personality_v0+0x272) [0x48f2da]]
2021-11-29 20:51:44  ERROR   OGG-00303  Problem at line 34.  Expecting file, table, or record definition: TimeZone: CST.
2021-11-29 20:51:44  ERROR   OGG-01668  PROCESS ABENDING.
GGSCI (mogdb) 38> 
[omm@mogdb ~]$ gsql -d enmotech -p26000 -Uroger
Password for user roger: 
gsql ((MogDB 2.0.0 build b75b585a) compiled at 2021-05-28 17:20:47 commit 0 last mr  )
Non-SSL connection (SSL connection is recommended when requiring high-security)
Type "help" for help.
enmotech=> select now();
now              
-------------------------------
2021-11-29 04:54:12.407791-08
(1 row)
enmotech=> show time zone; 
TimeZone  
------------
US/Pacific
(1 row)
enmotech=> select * from pg_timezone_names where name like '%hai%';
name      | abbrev | utc_offset | is_dst 
---------------+--------+------------+--------
Asia/Shanghai | CST    | 08:00:00   | f
(1 row)
enmotech=> \q
[omm@mogdb ~]$ cat /data/mogdb/data/db1/postgresql.conf|grep timezone
log_timezone = 'Asia/Shanghai'
#timezone = 'US/Pacific'
timezone = 'Asia/Shanghai'
#timezone_abbreviations = 'Default'     # Select the set of available time zone
# share/timezonesets/.
[omm@mogdb ~]$

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

** Running with the following parameters **

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

2021-11-29 20:51:44 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.

REPLICAT repmog

SOURCEDEFS /opt/ogg/dirdef/source.def

Source Context :

SourceModule : [er.init]

SourceID : [/scratch/aime1/adestore/views/aime1_adc4150256/oggcore/OpenSys/src/app/er/init.cpp]

SourceFunction : [get_infile_params]

SourceLine : [1567]

ThreadBacktrace : [7] elements

: [/opt/ogg/libgglog.so(CMessageContext::AddThreadContext()+0x26) [0x7fb5dc3d2946]]

: [/opt/ogg/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...)+0x366) [0x7fb5dc3cc636]]

: [/opt/ogg/libgglog.so(_MSG_ERR_STARTUP_PARAMERROR_ERRORTEXT(CSourceContext*, char const*, CMessageFactory::MessageDisposition)+0x42) [0x7fb5dc3a7312]]

: [/opt/ogg/replicat(get_infile_params(time_elt_def*, time_elt_def*, char**)+0x5429) [0x52fd79]]

: [/opt/ogg/replicat(main+0x115) [0x5607a5]]

: [/lib64/libc.so.6(__libc_start_main+0xf5) [0x7fb5d81d73d5]]

: [/opt/ogg/replicat(__gxx_personality_v0+0x272) [0x48f2da]]

2021-11-29 20:51:44 ERROR OGG-00303 Problem at line 34. Expecting file, table, or record definition: TimeZone: CST.

2021-11-29 20:51:44 ERROR OGG-01668 PROCESS ABENDING.

GGSCI (mogdb) 38>

[omm@mogdb ~]$ gsql -d enmotech -p26000 -Uroger

Password for user roger:

gsql ((MogDB 2.0.0 build b75b585a) compiled at 2021-05-28 17:20:47 commit 0 last mr )

Non-SSL connection (SSL connection is recommended when requiring high-security)

Type "help" for help.

enmotech=> select now();

now

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

2021-11-29 04:54:12.407791-08

(1 row)

enmotech=> show time zone;

TimeZone

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

US/Pacific

(1 row)

enmotech=> select * from pg_timezone_names where name like '%hai%';

name | abbrev | utc_offset | is_dst

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

Asia/Shanghai | CST | 08:00:00 | f

(1 row)

enmotech=> \q

[omm@mogdb ~]$ cat /data/mogdb/data/db1/postgresql.conf|grep timezone

log_timezone = 'Asia/Shanghai'

#timezone = 'US/Pacific'

timezone = 'Asia/Shanghai'

#timezone_abbreviations = 'Default' # Select the set of available time zone

# share/timezonesets/.

[omm@mogdb ~]$

上述错误是指配def文件中timezone有异常；虽然我在os层面将目标端和源端的timezone都调整了一致，发现仍然报错。这里通过在def配置文件中将Timezone 一行记录删除即可避免该错误。

参考如下mos文档：
OGG v11.2.1 Java Adapter Abend: OGG-00303 Expecting File, Table, Or Record Definition: TimeZone (Doc ID 2040347.1)

This is caused because the sourcedefs file created by the source v12.1.2 defgen has a slightly different format than what v11.2.1 EXTRACT expects.
OGG v12.1.2 REPLICAT has an option to override the default REPLICAT session
timezone. That is what the timezone value in the sourcedefs is used for.
So removal of the timezone value in the sourcedefs will have no impact on your OGG v11.2.1 Adapter target.

This is caused because the sourcedefs file created by the source v12.1.2 defgen has a slightly different format than what v11.2.1 EXTRACT expects.

OGG v12.1.2 REPLICAT has an option to override the default REPLICAT session

timezone. That is what the timezone value in the sourcedefs is used for.

So removal of the timezone value in the sourcedefs will have no impact on your OGG v11.2.1 Adapter target.

[omm@mogdb dirdat]$ cat /opt/ogg/dirdef/source.def
*+- Defgen version 4.0, Encoding UTF-8
*
* Definitions created/modified  2021-11-29 20:39
*
*  Field descriptions for each column entry:
*
*     1    Name
*     2    Data Type
*     3    External Length
*     4    Fetch Offset
*     5    Scale
*     6    Level
*     7    Null
*     8    Bump if Odd
*     9    Internal Length
*    10    Binary Length
*    11    Table Length
*    12    Most Significant DT
*    13    Least Significant DT
*    14    High Precision
*    15    Low Precision
*    16    Elementary Item
*    17    Occurs
*    18    Key Column
*    19    Sub Data Type
*    20    Native Data Type
*    21    Character Set
*
Database type: DB2 UDB
Character set ID: UTF-8
National character set ID: UTF-16
Locale: en_US
Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
TimeZone: CST               +++++++ 删除这行记录 +++++++
*
Definition for table ROGER.TEST1129
Record length: 34
Syskey: 0
Columns: 2
A   64     20        0  0  0 1 0     20     20     20 0 0 0 0 1    0 1   0   27       -1
B  134     23       23  0  0 1 0      8      8      4 0 0 0 0 1    0 1   0   20       -1
End of definition

[omm@mogdb dirdat]$ cat /opt/ogg/dirdef/source.def

*+- Defgen version 4.0, Encoding UTF-8

* Definitions created/modified 2021-11-29 20:39

* Field descriptions for each column entry:

* 1 Name

* 2 Data Type

* 3 External Length

* 4 Fetch Offset

* 5 Scale

* 6 Level

* 7 Null

* 8 Bump if Odd

* 9 Internal Length

* 10 Binary Length

* 11 Table Length

* 12 Most Significant DT

* 13 Least Significant DT

* 14 High Precision

* 15 Low Precision

* 16 Elementary Item

* 17 Occurs

* 18 Key Column

* 19 Sub Data Type

* 20 Native Data Type

* 21 Character Set

Database type: DB2 UDB

Character set ID: UTF-8

National character set ID: UTF-16

Locale: en_US

Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14

TimeZone: CST +++++++ 删除这行记录 +++++++

Definition for table ROGER.TEST1129

Record length: 34

Syskey: 0

Columns: 2

A 64 20 0 0 0 1 0 20 20 20 0 0 0 0 1 0 1 0 27 -1

B 134 23 23 0 0 1 0 8 8 4 0 0 0 0 1 0 1 0 20 -1

End of definition

2） goldengate由高到低数据同步，trail文件格式问题

2021-11-29 22:10:00  ERROR   OGG-01332  File /opt/ogg/dirdat/nd000000, with compatibility level 4, is not compatible with the current software version's compatibility level of 3. Modify the file writer's paramet
er file to generate the appropriate format using the FORMAT LEVEL 3 option.
2021-11-29 22:10:00  ERROR   OGG-01668  PROCESS ABENDING.
GGSCI (mogdb) 123>

2021-11-29 22:10:00 ERROR OGG-01332 File /opt/ogg/dirdat/nd000000, with compatibility level 4, is not compatible with the current software version's compatibility level of 3. Modify the file writer's paramet

er file to generate the appropriate format using the FORMAT LEVEL 3 option.

2021-11-29 22:10:00 ERROR OGG-01668 PROCESS ABENDING.

GGSCI (mogdb) 123>

由于这里GoldenGate同步是从高到低版本进行复制，因此需要加入相关参数。在源端的extract 进程和pump进程参数文件中加入如下参数：

extract： release format 11.2
pump： RMTTRAIL /opt/ogg/dirdat/nd,FORMAT LEVEL 3

extract： release format 11.2

pump： RMTTRAIL /opt/ogg/dirdat/nd,FORMAT LEVEL 3

最后简单总结一下：

1、GoldenGate 对于PostgreSQL 很早就已经支持了，但是对于PostgreSQL 9.2版本，最低支持replicate复制的版本是GoldenGate 11.2.1.0.2;
这是对于目标端而言，如果要进行PostgreSQL数据抽取，那么需要用GoldenGate 12.1版本。

2、GoldenGate支持DB2的多个版本的数据抓取和同步。

3、GoldenGate 赞不支持MogDB或者openGauss进行数据异构同步。

Tags: goldengate db2, goldengate postgresql, MogDB, ogg-00303, ogg-00551, ogg-01668
Posted in Goldengate, Oracle rdbms on 12月 6, 2021

You must be logged in to post a comment.

love wife love life —Roger的Oracle/MySQL/PostgreSQL数据恢复博客

Categories

Archives

最新评论

国内圈子

oracle security

利用GoldenGate实现DB2到PostgreSQL（MogDB）数据同步

Leave a Reply