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.

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

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”:

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

The database is created:

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

At this point we can create a new CDC service:

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.

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.

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:

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

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

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

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

Check if the connection to the source is successful:

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

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

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

Now, you can see the table in the list:

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:

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:

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

The script has been executed with success:

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

It looks like I have some errors…

Let’s click on Details:

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

Now, we can click on the Next button:

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

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:

Now let’s start the instance:

The Detailed status has changed from Initial to IDLE:

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:

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

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:

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 😉