question

EFLin-4569 avatar image
0 Votes"
EFLin-4569 asked Seshu-8446 answered

CDC for Oracle by Attunity stuck at status Logger after restart

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-generalsql-server-integration-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Monalv-msft avatar image
0 Votes"
Monalv-msft answered EFLin-4569 commented

Hi @EFLin-4569 ,

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @EFLin-4569 ,

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 tell us the reason that you stop the CDC Service for a period of time?

Best regards,
Mona

0 Votes 0 ·

It's more for testing. There would be good reason to stop the service in the real world scenario I assume.. such as database maintenance, server upgrade etc.. We want to ensure the tool can be stopped and resumed.

Thanks
Coby

0 Votes 0 ·
EFLin-4569 avatar image
0 Votes"
EFLin-4569 answered Seshu-8446 commented

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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I could replicate the same behavior in my environment too. I have stopped the service (instance under CDC Designer) yesterday and started this morning. It was in IDLE(running) state for few minutes and then failed, status is shown as "LOGGER" after that. Please see the screenshot below.

92087-image.png


0 Votes 0 ·
image.png (30.4 KiB)
Seshu-8446 avatar image
0 Votes"
Seshu-8446 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EFLin-4569 avatar image
0 Votes"
EFLin-4569 answered

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



image.png (14.3 KiB)
image.png (41.8 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Seshu-8446 avatar image
0 Votes"
Seshu-8446 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EFLin-4569 avatar image
0 Votes"
EFLin-4569 answered

@Seshu-8446 We don't have that issue. Logmining permission has been granted to the account without problem..

The only problem we have is when we resume the CDC service.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Seshu-8446 avatar image
0 Votes"
Seshu-8446 answered EFLin-4569 commented

We kind of know what is going wrong here but no solution yet. Its is not a problem with the stop and start for us. It is something to do with the Oracle archive logs. Our archive log process kickoff at around 8 PM(in Dev environment) and service is failing link between the online and archive logs seamlessly hence it goes into Suspend state. It is somehow unable to link the transaction logs between the archived one and online. Not sure if there is any fundamental gap here.
We are using Oracle 11g and SQL Server 2016.

Also, oracle account that we are using has DBA got permissions at DB end so assuming that it is not a permission related as such.

92901-image.png

92854-image.png



image.png (10.9 KiB)
image.png (67.2 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Would you CDC service go into the Logger mode by itself? Ours works fine if we just keep the CDC service up and running..

0 Votes 0 ·
Seshu-8446 avatar image
0 Votes"
Seshu-8446 answered

hi, Wonder if someone can advise on the following... The Name filed under V$ARCHIVED_LOG view is set to blank as shown in the below screenshot. Not sure if the "Name" filed is mandatary for Logminer to work particularly when the transactions are archived to a file ? It could be the reason why the CDC service is unable to locate the record after archiving takes place at 8 PM. It then goes into SUSPENDED->LOGGER state.

Stattus_message under cdc.xdbcdc_trace table shows as "ORACDC511E:The Oracle CDC failed to position."

Thanks for your help.

93544-image.png



image.png (159.3 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EFLin-4569 avatar image
0 Votes"
EFLin-4569 answered

Further testing, I left the service run for few days, and it went to Logger Mode by itself for some reasons...could someone please help...



timestamp,type,node,status,sub_status,status_message,source, text_data,binary_data
"11/05/2021 2:11:23 AM","INFO","Test_Server","RUNNING","IDLE","ORACDC303I:The Oracle CDC instance for service OracleCDCService1 and database ORACLE_CDC_TEST received a reconfigure signal.","service","",""
"11/05/2021 2:11:23 AM","INFO","Test_Server","RUNNING","IDLE","ORACDC305I:The value of the option 'source_env_report' was changed to 16.","infrastructure","",""
"11/05/2021 2:11:24 AM","REPORT","Test_Server","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: 172832214
Last SCN: 172832214
Greatest Sequence: 10091
Start SCN: 172832214
Transaction Sequencer: 2
Context Sequencer: 0
Max SCN: 0
Last RBASQN: 10068
Last RBABLK: 67066
Last RBABYTE: 460
State: 0

Redo logs:

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Seshu-8446 avatar image
0 Votes"
Seshu-8446 answered

We managed to troubleshoot the issue at our end couple of weeks ago. This was down to the Oracle Archiving process setup at our end, it triggers at around 8 PM in the night, right after that those archive logs (.ARC files) were being deleted by another job as part of the Daily Backups. Essentially that .arc file is being deleted by Daily backup job before CDC process is done with it, hence this issue.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.