Replication between SQL Server 2012 CDC and Oracle 12c using GoldenGate 19c

In this post I will walk you trough the steps to configure Oracle GoldeGate 19 to replicate transactions between Microsoft SQL Server 2012 using CDC Capture replication and Oracle 12c.

We are going to build a simple unidirectional replication of the table ORG.DEPT between two heterogeneous databases.

The below diagram shows the GoldenGate architecture of a replication unidirectional from SQL Server database to Oracle database.

Operating System Database Database Name GoldenGate version Oracle GoldenGate
Home
GoldenGate installer IP Database
Server
Manager
Port
Microsoft Windows 7
Professional 64 bits
SQL Server 2012
Enterprise Edition 64 bits
SQLSRC 19.1.0.0.3 c:\GG 191003_ggs_Windows_x64_MSSQL_64bit_CDC.zip 192.168.2.199 7809
Oracle Linux 7
64 bits
Oracle 12.1.0.2.0
Enterprise Edition 64 bits
orctrg 19.1.0.0.4 /u02/gg 191004_fbo_ggs_Linux_x64_shiphome.zip 192.168.2.130 7809


Below the list of steps to configure and setup GoldenGate with CDC

1. (Windows Source Server) Install Oracle GoldenGate 19c for Microsoft Server 2012
2. (Linux Target Server) Install Oracle GoldenGate 19c for Oracle Unix
3. (Windows Source Server) Preparing Source Data.
4. (Linux Target Server) Preparing Target Data.
5. (Windows Source Server) Configuring manager process
6. (Linux Target Server) Configuring manager process
7. Setting up the Extract and Replicat process for Initial Load
8. Setting Up CDC Extract, Data Pump and Replicat process for Live Replication.

1. (Windows Source Server) Install Oracle GoldenGate 19c for Microsoft Server 2012

This previous post explain how to Install Oracle GoldenGate 19c for Microsoft SQL Server

2. (Linux Target Server) Install Oracle GoldenGate 19c for Oracle Unix

This previous post explain how to Install Oracle GoldenGate 19c for Oracle Unix

3. (Windows Source Server) Preparing Source Data

a. Create a new SQL Server database “SQLSRC”

  1. Database name: SQLSRC

b. Select Full Recovery model for the database SQLSCR

  1. Select Options
  2. Select Full in Recovery Model
  3. Click on “OK” button.

c. Create the user login ggadmin with password oracle and the schema ggadmin in the database SQLSRC

-- Create the user loggin ggadmin with password oracle
USE [master]
GO
CREATE LOGIN [ggadmin] WITH PASSWORD=N'oracle', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=ON, CHECK_POLICY=ON
GO
ALTER SERVER ROLE [sysadmin] ADD MEMBER [ggadmin]
GO
USE [SQLSRC]
GO
CREATE USER [ggadmin] FOR LOGIN [ggadmin]
GO
USE [SQLSRC]
GO
ALTER ROLE [db_owner] ADD MEMBER [posteo]
GO

-- Create the schema ggadmin in the database SQLSRC
USE [SQLSRC]
GO
CREATE SCHEMA [ggadmin] AUTHORIZATION [ggadmin]
GO

d. Create the schema ORG and table DEPT that will be replicated

CREATE SCHEMA ORG
GO

CREATE TABLE [ORG].[dept] (
[id]   [smallint] not null primary key,
[name] barco(50) not null
)

INSERT INTO [ORG].[DEPT] values (1,'HR')
INSERT INTO [ORG].[DEPT] values (2,'IT')
INSERT INTO [ORG].[DEPT] values (3,'SALES')
INSERT INTO [ORG].[DEPT] values (4,'MARKETING')

e. Make a full database before you start GoldenGate extract process

Occasionally the GoldenGate extract process requires access to a log backup on the source system because the log records are no longer available in the online log and have been moved to the log backups.

  1. Select SQLSRC database > right click > Tasks > Back Up…> OK
-- SCRIPT to make a full backup of SQLSRC database
BACKUP DATABASE [SQLSRC] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\SQLSRC.bak' WITH NOFORMAT, NOINIT,  NAME = N'SQLSRC-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

f. Create a new ODBC Data Source Name

  1. Click on “System DSN” tab
  2. Select SQL Server Native Client 11.0 driver
  3. Click on “Finish” button
  • Enter information of the new Data Source to SQL Server
  1. Name: enter GGSRV
  2. Description: enterODBC for GOLDENGATE
  3. Server: enter server name where is located SQL-Sever
  4. Click on “Next” button
  • Enter the authentication of the Data Source
  1. Select the radio button “With SQL Server authentication using a login ID and password entered by the user”
  2. Enter login ID: ggadmin
  3. Password: oracle
  • Click on “Next” button
  • Click on “Finish” button
  • Click on “OK” button

