none
CDC for Oracle Continually goes to Aborted State - SQL Server 2012

    Frage

  • 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","",""

    Donnerstag, 24. Oktober 2013 14:28

Antworten

  • 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.
    Mittwoch, 26. März 2014 19:23

Alle Antworten

  • 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


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Freitag, 25. Oktober 2013 08:17
    Moderator
  • 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.

    Freitag, 25. Oktober 2013 13:57
  • 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,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Mittwoch, 30. Oktober 2013 04:08
    Moderator
  • Hi,

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


    Regards,
    Christian HL
    Microsoft Online Community Support


    Please remember to click “Mark as Answer” on the post that helps you, and to click “Unmark as Answer” if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

    Mittwoch, 30. Oktober 2013 04:46
  • 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.


    Donnerstag, 31. Oktober 2013 21:23
  • 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.


    Freitag, 1. November 2013 14:53
  • 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.
    Mittwoch, 26. März 2014 19:23
  • 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


    • Bearbeitet DaveDBA00 Mittwoch, 9. April 2014 10:14 redact hostname
    Mittwoch, 9. April 2014 10:13
  • 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):

    Mittwoch, 9. April 2014 10:25