Troubleshooting Tips for CDC for Oracle on SQL Server

Get Processing Range error Timeout

Reason: It's because you have a CDC transaction not committed yet.

Solution:
Run DBCC OPENTRAN
Identify the active sessions running the program: MSXDBCDC$OracleCDCService1$xxxxYourCDC Database

End the sessions
Rerun the Initial Load for "Mark Initial Load Start" and "Mark Initial Load End"

Error - LoadLibraryFromPath: The fully qualified DLL C:\Windows\oci.dll was not found.

Reason:

One of our SSIS projects repeatedly encountered the error mentioned in the subject.

Solution:
I checked the package and found it still uses the old Microsoft OLEDB driver for Oracle. A Windows Update mentioned in the link below has been installed on the server.
https://support.microsoft.com/en-us/kb/3147071

Could not find sys.sp_cdc_parse_captured_column_list

Upgrade CDC for Oracle version

Why does the component work, but I don't receive the latest data?

The dates do not match. Why? I'm not sure. I just migrated Oracle to another server, keeping the same server name and configuration. The only change was the IP address.

After I queried the table "[cdc].[xdbcdc_staged_transactions]" and compared it with the GUI, I found that the log had stopped at a certain transaction and was not progressing further.

So, I executed the command to check for any non-distributed transactions:

DBCC OPENTRAN

It requires a manual commit with command like

EXEC sp_repltrans

Microsoft has a solution for this.