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
b. Select Full Recovery model for the database SQLSCR
Select Options
Select Full in Recovery Model
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.
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
Click on “System DSN” tab
Select SQL Server Native Client 11.0 driver
Click on “Finish” button
Enter information of the new Data Source to SQL Server
Name: enter GGSRV
Description: enterODBC for GOLDENGATE
Server: enter server name where is located SQL-Sever
Click on “Next” button
Enter the authentication of the Data Source
Select the radio button “With SQL Server authentication using a login ID and password entered by the user”
Enter login ID: ggadmin
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
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]$ 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
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.
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
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
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
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
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
Apr 23 2020
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.
Home
Server
Port
Professional 64 bits
Enterprise Edition 64 bits
64 bits
Enterprise Edition 64 bits
Below the list of steps to configure and setup GoldenGate with CDC
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”
b. Select Full Recovery model for the database SQLSCR
c. Create the user login ggadmin with password oracle and the schema ggadmin in the database SQLSRC
d. Create the schema ORG and table DEPT that will be replicated
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.
f. Create a new ODBC Data Source Name
4. (LInux Target Server) Preparing Target Data
a. Create a new Oracle database “orctrg” with password oracle for system and sys users
b. Create a user golden gate administrator
c. Enable the parameter enable_goldengate_replication in the database orctrg
d. Configure a listener in the Oracle Linux Server (IP 192.168.2.130)
e. Start listener
5. (Windows Source Server) Configuring manager process in the Windows Server
6. (Linux Target Server) Configuring manager process in the Linux Server
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.
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.
c. In Windows Server, run the program defgen that generates a mapping file c:\gg\dirprm\defgen.prm
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
f. In SQL Server, drop the SQL Server CDC cleanup job for the database SQLSRC because it may cause loss for the extract process.
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.
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.
i. In GoldenGate for Oracle, create the replicat parameter in
j. In GoldenGate for SQL Server, add the extract process down1 and specify the table ORG.DEPT as source
k. In GoldenGate for Oracle, add the replicat process load1 as specialrun
l. In GoldenGate SQL Server, start the manager and extract process down1
m. In GoldenGate for Oracle, start the manager and the replicat process 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
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
c. In GoldenGate for SQL Server:
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
e. In GoldenGate for SQL Server:
f. In GoldenGate for SQL Server , start the extract process cdcext and the extract data pump edpump1
g. In GoldenGate for SQL Server , check the status of the procceses
h. In GoldenGate for Oracle, add a checkpointtable ggadmin.GGSCHKPT
i. In GoldenGate for Oracle, configure a replicate process load2 that map the table ORG.dept between SQL Server and Oracle
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
k. In Golden Gate for Oracle, start the replicat process load2
l. In Golden Gate for Oracle, check the status of the processes
m. In SQL Server, insert a new row in the database SQLSRC
n. In GoldenGate for Oracle, check the replicat process load2
By DBA_JAF • Databases, Golden Gate, Oracle, Replication, SQL Server • 0