Infrastructure at your Service

Stéphane Savorgnano

SQL Server: Change Data Capture for Oracle

This new feature has been introduced in SQL Server 2012 and needs an Enterprise Edition. It uses a Windows Service which scans Oracle Logs and tracks DML changes from Oracle tables into SQL Server change tables.
In other words, Change data capture records Insert, Update and Delete activities that is applied to Oracle tables.
Let’s see how it works.

Prerequisites

The database where you want to capture changes must be in ARCHIVELOG and OPEN.
The user which will be used to connect to the Oracle database, must have DBA privileges.

Change Data Capture services installation

Installation will be done via msi packages. Those packages are not installed automatically with SQL Server 2012 or 2014. You will find them in your installation media under ToolsAttunityCDCOraclex641033.
There are two msi packages, one for the CDC Service Configuration named AttunityOracleCdcService.msi and another for CDC Designer named AttunityOracleCdcDesigner.msi.

Double click on AttunityOracleCdcService.msi and install the package, same for AttunityOracleCdcDesigner.msi.

b2ap3_thumbnail_OracleCDCSercice.jpg

b2ap3_thumbnail_OracleCDCDesigner.jpg

When both installations are done, go to the Application Panel and launch “Oracle CDC Service Configuration”.

b2ap3_thumbnail_OracleCDCSercice2.jpg

We will now have to prepare our SQL Server instance to use it. Each Oracle CDC Service instance lies a single SQL Server instance which will be used to manage it.
To create this instance click on “Prepare SQL Server” or right click on Local CDC Service and select “Prepare SQL Server”:

b2ap3_thumbnail_CDCSQLServerPrepare.jpg

Once it is done select the SQL Server instance where you want to install the MSXDBCDC database and click on the Run button:

b2ap3_thumbnail_CDCSQLServerPrepare2.jpg

The database is created:

b2ap3_thumbnail_CDCSQLServerPrepare3.jpg

Let’s check from SQL Server Management Studio the newly created database:

b2ap3_thumbnail_CDCSQLServerPrepare4.jpg

At this point we can create a new CDC service:

b2ap3_thumbnail_OracleCDCSercice3.jpg

To create the windows service we have to provide the following information:

  • The Service Name
  • Use a local system account for the service account

In addition, we must provide the following information for the associated SQL Server instance:

  • Server name
  • Authentication and login

The next step consists in creating a master password for CDC service which will be used to create symmetric key.

b2ap3_thumbnail_OracleCDCSercice4.jpg

After that we will create an instance from the CDC Designer.
When I open it, I have to enter the SQL server instance, I have created before, which is associated with the CDC service. Credentials are required to connect to the concerned SQL Server Instance.

b2ap3_thumbnail_OracleCDCSercice6.jpg

After connecting to the SQL Server I can see all related Oracle CDC Services. Next, after selecting the OracleCDCService1, I will create a new instance for this service:

b2ap3_thumbnail_OracleCDCInstance1.jpg

Provide a name to the future CDC Instance and then create the change associated database:

b2ap3_thumbnail_OracleCDCInstance2.jpg

After clicking on “Create Database” button the wizard will assist us to create the new Oracle CDC instance and the change database.

b2ap3_thumbnail_OracleCDCSercice7.jpg

Click Next.
We have now to fill out the form with following information:

  • the Oracle connection string to our listener
  • user name
  • password

b2ap3_thumbnail_OracleCDCSercice8.jpg

Check if the connection to the source is successful:

b2ap3_thumbnail_OracleCDCSercice9.jpg

Click Next and then click on the Add button to select tables and columns for capturing changes.

b2ap3_thumbnail_OracleCDCSercice10.jpg

Select the schema and click on the Search button.
Select the SCOTT.EMP table and click on the Add button.

b2ap3_thumbnail_OracleCDCSercice11.jpg

A message warns up that the SCOTT_EMP table has been added to the list:

