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

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 with Microsoft SQL Server 2012.

<< Back to Microsoft SQL Server 2012 Change Data Capture for Oracle by Attunity Topics

Known Issues Listing:

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:
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/
  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 scheduled to be fixed in a future release download, which is not yet available.
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
- Fix in Cumulative update 5 for SQL Server 2012 SP1 as described in http://support.microsoft.com/kb/2850282
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
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.

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

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.

To do this, open the instance properties by selecting the Properties button on the right pane when the CDC instance is selected.

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.

After the issue occurs, run the following query to view the trace data, or export the diagnostic information to a text file.

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.

Example output from the log file
{
"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.

If you click the link to Collect Diagnostics, and specify an output file, note the following full error text in the output text file
{
"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","",""
}

Cause

The cause of this "does not exist" error is likely to be permissions related.
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.
SELECT * FROM v$database;

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.