CDC instance stops every night

[JHouston]

I have an Oracle CDC instance that runs great during the day, but stops every evening. I discovered that the Oracle source databases are actually performing offline backups each night, which causes the CDC instance to disconnect. Unfortunately, I am not able to change the way the backups are being performed so I am trying to find a solution within the CDC setup itself. Is there any way to pause the instance if it can't find the source and set it to start up again after a certain amount of time? Or some other way to prevent the instance from completely stopping each time it disconnects.

Thanks for any input!

[Gadi.Farhat]

Hi,

The CDC instance should reconnect automatically on disconnects. When that happens, please use the collect dignostics option from the Change Data Capture Desiner and attach the generated trace file.

Regards,

Gadi

[JHouston]

Hi Gadi,


Attached is the trace log after trying to start the instance this morning.
I appreciate you taking a look at it. Thanks

Jessie

[Gadi.Farhat]

Hi Jessie,

From the log it seems that the Oracle CDC Instance is not disconnecting. It is failing to add an archived redo log (With sequence 7628). This is most likely related to the backup process you mentioned. It looks like the backup process is deleting the archived logs while they are still required. The capture instance can't ignore such error because, that may cause lost changes.

You should verify the archive logs are available for enough time, after they were archived, before they can be deleted.


Regards,

Gadi

[JHouston]

I've let our Oracle administrator know and he's looking into it from that side. Thanks for your help Gadi!

[JHouston]

Hi Gadi,

We discovered the archive logs were being zipped every night which is why we were getting the original error. That has been fixed, but the instance is still failing every night when it can't reconnect to Oracle. When I attempt to restart the CDC instance in the morning now it will start up fine, but all of the tables are gone. I don't see anything that jumps out in the log, it just looks like the instance attempts to reconnect a couple of times then stops after about a minute and the log ends. I've attached the latest log if you would take a look at it and see what you think?

Thanks
Jessie

[Gadi.Farhat]

Hi Jessie,

The CDC instance is failing to connect to the Oracle database and therefore reports that it is failing abnormally. The CDC Instance tries to reconnect for 6 times, which is the default, and then gives up.

You can increase the number of retries by setting the "cdc_restart_limit" property to a higher number or to '-1'. This property defines after how many failures per hour the service stops to restart the instance. The value -1 means that the instance should be always restarted.

You can change this propety value by appending "cdc_restart_limit=-1" , for example, to the 'options' field value in the [MSXDBCDC].[dbo].[xdbcdc_services] table, in the target SQL Server instance.


Regards,

Gadi