b2ap3_thumbnail_OracleCDCSercice12.jpg

Now, you can see the table in the list:

b2ap3_thumbnail_OracleCDCSercice13.jpg

The Capture instance column, here SCOTT_EMP, will be used to name the capture instance specific to each table object in my SQL Server database.

At this point, no CDC gating role is specified so it means that no gating role will be used to limit access to the change data.

By default all the columns of the SCOTT.EMP table are selected for CDC. If I want to select just some columns I can click on the Edit button and choose the columns I want in CDC.
Click on OK when your selection is finished and after click on Next:

b2ap3_thumbnail_OracleCDCSercice17.jpg

To be able to capture Oracle changes, supplemental logging have to be set up for the Oracle databases tables. For this purpose, a script have been generated automatically and have to be run immediately or later but you have to be aware that changes will not be captured until the script is executed.
Click on the “Run Script” button:

b2ap3_thumbnail_OracleCDCSercice14.jpg

In order to run the script some credential must be provided and after click on the Run button:

b2ap3_thumbnail_OracleCDCSercice15.jpg

The script has been executed with success:

b2ap3_thumbnail_OracleCDCSercice16.jpg

Click on Next and in the last screen click on the “Run” button to finalize the CDC process:

b2ap3_thumbnail_OracleCDCSercice18.jpg

It looks like I have some errors…

b2ap3_thumbnail_OracleCDCSercice19.jpg

Let’s click on Details:

b2ap3_thumbnail_OracleCDCSercice20.jpg

Apparently I forgot to set up my Oracle database for supplemental logging, let’s do it:

b2ap3_thumbnail_OracleCDCSercice21.jpg

Now, we can click on the Next button:

b2ap3_thumbnail_OracleCDCSercice22.jpg

We have successfully created an Oracle CDC instance. Let’s check on my SQL Server:

b2ap3_thumbnail_OracleCDCSercice23.jpg

The mirror table will be always empty conceptually. The generated deployment script denies all DML permissions on the mirror table.

I have a new database named OracleCDCInstance1 with, for the moment, an empty table named SCOTT.EMP, which is the table I selected earlier from the wizard.

Back to my CDC Designer, I see that I have now an OracleCDCInstance1 which is for the moment not started:

b2ap3_thumbnail_OracleCDCSercice24.jpg

Now let’s start the instance:

b2ap3_thumbnail_OracleCDCSercice25.jpg

The Detailed status has changed from Initial to IDLE:

b2ap3_thumbnail_OracleCDCSercice26.jpg

It may take a few minutes (one or two) to start change capture process. You may notice the detailed status that will change from IDLE to PROCESSING. Likewise, Processing and Counters areas will also change as oracle logs are ridden.
Here I performed an update of the salary column in my table SCOTT.EMP from my Oracle database:

b2ap3_thumbnail_OracleCDCSercice28.jpg

We can see that the twelves operations are reflected in the counters area as twelves reads:

b2ap3_thumbnail_OracleCDCSercice27.jpg

If the Detailed Status changes to LOGGER instead of PROCESSING, it means that even you have a temporary delay mining Oracle logs and in this case PROCESSING will come back quickly even you have a problem of mining Oracle logs and in this case check the dbo.xdbcdc_trace in the MSXDBCDC database to check errors.

We are also able to check information from log traces by clicking on the “Collect diagnostics” link which will generate diagnostics data from both Oracle environment and dbo.xdbcdc_trace table into a trace file:

b2ap3_thumbnail_OracleCDCSercice29.jpg

This feature gives the opportunity to use Change Data Capture in a SQL Server database using an Oracle database as a source. A major advantage of using CDC for Oracle is certainly the reduction of data level latency and at the same time, Oracle ETL knowledge becomes useless. It is also a good way to source SQL Server database with Oracle Data.
Hope this article will help you, envoy 😉

11 Comments

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Stéphane Savorgnano
Stéphane Savorgnano

Senior Consultant