Implementing CDC for Oracle in SQL Server 2012

Source: Internet
Author: User
Tags prepare attunity ssis



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


Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.