CDC for Oracle by Attunity stuck at status Logger after restart

EF Lin 1 Reputation point
2021-04-16T01:51:28.633+00:00

Hi, I currently have Oracle CDC tool up and running. The changed data are flowing through from the Oracle Database to the CDC Database Instance which is created by the tool in SQL Server successfully. However, the only issue I have is the tool will stuck in the Logger Mode if I stop the CDC Service for a period of time (like an hour).

Could you please advise what caused this? And how resolve it?

Errors as below:

timestamp,type,node,status,sub_status,status_message,source, text_data,binary_data
"15/04/2021 10:04:54 PM","INFO","SQLSERVER","RUNNING","INIT","ORACDC300I:The Oracle CDC instance for service OracleCDCService1 and database ORACLE_CDC_TEST started.","service","",""
"15/04/2021 10:04:54 PM","INFO","SQLSERVER","RUNNING","INIT","ORACDC305I:The value of the option 'source_env_report' was changed to 11.","infrastructure","",""
"15/04/2021 10:05:04 PM","ERROR","SQLSERVER","SUSPENDED","LOGGER","ORACDC511E:The Oracle CDC failed to position.","source","",""
"15/04/2021 10:05:15 PM","ERROR","SQLSERVER","SUSPENDED","LOGGER","ORACDC511E:The Oracle CDC failed to position.","source","",""
"15/04/2021 10:05:55 PM","INFO","SQLSERVER","RUNNING","IDLE","ORACDC303I:The Oracle CDC instance for service OracleCDCService1 and database ORACLE_CDC_TEST received a reconfigure signal.","service","",""
"15/04/2021 10:05:55 PM","INFO","SQLSERVER","RUNNING","IDLE","ORACDC305I:The value of the option 'source_env_report' was changed to 12.","infrastructure","",""
"15/04/2021 10:05:55 PM","REPORT","SQLSERVER","SUSPENDED","LOGGER","ORACDC000R:Oracle Diagnostics","infrastructure","
CDC Service version is: 6.0.0.129

Character set information:
NLS_CHARACTERSET: 178
LS_NCHAR_CHARACTERSET: 2000

Reader Information:
Thread: 1
Last Transaction SCN: 165592140
Last SCN: 165592140
Greatest Sequence: 9813
Start SCN: 165592140
Transaction Sequencer: 1
Context Sequencer: 0
Max SCN: 0
Last RBASQN: 9801
Last RBABLK: 36610
Last RBABYTE: 16
State: 0

Redo logs:

Captured Tables:
82220
28846
28845
28841
28827
28821
28733
28729
",""

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,809 questions
SQL Server Integration Services
SQL Server Integration Services
A Microsoft platform for building enterprise-level data integration and data transformations solutions.
2,460 questions
0 comments

10 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Monalv-MSFT 5,896 Reputation points
    2021-04-19T07:05:09.63+00:00

    Hi @EF Lin ,

    1.May I know if you can share the version of your SQL Server?

    2.Please start the CDC Service when you use it.

    3.Please refer to the following links:
    a.Working with the Oracle CDC Service
    b.Change Data Capture Service for Oracle by Attunity System Architecture
    c.Known errors and resolutions with change data capture for Oracle by Attunity

    Best regards,

    Mona

    If the answer is helpful, please click "Accept Answer" and upvote it.

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

  2. EF Lin 1 Reputation point
    2021-04-19T22:05:54.893+00:00

    Thanks @Monalv-MSFT . I'm running SQL2019.

    I have gone through those links. The CDC service is running fine.

    Only issue is that it will go into Logger Mode with the error above when I paused it for a period of time.

  3. Seshu 1 Reputation point
    2021-04-27T11:33:33.763+00:00

    Hi, Wonder if there was any resolution fund for the problem mentioned above ? I am finding similar issue with Change Data Capture Service for Oracle by Attunity. I am using Oracle 11g as Source and SQL 2016 as target. Also using Windows 2016 Server to run the CDC designer.

    "27/04/2021 10:42:48","INFO","NPEW10VM01","RUNNING","IDLE","ORACDC305I:The value of the option 'source_env_report' was changed to 2.","infrastructure","",""
    "27/04/2021 10:42:49","REPORT","NPEW10VM01","SUSPENDED","LOGGER","ORACDC000R:Oracle Diagnostics","infrastructure","
    CDC Service version is: 4.0.0.107

    Character set information:
    NLS_CHARACTERSET: 178
    LS_NCHAR_CHARACTERSET: 2000

    Reader Information:
    Thread: 1
    Last Transaction SCN: 4818641329357
    Last SCN: 4818641329357
    Greatest Sequence: 24
    Start SCN: 4818641329357
    Transaction Sequencer: 1
    Context Sequencer: 0
    Max SCN: 0
    Last RBASQN: 13
    Last RBABLK: 135
    Last RBABYTE: 132
    State: 0

    Redo logs:

    Captured Tables:
    329415
    329413
    329412
    329411
    329410
    329409
    329408
    329407
    329406

    0 comments
  4. EF Lin 1 Reputation point
    2021-04-28T00:44:40.347+00:00

    And I also notice the timestamp in the xdbcdc_state table is different from what I can see in the UI. Why is that?

    91847-image.png

    91848-image.png

    0 comments
  5. Seshu 1 Reputation point
    2021-04-28T18:34:43.223+00:00

    FYI..
    In the link below, it has been mentioned that following permissions at Oracle end resolved the issue for them.
    I have asked our DBA to run the same at our end, but third line (GRANT LOGMINING TO <USER>;) comes up as invalid it seems.

    GRANT SELECT ON V_$INSTANCE TO <USER>;
    GRANT EXECUTE_CATALOG_ROLE TO <USER>;
    GRANT LOGMINING TO <USER>;

    https://social.msdn.microsoft.com/Forums/ie/en-US/fe5067a0-2eab-4125-bad3-709bd0b35e6f/cdc-for-oracle-by-attunity-stuck-at-status-logger?forum=sqlintegrationservices

    0 comments