MS SQL Server to Oracle data replication using Oracle GoldenGate - Part 1

This post provides a simple step by step procedure on how to configure and achieve data replication between MS SQL Server database and Oracle database using Oracle GoldenGate software

Note: I have provided the simple demo on how to perform a replication from an Oracle to Oracle database in my previous blog post Oracle GoldenGate simple hands-on demo. Most of the Oracle related steps will be redirected to refer the blog for details. This post is to provide information mostly on the prerequisites and how to configure GoldenGate software on the MS SQL Server database and perform replication (minimum mandatory setup to achieve replication where you have multiple options on the parameters available to be used). 

Since the post is long, I'll be breaking it to two parts and I have also provided a downloadable copy of the post in this link. Click on images to view full size if required.
Part 1 covers all the prerequisites required to be met before attempting Oracle GoldenGate replication and part 2 covers the param file set up along with the actual steps on achiveing replication.

1. Environment Details

Source:

MS SQL Server Enterprise 2012 running on Windows 10 enterprise

Source database properties

Target:

Oracle 12.1.0.2.0 database running on openSUSE 13.2 (Harlequin) (x86_64) under Oracle VM Virtual box with Windows 10 enterprise host.

Target Database properties

GoldenGate software used:

Source:

Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401 Windows x64 (optimized)

Target:

Version 12.2.0.1.0 OGGCORE_12.2.0.1.0_PLATFORMS_151101.1925.2_FBO Linux, x64, 64bit (optimized)

GoldenGate software can be downloaded from this link.

2.  GoldenGate Installation

Once the software has been downloaded from the link, let’s proceed with the installation of the software on both source and target and prepare the environments for the replication.

Source:

Unzip the file to a directory and that would be the installation of Oracle GoldenGate software for SQL Server on Windows.

Once the software is extracted, we can then login to the GoldenGate Software Command Interface (GGSCI) and create all the sub directories required.

Note: You should change your present working directory to the software location or the GoldenGate Home location and start ggsci.

C:\Windows\system32>cd E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit

C:\Windows\system32>e:

E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit>ggsci

Oracle GoldenGate Command Interpreter for SQL Server
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Windows x64 (optimized), Microsoft SQL Server on Dec 11 2015 15:50:22
Operating system character set identified as windows-1252.

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



GGSCI (Prinky) 1> create subdirs

Creating subdirectories under current directory E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit

Parameter files                E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirprm: created
Report files                   E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirrpt: created
Checkpoint files               E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirchk: created
Process status files           E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirpcs: created
SQL script files               E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirsql: created
Database definitions files     E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirdef: created
Extract data files             E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirdat: created
Temporary files                E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirtmp: created
Credential store files         E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dircrd: created
Masterkey wallet files         E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirwlt: created
Dump files                     E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirdmp: created


GGSCI (Prinky) 2> exit

E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit>
 

Optional step: To create manager process as a Windows service to run in background even when user logs off the system

E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit> INSTALL ADDSERVICE

Service 'GGSMGR' created.

Install program terminated normally.

E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit>
 

Target:

For installation on the Target Linux based system, refer my blog post and follow the steps intended to be performed on the Destination

Refer à Oracle GoldenGate Installation

Once installation is complete, login to the GoldenGate Software Command Interface (GGSCI) and create all the sub directories required.

Note: You should change your present working directory to the software location or the GoldenGate Home location and start ggsci.

oracle@opensuse:/u01/app/oracle/product/12.1/oggcore_1> ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 12.2.0.1.0 OGGCORE_12.2.0.1.0_PLATFORMS_151101.1925.2_FBO
Linux, x64, 64bit (optimized), Oracle 12c on Nov 11 2015 03:53:23
Operating system character set identified as UTF-8.

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



GGSCI (opensuse.selvapc.com) 1> create subdirs
 

We are now done with the installation and configuration of Oracle GoldenGate software on both source and target systems.

3. Environment preparation

 Once the GoldenGate installation is successful, we should prepare both the environments to be replication ready. Now let’s prepare the environment.

3.1        Source Table and Data:

I will be using GGATE database in my MS SQL Server for the purpose of this demo. You can your own existing database.

Under GGATE database

create schema gg_user
go

create table [gg_user].[emp] (
     [id] [smallint] not null,
     [first_name] varchar(50) not null,
     [last_name] varchar(50) not null,
constraint [emp_pk] primary key clustered (
     [id] asc
) with (pad_index = off, statistics_norecompute=off, ignore_dup_key=off, allow_row_locks=on, allow_page_locks=on) on [primary]
) on [primary]

go
 
-- Insert a few data into the table so that we have our source table set up. 
-- Query the table to see the records.

use ggate
select * from gg_user.emp

-- Result:
id first_name last_name
1 Selva  Kumar
2 Rajini  Kanth
3 David  Billa
4 Manic  Badsha
  

Now our Test DB with its data is ready.

3.2        Data Server Name (DSN) creation

Create DSN for the MSSQL data to be accessed by Oracle GoldenGate software. This step is very important as Oracle GoldenGate software will communicate to the database using DSN

Steps:

Type “ODBC Data Source” in the “Search the web and Windows” tab in the task bar and click on “ODBC Data Sources (64 bit)”

Click on System DSN and fill data as below. Use the database you are trying to connect.

Click on Next and enter details as below

Click on Next and provide the default database to connect

Click on Next and then Click Finish

Now Test the connectivity whether check if it works. Click on Test Data Source

