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.
