Replication from Oracle to PostgreSQL Using GoldenGate

posted May 17, 2017, 12:06 PM by

Andy Kerber (@dbakerber), Senior Consultant

This is part three of a four-part post on Oracle Golden Gate replication. In part one, we configured Oracle Multi-Master replication with basic conflict management. In part two, we configured Oracle to MySQL replication using GoldenGate. In this third part, we will configure Oracle to PostgreSQL replication using GoldenGate.

Environment:

Oracle database server: pgorcl
Oracle version: 12.1.0.2 (not using containers)
PostgreSQL database server: pgsql
PostgreSQL: version 9.6
GoldenGate: version 12c

Installation:

GoldenGate for PostgreSQL is in a zipped tar file. Unzip the file in your GoldenGate Home directory, $GGH, and then untar it (tar –xvf).

After installation, you will need to add the GoldenGate Home directory ($GGH) and the $GGH/lib directory to your LD_LIBRARY_PATH.

A caveat: GoldenGate for PostgreSQL uses odbc for the connection to PostgreSQL. Any data types that cannot be handled by odbc will not be handled by GoldenGate.

First, in PostgreSQL, we create the schema and database:

psql

postgres=# create user andy with superuser password 'andy';

postgres=#create database andy;

postgres=#\c andy

andy=# CREATE TABLE BONUS2
    (    ENAME VARCHAR(10),
         JOB VARCHAR(9),
         SAL BIGINT,
         COMM BIGINT,
         LAST_UPDT_DT_TM TIMESTAMP  DEFAULT CURRENT_TIMESTAMP 
    )
   ;
CREATE TABLE DEPT
    (    DEPTNO SMALLINT,
         DNAME VARCHAR(14),
         LOC VARCHAR(13),
         LAST_UPDT_DT_TM TIMESTAMP DEFAULT current_timestamp
    )
   ;
CREATE TABLE DEPTTEST
    (    DEPTNO SMALLINT,
         DNAME VARCHAR(14),
         LOC VARCHAR(13),
         LAST_UPDT_DT_TM TIMESTAMP DEFAULT current_timestamp
    )
   ;
CREATE TABLE EMP
    (    EMPNO SMALLINT,
         ENAME VARCHAR(10),
         JOB VARCHAR(9),
         MGR SMALLINT,
         HIREDATE TIMESTAMP,
         SAL DECIMAL(7,2),
         COMM DECIMAL(7,2),
         DEPTNO SMALLINT,
         LAST_UPDT_DT_TM TIMESTAMP DEFAULT current_timestamp
    )
   ;
CREATE TABLE MYTEST
    (    EMPNO SMALLINT,
         ENAME VARCHAR(10),
         JOB VARCHAR(9),
         MGR SMALLINT,
         HIREDATE TIMESTAMP,
         SAL DECIMAL(7,2),
         COMM DECIMAL(7,2),
         DEPTNO SMALLINT,
         LAST_UPDT_DT_TM TIMESTAMP DEFAULT current_timestamp
    )
   ;
CREATE TABLE SALGRADE
    (    GRADE BIGINT,
         LOSAL BIGINT,
         HISAL BIGINT,
         LAST_UPDT_DT_TM TIMESTAMP DEFAULT current_timestamp
    )
   ;
CREATE TABLE SCOTTWORK
    (    EMPNO SMALLINT,
         ENAME VARCHAR(10),
         JOB VARCHAR(9),
         MGR SMALLINT,
         HIREDATE TIMESTAMP,
         SAL DECIMAL(7,2),
         COMM DECIMAL(7,2),
         DEPTNO SMALLINT,
         LAST_UPDT_DT_TM TIMESTAMP DEFAULT current_timestamp
    )
   ;
CREATE TABLE SCOTTWORK3
    (    EMPNO SMALLINT,
         ENAME VARCHAR(10),
         JOB VARCHAR(9),
         MGR SMALLINT,
         HIREDATE TIMESTAMP,
         SAL DECIMAL(7,2),
         COMM DECIMAL(7,2),
         DEPTNO SMALLINT,
         LAST_UPDT_DT_TM TIMESTAMP DEFAULT current_timestamp
    )
   ;
