SqlServer to oracle Goldengate replication
Hello Friend's
In This Post we will discuss Replication from sqlserver to oracle table replication :
so lets get started
Enviornment :
VM1 : Goldenora
OS : OEL 7.5
DB : 12.2.0.1
GG : 12.3.0.1.6
User: Schema WL User ggadmin
VM2 : Goldensql
OS : Windows server 2016 Datacenter
DB : Sqlserver 2017
GG : 12.3.0.1.6
User: ggadm
Steps on source (Windows Server) :
unzip the software file : V978743-01
update the env variables in This PC -> Advanced system setting -> Environment variables
you will get an error when invoking GGSCI
Error : ggsci.exe program can't start because file MSVCR120.dll missing
install the below VC++ app in system to resolve the issue
vcredist_x64.exe : https://aka.ms/highdpimfc2013x64enu
After installing try invoking the ggsci
Step 1 : Create Database, schemas and tables in sqlserver for replication
Create database name sqldb
and new login e.g : ggadmin with sqlserver authentication and default database sqldb and make login owner of schema WL which we will create below
create schema WL
Table creation :
CREATE TABLE WL.wintab
(
SRNO int,
NAME CHAR(30),
ID int,
DESK_ID int,
PRIMARY KEY ( SRNO )
);
BEGIN TRANSACTION
INSERT INTO [WL].[wintab] VALUES (1,'Cage',10,100);
INSERT INTO [WL].[wintab] VALUES (2,'Danny',20,200);
INSERT INTO [WL].[wintab] VALUES (3,'Fist',30,300);
INSERT INTO [WL].[wintab] VALUES (4,'Luke',40,400);
commit TRANSACTION
--For later replication checks.
BEGIN TRANSACTION
INSERT INTO [WL].[wintab] VALUES (5,'Wing',40,500);
INSERT INTO [WL].[wintab] VALUES (6,'Cage',60,600);
INSERT INTO [WL].[wintab] VALUES (7,'Danny',70,700);
INSERT INTO [WL].[wintab] VALUES (8,'Gist',80,800);
INSERT INTO [WL].[wintab] VALUES (9,'Fluke',90,900);
INSERT INTO [WL].[wintab] VALUES (10,'Winga',100,1000);
INSERT INTO [WL].[wintab] VALUES (11,'Steve',110,1100);
INSERT INTO [WL].[wintab] VALUES (12,'Dann',120,1200);
INSERT INTO [WL].[wintab] VALUES (13,'Jacob',130,1300);
INSERT INTO [WL].[wintab] VALUES (14,'Chan',140,1400);
INSERT INTO [WL].[wintab] VALUES (15,'Jackie',150,1500);
commit TRANSACTION
Create ODBC connection as below given steps :
Steps to create odbc connection for sqlserver goldengate
Start with the Goldengate steps on windows
D:\GGHOME>ggsci
Oracle GoldenGate Command Interpreter for SQL Server
Version 12.3.0.1.6 OGGCORE_12.3.0.1.0_PLATFORMS_180531.2007
Windows x64 (optimized), Microsoft SQL Server on Jun 1 2018 13:03:19
Operating system character set identified as windows-1252.
Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.
GGSCI (WIN-JDKV0G3V31O) 1> CREATE SUBDIRS
Creating subdirectories under current directory D:\GGHOME
Parameter file D:\GGHOME\dirprm: created.
Report file D:\GGHOME\dirrpt: created.
Checkpoint file D:\GGHOME\dirchk: created.
Process status files D:\GGHOME\dirpcs: created.
SQL script files D:\GGHOME\dirsql: created.
Database definitions files D:\GGHOME\dirdef: created.
Extract data files D:\GGHOME\dirdat: created.
Temporary files D:\GGHOME\dirtmp: created.
Credential store files D:\GGHOME\dircrd: created.
Masterkey wallet files D:\GGHOME\dirwlt: created.
Dump files D:\GGHOME\dirdmp: created.
GGSCI (WIN-JDKV0G3V31O) 2> exit
D:\GGHOME>INSTALL ADDSERVICE
Service 'GGSMGR' created.
Install program terminated normally.
D:\GGHOME>ggsci
Oracle GoldenGate Command Interpreter for SQL Server
Version 12.3.0.1.6 OGGCORE_12.3.0.1.0_PLATFORMS_180531.2007
Windows x64 (optimized), Microsoft SQL Server on Jun 1 2018 13:03:19
Operating system character set identified as windows-1252.
Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.
GGSCI (WIN-JDKV0G3V31O) 1> edit param mgr
GGSCI (WIN-JDKV0G3V31O) 2> edit param ./GLOBALS
GGSCI (WIN-JDKV0G3V31O) 3> view param mgr
PORT 7809
GGSCI (WIN-JDKV0G3V31O) 4> view param ./GLOBALS
GGSCHEMA WL
GGSCI (WIN-JDKV0G3V31O) 5>
GGSCI (WIN-JDKV0G3V31O) 6> dblogin sourcedb sqldns , userid ggadmin password Password1
2020-07-24 03:57:39 WARNING OGG-05236 ODBC Warning: The specified DSN 'sqldns' uses a client driver that may be incompatible with the database server. Microsoft SQL Server requires ? or a more recent version.
2020-07-24 03:57:39 INFO OGG-03036 Database character set identified as windows-1252. Locale: en_US.
2020-07-24 03:57:39 INFO OGG-03037 Session character set identified as windows-1252.
Successfully logged into database.
Create wallet to add user in wallet sqlserver :
GGSCI (WIN-JDKV0G3V31O) 1> create wallet
Created wallet.
Opened wallet.
GGSCI (WIN-JDKV0G3V31O) 2>
GGSCI (WIN-JDKV0G3V31O) 2> Add CredentialStore
Credential store created.
GGSCI (WIN-JDKV0G3V31O) 3> alter credentialstore add user ggadmin Password Password1 alias ggadm
Credential store altered.
GGSCI (WIN-JDKV0G3V31O) 4> info CREDENTIALSTORE
Reading from credential store:
Default domain: OracleGoldenGate
Alias: ggadm
Userid: ggadmin
GGSCI (WIN-JDKV0G3V31O) 5> dblogin sourcedb sqldns ,useridalias ggadm
2020-07-24 04:08:06 WARNING OGG-05236 ODBC Warning: The specified DSN 'sqldns' uses a client driver that may be incompatible with the database server. Microsoft SQL Server requires ? or a more recent version.
2020-07-24 04:08:06 INFO OGG-03036 Database character set identified as windows-1252. Locale: en_US.
2020-07-24 04:08:06 INFO OGG-03037 Session character set identified as windows-1252.
Successfully logged into database.
GGSCI (WIN-JDKV0G3V31O as ggadmin@SQLDNS) 6>
CDC config Steps :
EXEC sys.sp_cdc_enable_db
EXECUTE sys.sp_cdc_drop_job 'cleanup';
command to check if cdc is enabled for the database :
SELECT name, is_cdc_enabled
FROM sys.databases
ogg_cdc_cleanup_setup.bat createJob user1 pword1 dbname1 HOSTNAME\INSTANCE oggschema
ogg_cdc_cleanup_setup.bat dropJob user1 pword1 dbname1 HOSTNAME\INSTANCE oggschema
D:\GGHOME>ogg_cdc_cleanup_setup.bat createjob ggadmin Password1 SQLDB WIN-JDKV0G3V31O WL
Oracle GoldenGate CDC cleanup job setup script
==============================================
Command: createjob
Oracle GoldenGate CDC Cleanup job and its relevant table(s) and procedure(s) are created.
Create defgen of table to be replicated in our case is (WL.wintab)
GGSCI (WIN-JDKV0G3V31O) 1> EDIT PARAMS DEFGEN
GGSCI (WIN-JDKV0G3V31O as ggadmin@SQLDNS) 2> view param defgen
defsfile D:\GGHOME\dirdef\wintab.def
sourcedb sqldns useridalias ggadm
table WL.wintab;
GGSCI (WIN-JDKV0G3V31O) 2> exit
D:\GGHOME>defgen paramfile D:\GGHOME\dirprm\defgen.prm
***********************************************************************
Oracle GoldenGate Table Definition Generator for SQL Server
Version 12.3.0.1.6 OGGCORE_12.3.0.1.0_PLATFORMS_180531.2007
Windows x64 (optimized), Microsoft SQL Server on Jun 1 2018 15:19:08
Copyright (C) 1995, 2018, Oracle and/or its affiliates. All rights reserved.
Starting at 2020-07-24 05:10:29
***********************************************************************
Operating System Version:
Microsoft Windows Server 2016, on x64
Version 10.0 (Build 14393)
Process id: 2464
***********************************************************************
** Running with the following parameters **
***********************************************************************
defsfile D:\GGHOME\dirdef\wintab.def
sourcedb sqldns useridalias ggadm
2020-07-24 05:10:29 WARNING OGG-05236 ODBC Warning: The specified DSN 'sqldns' uses a client driver that may be incompatible with the database server. Microsoft SQL Server requires ? or a more recent version.
2020-07-24 05:10:29 INFO OGG-03036 Database character set identified as windows-1252. Locale: en_US.
2020-07-24 05:10:29 INFO OGG-03037 Session character set identified as windows-1252.
table WL.wintab;
Retrieving definition for WL.wintab.
Definitions generated for 1 table in D:\GGHOME\dirdef\wintab.def.
Create initial load extract and main extract and pump for replication :
Initial load process :
GGSCI (WIN-JDKV0G3V31O) 1> EDIT PARAMS INTLD
GGSCI (WIN-JDKV0G3V31O) 2> view param INTLD
EXTRACT INTLD
SOURCEDB sqldns ,useridalias ggadm
RMTHOST 10.10.4.92, MGRPORT 7809
RMTTASK REPLICAT, GROUP INITRE
TABLE WL.wintab;
GGSCI (WIN-JDKV0G3V31O) 5> ADD EXTRACT INTLD, SOURCEISTABLE
EXTRACT added.
Extract process for windows :
GGSCI (WIN-JDKV0G3V31O) 1> edit param SQEXT
GGSCI (WIN-JDKV0G3V31O) 2> view param sqext
EXTRACT SQEXT
SOURCEDB sqldns ,useridalias ggadm
EXTTRAIL D:\GGHOME\dirdat\sq
TABLE WL.wintab;
GGSCI (WIN-JDKV0G3V31O) 4> ADD EXTRACT SQEXT, TRANLOG, BEGIN NOW
EXTRACT added.
GGSCI (WIN-JDKV0G3V31O) 5> add exttrail D:\GGHOME\dirdat\sq, extract SQEXT
EXTTRAIL added.
Pump process for windows :
GGSCI (WIN-JDKV0G3V31O) 6> edit param SQDP
GGSCI (WIN-JDKV0G3V31O) 7> view param sqldb
ERROR: PARAM file sqldb does not exist.
GGSCI (WIN-JDKV0G3V31O) 8> view param sqdp
EXTRACT SQDP
SOURCEDB sqldns ,useridalias ggadm
RMTHOST 10.10.4.92, MGRPORT 7809
RMTTRAIL /Golden/GGsql/dirdat/sq,
TABLE WL.wintab;
GGSCI (WIN-JDKV0G3V31O) 9> Add extract SQDP, EXTTRAILSOURCE D:\GGHOME\dirdat\sq
EXTRACT added.
GGSCI (WIN-JDKV0G3V31O) 10> Add rmttrail /Golden/GGsql/dirdat/sq, extract SQDP
RMTTRAIL added.
GGSCI (WIN-JDKV0G3V31O as ggadmin@SQLDNS) 16> add checkpointtable WL.chkpt
Successfully created checkpoint table WL.chkpt.
GGSCI (WIN-JDKV0G3V31O as ggadmin@SQLDNS) 18> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED SQDP 00:00:00 00:52:27
EXTRACT STOPPED SQEXT 00:00:00 01:01:31
****************************************************************************************
STEPS ON Linux server :
Unzip the software :
[gguser@dm01og01 oracle]$ unzip --q V975837-01.zip -d /Golden/GGsql/
Archive: V975837-01.zip
creating: /Golden/GGsql/fbo_ggs_Linux_x64_shiphome/
creating: /Golden/GGsql/fbo_ggs_Linux_x64_shiphome/Disk1/
creating: /Golden/GGsql/fbo_ggs_Linux_x64_shiphome/Disk1/install/
inflating: /Golden/GGsql/fbo_ggs_Linux_x64_shiphome/Disk1/install/.oui
inflating: /Golden/GGsql/fbo_ggs_Linux_x64_shiphome/Disk1/install/attachHome.sh
Start with Goldengate installation :
Go to location (/Golden/GGsql/fbo_ggs_Linux_x64_shiphome/Disk1 )and start with ./runinstaller
Login to PDB pdbsqlsrv, Create user and provide the provileges as below
create user ggadm identified by ggadm;
grant connect, resource to ggadm;
grant select any dictionary, select any table to ggadm;
grant create table to ggadm;
grant flashback any table to ggadm;
grant execute on dbms_flashback to ggadm;
grant execute on utl_file to ggadm;
grant create any table to ggadm;
grant insert any table to ggadm;
grant update any table to ggadm;
grant delete any table to ggadm;
grant drop any table to ggadm;
grant dba TO ggadm;
exec dbms_goldengate_auth.grant_admin_privilege('ggadm');
Create user LW and provide the nesseary privs :
Table to be created on linux side :
CREATE TABLE LW.lnxtab
(
SRNO number PRIMARY KEY,
NAME CHAR(30),
ID number,
DESK_ID number
);
switch to gguser (Sudo su - gguser) :
GGSCI (dm01og01.database.com) 1> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (dm01og01.database.com) 2>
GGSCI (dm01og01.database.com) 2>
GGSCI (dm01og01.database.com) 2> create subdirs
Creating subdirectories under current directory /Golden/GGsql
Parameter file /Golden/GGsql/dirprm: already exists.
Report file /Golden/GGsql/dirrpt: already exists.
Checkpoint file /Golden/GGsql/dirchk: already exists.
Process status files /Golden/GGsql/dirpcs: already exists.
SQL script files /Golden/GGsql/dirsql: already exists.
Database definitions files /Golden/GGsql/dirdef: already exists.
Extract data files /Golden/GGsql/dirdat: already exists.
Temporary files /Golden/GGsql/dirtmp: already exists.
Credential store files /Golden/GGsql/dircrd: already exists.
Masterkey wallet files /Golden/GGsql/dirwlt: already exists.
Dump files /Golden/GGsql/dirdmp: already exists.
GGSCI (dm01og01.database.com) 7> view param mgr
ACCESSRULE, PROG *, IPADDR *, ALLOW
PORT 7809
GGSCI (dm01og01.database.com) 8> start mgr
Manager started.
Steps to create wallet :
GGSCI (dm01og01.database.com) 9> Create Wallet
Created wallet.
Opened wallet.
GGSCI (dm01og01.database.com) 10>
GGSCI (dm01og01.database.com) 10> Add CredentialStore
Credential store created.
GGSCI (dm01og01.database.com) 11>
GGSCI (dm01og01.database.com) 11> alter credentialstore add user ggadm@PDBSQLSRV Password ggadm alias ggadm
Credential store altered.
GGSCI (dm01og01.database.com) 12>
GGSCI (dm01og01.database.com) 12> info credentialstore
Reading from credential store:
Default domain: OracleGoldenGate
Alias: ggadm
Userid: ggadm@PDBSQLSRV
GGSCI (dm01og01.database.com) 13>
GGSCI (dm01og01.database.com) 13> dblogin useridalias ggadm
Successfully logged into database PDBSQLSRV.
GGSCI (dm01og01.database.com as ggadm@CDBSQSRV/PDBSQLSRV) 14>
Add checkpoint table :
GGSCI (dm01og01.database.com as ggadm@CDBSQSRV/PDBSQLSRV) 14> edit param GLOBALS
GGSCI (dm01og01.database.com as ggadm@CDBSQSRV/PDBSQLSRV) 15> view param GLOBALS
PDBSQLSRV.ggadm.chkpoint
GGSCI (dm01og01.database.com as ggadm@CDBSQSRV/PDBSQLSRV) 16> add checkpointtable PDBSQLSRV.ggadm.chkpoint
Successfully created checkpoint table PDBSQLSRV.ggadm.chkpoint.
GGSCI (dm01og01.database.com as ggadm@CDBSQSRV/PDBSQLSRV) 19> info checkpointtable PDBSQLSRV.ggadm.chkpoint
Checkpoint table PDBSQLSRV.ggadm.chkpoint created 2020-07-24 19:06:27.
Add Initial load replicat and regular replicat for replication :
GGSCI (dm01og01.database.com) 1>
GGSCI (dm01og01.database.com) 1> EDIT PARAMS INITRE
GGSCI (dm01og01.database.com) 2> view param INITRE
REPLICAT INITRE
useridalias ggadm
SOURCEDEFS /Golden/GGsql/dirdef/wintab.def
MAP WL.wintab, TARGET LW.lnxtab;
GGSCI (dm01og01.database.com) 3> ADD REPLICAT INITRE, SPECIALRUN
REPLICAT added.
Create normal replication replicat
GGSCI (dm01og01.database.com) 4> edit param SQREP
GGSCI (dm01og01.database.com) 5> view param SQREP
REPLICAT SQREP
useridalias ggadm
SOURCEDEFS /Golden/GGsql/dirdef/wintab.def
MAP WL.wintab, TARGET LW.lnxtab;
GGSCI (dm01og01.database.com) 6> add replicat SQREP, exttrail /Golden/GGsql/dirdat/sq
ERROR: No checkpoint table specified for ADD REPLICAT.
GGSCI (dm01og01.database.com) 7> add replicat SQREP, exttrail /Golden/GGsql/dirdat/sq checkpointtable PDBSQLSRV.ggadm.chkpoint
REPLICAT added.
GGSCI (dm01og01.database.com) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED SQREP 00:00:00 00:01:01
*******************************************************************************************************************************
Config Completed both Windows and linux side , Now lets start the process and check the replication :
Windows side first start with Initial load process INTLD
GGSCI (WIN-JDKV0G3V31O as ggadmin@SQLDNS) 39> start intld
Sending START request to MANAGER ('GGSMGR') ...
EXTRACT INTLD starting
Check Table on linux side :
SQL> show con_name
CON_NAME
------------------------------
PDBSQLSRV
SQL>
SQL>
SQL> select * from LW.lnxtab;
no rows selected
SQL> !date
Fri Jul 24 19:18:51 IST 2020
SQL> select * from LW.lnxtab;
SRNO NAME ID DESK_ID
---------- ------------------------------ ---------- ----------
1 Cage 10 100
2 Danny 20 200
3 Fist 30 300
4 Luke 40 400
SQL> !date
Fri Jul 24 19:26:53 IST 2020
Now Initial load is done, Start with start of extract and pump on windows side :
GGSCI (WIN-JDKV0G3V31O as ggadmin@SQLDNS) 43> start SQEXT
Sending START request to MANAGER ('GGSMGR') ...
EXTRACT SQEXT starting
GGSCI (WIN-JDKV0G3V31O as ggadmin@SQLDNS) 44> start SQDP
Sending START request to MANAGER ('GGSMGR') ...
EXTRACT SQDP starting
GGSCI (WIN-JDKV0G3V31O as ggadmin@SQLDNS) 45>
GGSCI (WIN-JDKV0G3V31O as ggadmin@SQLDNS) 45>
GGSCI (WIN-JDKV0G3V31O as ggadmin@SQLDNS) 45> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING SQDP 00:00:00 01:09:35
EXTRACT RUNNING SQEXT 01:18:30 00:00:08
----Linux Side----
GGSCI (dm01og01.database.com) 15> start SQREP
Sending START request to MANAGER ...
REPLICAT SQREP starting
GGSCI (dm01og01.database.com) 16> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING SQREP 00:00:00 00:00:00
All our process are running on windows as well as linux , Now we will start with the remaining
Insert a row in wintab table sqlserver :
INSERT INTO [WL].[wintab] VALUES (5,'Wing',40,500);
Now check on linux server table :
SQL> select * from LW.lnxtab;
SRNO NAME ID DESK_ID
---------- ------------------------------ ---------- ----------
1 Cage 10 100
2 Danny 20 200
3 Fist 30 300
4 Luke 40 400
SQL> !date
Fri Jul 24 19:26:53 IST 2020
SQL> select * from LW.lnxtab;
SRNO NAME ID DESK_ID
---------- ------------------------------ ---------- ----------
1 Cage 10 100
2 Danny 20 200
3 Fist 30 300
4 Luke 40 400
5 Wing 40 500
SQL> !date
Fri Jul 24 19:39:57 IST 2020
Got our inserted row, replication is up and running now
Hope This Helps :
Regards
Sultan Khan
3 comments
Click here for commentsEasy steps to follow, thanks for such a post was able to configure the replication flawlessly
ReplyNice simple steps
ReplyKeep it up Sultan.. It's help for new folks are working on heterogenous replication enviroments.
ReplyConversionConversion EmoticonEmoticon