In this blog I will try to show how to setup golden gate heterogenous bidirectional replication (Oracle 11g <-> SQL Server 2008).
Software Setup
Virtual Box 1: OEL 6.0, Oracle 11g, Golden Gate 11, Oracle Instance oralin11g
Virtual Box 2: Windows 2008 Server, SQL Server 2008, Golden Gate 11, SQL Server Instance MSSQLSERVER
Pre-requisites
Install golden gate and create sub directories.
Create golden gate schema in Oracle Database and grant required permissions.
Turn on database into archive log mode and supplemental logging.
Note: Below configuration is not from production environment. In actual setup you have to consider lot of other things such as performance, conflict resolution, test insert/update/delete, testing actual application workflows etc.
Configuration
-SQL Server Table structure.
--Run following in SQL Server Management studio.
USE [HR]
GO
/****** Object: Table [dbo].[emp1] Script Date: 02/22/2011 16:27:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [hruser].[emp1](
[id] [smallint] NOT NULL,
[fname] varchar(50) NOT NULL,
[lname] varchar(50) NOT NULL,
CONSTRAINT [PK_emp1] 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
-- Oracle Table structure
SQL> create table vdesai.emp1
(id number not null,
fname varchar2(50),
lname varchar2(50)) tablespace users;
Table created.
- Create ODBC connection as per documentation (Reference e17805 Page 23). I have created ODBC connection named ggsqlds. Verify ODBC connection using golden gate.
GGSCI (WIN-NE9G39A65OR) 2> dblogin sourcedb ggsqlds , userid sa , password vishal
Successfully logged into database.
GGSCI (WIN-NE9G39A65OR) 3> add trandata hruser.emp1
Logging of supplemental log data is enabled for table hruser.emp1
- Generate defination files for SQL Server 2008 & Oracle.
GGSCI (WIN-NE9G39A65OR) 8> edit params defgen
defsfile C:\app\Administrator\ggssql\dirdef\emp1.def
sourcedb ggsqlds, userid sa, password vishal
table hruser.emp1;
C:\app\Administrator\ggssql>defgen paramfile C:\app\Administrator\ggssql\dirprm\defgen.prm
C:\app\Administrator\ggssql>defgen paramfile C:\app\Administrator\ggssql\dirprm\defgen.prm
***********************************************************************
Oracle GoldenGate Table Definition Generator for ODBC
Version 11.1.1.0.0 Build 078
Windows x64 (optimized), Microsoft SQL Server on Jul 28 2010 15:01:58
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
Starting at 2011-02-22 16:44:51
***********************************************************************
Operating System Version:
Microsoft Windows Server 2008 R2 , on x64
Version 6.1 (Build 7600: )
Process id: 1284
***********************************************************************
** Running with the following parameters **
***********************************************************************
defsfile C:\app\Administrator\ggssql\dirdef\emp1.def
sourcedb ggsqlds, userid sa, password *********
table hruser.emp1;
Retrieving definition for HRUSER.EMP1
Definitions generated for 1 tables in C:\app\Administrator\ggssql\dirdef\emp1.def
GGSCI (linux6) 9> edit params defgen
defsfile /app/oracle/ggs/dirdef/emp1.def
USERID ggs_owner, PASSWORD vishal
table vdesai.emp1;
$ defgen paramfile /app/oracle/ggs/dirprm/defgen.prm
***********************************************************************
Oracle GoldenGate Table Definition Generator for Oracle
Version 11.1.1.0.0 Build 078
Linux, x64, 64bit (optimized), Oracle 11 on Jul 28 2010 13:15:49
Copyright (C) 1995, 2010, Oracle and/or its affiliates. All rights reserved.
Starting at 2011-02-23 19:54:24
***********************************************************************
Operating System Version:
Linux
Version #1 SMP Wed Feb 2 18:40:23 EST 2011, Release 2.6.32-100.28.5.el6.x86_64
Node: linux6
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 2927
***********************************************************************
** Running with the following parameters **
***********************************************************************
defsfile /app/oracle/ggs/dirdef/emp1.def
USERID ggs_owner, PASSWORD ******
table vdesai.emp1;
Retrieving definition for VDESAI.EMP1
2011-02-23 19:54:31 WARNING OGG-00869 No unique key is defined for table EMP1. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Definitions generated for 1 tables in /app/oracle/ggs/dirdef/emp1.def
- Copy linux emp1.def to windows c:\app\Administrator\ggssql\dirdef\emp1.def. Copy windows emp1.def to linux /app/oracle/ggs/dirdef/emp1.def. Make sure not to overwrite same file on source and target.
- For SQL Server 2008 set database option and take full backup
exec sp_dboption “HR”, “trunc. log on chkpt.”, “false”
Take full backup of database
- Setup Manager & Extract on Virtual box 2 (MSQSQLSERVER)
GGSCI (WIN-NE9G39A65OR) 3> edit param ./globals
MGRSERVNAME MGRSQLSERVER
GGSCI (WIN-NE9G39A65OR) 2> edit param mgr
PORT 7809
GGSCI (WIN-NE9G39A65OR) 9> ADD EXTRACT sqlext, TRANLOG, BEGIN NOW
EXTRACT added.
GGSCI (WIN-NE9G39A65OR) 10> ADD EXTTRAIL C:\app\Administrator\ggssql\dirdat\lt,EXTRACT sqlext
EXTTRAIL added.
GGSCI (WIN-NE9G39A65OR) 11> ADD EXTRACT sqldpump, EXTTRAILSOURCE C:\app\Administrator\ggssql\dirdat\lt
EXTRACT added.
GGSCI (WIN-NE9G39A65OR) 12> edit params sqlext
EXTRACT sqlext
sourcedb ggsqlds, userid sa, password vishal
TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
EXTTRAIL C:\app\Administrator\ggssql\dirdat\lt
TABLE hruser.emp1;
GGSCI (WIN-NE9G39A65OR) 13> ADD RMTTRAIL /app/oracle/ggs/dirdat/rt, EXTRACT sqldpump
RMTTRAIL added.
GGSCI (WIN-NE9G39A65OR) 14> EDIT PARAMS sqldpump
EXTRACT sqldpump
sourcedb ggsqlds, userid sa, password vishal
RMTHOST 192.168.56.101, MGRPORT 7809
RMTTRAIL /app/oracle/ggs/dirdat/rt
PASSTHRU
TABLE hruser.emp1;
- Setup Replicat on Virtual Box 1 (oralin11g)
GGSCI (linux6) 10> ADD REPLICAT orarep, EXTTRAIL /app/oracle/ggs/dirdat/rt
REPLICAT added.
GGSCI (linux6) 11> edit params orarep
REPLICAT orarep
sourcedefs /app/oracle/ggs/dirdef/emp1.def
USERID ggs_owner, PASSWORD vishal
MAP hruser.emp1, TARGET vdesai.emp1 ;
-Setup Extract on Virtual Box 1 (oralin11g)
GGSCI (linux6) 12> ADD EXTRACT oraext, TRANLOG, BEGIN NOW
EXTRACT added.
GGSCI (linux6) 13> ADD EXTTRAIL /app/oracle/ggs/dirdat/lt, EXTRACT oraext
EXTTRAIL added.
GGSCI (linux6) 14> ADD EXTRACT oradpump, EXTTRAILSOURCE /app/oracle/ggs/dirdat/ltEXTRACT added.
GGSCI (linux6) 15> edit params oraext
EXTRACT oraext
USERID ggs_owner, PASSWORD vishal
TRANLOGOPTIONS EXCLUDEUSER ggs_owner
EXTTRAIL /app/oracle/ggs/dirdat/lt
TABLE vdesai.emp1 KEYCOLS ID;
GGSCI (linux6) 16> ADD RMTTRAIL C:\app\Administrator\ggssql\dirdat\rt, EXTRACT oradpump
RMTTRAIL added.
GGSCI (linux6) 17> EDIT PARAMS oradpump
EXTRACT oradpump
userid ggs_owner, password vishal
RMTHOST 192.168.56.103, MGRPORT 7809
RMTTRAIL C:\app\Administrator\ggssql\dirdat\rt
PASSTHRU
TABLE vdesai.emp1;
- Create checkpoint table on Virtual Box 2 (MSSQLSERVER) and setup Replicat.
GGSCI (WIN-NE9G39A65OR) 10> dblogin sourcedb ggsqlds, userid sa, password vishal
Successfully logged into database.
GGSCI (WIN-NE9G39A65OR) 11> ADD CHECKPOINTTABLE hruser.chkptab
Successfully created checkpoint table HRUSER.CHKPTAB.
GGSCI (WIN-NE9G39A65OR) 15> ADD REPLICAT sqlrep, EXTTRAIL C:\app\Administrator\ggssql\dirdat\rt CHECKPOINTTABLE HRUSER.CHKPTAB
REPLICAT added.
GGSCI (WIN-NE9G39A65OR) 16> edit params sqlrep
REPLICAT sqlrep
sourcedefs C:\app\Administrator\ggssql\dirdef\emp1.def
sourcedb ggsqlds, userid sa, password vishal
MAP vdesai.emp1, TARGET hruser.emp1;
- Start Extract, pump and Replicat processes.
GGSCI (WIN-NE9G39A65OR) 17> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED SQLDPUMP 00:00:00 00:06:38
EXTRACT STOPPED SQLEXT 00:00:00 00:07:10
REPLICAT STOPPED SQLREP 00:00:00 00:00:59
GGSCI (WIN-NE9G39A65OR) 18> start extract sqlext
Sending START request to MANAGER ('MGRSQLSERVER') ...
EXTRACT SQLEXT starting
GGSCI (WIN-NE9G39A65OR) 19> start extract sqldpump
Sending START request to MANAGER ('MGRSQLSERVER') ...
EXTRACT SQLDPUMP starting
GGSCI (WIN-NE9G39A65OR) 20> start replicat sqlrep
Sending START request to MANAGER ('MGRSQLSERVER') ...
REPLICAT SQLREP starting
GGSCI (WIN-NE9G39A65OR) 21> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING SQLDPUMP 00:00:00 00:07:02
EXTRACT RUNNING SQLEXT 00:00:00 00:00:05
REPLICAT RUNNING SQLREP 00:00:00 00:01:24
GGSCI (linux6) 21> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED ORADPUMP 00:00:00 00:03:59
EXTRACT STOPPED ORAEXT 00:00:00 00:04:22
REPLICAT STOPPED ORAREP 00:00:00 00:05:08
GGSCI (linux6) 22> start extract oraext
Sending START request to MANAGER ...
EXTRACT ORAEXT starting
GGSCI (linux6) 23> start extract oradpump
Sending START request to MANAGER ...
EXTRACT ORADPUMP starting
GGSCI (linux6) 24> start replicat orarep
Sending START request to MANAGER ...
REPLICAT ORAREP starting
GGSCI (linux6) 25> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ORADPUMP 00:00:00 00:04:15
EXTRACT RUNNING ORAEXT 00:00:00 00:00:01
REPLICAT RUNNING ORAREP 00:00:00 00:05:24
- Testing
-- SQL Server
begin tran
insert into hruser.emp1 values (1,'Vishal','Desai')
commit tran
-- Oracle
SQL> select * from vdesai.emp1;
ID FNAME LNAME
---------- -------------------- --------------------
1 Vishal Desai
SQL> insert into vdesai.emp1 values (11,'John','Mako');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from vdesai.emp1;
ID FNAME LNAME
---------- -------------------- --------------------
11 John Mako
1 Vishal Desai
- SQL Server from SQL Management Studio.
select * from hruser.emp1
id fname lname
1 Vishal Desai
11 John Mako
