MS SQL Server to Oracle Replication Using Golden Gate
Replicate data from SQL Server to Oracle with GoldenGate.
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 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.