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