AbroVision

Goldengate is a tool for real-time change data capture and replication. In this article, we will see how we can configure the tool to support data replication from SQL Server to Oracle database. We will be focusing the SQL Server side of the configuration.

Requirements:

If you are using SQL Server 2012, 2014, 2016, or 2017 as a source database, Oracle highly recommends that you apply the latest Service Pack or Cumulative Update for your version of SQL Server.
Only user databases are supported (can be AlwaysOn Primary or readable Synchronous Secondary).
The SQL Server Agent must be running.
Ensure that Auto Update Statistics is enabled for the database.
Ensure that the server where Oracle GoldenGate is installed has the same system time and time zone as the database server.
SQL Server CDC need to be enabled
SQL Login with sysadmin privileges on the source database.

Restrictions (see detail in Database Requirements section):

No system databases
No Contained databases
No databases enabled with In-Memory Optimization (2014/2016/2017 feature)
Database Compatibility level must be 110 or higher
Asynchronous AlwaysOn databases are not supported

High level Steps:

Make sure the source database is in Full Recovery model and CDC is enabled for it.
Download the OADC 19.3.1 runtime from the below link (the OADC version can differ)

OADC link: https://www.oracle.com/database/technologies/dotnet-odacdeploy-downloads.html

Setup and install the initial Data Access client

Unzip the file in any temporary folder of your choice.
You should see a setup.exe file there. You can right click on it and run as Administrator and launch it.
You can choose to accept most defaults and just click on next including the Configure ODP.NET and other Oracle Providers for ASP.NET checkbox

Test the Oracle connection via Microsoft Data Link

Create an empty text file on the Windows Desktop named OraTestLink.udl
Provide Data Source, username and password from the Oracle side

Install and Setup GoldenGate on the Windows Server that has SQL Server Database

Download the zip file from the following location: https://www.oracle.com/middleware/technologies/goldengate-downloads.html
Create a directory on the destination computer
Unzip the downloaded zip file to the newly created directory
Open a command prompt as Administrator and launch the ggsci program
cd <new directory>
<new directory> ggsci.exe

Add the tables (objects) to be replicated

ADD TRANDATA <tablename>

Configuring Oracle Goldengate for SQL Server