Implementing CDC for Oracle in SQL Server 2012

In the previous article, using CDC (data change capture) in SSIS 2012 , describes how to use CDC in SSIS 2012, which is explained on this basis. How to implement change capture on Oracle data tables through the Attunity provided by the changes data capture Designer for Oracle .

The same need to do some preparatory work:

1. Configure Oracle database to archive mode. And gets the specified permissions for the browse log.

/* -- ======================================================
-- Change Oracle properties
---Generate By downmoon, 3w@live.cn
-- ======================================================
ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
ALTER DATABASE ARCHIVELOG;

2. Install the CDC Service Configuration and Designer two consoles.

There are two installation files under the installation source \tools\attunitycdcoracle\x64\1033 of SQL Server, 32 bits corresponding path is x86,1033 English, 2052 is Chinese simplified.

D:\Tools\AttunityCDCOracle\x64\1033\AttunityOracleCdcDesigner.msi
D:\Tools\AttunityCDCOracle\x64\1033\AttunityOracleCdcService.msi
D:\Tools\AttunityCDCOracle\x86\1033\AttunityOracleCdcDesigner.msi
D:\Tools\AttunityCDCOracle\x86\1033\AttunityOracleCdcService.msi

For detailed installation methods, please refer to:

http://social.technet.microsoft.com/wiki/contents/articles/7647. Installing-microsoft-sql-server-2012-change-data-capture-for-oracle-by-attunity.aspx

Double-click the MSI to install it. The default installation path. 64 people here: C:\Program files\change Data Capture for Oracle by Attunity

Assume there is no installation source. Can be downloaded on the official website: http://www.microsoft.com/en-us/download/details.aspx?id=35580

Note: assuming that your SQL Server 2012 is 64-bit, you must choose a 64-bit installation source , and if you use a oracleclient of 32 bits, then the trouble is behind. Oh.

The front is relatively simple. In two steps, you can:

First step: Configure the CDC for Oracle service

At the beginning-attunity change Data Capture for ORACLE>CDC service configuration, open the Services config console:

Prepare a local instance of SQL Server. Used to record the changes of the remote Oracle table, assuming you did not first prepare the database, the system will be quite humane to give hints, after the completion of the same.

The configuration service is simpler, finally for example, note: The last line of the master key is used to encrypt the stored Oracle credentials.

At this point, the first step of the service configuration is complete, simple, is not?

Step Two: Design CDC for Oracle

At the beginning-attunity change Data Capture for ORACLE>CDC Designer Configuration. Open the Design configuration console:

To create a new instance, first create a CDC database name called ORACLE_CDC, which is located on the SQL Server side. You can do it.

Get down. Connect to Oracle Source

The above situation occurs. It was because my other application had to install an Oracle 32-bit client, and the tragedy arose.

Remedial action: Install a green Oracle 64-bit client:http://www.oracle.com/technetwork/topics/winx64soft-089540.html

Download, direct decompression can be, this article, the path path has two:

E:\Ora11\product\11.2.0\dbhome_1\bin (32-bit client installation path in Path path)
E:\Ora11\Client12; E:\Ora11\product\11.2.0\dbhome_1\bin (64-bit client green path in Path path, after change)

And then again in the above interface "Test connection", Success!

You can create a new table in Oracle in advance for testing:

/* -- ======================================================
-- Change Oracle properties
---Generate By downmoon, 3w@live.cn
-- ======================================================
-- Create tableCreate table CDCTest01( TCode VARCHAR2(20) primary Key, TName VARCHAR2(500)); Insert into CDCTest01select ‘1‘, ‘Yang Dingtian’ from dualunion allselect ‘2’, ‘Zhang Sanfeng’ from dual;

After creation. You can select the tables you want to capture in a later step:

Perform some necessary actions on the table:

Suppose you didn't do the preparation before this article. You will receive an error such as the following:

Remedial measures, such as the following:

Next, the design is complete.

You can try to start this Windows service, Ten will receive this error for the first time:

In fact, this is because the native configuration of the 64-bit client is stored in the wrong position in the register, the change to 64-bit Green client location can be. The original value is the path of the 32-bit installation version .

[Hkey_local_machine\software\oracle]
"Oracle_home" = "e:\\ora11\\client12"

There is no need to restart after the change, another start service, OK

Let's add two sentences to test:

/* -- ======================================================
-- Create test data
---Generate By downmoon, 3w@live.cn
-- ======================================================

Insert into CDCTest01 select ‘3‘, ‘Jinmaoshiwang’ from dual;
Update CDCTest01 set TName=‘jueyuan‘ where TCode=‘1’; 

Psid=1 "alt=" Invitation month Studio "height=" 417 "width=" 726 "/>

SQL Server-side self-generated tables:

Thank you for reading. I hope to be of some help to you.

This article:

id=35580%20%20http://msdn.microsoft.com/zh-cn/library/dn175414%28v=sql.120%29.aspx%20%20http:// www.oracle.com/technetwork/topics/winx64soft-089540.html%20%20http://technet.microsoft.com/en-us/library/ ee470675%28v=sql.100%29.aspx%20http://blogs.msdn.com/b/mattm/archive/2012/03/26/ cdc-for-oracle-in-sql-server-2012.aspx%20%20%20http://www.attunity.com/forums/microsoft-ssis-oracle-connector/ Error-failed-load-oci-dll-1308.html ">http://www.attunity.com/products/attunity-cdc-ssis/oracle-cdc-for-ssis
http://www.microsoft.com/en-us/download/confirmation.aspx?id=35580
http://msdn.microsoft.com/zh-cn/ Library/dn175414%28v=sql.120%29.aspx
Http://www.oracle.com/technetwork/topics/winx64soft-089540.html
Http://technet.microsoft.com/en-us/library/ee470675%28v=sql.100%29.aspx
http://blogs.msdn.com/b/mattm/ Archive/2012/03/26/cdc-for-oracle-in-sql-server-2012.aspx
http://www.attunity.com/forums/ microsoft-ssis-oracle-connector/error-failed-load-oci-dll-1308.html

Invitation Month Note: This article copyright by invitation month and Csdn common all. Reprint please indicate the source.
Helping people equals self-help! [Email protected]

Implementing CDC for Oracle in SQL Server 2012