Share via

Troubleshoot CDC Instance Errors in Microsoft Change Data Capture for Oracle by Attunity

  • Article

This article lists the top Troubleshooting errors and issues when viewing a CDC Instance in the Oracle CDC Designer Configuration tool. This tool is part of the Change Data Capture for Oracle by Attunity that is included starting with Microsoft SQL Server 2012.

<< Back to [[articles:Microsoft SQL Server 2012 Change Data Capture for Oracle by Attunity Topics]]

 Before spending too much time troubleshooting, it is important to use the latest builds of CDC for Oracle by Attunity, to avoid known issues such as these:

SQL 2017 Known Issues Listing:

  • Version 5.0.0.93 contains these fixes: 

             Microsoft CDC for Oracle by Attunity Designer fails with "Incorrect syntax near the keyword 'KEY'" error when adding an Oracle Table. 

SQL 2016 Known Issues Listing:

  • Version 4.0.0.95 contains these fixes: 

             Microsoft CDC for Oracle by Attunity Designer fails with "Incorrect syntax near the keyword 'KEY'" error when adding an Oracle Table. 

  • Version 4.0.0.88 contains these fixes: 

             Properties added in the Advanced options of the Attunity CDC instance are removed when a table is added or removed from CDC. 

             Attunity CDC stops working after applying SQL fix that adds __$command_id column

SQL 2014 Known Issues Listing:

  • Version 2.0.0.92 contains these fixes:

             Properties added in the Advanced options of the Attunity CDC instance are removed when a table is added or removed from CDC.              Attunity CDC stops working after applying SQL fix that adds __$command_id column

  • The metadata validation for Oracle table cdc.table_name failed. Column column_name index is out of range.  And this issue: Oracle CDC service shows aborted status when you use CDC for Oracle by Attunity: Fixed in Cumulative Update 1 for SQL Server 2014 RTM as described http://support.microsoft.com/kb/2894025  

  • Some changes are missed and they are not replicated to the SQL Server databases. This issue occurs when a table contains more than one character large binary object (CLOB) and one of the CLOBs has a large value. Fixed in Cumulative Update 1 for SQL Server 2014 SP1 and Cumulative Update 8 for SQL Server 2014 RTM as described https://support.microsoft.com/en-us/kb/3029096 

  • Change Data Capture for Oracle by Attunity stops working when Oracle tables have column with Long data type

