[Attunity CDC for Oracle]my own odd Troubleshooting

Have you ever encountered “Aborted” status on “Change Data Capture Designer for Oracle by Attunity”?
or on “Oracle CDC for SSIS”? Then you dump CDC trace even used “SOURCE” but can’t find useful information.
For me, yes. I’ve encountered the same situation and I’ll describe a little about my story and how to troubleshooting.
My Environment:

• Windows 2012 R2
• SQL 2012 SP2 + CU6

There are two components(AttunityOracleCdcDesigner.msi, AttunityOracleCdcService.msi) you have to download and install:
Remember the bit must match ODAC’s bit (32 bits v.s. 32 bits; 64 bits v.s 64 bits)

Then, you have to Prepare SQL Database first. it will create a user database called “MSXDBCDC”, and you will notice the Collation is “SQL_Latin1_General_CP1_CS_AS”. It’s because Oracle is case sense database. And recovery_model is FULL (even if CDC doesn’t require what recovery mode is). I kept cdc_service_name as default but tweak MSXDBCDC database.

How do you know my database CDC feature is ON/OFF?

SELECT [name], database_id, is_cdc_enabled, is_broker_enabled, log_reuse_wait_desc
FROM sys.databases
ORDER BY is_cdc_enabled DESC
GO

When you create a CDC service, it will require the Master Key, you just need to type once and if you are a local Windows Admin Group, then you don’t have to type it again when you open the console. Then you create a “New Oracle CDC Instance”, it alternatively means a “Database name for SQL server”.
I used to put a few “Advanced” options. You can modify it manually or update the table “cdc.xdbcdc_config” directly anytime, below is my favors

OPTIONS
When you set up trace in “OPTION” tab, you can use T-SQL to get the information

select * from  MSXDBCDC.dbo.xdbcdc_trace
order by timestamp desc
select * from xxxYouCDCDBxxx.cdc.xdbcdc_state
order by timestamp desc
select * from xxxYouCDCDBxxx.cdc.lsn_time_mapping
select * from xxxYouCDCDBxxx.cdc.xdbcdc_config 
--TRUNCATE TABLE MSXDBCDC.dbo.xdbcdc_trace

Options settings I used to set up:

• cdc_stop_on_breaking_schema_changes=1;
• trace=SOURCE;
• source_env_report=1;
• target_idle_lsn_update_interval=60;
• cdc_restart_limit=36;
• trace_data_errors=Ture

You can use the T-SQL script to update the cdc config table directly

UPDATE cdc.xdbcdc_config 
SET options= N'cdc_stop_on_breaking_schema_changes=1;trace=1;
source_env_report=1;target_idle_lsn_update_interval=60;
cdc_restart_limit=36;trace_data_errors=Ture'

You can find the details of options content here:
https://msdn.microsoft.com/en-us/library/dn175460.aspx#bkmk_cdcxdbcdc_config

PERMISSION
Oracle user:

• SELECT on <any-captured-table>
• SELECT ANY TRANSACTION
• EXECUTE on DBMS_LOGMNR
• SELECT on V$LOGMNR_CONTENTS
• SELECT on V$ARCHIVED_LOG
• SELECT on V$LOG
• SELECT on V$LOGFILE
• SELECT on V$DATABASE
• SELECT on V$THREAD
• SELECT on ALL_INDEXES
• SELECT on ALL_OBJECTS
• SELECT on DBA_OBJECTS
• SELECT on ALL_TABLES

CDC Service Administrator :

• dbcreator
• public
• db_owner for MSXDBCDC, Oracle Instance Database

Installation user: Will be needed for once

• Local Window Admin Group
• SQL Admin role

I used one Domain account to implement all of installation except the Oracle user, and the account will obtain:
Server Level

• dbcreator and public

Database Level

• db_datareader, db_datawriter, public and default dbo schema on MSXDBCDC
• db_owner, public, cdc_service user, default dbo schema on Oracle Instance Database

Windows Server Level

• Read premission on ODAC tnsnames.ora file

I was encountering issues such like “Violate Index” for Lsn or “xx Position”. Microsoft answered to apply SQL 2012 SP1 CU8 cumulative package. Other sites told me to update the table “[cdc].[lsn_time_mapping]”. I’ve done both of them (oh, I don’t need to apply the first one, I have SQ 2012 SP2 + CU6). But the console is still aborted. Oracle side didn’t show any errors for “Log Mining”. So I went to check the console version and found the version is 1.0.

please, please check out your version of AttunityOracleCdcDesigner and AttunityOracleCdcService. It’s really my story and I’ve spent 5 days to figure it out.

Yes, 1.0. The old version is 1.0 and it supports SQL 2012, BUT it DOESN’T support Windows 2012.

So please download the latest version that the same bits as Oracle Client (64bit <-> 64bits) (32bits <-> 32 bits)

• DOWNLOAD site is here: Microsoft® SQL Server® 2012 SP2 Feature Pack
• https://www.microsoft.com/en-us/download/details.aspx?id=43339

Once you uninstall the old one and install the new one, your console version shows 1.1. And the problem was
FIXED!!


Reference websites:
SQL Server 2012 CDC for Oracle – a Review of One Implementation

• http://blogs.msdn.com/b/dfurman/archive/2013/04/15/sql-server-2012-cdc-for-oracle-a-review-of-one-implementation.aspx

cdc.xdbcdc_config

• https://msdn.microsoft.com/en-us/library/dn175460.aspx#bkmk_cdcxdbcdc_config

CDC cleanup job timestamp explanation

• https://social.technet.microsoft.com/Forums/en-US/6c556380-f0d3-405c-8ee2-651c25406efb/change-data-capture-cdc-cleanup-job-only-removes-a-few-records-at-a-time?forum=sqldatabaseengine

How to cleanup Replication Bits

• http://blogs.msdn.com/b/repltalk/archive/2010/11/17/how-to-cleanup-replication-bits.aspx