CREATE TABLE TCUSTMER
    (    CUST_CODE VARCHAR(4),
         NAME VARCHAR(30),
         CITY VARCHAR(20),
         STATE CHAR(2),
         LAST_UPDT_DT_TM TIMESTAMP DEFAULT current_timestamp
    )
   ;
CREATE TABLE TCUSTORD
    (    CUST_CODE VARCHAR(4),
         ORDER_DATE TIMESTAMP,
         PRODUCT_CODE VARCHAR(8),
         ORDER_ID BIGINT,
         PRODUCT_PRICE DECIMAL(8,2),
         PRODUCT_AMOUNT INT,
         TRANSACTION_ID BIGINT,
         LAST_UPDT_DT_TM TIMESTAMP DEFAULT current_timestamp
    )
   ;
CREATE TABLE TESTAB
    (    EMPNO SMALLINT,
         ENAME VARCHAR(10),
         JOB VARCHAR(9),
         MGR SMALLINT,
         HIREDATE TIMESTAMP,
         SAL DECIMAL(7,2),
         COMM DECIMAL(7,2),
         DEPTNO SMALLINT,
         LAST_UPDT_DT_TM TIMESTAMP DEFAULT current_timestamp
    )
   ;
 
CREATE UNIQUE INDEX PK_DEPT ON DEPT (DEPTNO)
   ;
CREATE UNIQUE INDEX PK_EMP ON EMP (EMPNO)
   ;
CREATE UNIQUE INDEX SYS_C0011589 ON TCUSTMER (CUST_CODE)
   ;
CREATE UNIQUE INDEX SYS_C0011590 ON TCUSTORD (CUST_CODE, ORDER_DATE, PRODUCT_CODE, ORDER_ID)
   ;
 
ALTER TABLE DEPT ADD CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO);
ALTER TABLE EMP ADD CONSTRAINT PK_EMP PRIMARY KEY (EMPNO);
ALTER TABLE TCUSTMER ADD PRIMARY KEY (CUST_CODE);
ALTER TABLE TCUSTORD ADD PRIMARY KEY (CUST_CODE, ORDER_DATE, PRODUCT_CODE, ORDER_ID);
ALTER TABLE EMP add FOREIGN KEY(DEPTNO) references dept(deptno);

 

Next, create the odbc data source. The odbc drivers are included in GoldenGate, but make sure that $GGH is in your path.

The file below, which is your odbc ini file:

vi odbc.ini

[ODBC Data Sources]
PostgreSQL on pgsql
[ODBC]
IANAAppCodePage=4
InstallDir=/u01/gghome
[pg96db]
Driver=/u01/gghome/lib/GGpsql25.so
Description=Postgres driver
Database=andy
HostName=pgsql
PortNumber=5432
LogonID=andy
Password=andy

Set the environment variable, ODBCINI, to point to this file:

export ODBCINI=/u01/gghome/odbc.ini

 

Next, create the defgen file on the oracle server. These are the same steps as used to create the defgen file for MySQL:

This allows GoldenGate to translate the Oracle definition to the PostgreSQL definition.

GGSCI@pgorcl>edit params defgen

defsfile ./dirdef/andy.def, purge
userid ggadmin, password ggadmin
table andy.*;

From the OS level, generate the defgen file.

$defgen paramfile ./dirprm/defgen.prm

The command will write the defgen file to the file specified, in this case $GGH/dirdef/andy.def

The file will have a section like this at the start:

Database type: ORACLE
Character set ID: UTF-8
National character set ID: UTF-16
Locale: neutral
Case sensitivity: 14 14 14 14 14 14 14 14 14 14 14 14 11 14 14 14
TimeZone: GMT
*

 

And sections like this for each table:

