CDC for Oracle in SQL Server 2012

CDC for Oracle in SQL Server 2012

Rate This
  • Comments 13

One of the new features introduced in SQL Server 2012 is the ability to use SQL Server CDC for tracking changes on an Oracle table. In the following post you will understand how to setup such a service for tracking changes on your Oracle tables.

Configuring your Oracle server

Before creating the service, you must ensure that the following configuration steps have been performed on your source Oracle database:

1. The database is set to ARCHIVELOG and is OPEN

2. The user that is going to connect to the Oracle database has DBA privileges on the database (since you will look up the log miner data from the database)

You should also install the Oracle client and configure the service names on the machine where you will be installing the CDC services on.

Installing the CDC Service Configuration and Designer MMCs

The MMCs are not installed by default when you install your SQL Server instance. You must install them separately by installing them using their MSIs.

The MSI installer for CDC Service Configuration MMC is named AttunityOracleCdcService.msi and the installer for CDC Designer is named AttunityOracleCdcDesigner.msi. They can be found in your installation media under the folder \Tools\AttunityCDCOracle. In the folder you would find x64 or x86 folders which correspond to the different processor architectures. Within the x86 or x64 folder you would find folders for various locales including 1033 which corresponds to English (US).

Thus, to install the English (US) x64 version you must go to \Tools\AttunityCDCOracle\x64\1033 folder.

Once installed, you would find a shortcut created under the start menu at Start>Program>Attunity  Change Data Capture for Oracle>CDC Service Configuration

Note You must install the same bit version of the MSI as the Oracle client installation you have on the local system. Thus, if you have installed the x86 version of Oracle client then you must also install x86 version of the Oracle CDC service.

Setting up your service

You can set up a new CDC Service using the CDC Service configuration MMC. But before you create a new service, you will have to prepare a local SQL Server instance. You can do so by using the actions pane in the MMC as shown below.

clip_image002

Once the local SQL server is prepared you can use the New Service action to create a new CDC service as shown below.

clip_image004

Couple of things to note here:

1. The service account you use should have logon as service policy assigned to it

2. The master password will be used to create a asymmetric key which will be used to encrypt the Oracle credentials which will be provided later

Once the service is created you can see it under the services node.

clip_image006

Creating a CDC instance

You can create a CDC instance using the CDC designer configuration MMC which you installed earlier. A CDC instance is defined as an object consisting of:

· The source Oracle database connection information

· Credentials and properties to connect to the source Oracle databases

· Source tables for which you need to capture CDC. You can even configure the instance to capture only a subset of the columns of each table.

· Mirror SQL Server database name which will contain the mirror tables for the source tables/columns defined in the previous step

You can create the instance as shown in the following steps

1. First you should connect to the SQL Server instance on which you created the service

clip_image008

2. Now you can see all the services available for the instance

clip_image010

3. Now you can create a new instance using the right click context menu for a service

clip_image012

4. The Oracle CDC Instance wizard opens up and the first pane allows you to define a service name. It will also ask you the name for a mirror database that would be created on the same SQL Server instance as step 1. You can rename the mirror database as well.

clip_image014

5. The next steps will ask you to input the Oracle connection string as well as the user credentials for connecting to the Oracle database. Note that it takes the Oracle connection string in the following format: host[:port][/service name]

Note that you should have installed the Oracle client and setup the service naming before this step.

clip_image016

6. Now you can select the tables you want to include in your CDC instance.

clip_image018

7. You can also edit the instance and select specific columns as well as specify a CDC role to be used when creating the CDC instance on the local SQL server database

clip_image020

8. In the next steps you would asked to run a script on your source Oracle database which essentially sets up Supplemental logging

9. You can click through the wizard to Generate the Oracle CDC instance

Operating a CDC instance

Once you have created the CDC instance you can now start and monitor the CDC instance using the designer MMC. By default the Service would be in INITIAL (stopped) state. You can start it as shown in the figure below.

clip_image022

The service will be started and the status would inform you of the current state of the service, transactions’ SCN for Oracle and the number of transactions being processed at that instance as shown below.

clip_image024

You would notice that a database would be created on the SQL Server instance you specified and the mirrored table would be setup. CDC would have also been enabled on the table and a corresponding _CT table would have been created capturing the changes from the mirrored table as shown below.

clip_image026

This post has been an introduction on how to get up and running with the new CDC for Oracle features introduced in SQL Server 2012.

Leave a Comment
  • Please add 7 and 2 and type the answer here:
  • Post
  • I've noted elsewhere that SSIS CDC packages require a step to populate the mirror table in SQL Server with all the data from the Oracle table prior to beginning the CDC updates.  Does the CDC Designer perform an initial population of the selected Oracle table(s)?  If so, what step does this occur in and if not, is there a best practice when using the CDC Designer to get the mirror table an initial load?   Great article!!  Very helpful for trying to understand this new functionality!

  • How about SybaseASE ? will it work

  • Tim,

    If I understand your question properly, the initial load that you are talking about is the CDC components initial load that you must perform from the source CDC table to a target table (irrespective the table originating from Oracle or SQL Server). The Oracle CDC Service once setup will sync changes from the Oracle table to the destination SQL Server table. The initial sync of the source and target table must be done by the user. 

    Ideally, once the mirror table and the CT table have been populated with the initial sync of changes you can start the CDC operation using the CDC components in SSIS from the mirror table.

    Kin,

    No, the service is only for Oracle databases.

  • Which version of Oracle supported?

    (it will works against Oracle 9i?)

  • Oracle 9i is not supported. The supported Oracle database versions are the following or later patches of:

    1. Oracle Database 10g Release 2: 10.2.0.1—10.2.0.5 (patchset as of April 2010)

    2. Oracle Database 11g Release 1: 11.1.0.6—11.1.0.7 (patchset as of September 2008)

    3. Oracle Database 11g Release 2: 11.2.0.1—11.2.0.2 (patchset as of November 2010)

  • The latest good news is that the next patchset will support Oracle 11g Release 2 *11.2.0.3* as well. Stay tuned!

  • Isn't this Attunity Oracle Cdc a trial version?

  • Does this require a special edition of Oracle? E.g. Enterprise?

    (I'm not familiar with Oracle licensing models)

    I just want to try out the CDC Oracle functionality in SSIS 2012 for demo purposes.

  • No, I don't think you need a special edition of Oracle. You just need to be running LogMiner... unfortunately I don't know the exact requirements for that.

  • Logminer, never heard of it :) Googling if it is included in Oracle XE doesn't give much answers.

    I'll try it out tonight to see if I can get it working.

  • How about supported for ORACLE  10gR1 (10.1.0.4)?

    FETC DBA Tony.

  • How can you get the incremental load package to run either to complete success or complete failure?  When we set that package transaction to required we get a timeout error in the Mark End CDC control.

  • Hi Nice Blog !

    tried each and every step but I am still unable to view any data on SQL server side although the console shows the changes but i am not able to see any data in target SQL-Server database .Can u please help

    Thanks in advance  

Page 1 of 1 (13 items)