OGG enables Oracle-to-SQL Server 2005 synchronization

Source: Internet
Author: User
Tags one table table definition windows x64

One, source-side (Oracle) configuration
1. Create a synchronization test table
CREATE TABLE gg_user.t01 (name varchar (primary key));
CREATE TABLE gg_user.t02 (ID int primary key,name varchar (20));
2. Add the definition file (is the transfer between heterogeneous, need to convert the field type and other processing needs to Defgen tool generation definition file)
Ggsci (kermart) 4> edit params Defgen
Defsfile D:\ggate\dirdef\t01.def
UserID Gg_user,password Oracle
Table gg_user.t01;
Table gg_user.t02;
Note: If you have more than one table, you need to list each of them, if the user all the tables need to be synchronized, directly on the table gg_user.*;
If it is not listed, the replication process startup will report the following error:
ERROR OGG-00423 Oracle GoldenGate Delivery for SQL Server, RPL01.prm:Could not find definition for gg_user. T02.
ERROR OGG-01668 Oracle GoldenGate Delivery for SQL Server, RPL01.prm:PROCESS abending.
Build definition File
D:\ggate>defgen.exe Paramfile D:\GGATE\DIRPRM\DEFGEN.PRM

***********************************************************************
Oracle GoldenGate Table Definition Generator for Oracle
Version 11.2.1.0.1 oggcore_11.2.1.0.1_platforms_120423.0230
Windows x64 (optimized), Oracle 11g on APR 23 2012 05:48:41

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

Starting at 2014-04-24 16:43:37
***********************************************************************

Operating System Version:
Microsoft Windows 7, on x64
Version 6.1 (Build 7601:service Pack 1)

Process id:6828

***********************************************************************
* * Running with the following parameters * *
***********************************************************************
Defsfile D:\ggate\dirdef\t01.def
UserID Gg_user,password ******
Table gg_user.t;
Retrieving definition for Gg_user. T

Definitions generated for 1 table in D:\ggate\dirdef\t01.def
Copy the t01.def to the Dirdef directory under the SQL Server Ogg.
3. Add a supplemental log
Ggsci (Kermart) 7> dblogin userid Gg_user,password Oracle
Ggsci (Kermart) 8> add Trandata gg_user.t01
Ggsci (Kermart) 8> add Trandata gg_user.t02

4. Adding the extraction process
Ggsci (kermart) 5> edit params ext01
Extract ext01
UserID Gg_user,password Oracle
Exttrail D:\ggate\dirdat\et
Dynamicresolution
Gettruncates
TABLE gg_user.t01;
TABLE gg_user.t02;

Ggsci (Kermart) 1> Add extract Ext01,tranlog,begin now
Ggsci (Kermart) 2> add Exttrail d:\ggate\dirdat\et,extract ext01

5. Add a delivery process
Ggsci (kermart) 6> edit params pump01
Extract PUMP01
UserID Gg_user,password Oracle
Rmthost 127.0.0.1,mgrport 7810
Rmttrail E:\ggate\dirdat\rt
PASSTHRU
TABLE gg_user.t01;
TABLE gg_user.t02;

Ggsci (Kermart) 5> Add extract Pump01,exttrailsource D:\ggate\dirdat\et,begin now
Ggsci (Kermart) 6> add Rmttrail e:\ggate\dirdat\rt,extract pump01

Ii. target-side (SQL Server) configuration
1. Configure an ODBC data source
Control Panel-Administrative Tools-Data source (ODBC), add system DNS, named test01, note Select driver type for SQL Server Native Client 10.0
2. Create a test table (the structure is consistent with the source side)
CREATE TABLE hjj.t01 (name varchar (primary key));
CREATE TABLE hjj.t02 (ID int primary key,name varchar (20));
3. Add checkpointtable
Ggsci (kermart) 10> edit param./globals
Checkpointtable HJJ.CKP
Ggsci (Kermart) 8> dblogin sourcedb t01 userid sa password SA
Ggsci (Kermart) 9> add checkpointtable HJJ.CKP
3. Adding a replication process
Ggsci (kermart) 58> edit param rpl01
Replicat rpl01
Sourcedefs E:\ggate\dirdef\t01.def
Targetdb t01 userid SA, password SA
Reperror Default,discard
Discardfile E:\GGATE\DIRRPT\RPL.DSC Append
Gettruncates
MAP gg_user.t01, TARGET hjj.t01;
MAP gg_user.t02, TARGET hjj.t02;

Ggsci (Kermart) 12> add replicat rpl01,exttrail e:\ggate\dirdat\rt,begin now,checkpointtable HJJ.CKP

Third, testing
1. Start the process
SOURCE side:
Start ext01
Start PUMP01
Ggsci (kermart) 9> info All

Program Status Group lags at chkpt time Since chkpt

MANAGER RUNNING
EXTRACT STOPPED EXDP 00:00:00 16:40:29
EXTRACT STOPPED Exora 00:00:00 16:40:32
EXTRACT RUNNING EXT01 00:00:00 00:00:09
EXTRACT STOPPED EXT1 00:00:00 162:50:03
EXTRACT RUNNING PUMP01 00:00:00 00:00:05
EXTRACT STOPPED PUMP1 00:00:00 162:49:59
Replicat STOPPED msrep 00:00:00 187:22:15
Replicat STOPPED REP1 00:00:00 163:47:29
Target side:
Start RPL01
Ggsci (kermart) 59> info All

Program Status Group lags at chkpt time Since chkpt

MANAGER RUNNING
EXTRACT STOPPED EXT1 00:00:00 163:48:28
EXTRACT STOPPED msext 00:00:00 187:23:13
EXTRACT STOPPED PUMP1 00:00:00 163:48:25
Replicat abended orarep 00:00:00 16:57:48
Replicat STOPPED REP1 00:00:00 162:49:46
Replicat RUNNING RPL01 00:00:00 00:00:04
2. DML operations on the source side (Oracle)
sql> INSERT INTO t01 select ' Lyn ' | | RowNum from dual connect by level<=100;

100 rows have been created.

Sql> commit;

Submit complete.

sql> INSERT INTO t02 select RowNum, ' Moon ' | | RowNum from dual connect by level<=100;

100 rows have been created.

Sql> commit;

Submit complete.

Sql> Select COUNT (*) from t01;

COUNT (*)
----------
100

Sql> Select COUNT (*) from t02;

COUNT (*)
----------
100
3. View data synchronization replication on the target side (SQL Server)
C:\>sqlcmd-s kermart-u sa-p sa-d TEST
1> Select COUNT (*) from hjj.t01;
2> Go

-----------
100

(1 rows affected)
1> Select COUNT (*) from hjj.t02
2> Go

-----------
100

(1 rows affected)

OGG enables Oracle-to-SQL Server 2005 synchronization

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.