MS SQL Server to Oracle Replication Using Golden Gate

In this project, we will perform single table DML replication from MS SQL server (on windows 10) to Oracle database (on Linux) using Oracle Golden Gate. We will also look at how to replicate when the Golden Gate source version is higher than the target Golden Gate version.

• Setup Source SQL Server on Windows 10

• Create ODBC Data Source

• Install Golden Gate 12.3 on source

• Enable and Take Source DB Backup

• Configure Initial Load Extract and Replicat

• Configure Change Sync Extract and Replicat

• Test SQL Server to Oracle Replication

Setup Source SQL Server on Windows 10

Create a new virtual machine and install windows 10. You can also use your host operating system to install an SQL server if it is windows. Download SQL server 2014 and perform the installation.

Once SQL server 2014 installation is done, create a new QADB database. Add a new schema HR

create schema HR

Create below table under QADB. We will be replicating this table from source to target FOX.WIN_REP_TAB

CREATE TABLE hr.rep_tab
(
c1	int,
c2	CHAR(30),
c3	int,
c4	int,
PRIMARY KEY ( c1 )
);

Insert some dummy records on the source table. We will make sure Golden Gate initial load copy these records on target

INSERT INTO [hr].[rep_tab] VALUES (1,'Alpha',10,100);
INSERT INTO [hr].[rep_tab] VALUES (2,'Beta',20,200);
INSERT INTO [hr].[rep_tab] VALUES (3,'John',30,300);
INSERT INTO [hr].[rep_tab] VALUES (4,'Tom',40,400);

Create target table on Oracle database under FOX schema

CREATE TABLE fox.win_rep_tab
(
c1 number PRIMARY KEY,
c2 CHAR(30),
c3 number,
c4 number
);

Create ODBC Data Source

In order for Oracle Golden Gate to access QADB, you have to create an ODBC data source for it. Go to Control Panel > Administrative tools > ODBC Data Sources (64-bit). Select system DNS tab

Click on ADD, choose SQL Server, and click on Finish

Give Name as GG (Remember, this will be your connection name going forward. You will use GG to connect from golden gate to SQL server). Select your local server

Just click Next

Select database as QADB

Nothing to select on the final screen. Just click on Finish

The final configuration should look like below

Click on Test Data Source to check

Click on OK and your data source has been added successfully.

Install Golden Gate 12.3 on source

Download Golden Gate for MS SQL server on windows here. Now we are downloading GG 12.3 as on our target machine GGPROD, we have GG 12.2 version

Unzip the downloaded GG file under E:\gg. This will be your Golden Gate Home on a windows machine

Open command prompt, navigate to E:\gg, start GGSCI and create subdirs

E:
cd gg
ggsci
create subdirs
edit param mgr

PORT 7809

Start mgr

exit

Create GLOBALS file and add below

ggsci	
edit param ./GLOBALS	 name must be in capital
GGSCHEMA HR	         just put this one line

Add supplemental logging for HR.REP_TAB

GGSCI>	dblogin sourcedb gg	   gg is data source connection name
GGSCI>	add trandata hr.rep_tab

Create source definitions file

As we are performing hybrid replication, we must generate source definitions file and copy it on target

EDIT PARAMS DEFGEN

defsfile e:\gg\dirdef\rep_tab.def
sourcedb gg
table hr.rep_tab;

E:\gg> defgen paramfile e:\gg\dirprm\defgen.prm

Copy e:\gg\dirdef\rep_tab.def on target Oracle server under GG_HOME/dirdef using WinSCP

Enable and Take Source DB Backup

This is an additional step when you have an SQL Server database as a source. You must confirm that the database is set to full recovery and then take a full backup of QADB database. Right click QADB > Properties > Options > Recovery Model = Full

Now take a full backup of QADB. Right-click on QADB > Tasks > Backup and click on OK

Configure Initial Load Extract and Replicat

As our source table is having some rows, we must configure initial load in order to copy the existing rows from source to target Oracle database. Note the new parameter FORMAT RELEASE 12.2

On Source:
==========
GGSCI> ADD EXTRACT INITEX, SOURCEISTABLE

GGSCI> EDIT PARAMS INITEX

EXTRACT INITEX
SOURCEDB gg
RMTHOST 192.168.0.181, MGRPORT 7809
RMTTASK REPLICAT, GROUP INITRE, FORMAT RELEASE 12.2
TABLE hr.rep_tab;

Add Initial load replicat on target

On Target:
=========
GGSCI> ADD REPLICAT INITRE, SPECIALRUN

GGSCI> EDIT PARAMS INITRE

REPLICAT INITRE
USERID ogg, PASSWORD ogg
SOURCEDEFS /u01/app/oracle/product/gg/dirdef/rep_tab.def
MAP hr.rep_tab, TARGET fox.win_rep_tab;

Make sure you add below line to target mgr parameter file

ACCESSRULE, PROG *, IPADDR *, ALLOW

Configure Change Sync Extract and Replicat

Add extract on source

On source:
==========
GGSCI> ADD EXTRACT MSEXT, TRANLOG, BEGIN NOW

GGSCI> add exttrail E:\gg\dirdat\ms, extract MSEXT

GGSCI> edit param MSEXT

EXTRACT MSEXT
SOURCEDB gg
EXTTRAIL E:\gg\dirdat\ms
TABLE hr.rep_tab;

Add pump on the source. Again, notice the FORMAT RELEASE parameter used in pump

On source:
==========
GGSCI> Add extract MSDP, EXTTRAILSOURCE E:\gg\dirdat\ms

GGSCI> Add rmttrail /u01/app/oracle/product/gg/dirdat/ms, extract MSDP

GGSCI> edit param MSDP

EXTRACT MSDP
SOURCEDB gg
RMTHOST 192.168.0.181, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/ms, FORMAT RELEASE 12.2 TABLE hr.rep_tab;

Add checkpoint table on the target

GGSCI> add checkpointtable ogg.repchkpt

Add replicat on target

GGSCI> add replicat ORAREP, exttrail /u01/app/oracle/gg/dirdat/ms

GGSCI> edit param ORAREP

REPLICAT ORAREP
USERID ogg, PASSWORD ogg
SOURCEDEFS /u01/app/oracle/product/gg/dirdef/rep_tab.def MAP hr.rep_tab, TARGET fox.win_rep_tab;

Test SQL Server to Oracle Replication

First, start the change sync extract and data pump on the source. This will start capturing changes while we perform the initial load. Do not start replicat at this point

On source:
==========
GGSCI> start MSEXT
GGSCI> start MSDP

Now start the initial load extract. Remember, this will automatically start the initial load replicat on the target

On source:
==========
GGSCI> start INITEX

GGSCI> INFO INITEX

EXTRACT	INITEX	    Last Started	2016-01-11 15:59   Status STOPPED
Checkpoint Lag	    Not Available	
Log Read Checkpoint Table FOX.TAB1	
		2016-01-11 15:59:57  Record 4
Task		    SOURCEISTABLE		

Verify on target if all the 4 records have been loaded on the target table or not

On Oracle DB:
=============
SQL> conn fox/fox
SQL> select * from win_rep_tab;

Now start the change sync replicat

On Oracle DB:
=============
GGSCI> start ORAREP

Note: At this stage, you can delete the initial load extract and replicat process as they are no longer needed.

Great, we replicated one single table from source MS SQL Server to target Oracle database!

Further experiment

• Create one newer table on source and target

• Add a new table to Extract, Pump and Replicat

• Try to insert some new records on the source and check if it is being reflected on target.