News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Using GoldenGate for real time data integration – SQL Server to Oracle 11g

  • Posted by Gavin Soorma
  • On June 20, 2010
  • 7 Comments
  • extract, GoldenGate, replicat, sql server

I would like to share a simple test case which explains how we can use GoldenGate to replicate data between a Microsoft SQL Server 2005 source and an Oracle 11g target database on a Red Hat Linux platform.

We can use a number of third party tools as well as Oracle’s SQL Developer to generate scripts to convert SQL Server DDL into Oracle compliant DDL – I will try and cover this conversion aspect in a future post.

In this case, I have created the table in the Oracle 11g database using the following CREATE TABLE statement. Note that while the column names are the same, the data types are different and to cater for this difference in the data types in both databases, we have to create a data definitions file.

Let as assume we have a DEPT table in the AdventureWorks database in the HumanResources schema.

The structure of the table in SQL Server 2005 is as follows:

CREATE TABLE [HumanResources].[dept](
[DepartmentID] [smallint] NOT NULL,
[Name] [dbo].[Name] NOT NULL,
[GroupName] [dbo].[Name] NOT NULL,
[ModifiedDate] [datetime] NOT NULL CONSTRAINT [DF_Department_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_Dept_DepartmentID] PRIMARY KEY CLUSTERED
(
[DepartmentID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Let us now create the table in the GGS_OWNER schema in the target 11g database with the same structure.

Create the table in Oracle 11g database

SQL>CREATE TABLE dept
(departmentid number not null,
name varchar2(50),
groupname varchar2(50),
modifieddate date default sysdate)

SQL> /

Table created.

SQL> alter table dept add constraint
2 pk_dept primary key (departmentid);

Table altered.

We now enable additional logging for the DEPT table via the ADD TRANDATA command.

GGSCI (Dell-PC) 1> dblogin sourcedb sql2005
Successfully logged into database.

GGSCI (Dell-PC) 2> add trandata humanresources.dept

Logging of supplemental log data is enabled for table HumanResources.dept

Because the data types differ in SQL Server with Oracle, we need to create a Data Definition file using the defgen utility as shown below.

GGSCI (Dell-PC)> edit params defgen

defsfile d:\goldengate\dirdef\dept.def
sourcedb sql2005
table humanresources.dept;

D:\goldengate>defgen paramfile d:\goldengate\dirprm\defgen.prm

***********************************************************************
Oracle GoldenGate Table Definition Generator for ODBC
Version 10.4.0.19 Build 002
Windows x64 (optimized), Microsoft SQL Server on Sep 21 2009 09:40:36

Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.

Starting at 2010-06-20 14:48:46
***********************************************************************

Operating System Version:
Microsoft Windows 7 , on x64
Version 6.1 (Build 7600: )

Process id: 7900

***********************************************************************
** Running with the following parameters **
***********************************************************************
defsfile d:\goldengate\dirdef\dept.def
sourcedb sql2005
table humanresources.dept;
Retrieving definition for HUMANRESOURCES.DEPT

Definitions generated for 1 tables in d:\goldengate\dirdef\dept.def

We will now FTP or SCP this data definition file which was generated to the following directory on the target Linux machine.

Now FTP the file to the Linux machine in the GoldenGate directory

/home/oracle/goldengate/dirdef

We will now create the initial data extract process – initext

GGSCI (Dell-PC) 3> edit params initext

SOURCEISTABLE
SOURCEDB SQL2005
RMTHOST 192.168.10.94, MGRPORT 7809
RMTFILE /home/oracle/goldengate/dirdat/ex
TABLE humanresources.dept;

On the target, we will create the initial data load process – initrep

Note that since this is a one time operation we are using the keyword SPECIALRUN. We also include the keyword SOURCEDEFS to specify the data definitions file location – this is the file we had generated on the Windows source and had copied to the Linux target.

GGSCI (linux01.oncalldba.com) 2> edit params initrep

SPECIALRUN
END RUNTIME
USERID ggs_owner, PASSWORD ggs_owner
EXTFILE /home/oracle/goldengate/dirdat/ex
sourcedefs /home/oracle/goldengate/dirdef/dept.def
MAP humanresources.dept, TARGET ggs_owner.dept ;

Start the initial load job from the Windows command line in the GoldenGate directory

D:\goldengate>extract paramfile dirprm\initext.prm reportfile dirrpt\initext.rpt

***********************************************************************
Oracle GoldenGate Capture for ODBC
Version 10.4.0.19 Build 002
Windows x64 (optimized), Microsoft SQL Server on Sep 21 2009 09:42:03

Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.

Starting at 2010-06-20 15:07:55
***********************************************************************

Operating System Version:
Microsoft Windows 7 , on x64
Version 6.1 (Build 7600: )

Process id: 7204

Description:

***********************************************************************
** Running with the following parameters **
***********************************************************************
SOURCEISTABLE

2010-06-20 15:07:55 GGS INFO 414 Wildcard resolution set to IMMEDIATE b
ecause SOURCEISTABLE is used.
SOURCEDB SQL2005
RMTHOST 192.168.10.94, MGRPORT 7809
RMTFILE /home/oracle/goldengate/dirdat/ex
TABLE humanresources.dept;
Using the following key columns for source table HUMANRESOURCES.DEPT: Department
ID.

CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 4G
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 4.77G
CACHESIZEMAX (strict force to disk): 4.57G

Database Version:
Microsoft SQL Server
Version 09.00.4035
ODBC Version 03.80.0000

Driver Information:
SQLNCLI.DLL
Version 09.00.4035
ODBC Version 03.52

Database Language and Character Set:

Warning: Unable to determine the application and database codepage settings.
Please refer to user manual for more information.

2010-06-20 15:07:56 GGS INFO Z0-05M Output file /home/oracle/goldengate/di
rdat/ex is using format RELEASE 10.4.

2010-06-20 15:08:01 GGS INFO 406 Socket buffer size set to 27985 (flush
size 27985).

Processing table HUMANRESOURCES.DEPT

***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************

Report at 2010-06-20 15:08:01 (activity since 2010-06-20 15:07:56)

Output to /home/oracle/goldengate/dirdat/ex:

From Table HUMANRESOURCES.DEPT:
# inserts: 5
# updates: 0
# deletes: 0
# discards: 0

Start the initial replicat process on the Linux machine

[oracle@linux01 goldengate]$ ./replicat paramfile dirprm/initrep.prm
***********************************************************************
Oracle GoldenGate Delivery for Oracle
Version 10.4.0.19 Build 002
Linux, x86, 32bit (optimized), Oracle 11 on Sep 29 2009 09:00:07

Copyright (C) 1995, 2009, Oracle and/or its affiliates. All rights reserved.

Starting at 2010-06-20 15:10:06
***********************************************************************

Operating System Version:
Linux
Version #1 SMP Mon Mar 29 20:19:03 EDT 2010, Release 2.6.18-194.el5PAE
Node: linux01.oncalldba.com
Machine: i686
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited

Process id: 22748

Description:

***********************************************************************
** Running with the following parameters **
***********************************************************************
SPECIALRUN
END RUNTIME
USERID ggs_owner, PASSWORD *********
EXTFILE /home/oracle/goldengate/dirdat/ex
sourcedefs /home/oracle/goldengate/dirdef/dept.def
MAP humanresources.dept, TARGET ggs_owner.dept ;

CACHEMGR virtual memory values (may have been adjusted)
CACHEBUFFERSIZE: 64K
CACHESIZE: 512M
CACHEBUFFERSIZE (soft max): 4M
CACHEPAGEOUTSIZE (normal): 4M
PROCESS VM AVAIL FROM OS (min): 1G
CACHESIZEMAX (strict force to disk): 881M

Database Version:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
PL/SQL Release 11.1.0.6.0 – Production
CORE 11.1.0.6.0 Production
TNS for Linux: Version 11.1.0.6.0 – Production
NLSRTL Version 11.1.0.6.0 – Production

Database Language and Character Set:
NLS_LANG environment variable not set, using default value AMERICAN_AMERICA.US7ASCII.
NLS_LANGUAGE = “AMERICAN”
NLS_TERRITORY = “AMERICA”
NLS_CHARACTERSET = “AL32UTF8”

Warning: NLS_LANG is not set. Please refer to user manual for more information.
Opened trail file /home/oracle/goldengate/dirdat/ex at 2010-06-20 15:10:06

2010-06-20 15:10:06 GGS INFO 379 Positioning with begin time: Jan 1, 1970 12:00:00 AM, starting record time: Jun 20, 2010 3:20:57 PM at extrba 807.

***********************************************************************
** Run Time Messages **
***********************************************************************

Opened trail file /home/oracle/goldengate/dirdat/ex at 2010-06-20 15:10:06

MAP resolved (entry HUMANRESOURCES.DEPT):
MAP HUMANRESOURCES.DEPT, TARGET ggs_owner.dept ;
Using following columns in default map by name:
DEPARTMENTID, NAME, GROUPNAME, MODIFIEDDATE

Using the following key columns for target table GGS_OWNER.DEPT: DEPARTMENTID.

***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************

Last record for the last committed transaction is the following:
___________________________________________________________________
Trail name : /home/oracle/goldengate/dirdat/ex
Hdr-Ind : E (x45) Partition : . (x04)
UndoFlag : . (x00) BeforeAfter: A (x41)
RecLength : 133 (x0085) IO Time : 2010-06-20 15:07:35.802212
IOType : 5 (x05) OrigNode : 255 (xff)
TransInd : . (x03) FormatType : R (x52)
SyskeyLen : 0 (x00) Incomplete : . (x00)
AuditRBA : 0 AuditPos : 0
Continued : N (x00) RecCount : 1 (x01)

2010-06-20 15:07:35.802212 Insert Len 133 RBA 1517
Name: HUMANRESOURCES.DEPT
___________________________________________________________________

Reading /home/oracle/goldengate/dirdat/ex, current RBA 1720, 5 records

Report at 2010-06-20 15:10:07 (activity since 2010-06-20 15:10:07)

From Table HUMANRESOURCES.DEPT to GGS_OWNER.DEPT:
# inserts: 5
# updates: 0
# deletes: 0
# discards: 0

Last log location read:
FILE: /home/oracle/goldengate/dirdat/ex
RBA: 1720
TIMESTAMP: 2010-06-20 15:07:35.802212
EOF: NO
READERR: 400

We will now connect as ggs_owner in the target Oracle database and we can find that there are now 5 rows in tge DEPT table.

SQL> select * from dept;

DEPARTMENTID NAME                 GROUPNAME            MODIFIEDD
------------ -------------------- -------------------- ---------
           1 Sales                Marketing            20-JUN-10
           2 Networks             IT Infrastructure    20-JUN-10
           3 Help Desk            IT Support           20-JUN-10
           4 DBA Oracle           IT Infrastructure    20-JUN-10
           5 Unix System Admin    IT Infrastructure    20-JUN-10

Now that we have configured the initial data load, we can create the extract and replicat process to enable online change synchronization.

Create the Extract process on Source (Windows)

GGSCI (Dell-PC) 2> ADD EXTRACT myext, TRANLOG, BEGIN NOW
EXTRACT added.

GGSCI (Dell-PC) 3> ADD RMTTRAIL /home/oracle/goldengate/dirdat/my, EXTRACT myex

RMTTRAIL added.

GGSCI (Dell-PC) 6> edit params myext

EXTRACT myext
sourcedb sql2005
TRANLOGOPTIONS MANAGESECONDARYTRUNCATIONPOINT
RMTHOST 192.168.10.94, MGRPORT 7809
RMTTRAIL /home/oracle/goldengate/dirdat/my

Create the Replicat process on Target (Linux)

GGSCI (linux01.oncalldba.com) 1> ADD REPLICAT myrep, EXTTRAIL /home/oracle/goldengate/dirdat/my
REPLICAT added.

GGSCI (linux01.oncalldba.com) 4> edit params myrep

REPLICAT myrep
sourcedefs /home/oracle/goldengate/dirdef/dept.def
USERID ggs_owner, PASSWORD ggs_owner
MAP humanresources.dept, TARGET ggs_owner.dept ;

Start the Extract on Source

GGSCI (Dell-PC) 7> start extract myext

Sending START request to MANAGER (‘GGSMGR’) …
EXTRACT MYEXT starting

GGSCI (Dell-PC) 8> info extract myext
EXTRACT MYEXT Last Started 2010-06-20 16:17 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:05:49 ago)
VAM Read Checkpoint 2010-06-20 16:11:45.664000

Start the Replicat on Target

GGSCI (linux01.oncalldba.com) 5> start replicat myrep

Sending START request to MANAGER …
REPLICAT MYREP starting

GGSCI (linux01.oncalldba.com) 6> info replicat myrep

REPLICAT MYREP Last Started 2010-06-20 16:17 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:05 ago)
Log Read Checkpoint File /home/oracle/goldengate/dirdat/my000000
First Record RBA 825

On Source SQL Server insert two more rows

BEGIN TRAN
INSERT INTO HUMANRESOURCES.DEPT
(DEPARTMENTID,NAME,GROUPNAME,MODIFIEDDATE)
VALUES
(6,’Enterprise Monitoring’,’Operations’,’20-JUN-2010′)
COMMIT tran

BEGIN TRAN
INSERT INTO HUMANRESOURCES.DEPT
(DEPARTMENTID,NAME,GROUPNAME,MODIFIEDDATE)
VALUES
(7,’PC Support’,’I.T Support’,’20-JUN-2010′)
COMMIT tran

We now see that the Extract process on the Windows machine has extracted these two inserts as well

GGSCI (Dell-PC) 10> stats extract myext

Sending STATS request to EXTRACT MYEXT ...

Start of Statistics at 2010-06-20 16:22:08.

Output to /home/oracle/goldengate/dirdat/my:

Extracting from HUMANRESOURCES.DEPT to HUMANRESOURCES.DEPT:

*** Total statistics since 2010-06-20 16:19:50 ***
        Total inserts                                2.00
        Total updates                                0.00
        Total deletes                                0.00
        Total discards                               0.00
        Total operations                             2.00

On Target Oracle 11g, we will see that these two records have been inserted in the table!

SQL> select count(*) from dept;

  COUNT(*)
----------
         7

SQL> select * from dept;

DEPARTMENTID NAME                 GROUPNAME            MODIFIEDD
------------ -------------------- -------------------- ---------
           1 Sales                Marketing            20-JUN-10
           2 Networks             IT Infrastructure    20-JUN-10
           3 Help Desk            IT Support           20-JUN-10
           4 DBA Oracle           IT Infrastructure    20-JUN-10
           5 Unix System Admin    IT Infrastructure    20-JUN-10
           6 Enterprise Monitorin Operations           20-JUN-10
             g

           7 PC Support           I.T Support          20-JUN-10

7 rows selected.
 

7 Comments

Vijay
  • Jul 19 2010
good article ...Keep going
Venkat
  • Jul 26 2010
Good articile. thanks for sharing your exp and knowledge. Really appriciate.
Gavin Soorma
  • Jul 28 2010
Hi Kim - can you try this on any other database other than MASTER - also confirm that the recovery model of the database is Full - take a backup of the database first ... Read the document GoldenGate Microsoft SQL Server Installation and Setup Guide It has more details on what's required to set up GoldenGate on SQL Server ... "The SQL Server database must be set to the full recovery model, and at least one full database backup must be done before GoldenGate"
Rahul
  • Jan 17 2011
Hi Please help!!!! I'm getting the below error: GGSCI (iterate) 2> add trandata dbo.rahul 2011-01-18 07:02:17 WARNING OGG-00552 Database operation failed: SQLExecDirect error: if not exists ( SELECT * FROM master.dbo.sysdatabases WHERE name = N'AutoVikasDms' collate database_default AND (category & 1) = 1)be gin exec master..sp_replicationdboption @dbname = N'AutoVikasDms' , @optna me = N'publish' , @value = N'true' end if not exists (select * from syspublications where name = N'GoldenGate AutoVikas Dms Publisher') begin exec sp_addpublication @publication = N'GoldenGate AutoVikasDms Publisher', @des cription = N'GoldenGate Publisher for [AutoVikasDms] Database', @sync_method = N 'native', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_a nonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolde r = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anony mous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N 'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'true', @enabled_for_p2p = N'false', @enable d_for_het_sub = N'false' end. ODBC error: SQLSTATE 37000 native database error 20028. [Microsoft][SQL Nat ive Client][SQL Server]The Distributor has not been installed correctly. Could n ot enable database for publishing. 2011-01-18 07:02:17 WARNING OGG-00782 Error in changing transaction logging fo r table: 'dbo.Rahul'. ERROR: ODBC Error occurred. See event log for details.. GGSCI (iterate) 3> SQL Server 2005 - SP4 Regards Rahul
phil
  • Feb 9 2011
I do not suppose you have done this the other way around , Oracle to SQLServer I am trying to do just that and cannot get my replicat to connect to the target db C:\temp\GoldenGateMSTarget>replicat paramfile C:\temp\GoldenGateMSTarget\dirprm\ initrep.prm gives the error 2011-02-09 13:46:35 ERROR OGG-01557 OLE DB Error: Cannot open data source. E rror code 0x80040e73 Detail: OLE DB: GetDataSource. initrep.prm SPECIALRUN END RUNTIME sourcedb pktest3 EXTFILE C:\temp\GoldenGateMSTarget\dirdat\ex sourcedefs C:\temp\GoldenGateMSTarget\dirdef\tcustmer.def MAP schema1.tcustmer, TARGET schema1.tcustmer; any help would be appreciated
phil
  • Feb 9 2011
no sooner had I written this and I figured it out. ODBC incorrectly set up !
Lalit
  • Feb 16 2011
Hi, This is really wonderful article. I was looking for something like this. Thanks for the post. Regards Lalit

Leave Reply

Your email address will not be published. Required fields are marked *