4. (LInux Target Server) Preparing Target Data

a. Create a new Oracle database “orctrg” with password oracle for system and sys users

[oracle@ocm12 templates]$ dbca -syspassword oracle -systempassword oracle -silent -createDatabase -templateName /u01/app/oracle/product/12.1.0/dbhome_1/assistants/dbca/templates/General_Purpose.dbc -gdbname orctrg -sid orctrg -responseFile NO_VALUE  -characterSet AL32UTF8 -memoryPercentage 30 -emConfiguration NONE


b. Create a user golden gate administrator

create user ggadmin identified by oracle default tablespace users temporary tablespace temp;

grant  connect, resource, select any dictionary to ggadmin;
grant insert any table, update any table,delete any table   to ggadmin;
grant unlimited tablespace to ggadmin;

exec dbms_goldengate_auth.grant_admin_privilege('ggadmin');

c. Enable the parameter enable_goldengate_replication in the database orctrg

SQL> show parameter enable_goldengate_replication

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication        boolean     FALSE
SQL> alter system set enable_goldengate_replication=true scope=both;

d. Configure a listener in the Oracle Linux Server (IP 192.168.2.130)

[oracle@ocm12 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ocm12.cba.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

e. Start listener

[oracle@ocm12 admin]$ lsnrctl start listener

[oracle@ocm12 admin]$ lsnrctl status listener

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 20-APR-2020 19:05:54

Copyright (c) 1991, 2014, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=ocm12.cba.com)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     listener
Version                   TNSLSNR for Linux: Version 12.1.0.2.0 - Production
Start Date                20-APR-2020 19:04:58
Uptime                    0 days 0 hr. 0 min. 55 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ocm12/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ocm12.cba.com)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
Services Summary...
Service "orctrg" has 1 instance(s).
  Instance "orctrg", status READY, has 1 handler(s) for this service...
Service "orctrgXDB" has 1 instance(s).
  Instance "orctrg", status READY, has 1 handler(s) for this service...
The command completed successfully

5. (Windows Source Server) Configuring manager process in the Windows Server

  • Edit the parameter file of the manager process and enable the port 7809 in the Windows Server
c:\>cd c:\gg

c:\GG>ggsci
Oracle GoldenGate Command Interpreter for SQL Server
Version 19.1.0.0.3 OGGCORE_19.1.0.0.0_PLATFORMS_190907.0144
Windows x64 (optimized), Microsoft SQL Server on Sep  7 2019 13:37:56
Operating system character set identified as windows-1252.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
-- Setup the manager process
GGSCI (DELL-PC) 1> edit param mgr
PORT 7809
GGSCI (DELL-PC) 2>   

6. (Linux Target Server) Configuring manager process in the Linux Server

  • Edit the parameter file /u02/gg/dirprm/mgr.prm and enable the manager process to listener in the port 7809 and add an access rule to allow to the Source Windows Server (IP 192.168.2.199) to create a remote trail file in the Linux Target Server.
[oracle@ocm12 gg]$ cd /u02/gg
[oracle@ocm12 gg]$ ./ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Oct 18 2019 01:38:51
Operating system character set identified as UTF-8.

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.

GGSCI (ocm12.cba.com) 2> edit params mgr

-- Setup the manager process
PORT 7809
ACCESSRULE, PROG *, IPADDR 192.168.2.199, ALLOW

7. Setting up the Extract and Replicat process for Initial Load

a. In Window Server, create the GLOBAL parameter file in the Oracle GoldenGate home c:\GG adding the parameter GGSCHEMA that specify the name of the schema that contains the database objects that are owned by Oracle GoldenGate

Using GGSCHEMA in the GLOBALS file is a new requirement for Oracle GoldenGate for SQL Server CDC Capture.

-- Setup the GLOBAL parameter file adding the line GGSCHEMA ggadmin
GGSCI (DELL-PC) 3> EDIT PARAMS ./GLOBALS
GGSCHEMA ggadmin
GGSCI (DELL-PC) 4>  

b. Because the datatypes of SQL Server and Oracle database are different is necessary create a defgen parameter file that includes the tables need to be mapped.

edit params defgen
defsfile  c:\gg\dirdef\dept.def 
sourcedb  GGSRV userid ggadmin password oracle
table     ORG.dept;

Definitions generated for 1 table in c:\gg\dirdef\dept.def.

c. In Windows Server, run the program defgen that generates a mapping file c:\gg\dirprm\defgen.prm