Definition for table ANDY.EMP
Record length: 144
Syskey: 0
Columns: 9
EMPNO            134    8      0 0 0 1 0     8     8     8 0 0 0 0 1   0 1   3   2       -1     0 0 0
ENAME            64     10     12 0 0 1 0    10    10    0 0 0 0 0 1   0 0   0   1       -1     0 0 0
JOB              64     9      28 0 0 1 0    9     9     0 0 0 0 0 1   0 0   0   1       -1     0 0 0
MGR              134    8      42 0 0 1 0    8     8     8 0 0 0 0 1   0 0   3   2       -1     0 0 0
HIREDATE         192    19     54 0 0 1 0    19    19    19 0 5 0 0 1  0 0   0   12      -1     0 0 0
SAL              134    9      76 2 0 1 0    8     8     8 0 0 0 0 1   0 0   3   2       -1     0 0 0
COMM             134    9      88 2 0 1 0    8     8     8 0 0 0 0 1   0 0   3   2       -1     0 0 0
DEPTNO           134    8      100 0 0 1 0   8     8     8 0 0 0 0 1   0 0   3   2       -1     0 0 0
LAST_UPDT_DT_TM  192    29     112 0 0 1 0   29    29    29 0 6 0 0 1  0 0   0   187     -1     0 0 0
End of definition

 

The file is used to translate the Oracle data to MySQL data. After generating the file, copy it to the corresponding directory on the PostgreSQL server.

eg scp andy.def pgsql:/u01/gghome/dirdef

Switch back to the Oracle server and create the initial load extract (this is very similar to the one we created for MySQL):

GGSCI@pgorcl > add extract pgext, sourceistable
GGSCI@pgorcl > edit params pgext
Extract pgext
userid ggadmin password ggadmin
rmthost pgsql, mgrport 7809 ← Parameters for the PostgreSQL server
rmttask replicat, group reppgext ← Points to the replicat on the PostgreSQL server
table andy.*;

 

Connect to ggsci on the PostgreSQL server and create the initial load replicat.

GGSCI@pgsql> create replicat reppgext, specialrun

GGSCO@pgsql> edit params reppgext
replicat reppgext ← Same name as defined in initial load extract on pgorcl
sourcedefs ./dirdef/andy.def
SETENV ( PGCLIENTENCODING = "UTF8" )
SETENV (ODBCINI="/u01/gghome/odbc.ini" ) ← odbc ini file
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
targetdb pg96db, userid andy, password andy ← pg96db is the driver defined in odbc.ini
discardfile ./dirrpt/oracl.dsc, purge
map andy.* target public.*;  ← Public schema in database andy in PostgreSQL. We could create the andy schema if we wanted.

 

Return to the Oracle server and start the extract:

GGSCI@pgorcl> start extract pgext

 

And tail to the ggserr.log file. If you see ‘access denied’ in the error log, then go to the PostgreSQL server, and do this:

GGSCI@pgsql> stop mgr
Are you sure? Yes
(note: tail ggserr.log to make sure it stops, If necessary kill the process manually)

GGSCI@pgsql> edit params mgr
PORT 7809
ACCESSRULE, PROG *, IPADDR *, ALLOW ← add this line, access rules are a new feature of 12c

GGSCI@pgsl> start mgr

 

Return to the Oracle server and restart the extract:

GGSCI@pgorcl> start extract pgext

 

Return to the PostgreSQL server and verify that data is in the file:

psql> \c andy
psql> select count(1) from emp;
count
-------
12
(1 row)
andy=#

 

The initial load has succeeded.

Now configure change data capture. On the source DB, configure the extract:

GGSCI@pgorcl> edit params extorapg

extract extorapg
userid ggadmin, password ggadmin
rmthost pgsql, mgrport 7809
RMTTRAIL ./dirdat/ep
TABLE andy.*;

GGSCI@pgorcl> add extract extorapg, tranlog, begin now
GGSCI@pgorcl> add exttrail ./dirdat/ep, extract extorapg megabytes 10

 

