Skip to Main Content

GoldenGate

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Goldengate for PostgreSQL doesn't write rows

Gogala MladenJan 12 2024

I am trying to set up replication between Oracle 21c and PostgreSQL 13.3 in my home lab. I will soon have to do something similar in the office, so I decided to give it a try. The configuration is working and showing no errors, but nothing gets written. Here is my configuration:

EXTRACT esrc1
SETENV(ORACLE_HOME="/opt/oracle/product/21c/dbhome_1")
SETENV(ORACLE_SID="ORCLCDB")
SETENV(TNS_ADMIN="/opt/oracle/homes/OraDBHome21cEE/network/admin")
USERID c##ggadmin@orclpdb1 PASSWORD qwerty
rmthost pgsql16, mgrport 7809
RMTTRAIL ./dirdat/pr
TABLE SCOTT.TEST_TAB;

REPLICAT pgrep
container mem_limit 6G
cachemgr cachesize 1G
SETENV ( PGCLIENTENCODING = "UTF8" )
SETENV (ODBCINI="/home/GG_home/.odbc.ini" )
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
TARGETDB pgsql13 USERID ggadmin, PASSWORD qwerty
DISCARDFILE ./dirrpt/pgsql13.dsc, PURGE
GETTRUNCATES
BATCHSQL
MAP SCOTT.* TARGET scott.*;

ODBC is set up properly and is causing no issues:

GGSCI (pgsql16.home.com) 2> dblogin sourcedb pgsql13 userid ggadmin password qwerty

2024-01-12 14:56:46 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.UTF-8.

2024-01-12 14:56:46 INFO OGG-03037 Session character set identified as UTF-8.
Successfully logged into database.

Here is PGREP.rpt:

ETENV ( PGCLIENTENCODING = "UTF8" )
SETENV (ODBCINI="/home/GG_home/.odbc.ini" )
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
TARGETDB pgsql13 USERID ggadmin, PASSWORD ***

2024-01-12 14:28:09 INFO OGG-03036 Database character set identified as UTF-8. Locale: en_US.UTF-8.

2024-01-12 14:28:09 INFO OGG-03037 Session character set identified as UTF-8.
DISCARDFILE ./dirrpt/pgsql13.dsc, PURGE
GETTRUNCATES
BATCHSQL
MAP SCOTT.* TARGET scott.*;

2024-01-12 14:28:09 INFO OGG-02734 Using heartbeat table from schema ggadmin.

2024-01-12 14:28:09 INFO OGG-01851 filecaching started: thread ID: 140117688710720.

2024-01-12 14:28:09 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:
/home/GG_home/dirtmp.

2024-01-12 14:28:09 INFO OGG-25340
Database Version:
PostgreSQL
Version 13.13.0000 PostgreSQL 13.13
ODBC Version 03.52.0000

Driver Information:
GGpsql25.so
Version 07.16.0445 (B0598, U0438)
ODBC Version 03.52.

2024-01-12 14:28:09 INFO OGG-25341
Database Language and Character Set:
SERVER_ENCODING = "UTF8"
2024-01-12 14:28:09 INFO OGG-25340
Database Version:
PostgreSQL
Version 13.13.0000 PostgreSQL 13.13
ODBC Version 03.52.0000

Driver Information:
GGpsql25.so
Version 07.16.0445 (B0598, U0438)
ODBC Version 03.52.

2024-01-12 14:28:09 INFO OGG-25341
atabase Version:
PostgreSQL
Version 13.13.0000 PostgreSQL 13.13
ODBC Version 03.52.0000

Driver Information:
GGpsql25.so
Version 07.16.0445 (B0598, U0438)
ODBC Version 03.52.

2024-01-12 14:28:09 INFO OGG-25341
Database Language and Character Set:
SERVER_ENCODING = "UTF8"
LC_CTYPE = "en_US.UTF-8"
TIMEZONE = "America/New_York".

2024-01-12 14:28:09 INFO OGG-25339 Connected to database scott, server pgsql13, DSN pgsql13, CPU information not available.

***********************************************************************
** Run Time Messages **
***********************************************************************

2024-01-12 14:28:09 INFO OGG-30067 Opened trail file /home/GG_home/dirdat/pr000000005, RBA: 1,388 at 2024-01-12 14:28:09.770738.

2024-01-12 14:28:09 INFO OGG-03506 The source database character set, as determined from the trail file, is UTF-8.

2024-01-12 14:36:41 INFO OGG-02232 Switching to next trail file /home/GG_home/dirdat/pr000000006 at 2024-01-12 14:36:41.919797 due to EOF. with current RBA 1,388.

2024-01-12 14:36:41 INFO OGG-04048 Processed graceful restart record at seq 6 rba 1,328.

However, nothing is getting written to the Postgres database:

[postgres@pgsql16 dirrpt]$ psql -U scott
psql (13.13)
Type "help" for help.

scott=> select count(*) from scott.test_tab;
count
-------
0
(1 row)

Everything is running:

GGSCI (pgsql16.home.com) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
REPLICAT RUNNING PGREP 00:00:00 00:00:02

GGSCI (ora21c.home.com) 1> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING
EXTRACT RUNNING ESRC1 02:23:51 00:00:03

I have tried attaching the log files for both Oracle and Postgres but attachment screen doesn't work..

Thanks for any help you can provide. This is a mystery: everything seems to be working, yet nothing gets replicated.

Regards

Comments
Post Details
Added on Jan 12 2024
5 comments
45 views