CDC for Oracle by Attunity stuck at status Logger after restart

Question

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

Answer 1

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.

Answer 2

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.

Answer 3

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

Answer 4

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

Answer 5

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