Now we are done with the setup of DSN successfully.

3.3        Backup Type and performing Backup

It is a mandatory step to set up “Full recovery model” as backup strategy for the MS SQL database and a backup has to be taken and available at disk to start with the real time extraction.

Note: Initial replication doesn’t need the above to be done but is necessary for real time synchronization.

So now let’s turn on the Full recovery model and take a backup as below.

Check the current setting and change to Full recovery model if required.

In SQL Server Management Studio, right click on database name (GGATE in our case) à Properties à Options à Recovery Model

Once set, click on OK so the setting be saved.

Take a backup of database with transaction logs. Transaction logs backup is mandatory to be taken and it will be taken along with the database backup by default in the Full recovery mode. Oracle GoldenGate gets the changed data from transaction logs in most cases and if it not found in the logs then it would get it from the transaction log backup and for this reason the backup should be of native MS SQL Server backup and Oracle GoldenGate doesn’t support third party backup.

Use the graphical method to take backup by Right click on database (GGATE in our case) à Tasks à Backup as shown below.

Edit the fields as desired and click OK to perform the backup.

If you would like to use command line to take the backup, open New Query in the SQL Server Management Studio and execute the below commands.

USE [master]
GO
BACKUP DATABASE [Ggate] 
TO DISK = N'E:\oracle\Ggate_Full.bak' 
WITH NAME = N'Ggate-Full Database Backup', STATS = 10, INIT
GO
 

3.4        Target environment setup

Once software installation is completed on the target system, please refer to Oracle GoldenGate simple hands-on demo, section 3. Prerequisites and Oracle GoldenGate software set up and perform the On Target part of 3.2 OGG software setup

Now once this is done, we have set up our source and target environment for the GoldenGate replication and let’s continue with the rest of the operations.

4. Enable Trandata

Enabling trandata on the source database would require login to the database and hence use the created DSN name (Command line 1) to connect to the database using GGSCI. The defgen parameter file will be created in the command line 3.

E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit>ggsci

Oracle GoldenGate Command Interpreter for SQL Server
Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
Windows x64 (optimized), Microsoft SQL Server on Dec 11 2015 15:50:22
Operating system character set identified as windows-1252.

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



GGSCI (Prinky) 1> dblogin sourcedb GG 

2016-05-07 16:27:30  WARNING OGG-05236  ODBC Warning: The specified DSN 'GG' uses a client driver that may be incompatible with the database server. Microsoft SQL Server 2012 requires SQLNCLI11.DLL or a more recent version.

2016-05-07 16:27:30  INFO    OGG-03036  Database character set identified as windows-1252. Locale: en_US.

2016-05-07 16:27:30  INFO    OGG-03037  Session character set identified as windows-1252.
Successfully logged into database.

GGSCI (Prinky) 2> add trandata gg_user.emp

Logging of supplemental log data is enabled for table gg_user.emp

GGSCI (Prinky) 3> edit params defgen

GGSCI (Prinky) 4> exit

E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit>
 

The contents of the defgen.prm are as below

defsfile E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirdef\emp.def

sourcedb GG

table gg_user.emp;

You can see all the tables for which supplement logging (or published for replication) from MS-SQL databases using the below command.

SELECT *
FROM sys.tables
WHERE is_replicated = 1

-- Result
name object_id is_replicated create_date modify_date
emp 245575913 1 2016-05-07 15:41:04.973 2016-05-07 16:28:03.593
 

Now we need to invoke defgen utility to create a definitions file for the table EMP under GG_USER schema. So let’s do it.

E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit>defgen paramfile E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirprm\defgen.prm

***********************************************************************
      Oracle GoldenGate Table Definition Generator for SQL Server
      Version 12.2.0.1.1 OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
 Windows x64 (optimized), Microsoft SQL Server on Dec 11 2015 16:16:15

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


                    Starting at 2016-05-07 16:42:09
***********************************************************************

Operating System Version:
Microsoft Windows 8 , on x64
Version 6.2 (Build 9200)

Process id: 3116

***********************************************************************
**            Running with the following parameters                  **
***********************************************************************
defsfile E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirdef\emp.def
sourcedb GG

2016-05-07 16:42:09  WARNING OGG-05236  ODBC Warning: The specified DSN 'GG' uses a client driver that may be incompatible with the database server. Microsoft SQL Server 2012 requires SQLNCLI11.DLL or a more recent version.

2016-05-07 16:42:09  INFO    OGG-03036  Database character set identified as windows-1252. Locale: en_US.

2016-05-07 16:42:09  INFO    OGG-03037  Session character set identified as windows-1252.
table gg_user.emp;
Retrieving definition for gg_user.emp.


Definitions generated for 1 table in E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirdef\emp.def.


E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit>
 

We can see that the definition file has been created in the dirdef directory. You can check the contents of the definition file create if you would like to…

This definition file is needed for Oracle to understand and translate the data types from MS SQL to Oracle data types. Transfer the generated definition file E:\oracle\oggWinx64\ggs_Windows_x64_MSSQL_64bit\dirdef\emp.def to the target server under dirdef directory of the GoldenGate home. I’m using Filezilla client to transfer the file and you are free to use any of your tools such as Filezilla, Winscp, etc.

Once the above said steps has been completed, then the rest of the steps are of same like the Oracle to Oracle replication as described in this post.

I’ll go through the steps quickly with explanations wherever required. Rest of the steps is available in the next part of the blog post and is available in this link

Happy Replicating!!