Vishal desai’s Oracle Blog

February 24, 2011

Golden Gate Bidirectional Replication Oracle to SQL Server example

Filed under: Golden Gate, Replication — vishaldesai @ 3:37 am

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

About these ads

7 Comments »

  1. I have a question about Flashback Data Archive tables in Goldengate replication. Are these tables usually excluded in an extract like “TABLEEXCLUDE .SYS_FBA_*”?
    What is the approach for replicating and initial load of Flash Back Data Archive tables?
    Here is my problem. I exported a user schema using Oracle DataPump, imported into destination database. GoldenGate abended saying some tables dont exist on dest. I checked tables and there were about 200 tables that were not exported because they are FBDA tables and DataPump just ignores them. So, I recreated them on source with scripts.
    So, how is this done? Source and destination have their own FBDA and these tables should not be neither recreated with scripts on dest or replicated? Or they have to be replicated with contents?

    Comment by Vladimir Grigorian — October 26, 2011 @ 1:49 am

  2. Hi Vladimir,

    expdp does not export SYS schema. Use rman restore and let Oracle manage FBDA.

    Metalink note 966212.1 describes what type of flashback operations are supported in goldengate.

    Thanks,
    Vishal

    Comment by vishaldesai — October 26, 2011 @ 9:31 pm

  3. Oracle11g to SQL Server 2008 bidirectional replication which version of GoldenGate is require ( in both server is running windows server)?
    i.e
    1. Virtual box 1: windows server 2008 running oracle 11g which GodenGate version is require?
    2. Virtual box 2: windows server 2008 running sql server 2008 which GoldenGate is require?

    Comment by Md Solaiman — November 24, 2011 @ 4:52 am

    • Golden Gate release 11.1.1.1.1

      Comment by vishaldesai — December 8, 2011 @ 11:52 pm

  4. I need to Replicate a Oracle(Linux) to MS-SQL 2008(Windows 2003) using goldengate what are the necessary softwares and where i’ve to install goldengate(both servers or a individual seperate machine).

    Comment by Pitchu — January 30, 2012 @ 6:22 pm

    • You can download golden gate from http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html for Oracle platform. For SQL Server you have to download golden gate from https://edelivery.oracle.com/. Yes you need to install golden gate on server running Oracle & SQL Server.

      Comment by vishaldesai — January 31, 2012 @ 1:10 am

      • Thanks vishal since i’m new to this…. i need some tutorial(basic simple table replication) on this do u have any basic tutorial link that explain very clearly the concept…… as well as i’ve one doubt we are going to install in both the machine whether we face any performance impact on live servers.

        Comment by Pitchu — January 31, 2012 @ 6:33 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Silver is the New Black Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: