I am setting up a test environment for CDC with an Oracle system.  I have everything set up to the point where logs are being read and data appears to be getting added to the CDC tables in SQL Server.  However, at different times during the day, the status of the service goes to Aborted and the service is completely stopped.  The first time I start the service, it will run for about 1 hour, before it aborts.  After that, it only takes 2 or 3 minutes, sometimes less, before this happens again.

Below are the last entries in the log when the instance aborted.  Whenever the instance aborts, the last message is always "Reading Complementary records for large log miner SQL_REDO/SQL_UNDO".  I'm guessing there's a permission or setting in Oracle that I'm missing, but I don't know what it is.

Version information:
Oracle: 10.2.0.5.0
CDC Service: SQL Server 2012 SP 1
SQL Server Database Engine: 11.0.3128

record","source","","0xDD070A00170012001C0004000043073800000000000000000626001100F8DD3A00000000061474B6B30100000100012B190000CC8E01340000000000"
"10/23/2013 6:28:04 PM","TRACE","ETL-BI12-01-T","RUNNING","IDLE","ORACDC000T:Reading next record","source","",""
"10/23/2013 6:28:04 PM","TRACE","ETL-BI12-01-T","RUNNING","IDLE","ORACDC000T:Enqueue transaction record","source","","0xDD070A00170012001C0004004085163800000000000000000626002F00E2DD3A00000000061474B6B40100000100012B190000CCA201C40000000000"
"10/23/2013 6:28:04 PM","TRACE","ETL-BI12-01-T","RUNNING","IDLE","ORACDC000T:Reading next record","source","",""
"10/23/2013 6:28:04 PM","TRACE","ETL-BI12-01-T","RUNNING","IDLE","ORACDC000T:Enqueue transaction record","source","","0xDD070A00170012001C000400C009353800000000000000000026002F00E2DD3A00000000061474B6B50100000100012B190000CCA500A00000000000"
"10/23/2013 6:28:04 PM","TRACE","ETL-BI12-01-T","RUNNING","IDLE","ORACDC000T:Reading next record","source","",""
"10/23/2013 6:28:04 PM","TRACE","ETL-BI12-01-T","RUNNING","IDLE","ORACDC000T:Enqueue transaction record","source","","0xDD070A00170012001C000400408E533800000000000000000026001100F8DD3A00000000061474B6B70100000100012B190000CCAF00440000000000"
"10/23/2013 6:28:04 PM","TRACE","ETL-BI12-01-T","RUNNING","IDLE","ORACDC000T:Reading next record","source","",""
"10/23/2013 6:28:04 PM","TRACE","ETL-BI12-01-T","RUNNING","IDLE","ORACDC000T:Enqueue transaction record","source","","0xDD070A00170012001C00040080D0623800000000000000000626002D00DBDD3A00000000061474B8780100000100012B19000108B500280000000000"
"10/23/2013 6:28:04 PM","TRACE","ETL-BI12-01-T","RUNNING","IDLE","ORACDC000T:Reading next record","source","",""
"10/23/2013 6:28:04 PM","TRACE","ETL-BI12-01-T","RUNNING","IDLE","ORACDC000T:Enqueue transaction record","source","","0xDD070A00170012001C0004000055813800000000000000000626001500E0DD3A00000000061474B8790100000100012B19000108C7003C0000000000"
"10/23/2013 6:28:04 PM","TRACE","ETL-BI12-01-T","RUNNING","IDLE","ORACDC000T:Reading next record","source","",""
"10/23/2013 6:28:04 PM","TRACE","ETL-BI12-01-T","RUNNING","IDLE","ORACDC000T:Enqueue transaction record","source","","0xDD070A00170012001C00040080D99F3800000000000000000026001500E0DD3A00000000061474B87A0100000100012B19000108C901140000000000"
"10/23/2013 6:28:04 PM","TRACE","ETL-BI12-01-T","RUNNING","IDLE","ORACDC000T:Reading next record","source","",""
"10/23/2013 6:28:04 PM","TRACE","ETL-BI12-01-T","RUNNING","IDLE","ORACDC000T:Enqueue transaction record","source","","0xDD070A00170012001C000400C01BAF3800000000000000000026002D00DBDD3A00000000061474B87C0100000100012B19000108D301540000000000"
"10/23/2013 6:28:04 PM","TRACE","ETL-BI12-01-T","RUNNING","IDLE","ORACDC000T:Reading next record","source","",""
"10/23/2013 6:28:04 PM","TRACE","ETL-BI12-01-T","RUNNING","IDLE","ORACDC000T:Reading Complementary records for large log miner SQL_REDO/SQL_UNDO","source","",""

Hi Jason,

From the Oracle CDC State stuck on Aborted which has a similar topic, we can see that the issue was finally resolved by restarting the SQL Server instance. Could you please give it a try?

Regards,
Mike Yin

If you have any feedback on our support, please click here

Unfortunately, I made that post.  It worked one day to restart the Oracle database, and things worked for several hours, but then it aborted again.

There is still a problem...Reaching out to support.

Hi Jason,

Thank you for your posting.

I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.

Thank you for your understanding and support.

Thanks,

Hi,

Can you try to apply SP1 CU 6 : http://support.microsoft.com/kb/2874879

I have applied SP 1 CU 6 for CDC Designer and Service.  Unfortunately, I am getting the same behavior with the CDC instance.

Today, I did make one discovery.  The instance appears to be going to an aborted state, if it can't find new log records in Oracle.  I had a process that ran for about 2 hours, creating and updating data in Oracle.  During that time, the instance ran fine, as it was idle or processing data.  As soon as the process stopped, which was the only thing running against the Oracle database, the following message appeared in the trace 5 or 6 times, 10 seconds a part, before the instance went to an Aborted state.

ORACDC000T:Reading Complementary records for large log miner SQL_REDO/SQL_UNDO

I would have expected the instance to be in an idle state and keep running, rather than stopping with an Aborted state.  I'm not sure if there's a permission missing somewhere or not.

• Edited by Jason Dempster Thursday, October 31, 2013 9:24 PM

Here are the permissions we have set up on the Oracle side: (Please forgive the poor formatting.  The editor wants to remove all of the spaces and add extra carriage returns.)

GRANT

EXECUTE ON  "SYS"."DBMS_LOGMNR" TO "LOGREADER";

GRANT

EXECUTE ON  "SYS"."DBMS_LOGMNR_D" TO "LOGREADER";

grant

select any transaction to logreader;

GRANT

"CONNECT" TO "LOGREADER";

GRANT

select ON sys.v_$database to "LOGREADER";

GRANT

select ON sys.v_$logmnr_contents to "LOGREADER";

GRANT

select ON sys.v_$logmnr_dictionary to "LOGREADER";

GRANT

selectONsys.v_$logmnr_logfileto"LOGREADER";

GRANT

selectONsys.v_$logmnr_logsto"LOGREADER";

GRANT

selectONsys.v_$logmnr_parametersto"LOGREADER";

GRANT

selectONsys.v_$logmnr_sessionto"LOGREADER";

GRANT

selectONsys.v_$logmnr_transactionto"LOGREADER";

GRANT

selectONsys.v_$logto"LOGREADER";

GRANT

selectONsys.v_$logfileto"LOGREADER";

GRANT

selectONsys.v_$archived_logto"LOGREADER";

GRANT

select ON sys.dba_registry to "LOGREADER";

GRANT

select ON sys.v_$instance to "LOGREADER";

GRANT

select ON sys.v_$thread to "LOGREADER";

GRANT

select ON sys.v_$parameter to "LOGREADER";

For each table we want to capture changes on, select rights have been granted to this user, as well.

• Edited by Jason Dempster Friday, November 01, 2013 2:55 PM

This issue has been resolved.  SQL Server 2012 SP1 CU8 has an update to the Attunity CDC for Oracle components that fix the problem I was encountering.

• Marked as answer by Jason Dempster Wednesday, March 26, 2014 7:23 PM

Hi,

I have applied CU 9, which contains the fix suggested in CU8.  I am still receiving this error.

I am at a loss as I have tried everything I can think of.

Any more suggestions?  MY SQL Server 2012 install is patched to 11.0.3412 via:

Package:
-----------------------------------------------------------
-----------------------------------------------------------
KB Article Number(s): 2723979, 2861456, 2913206, 2920987, 2921630, 2922799, 2922935, 2923837, 2924827, 2926089, 2926217, 2926223, 2926699, 2926712, 2927511, 2927524, 2927748, 2927779, 2927844, 2928732, 2929193, 2929832, 2929903, 2931001, 2931078, 2931241, 2932120, 2932340, 2932341, 2933780, 2934934, 2935681, 2936004, 2938828
Language: All (Global)
Platform: x64
Location: (http://hotfixv4.microsoft.com/SQL%20Server%202012/sp1/SQLServer2012_SP1_CU9_2931078_11_0_3412_/11.0.3412.0/free/473913_intl_x64_zip.exe)

If I have trace set to SOURCE I get the following:

"09/04/2014 10:02:01","TRACE","<HOSTNAME>","RUNNING","IDLE","ORACDC000T:Reading Complementary records for large log miner SQL_REDO/SQL_UNDO","source","",""

If I set it to ON I get the following:

"09/04/2014 10:10:02","ERROR","<HOSTNAME>","RUNNING","PROCESSING","ORACDC423E:ODBC error: RetCode: SQL_ERROR  SqlStat: 23000 NativeError: 2627 Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of PRIMARY KEY constraint 'lsn_time_mapping_clustered_idx'. Cannot insert duplicate key in object 'cdc.l...","infrastructure","insert into cdc.lsn_time_mapping ([start_lsn],[tran_begin_time],[tran_end_time],[tran_id],[tran_begin_lsn]) values (?,?,?,?,?)",""

Please, if you have any further suggestions they would be greatly received.

Thanks,

David

• Edited by DaveDBA00 15 minutes ago redact hostname

Hi,

I have applied CU 9, which contains the fix suggested in CU8.  I am still receiving this error.

I am at a loss as I have tried everything I can think of.

Any more suggestions?  MY SQL Server 2012 install is patched to 11.0.3412 via:

Package:
-----------------------------------------------------------
-----------------------------------------------------------
KB Article Number(s): 2723979, 2861456, 2913206, 2920987, 2921630, 2922799, 2922935, 2923837, 2924827, 2926089, 2926217, 2926223, 2926699, 2926712, 2927511, 2927524, 2927748, 2927779, 2927844, 2928732, 2929193, 2929832, 2929903, 2931001, 2931078, 2931241, 2932120, 2932340, 2932341, 2933780, 2934934, 2935681, 2936004, 2938828
Language: All (Global)
Platform: x64
Location: (http://hotfixv4.microsoft.com/SQL%20Server%202012/sp1/SQLServer2012_SP1_CU9_2931078_11_0_3412_/11.0.3412.0/free/473913_intl_x64_zip.exe)

If I have trace set to SOURCE I get the following:

"09/04/2014 10:02:01","TRACE","<HOSTNAME>","RUNNING","IDLE","ORACDC000T:Reading Complementary records for large log miner SQL_REDO/SQL_UNDO","source","",""

If I set it to ON I get the following:

"09/04/2014 10:10:02","ERROR","<HOSTNAME>","RUNNING","PROCESSING","ORACDC423E:ODBC error: RetCode: SQL_ERROR  SqlStat: 23000 NativeError: 2627 Message: [Microsoft][SQL Server Native Client 11.0][SQL Server]Violation of PRIMARY KEY constraint 'lsn_time_mapping_clustered_idx'. Cannot insert duplicate key in object 'cdc.l...","infrastructure","insert into cdc.lsn_time_mapping ([start_lsn],[tran_begin_time],[tran_end_time],[tran_id],[tran_begin_lsn]) values (?,?,?,?,?)",""

Please, if you have any further suggestions they would be greatly received.

Thanks,

David

Can I confirm that I have applied all of CU9?  There were 4 files with no explanation of each so I went for the one I thought was correct (the one that is ticked below):