c:\GG>defgen paramfile c:\gg\dirprm\defgen.prm
***********************************************************************
      Oracle GoldenGate Table Definition Generator for SQL Server
      Version 19.1.0.0.3 OGGCORE_19.1.0.0.0_PLATFORMS_190907.0144
 Windows x64 (optimized), Microsoft SQL Server on Sep  7 2019 14:40:48

Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.

                    Starting at 2020-04-12 17:53:45
***********************************************************************
Operating System Version:
Microsoft Windows 7, on x64
Version 6.1 (Build 7601: Service Pack 1)

Process id: 8408

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
defsfile  c:\gg\dirdef\dept.def
sourcedb  GGSQL userid ggadmin password ***

2020-04-12 17:53:46  INFO    OGG-03036  Database character set identified as windows-1252. Locale: en_US.

2020-04-12 17:53:46  INFO    OGG-03037  Session character set identified as windows-1252.
table     ORG.dept;
Retrieving definition for ORG.dept.

Definitions generated for 1 table in c:\gg\dirdef\dept.de

d. Transfer the file C:\GG\dirdef\dept.def generated in the step b. to the directory /u02/gg/dirdef/dept.def in Oracle Linux using any SFTP tool.

e. In Microsoft SQL Server Management Studio start SQL Server Agent

  1. SQL Server Agent Right Click > Start > Yes

f. In SQL Server, drop the SQL Server CDC cleanup job for the database SQLSRC because it may cause loss for the extract process.

use SQLSRC
execute sys.sp_cdc_drop_job 'cleanup';

g. In Windows Server, run the utility ogg_cdc_cleanup_setup.bat (in the Oracle GoldenGate home) to create the Oracle Golden Gate CDC cleanup job and associate objects.

c:\GG>ogg_cdc_cleanup_setup.bat createJob ggadmin oracle SQLSRC DELL-PC ggadmin

Oracle GoldenGate CDC cleanup job setup script
==============================================

Command: createJob

The Oracle GoldenGate CDC Cleanup job and its relevant tables and procedures have been created.

h. In GoldenGate for SQL Server, create the extract parameter file down1.prm used for the initial Load

The extract process uses the down1.prm parameter file, that connects to the database SQLSRC, select all rows of the table ORG.dept and create a remote trail file in Oracle Linux Server.

The replicat process load1 read the trail file and replicates all transaction in the Oracle database orctrg.

GGSCI (DELL-PC) 4>edit params down1

extract down1
sourcedb  GGSQL userid ggadmin password oracle
rmthost 192.168.2.130, mgrport 7809
rmttask replicat, group load1
table ORG.dept;

i. In GoldenGate for Oracle, create the replicat parameter in

GGSCI (ocm12.cba.com) 1> edit params load1edit params load1 

replicat load1
SETENV (TNS_ADMIN="/u01/app/oracle/product/12.1.0/dbhome_1/network/admin")
SETENV (ORACLE_HOME="/u01/app/oracle/product/12.1.0/dbhome_1")
userid ggadmin , password oracle
discardfile ./dirrpt/load1.dsc, purge
map ORG.dept, target ORG.DEPT;

j. In GoldenGate for SQL Server, add the extract process down1 and specify the table ORG.DEPT as source

GGSCI (DELL-PC) 5> add extract down1 ,sourceistable
GGSCI (DELL-PC) 6>

k. In GoldenGate for Oracle, add the replicat process load1 as specialrun

GGSCI (ocm12.cba.com) 2> add replicat load1, specialrun

l. In GoldenGate SQL Server, start the manager and extract process down1

GGSCI (DELL-PC) 7>start mgr
Manager started.
GGSCI (DELL-PC) 8>start down1

m. In GoldenGate for Oracle, start the manager and the replicat process load1

GGSCI (ocm12.cba.com) 3> start mgr
Manager started.
GGSCI (ocm12.cba.com) 4> start load1

8. Setting Up CDC Extract, Data Pump and Replicat process for Live Replication.

a. In GoldenGate for SQL Server , connect to the source database from GGSCI and enable supplemental logging for the table ORG.DEPT

GGSCI (DELL-PC as ggadmin@GGSRV) 9>  DBLOGIN SOURCEDB GGSRV userid ggadmin password oracle
GGSCI (DELL-PC as ggadmin@GGSRV) 10> add trandata org.dept

Logging of supplemental log data is enabled for table ORG.dept in filegroup PRIMARY

b. In GoldenGate for SQL Server, configure the cdcext extract process, this process capture on real time all changes in the table ORG.DEPT of SQL Server database and create a local extract trail file cexxxxxxxxx (where x is a sequence) in the Windows folder C:\GG\dirdat

GGSCI (DELL-PC) 11>edit cdcext

