Using Oracle Goldengate 12C for CDC Between Oracle 12C Multitenant Database and MS SQL

Using Oracle GoldenGate 12c for CDC between Oracle 12c Multitenant Database and MS SQL Server 2012

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 PASSWORDxxx

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.

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.

GGSCI (orasql-001-deV) 1> EDIT PARAM defgen

DEFSFILE ./dirdat/source.def,

USERIDALIAS gg_sales

TABLE sales.sh.customers;

[oracle@orasql-001-dev goldengate]$ ./defgenparamfiledirprm/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

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]

/****** Object: Table [Person].[myobjects] Script Date: 4/02/2014 9:04:24 AM ******/

SETANSI_NULLSON

SETQUOTED_IDENTIFIERON

CREATETABLE[SCOTT].[myobjects](

[OBJECT_ID][int]NOTNULL,

[OBJECT_NAME][nvarchar](20)NULL,

[OBJECT_TYPE][nvarchar](40)NULL

)ON[PRIMARY]

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> dbloginuseridaliasgg_sales

Successfully logged into database SALES.

GGSCI (orasql-001-deV) 5> add trandatasales.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> dbloginuseridaliasgg_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]$ sqlplussh/sh@localhost:1525/sales

SQL*Plus: Release 12.1.0.1.0 Production on Fri Jan 31 08:52:03 2014

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 setobject_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