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