This note describes the process of
configuring a initial data load job as well as Change Data Capture from an
Oracle 12c pluggable database source and a MS SQL Server 2012 target database.
It assumes the GoldenGate user has
been created in the Oracle 12c source database and other setup tasks like
enabling supplemental logging in the database have been completed as well.
·
Downloaded from
OTN and unzipped on the SQL Server 2012 box
121201_ggs_Windows_x64_MSSQL_64bit.zip
·
From the
unzipped location ran ggsci
·
Create the GLOBALS file
Add the following line
MGRSERVNAME
MANAGER
Save the file.
The file is saved automatically with the name
GLOBALS, without a file extension
·
Create the Manager service
D:\software\goldengate12c>install ADDSERVICE USER CORP.DOMAIN\gavin PASSWORD xxx
Service
'MANAGER' created.
·
Check the status of the manager service
D:\software\goldengate12c>ggsci
Oracle
GoldenGate Command Interpreter for SQL Server
Version
12.1.2.0.1 17597485 OGGCORE_12.1.2.0.T2_PLATFORMS_131206.0309
Windows
x64 (optimized), Microsoft SQL Server on Dec
6 2013 12:06:08
Operating
system character set identified as windows-1252.
Copyright
(C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
GGSCI
(kensmssql001uat) 1> info all
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI
(kensmssql001uat) 3> EDIT PARAMS mgr
GGSCI
(kensmssql001uat) 4>
PORT
7809
GGSCI
(kensmssql001uat) 8> START MANAGER
Starting
Manager as service ('MANAGER')...
Service
started.
GGSCI
(kensmssql001uat) 9> INFO ALL
Program Status
Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
·
Downloaded and installed Oracle Data Access
Components
ODAC1120320_x64
·
Create the data source via ODBC Data Source
Administrator (launched
from the ODAC Oracle Client Home)
For login authentication, select With Integrated Windows Authentication for
Oracle GoldenGate to use Windows authentication, or select With SQL Server authentication using a login ID and
password entered by the user for Oracle GoldenGate to use database
credentials
·
Test the data source by logging in via
Oracle GoldenGate
GGSCI
(kensmssql001uat) 10> DBLOGIN
SOURCEDB sqlserver2012
2014-01-30
08:28:05 INFO OGG-03036
Database character set identified as win
dows-1252.
Locale: en_US.
2014-01-30
08:28:05 INFO OGG-03037
Session character set identified as wind
ows-1252.
Successfully
logged into database.
·
Create the definitions file using the
defgen utility
[oracle@orasql-001-dev
goldengate]$ ./ggsci
Oracle
GoldenGate Command Interpreter for Oracle
Version
12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux,
x64, 64bit (optimized), Oracle 12c on Sep 25 2013 02:33:54
Operating
system character set identified as UTF-8.
Copyright
(C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
GGSCI
(orasql-001-deV) 1> EDIT PARAM defgen
DEFSFILE
./dirdat/source.def,
USERIDALIAS
gg_sales
TABLE
sales.sh.customers;
[oracle@orasql-001-dev
goldengate]$ ./defgen paramfile
dirprm/defgen.prm
***********************************************************************
Oracle GoldenGate Table Definition
Generator for Oracle
Version 12.1.2.0.0 17185003
OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316
Linux, x64, 64bit (optimized), Oracle 12c on
Sep 24 2013 16:23:06
Copyright
(C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
Starting at 2014-01-30
11:36:28
***********************************************************************
Operating
System Version:
Linux
Version
#1 SMP Wed Aug 28 09:09:20 PDT 2013, Release 2.6.32-400.29.3.el5uek
Node:
orasql-001-deV
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: 32174
***********************************************************************
** Running with the following
parameters **
***********************************************************************
DEFSFILE
./dirdat/source.def,
USERIDALIAS
gg_sales
TABLE
sales.sh.myobjects;
Retrieving
definition for SALES.SH.MYOBJECTS.
Definitions
generated for 1 table in ./dirdat/source.def.
[oracle@orasql-001-dev
goldengate]$
·
SCP or FTP the source.def file to the
Windows Server hosting SQL Server 2012 instance
Copy it in
the dirdef subdirectory in the GoldenGate software installation home directory
·
Create the table in SQL server
USE [AdventureWorks]
GO
/******
Object: Table [Person].[myobjects] Script Date: 4/02/2014 9:04:24 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [SCOTT].[myobjects](
[OBJECT_ID] [int] NOT NULL,
[OBJECT_NAME] [nvarchar](20) NULL,
[OBJECT_TYPE] [nvarchar](40) NULL
) ON [PRIMARY]
GO
·
Create the Initial Load
Extract
GGSCI
(orasql-001-deV) 2> DBLOGIN
USERIDALIAS gg_root
Successfully
logged into database CDB$ROOT.
GGSCI
(orasql-001-deV) 1> ADD EXTRACT eini1
SOURCEISTABLE
EXTRACT
added.
GGSCI
(orasql-001-deV) 2> EDIT PARAMS eini1
EXTRACT
eini1
USERIDALIAS
gg_root
RMTHOST
kensmssql001uat, MGRPORT 7809
RMTTASK
REPLICAT, GROUP rini1
TABLE
sales.sh.customers;
·
Create the Initial Load Replicat job
GGSCI
(kensmssql001uat) 11> ADD REPLICAT
rini1 SPECIALRUN
REPLICAT
added.
GGSCI
(kensmssql001uat) 12> EDIT PARAMS
rini1
GGSCI
(kensmssql001uat) 13> VIEW PARAMS
rini1
REPLICAT
rini1
TARGETDB
sqlserver2012
SOURCEDEFS
./dirdef/source.def
MAP
sales.sh.customers, TARGET person.customers;
·
Start the Initial Load Extract
GGSCI
(orasql-001-deV) 2> start extract
eini1
Sending
START request to MANAGER ...
EXTRACT
EINI1 starting
GGSCI
(orasql-001-deV) 3> info extract
eini1
EXTRACT EINI1
Last Started 2014-01-30 11:40
Status RUNNING
Checkpoint
Lag Not Available
Process
ID 32456
Log
Read Checkpoint Table SALES.SH.MYOBJECTS
2014-01-30 11:40:42 Record 1
Task SOURCEISTABLE
GGSCI
(orasql-001-deV) 11> info extract
eini1
EXTRACT EINI1
Last Started 2014-01-30 11:40
Status STOPPED
Checkpoint
Lag Not Available
Log
Read Checkpoint Table SALES.SH.MYOBJECTS
2014-01-30 11:41:41 Record 77695
Task SOURCEISTABLE
·
On the SQL Server 2012 database confirm the initial load to the
MYOBJECTS table has completed
·
Configure Change Data Capture
Add
supplemental logging at the table level and create the integrated extract.
Note – for
12c Multitenant Container databases we have to use Integrated Extracts in place
of Classic Extracts
To add
trandata we need to be connected to the individual pluggable database and not
the root container. In this case the PDB is SALES.
GGSCI
(orasql-001-deV) 4> dblogin
useridalias gg_sales
Successfully
logged into database SALES.
GGSCI
(orasql-001-deV) 5> add trandata
sales.sh.myobjects
Logging
of supplemental redo data enabled for table SALES.SH.MYOBJECTS.
TRANDATA
for scheduling columns has been added on table 'SALES.SH.MYOBJECTS'.
GGSCI
(orasql-001-deV) 7> dblogin
useridalias gg_root
Successfully
logged into database CDB$ROOT.
GGSCI
(orasql-001-deV) 8> register extract eora1 database container (sales)
Extract
EORA1 successfully registered with database at SCN 4859756.
GGSCI
(orasql-001-deV) 9> ADD EXTRACT eora1
INTEGRATED TRANLOG BEGIN NOW
EXTRACT
added.
GGSCI
(orasql-001-deV) 11> edit params
eora1
EXTRACT
eora1
USERIDALIAS
gg_root
RMTHOST
kensmssql001uat, MGRPORT 7809
RMTTRAIL
./dirdat/ab
TABLE
sales.sh.myobjects;
GGSCI
(orasql-001-deV) 12> ADD RMTTRAIL
./dirdat/ab EXTRACT eora1
RMTTRAIL
added.
GGSCI
(orasql-001-deV) 13> START EXTRACT
eora1
Sending
START request to MANAGER ...
EXTRACT
EORA1 starting
GGSCI
(orasql-001-deV) 14> INFO EXTRACT
eora1
EXTRACT EORA1
Initialized 2014-01-31
08:24 Status STARTING
Checkpoint
Lag 00:00:00 (updated 00:14:43 ago)
Process
ID 5869
Log
Read Checkpoint Oracle Integrated Redo
Logs
2014-01-31 08:24:07
SCN 0.0 (0)
GGSCI
(orasql-001-deV) 15> INFO EXTRACT
eora1
EXTRACT EORA1
Last Started 2014-01-31 08:38
Status RUNNING
Checkpoint
Lag 00:00:06 (updated 00:00:05 ago)
Process
ID 5869
Log
Read Checkpoint Oracle Integrated Redo
Logs
2014-01-31 08:39:30
SCN 0.4911465 (4911465)
·
On the Windows 2008 server create the Replicat process
GGSCI
(kensmssql001uat) 18> ADD REPLICAT
rora1, EXTTRAIL ./dirdat/ab
ERROR:
No checkpoint table specified for ADD REPLICAT.
GGSCI
(kensmssql001uat) 19> ADD
CHECKPOINTTABLE PERSON.CHKTAB
Successfully
created checkpoint table PERSON.CHKTAB.
GGSCI
(kensmssql001uat) 20> EDIT PARAMS
./GLOBALS
MGRSERVNAME
MANAGER
CHECKPOINTTABLE
PERSON.CHKTAB
NOTE:
For the GLOBALS configuration to take
effect, you must exit the session in which the changes were made.
GGSCI
(kensmssql001uat) 22> ADD REPLICAT
rora1, EXTTRAIL ./dirdat/ab CHECKPOINTT
ABLE PERSON.CHKTAB
REPLICAT
added.
GGSCI
(kensmssql001uat) 24> EDIT PARAMS
rora1
REPLICAT
rora1
TARGETDB
sqlserver2012
SOURCEDEFS
./dirdef/source.def
MAP
sales.sh.myobjects, TARGET person.myobjects;
GGSCI
(kensmssql001uat) 26> START REPLICAT
rora1
Sending
START request to MANAGER ('MANAGER') ...
REPLICAT
RORA1 starting
GGSCI
(kensmssql001uat) 27> INFO REPLICAT
rora1
REPLICAT RORA1
Last Started 2014-01-31
08:50 Status RUNNING
Checkpoint
Lag 00:00:00 (updated 00:00:08 ago)
Process
ID 5300
Log
Read Checkpoint File ./dirdat/ab000000
First Record RBA 1480
·
Issue an UPDATE statement on the Oracle 12c
Pluggable Database
[oracle@orasql-001-dev
goldengate]$ sqlplus
sh/sh@localhost:1525/sales
SQL*Plus:
Release 12.1.0.1.0 Production on Fri Jan 31 08:52:03 2014
Copyright
(c) 1982, 2013, Oracle. All rights reserved.
Last
Successful login time: Thu Jan 30 2014 10:04:23 +08:00
Connected
to:
Oracle
Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With
the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL>
update myobjects
2 set object_type='TABLE';
77695
rows updated.
SQL>
commit;
Commit
complete.
GGSCI
(kensmssql001uat) 39> stats replicat rora1 latest
Sending
STATS request to REPLICAT RORA1 ...
Start
of Statistics at 2014-01-31 08:58:34.
Replicating
from SALES.SH.MYOBJECTS to Person.myobjects:
***
Total statistics since 2014-01-31 08:58:04 ***
Total inserts 0.00
Total updates 37586.00
Total deletes 0.00
Total discards 0.00
Total operations 37586.00
GGSCI
(kensmssql001uat) 40> send replicat rora1 getlag
Sending
GETLAG request to REPLICAT RORA1 ...
Last
record lag 387 seconds.
GGSCI
(kensmssql001uat) 41> send replicat rora1 getlag
Sending
GETLAG request to REPLICAT RORA1 ...
Last
record lag 395 seconds.
At
EOF, no more records to process.
GGSCI
(kensmssql001uat) 42> stats replicat rora1 latest
Sending
STATS request to REPLICAT RORA1 ...
Start
of Statistics at 2014-01-31 08:59:20.
Replicating
from SALES.SH.MYOBJECTS to Person.myobjects:
***
Total statistics since 2014-01-31 08:58:04 ***
Total inserts 0.00
Total updates 77695.00
Total deletes 0.00
Total discards 0.00
Total operations 77695.00
·
On the SQL Server 2012 instance verify the
update has been performed as well
Ahmed Hazzaf
No comments:
Post a Comment