[Attunity CDC for Oracle]my own odd Troubleshooting

2015-06-25

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)

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

Reference websites:
SQL Server 2012 CDC for Oracle – a Review of One Implementation
cdc.xdbcdc_config
CDC cleanup job timestamp explanation
How to cleanup Replication Bits

3 comments

Unknown said...

Hi,
Thanks al lot.I've upgraded the CDC to the version you recommended, but still getting the same Aborted status. But it seems my situation is a bit different. It works perfectly with Oracle 11g.It works for some few minute reading from Oracle12 c and stall ,showing the aborted status. Currently running Microsoft SQL Server 2012 (SP3-CU10).Do you know if Microsoft SQL Server 2012 supports Oracle 12c?
Thanks

Allen said...

Hi Afolabi Fajuyitan,

The CDC for Attunity component in SQL 2012 doesn't support Oracle 12c. You could try the new CDC for Attunity in original SQL 2012 SP2 ISO, or try the new in SQL 2016 or SQL 2017.

v3.0 in SQL 2014
v4.0 in SQL 2016
v5.0 in SQL 2017

Mine is installed with v5.0 which in SQL 2017, it works fine with Oracle 12C.
I have tried v4.0 in SQ 2016, it worked fine too.

Cory S said...

Goodd reading this post

Newer Older