Now log into the PostgreSQL server and configure the replicat:

GGSCI@pgsql>  edit params reporapg

replicat reporapg
sourcedefs ./dirdef/andy.def
SETENV ( PGCLIENTENCODING = "UTF8" )
SETENV (ODBCINI="/u01/gghome/odbc.ini" )
SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")
targetdb pg96db, userid andy, password andy
discardfile ./dirrpt/oracl.dsc, purge
map andy.* target public.*;

GGSCI@pgsql>  add replicat reporapg, NODBCHECKPOINT, exttrail ./dirdat/ep

GGSCI@pgsql>  start replicat reporapg

Verify, check row count in PostgreSQL:
psql> select count(1) from emp;
count
-------
12
(1 row)

 

Insert a row in Oracle:

SQLPLUS> insert into emp values (7401, 'ANNE', 'SALESLDY', 7698, trunc(sysdate),95000,10000,20,systimestamp);

SQLPLUS> commit;

 

Select the count from PostgreSQL:

andy=# select count(1) from emp;
count
-------
13
(1 row)

andy=# select * from emp;
empno| ename  |   job    | mgr  |     hiredate        |   sal    |   comm   | deptno |     last_updt_dt_tm
-------+--------+-----------+------+---------------------+----------+----------+--------+----------------------------
7499 | ALLEN  | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600.00  |   300.00 |     30 | 2017-04-22 14:49:52.147335
7566 | JONES  | MANAGER  | 7839 | 1981-04-02 00:00:00 | 2975.00  |          |     20 | 2017-04-22 14:49:52.147335
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250.00  | 1400.00  |     30 | 2017-04-22 14:49:52.147335
7698 | BLAKE  | MANAGER  | 7839 | 1981-05-01 00:00:00 | 2850.00  |          |     30 | 2017-04-22 14:49:52.147335
7782 | CLARK  | MANAGER  | 7839 | 1981-06-09 00:00:00 | 2450.00  |          |     10 | 2017-04-22 14:49:52.147335
7788 | SCOTT  | ANALYST  | 7566 | 1987-04-19 00:00:00 | 3000.00  |          |     20 | 2017-04-22 14:49:52.147335
7839 | KING   | PRESIDENT|      | 1981-11-17 00:00:00 | 5000.00  |          |     10 | 2017-04-22 14:49:52.147335
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500.00  |     0.00 |     30 | 2017-04-22 14:49:52.147335
7350 | DUMMY2 | SALES    | 7698 | 2017-04-19 00:00:00 | 35000.00 | 8000.00  |     30 | 2017-04-22 14:49:52.147335
7800 | SMARTY | SMARTIT  | 7839 | 2017-04-26 00:00:00 | 90000.00 | 10000.00 |     20 | 2017-04-26 19:53:16.731168
7400 | ANDY   | SALESSTUD| 7698 | 2017-04-23 00:00:00 | 45000.00 | 10000.00 |     20 | 2017-04-23 15:14:10.288964
7410 | ANDY   | TOPSTUD  | 7698 | 2017-04-29 22:44:58 | 80000.00 | 10000.00 |     20 | 2017-04-29 22:44:58.77862
7401 | ANNE   | SALESLDY | 7698 | 2017-05-01 00:00:00 | 95000.00 | 10000.00 |     20 | 2017-05-01 16:53:25.733787 ← Row we added

 

The replication process is working as expected.

We have configured one way replication to a PostgreSQL database.  In part four, we will add a third master database, Oracle version 11.2.0.1, where we cannot use integrated replicat and extract.

Please note: this blog contains code examples provided for your reference. All sample code is provided for illustrative purposes only. Use of information appearing in this blog is solely at your own risk. Please read our full disclaimer for details.

Share with your networkTweet about this on TwitterShare on LinkedInShare on FacebookDigg thisEmail this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *

WANT TO LEARN MORE?

Share with your networkTweet about this on TwitterShare on LinkedInShare on FacebookDigg thisEmail this to someone