EXTRACT cdcext
SOURCEDB GGSRV USERID ggadmin PASSWORD oracle
EXTTRAIL C:\GG\dirdat\ce
table ORG.DEPT;

c. In GoldenGate for SQL Server:

  • register the extract process cdcext specifying that will use the tranlog file to capture the changes on the table ORG.DEPT
  • register the trail file C:\GG\dirdat\ce and associate it with the extract process cdcext
GGSCI (DELL-PC) 12> add extract cdcext , TRANLOG, BEGIN NOW
GGSCI (DELL-PC) 13>add EXTTRAIL C:\GG\dirdat\ce,  EXTRACT cdcext

d. In GoldenGate for SQL Server, configure the edpump1 data pump process , this process capture the changes in the table ORG.DEPT from the local trail file C:\GG\dirdat\cexxxxxxxxx and create a remote trail file in Oracle Linux Server in the folder /u02/app/dirdat/dp

GGSCI (DELL-PC) 14>EDIT PARAMS EDPUMP1

extract edpump1
RMTHOST 192.168.2.130, mgrport 7809
RMTTRAIL /u02/gg/dirdat/dp
TABLE ORG.dept;

e. In GoldenGate for SQL Server:

  • register the data pump extract process edpump1 specifying will use the local trail file C:\GG\dirdat\ce
  • register the remote trail file /u02/gg/dirdat/dp and associate it with the data pump extract process edpump1
GGSCI (DELL-PC as ggadmin@GGSRV) 15> add extract  edpump1 , exttrailsource C:\GG\dirdat\ce
EXTRACT added.

GGSCI (DELL-PC as ggadmin@GGSRV) 16> add rmttrail /u02/gg/dirdat/dp, extract edpump1
RMTTRAIL added.

f. In GoldenGate for SQL Server , start the extract process cdcext and the extract data pump edpump1

GGSCI (DELL-PC as ggadmin@GGSRV) 17> start cdcext

GGSCI (DELL-PC as ggadmin@GGSRV) 18> start edpump1
Sending START request to MANAGER ...
EXTRACT EDPUMP1 starting

g. In GoldenGate for SQL Server , check the status of the procceses

GGSCI (DELL-PC as ggadmin@GGSRV) 19> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
EXTRACT     RUNNING     CDCEXT      00:00:00      00:00:07
EXTRACT     RUNNING     EDPUMP1     00:00:00      00:00:01

h. In GoldenGate for Oracle, add a checkpointtable ggadmin.GGSCHKPT

GGSCI (ocm12.cba.com) 5> dblogin userid ggadmin, password oracle
Successfully logged into database.

GGSCI (ocm12.cba.com as ggadmin@orctrg) 6> add checkpointtable ggadmin.GGSCHKPT
Successfully created checkpoint table ggadmin.GGSCHKPT.

i. In GoldenGate for Oracle, configure a replicate process load2 that map the table ORG.dept between SQL Server and Oracle

GGSCI (ocm12.cba.com as ggadmin@orctrg) 7> edit param load2

replicat load2
SETENV (TNS_ADMIN="/u01/app/oracle/product/12.1.0/dbhome_1/network/admin")
SETENV (ORACLE_HOME="/u01/app/oracle/product/12.1.0/dbhome_1")
userid ggadmin , password oracle
discardfile ./dirrpt/load2.dsc, purge
map ORG.dept, target ORG.DEPT;

j. In GoldenGate for Oracle, register the replicat load2 process and link it with the trail file /u02/gg/dirdat/dp using a checkpointtable ggadmin.GGSCHKPT

GGSCI (ocm12.cba.com as ggadmin@orctrg) 8> add replicat load2 , exttrail /u02/gg/dirdat/dp, checkpointtable ggadmin.GGSCHKPT
REPLICAT added.

k. In Golden Gate for Oracle, start the replicat process load2

GGSCI (ocm12.cba.com as ggadmin@orctrg) 9> start load2

Sending START request to MANAGER ...
REPLICAT LOAD2 starting

l. In Golden Gate for Oracle, check the status of the processes

GGSCI (ocm12.cba.com as ggadmin@orctrg) 10> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
REPLICAT    RUNNING     LOAD2       00:00:00      00:00:01

m. In SQL Server, insert a new row in the database SQLSRC

use SQLSRC
insert into org.dept values (5,'PURCHASING');

n. In GoldenGate for Oracle, check the replicat process load2

GGSCI (ocm12.cba.com as ggadmin@orctrg) 11> info replicat load2

REPLICAT   LOAD2     Last Started 2020-04-13 20:18   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:00 ago)
Process ID           15922
Log Read Checkpoint  File /u02/gg/dirdat/dp000000000
                     2020-04-13 20:23:51.452476  RBA 1874