从DB2到MogDB的数据同步测试

原创李真旭云和恩墨交付战队 2021-12-03

1809

近期由于某项目需要，需要测试一下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 
........
复制

2、创建dascrt

[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.

复制

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 ~]$ 

复制

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 ~]$ 
复制

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 ~]$ 

复制

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

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> 



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 ~]$


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>


**源端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]$

最后修改用户.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


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

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

复制



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

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)


可见对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.



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

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>


开始以为是timezone设置有问题，因此进行了调整，但是发现并不起作用。
复制

[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

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.



2、其他问题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>


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

mogdb技术分享