SQL 2012 Known Issues Listing: 

  • Version 1.1.0.102 contains these fixes: 

             Properties added in the Advanced options of the Attunity CDC instance are removed when a table is added or removed from CDC.              Attunity CDC stops working after applying SQL fix that adds __$command_id column

  • CDC for Oracle instance hangs when you start it, and does not capture changes. Oracle server memory may increase until it runs out of memory or crash.

    • 2672759 Error message when you use the Microsoft Change Data Capture Service for Oracle by Attunity: "ORA-00600: internal error code" http://support.microsoft.com/kb/2672759/EN-US Add the SOURCE level tracing and confirm if you get the same ORA-00600 error. Fixed by an Oracle patch download.

  • Multiple Partitions

    • When you use more than 10 partitions on an Oracle table, the CDC instance cannot capture all the changes for the table. When the Oracle table is defined with more than 10 partitions, the changes are only captured from the last 10 partitions. 
    • This problem is fixed in the Service Pack 1 release for SQL Server 2012. (see SP1 Feature Pack download page http://www.microsoft.com/en-us/download/details.aspx?id=35580 )  

  • Changes are lost

    • The capturing of events can go into an infinite loop and stop capturing new data changes (related to oracle bug 5623813)
    • When on Oracle RAC environment and doing the stop/resume the CDC instance, changes can be skipped/lost, which means the SQL change capture will be missing important rows, and thus data loss in the data warehouse or subscribing system.
    • This problem is fixed in the Service Pack 1 release for SQL Server 2012. (see SP1 Feature Pack download page http://www.microsoft.com/en-us/download/details.aspx?id=35580 )

  • Double widths on columns in SQL

    • When creating a CDC for Oracle instance, in the scripts to run against SQL Server, the length of a variable width data type column is doubled in SQL Server tables that are created in the script. For example, if you try to track changes on a VARCHAR2(10) column in an Oracle table, then the corresponding column in the SQL Server table is NVARCHAR(20) in the deployment script. Fix in either Cumulative Update 2 for SQL Server 2012 SP1 or Cumulative update 5 for SQL Server 2012 as described in http://support.microsoft.com/kb/2769673

  • DDL Data is truncated

    • When you run a Data Definition Language (DDL) statement that is more than 4,000 bytes against an Oracle database that contains non-Latin characters, CDC for Oracle by Attunity fails. Additionally, you receive the following error message:

      ORA-01406: fetched column value was truncated.

    • Fix in Cumulative Update 4 for SQL Server 2012 SP1 as described in http://support.microsoft.com/kb/2839806

  • Changes are lost in last two columns

  • SQL Transaction log grows when you use CDC for Oracle

    •  When Change Data Capture for Oracle instances are configured, the SQL database that receives the change data will have mirrored tables, with   transactions marked for replication. This behavior occurs because CDC for Oracle relies on underlying system stored procedures that resemble those that are used in CDC for SQL Server. However, because there is no SQL CDC replication involved when CDC for Oracle is used alone, there is no log reader to clear the transactions that are marked for replication. Because the transaction does not have to be replicated in SQL Server, it's safe to manually mark the transaction as distributed by using the workaround that's described later in this article.
    • http://support.microsoft.com/kb/2871474/en-us

  • Error ORACDC000T:Error encountered at position to change event - SCN not found - EOF simulated.

  • The metadata validation for Oracle table cdc.table_name failed. Column column_name index is out of range.

  • Oracle CDC service shows aborted status when you use CDC for Oracle by Attunity in SQL Server 2012 

    Some changes are missed and they are not replicated to the SQL Server databases. This issue occurs when a table contains more than one character large binary object (CLOB) and one of the CLOBs has a large value.

    Change Data Capture for Oracle by Attunity stops working when Oracle tables have column with Long data type

  • See below for more known issues.

 

Scenario 1 - Collecting detailed logs from the CDC instance for troubleshooting 

 

A. Basic errors and events

You can see the error in the Status messages field on the Oracle Change Data Capture Designer management console, when a CDC instance is highlighed in the left pane.

You can query the trace table in the CDC database in SQL Server to see if anything has been logged.   { "2/18/2012 12:16:34 AM","ERROR","computername","ERROR","UNEXPECTED","ORACDC508E:Oracle method OCIStmtExecute failed with error: ORA-00942: table or view does not exist

","source","",""

"2/18/2012 12:16:34 AM","ERROR","computername","RUNNING","IDLE","ORACDC518E:Failed to verify archive log mode.","source","","" "2/18/2012 12:16:34 AM","ERROR","computername","ERROR","UNEXPECTED","ORACDC517E:Oracle Call Intreface (OCI) method failed: ORA-00942: table or view does not exist .","source","","" "2/18/2012 12:16:34 AM","ERROR","computername","ERROR","UNEXPECTED","ORACDC414E:The Engine component failed with return code 1.","engine","","" "2/18/2012 12:16:34 AM","INFO","computername","ERROR","STOP","ORACDC312I:The Oracle CDC instance for service OracleCDCService1 and database HR Instance finished abnormally.","service","","" 

} 

B. To save the output from basic logging

1.Click Collect Diagnostics hyperlink on the Status tab in the Oracle Change Data Capture Designer management console. 2.Review the output log file for any obvious error messages

C. For detailed errors and events

You can raise the amount of tracing collected by the instance and repeat the scenario to gather more detailed logging.

Add a new property in the Advanced Settings grid on the Advanced tab, set the name of the property to "trace," and then set the value to "SOURCE" without quotes.

Click OK to save the change.

Now reproduce the error. For example, you may start the CDC Instance and wait until an error has occurred.

Click Collect Diagnostics hyperlink on the Status tab in the Oracle Change Data Capture Designer management console.

Specify the full path and output file name, then click Create.

"2/18/2012 12:16:34 AM","ERROR","computername","ERROR","UNEXPECTED","ORACDC508E:Oracle method OCIStmtExecute failed with error: ORA-00942: table or view does not exist","source","",""

"2/18/2012 12:16:34 AM","ERROR","computername","RUNNING","IDLE","ORACDC518E:Failed to verify archive log mode.","source","",""

"2/18/2012 12:16:34 AM","ERROR","computername","ERROR","UNEXPECTED","ORACDC517E:Oracle Call Intreface (OCI) method failed: ORA-00942: table or view does not exist.","source","",""

"2/18/2012 12:16:34 AM","ERROR","computername","ERROR","UNEXPECTED","ORACDC414E:The Engine component failed with return code 1.","engine","",""

"2/18/2012 12:16:34 AM","INFO","computername","ERROR","STOP","ORACDC312I:The Oracle CDC instance for service OracleCDCService1 and database HR Instance finished abnormally.","service","",""

Scenario 2 - Oracle Call Intreface (OCI) method failed: ORA-00942 table of view does not exist.

Symptom

When using the CDC Instance there are various runtime errors and scenarios you may experience. This is a very common error as seen in the Status message field of the CDC Instance. The instance retries numerous times (Status icon will change to green momentarily) but then it will fail with the Red Exclamation and the UNEXPECTED status at the end of each try.

"2/18/2012 12:16:34 AM","ERROR","computername","ERROR","UNEXPECTED","ORACDC508E:Oracle method OCIStmtExecute failed with error: ORA-00942: table or view does not exist ","source","",""

"2/18/2012 12:16:34 AM","ERROR","computername","RUNNING","IDLE","ORACDC518E:Failed to verify archive log mode.","source","",""

"2/18/2012 12:16:34 AM","ERROR","computername","ERROR","UNEXPECTED","ORACDC517E:Oracle Call Intreface (OCI) method failed: ORA-00942: table or view does not exist .","source","",""

"2/18/2012 12:16:34 AM","ERROR","computername","ERROR","UNEXPECTED","ORACDC414E:The Engine component failed with return code 1.","engine","",""

When the Oracle account specified for connecting from the CDC Instance to the Oracle server does not have permissions to see the various system log miner views, it cannot see that the view exists, and will raise the error.

Resolution

To resolve the error, you can either change the account used for connecting to Oracle from the CDC instance to an account with the necessary permissions, or you can grant the currently configured user the appropriate permissions within the Oracle database system.

The user must be able to query this system view, in order to avoid the error message.

The list of all the necessarily permissions is detailed in the help file included in the installation program files folder [C:\Program Files\Change Data Capture for Oracle by Attunity\Attunity.SqlServer.XdbCdcDesigner.chm] See the page entitled "Connect to an Oracle Source Database" within that chm file for the complete list.

You can set the user account by selecting the CDCInstance from the left pane and clicking the Properties button in the Actions rightmost pane within the CDC Designer window. You can change the Oracle log mining authentication account from the properties dialogue page.

<< Back to [[articles:Microsoft SQL Server 2012 Change Data Capture for Oracle by Attunity Topics]]