Oracle - Latest

Oracle CHAR, RAW and VARCHAR2 column size limitations

Note: The following limitations apply only to trigger-based replication. There is no limitation for Log-based capture. Connect CDC supports a maximum column size of 1996 characters for the following Oracle datatypes:

CHAR
VARCHAR2

Connect CDC supports a maximum column size of 998 bytes for the following Oracle datatype:

RAW

Note: The above size restrictions also apply when replicating data from other databases into Oracle targets, if the Oracle target also acts as a source server for replication. For example, if data is sent from MS SQL Server to Oracle and also from Oracle to MS SQL Server, then the same size restrictions must be observed in MS SQL Server, even though larger sizes are allowed. If a value greater than the specified limit is replicated into Oracle, any subsequent delete statements executed against the data in Oracle will fail. The following error message may be issued from the trigger:

**ERROR** 0277:ERROR: ORA-06502: PL/SQL: numeric or value error: character
                  string buffer too small

TIMESTAMP values between 02:00 and 02:59 during the Spring clock change are not valid in MySQL

The Connect CDC Applier processes MySQL TIMESTAMP column values so that they are updated appropriately in the MySQL database. Since this problem is occurring because MySQL is presuming that TIMESTAMP values are in the local time zone of the client, the Applier determines what the time zone offset is, and converts it to GMT to determine if that value is valid.

Since MySQL stores TIMESTAMP column values in GMT format in the database, unexpected conversions may occur if the TIMESTAMP value arriving from the source is within the hour when the Spring Daylight Savings time switch occurs. When updated into MySQL and then displayed after the value is replicated, it is likely that the TIMESTAMP value will be offset by 1 hour. This is how MySQL processes TIMESTAMP values and is not a defect in Connect CDC.

Connect CDC cannot be setup to delete Oracle archive logs

In a remote host Oracle configuration, Connect CDC cannot be setup to delete Oracle archive logs. Since the kernel is not running on the Oracle host, there is no mechanism to delete the archive logs.

Oracle TIMESTAMP Datatype Support

Oracle supports the TIMESTAMP datatype with a fractional second precision up to 9 digits. Connect CDC only supports a fractional second precision up to a maximum of 6 digits, the default.

Oracle unicode target column values replicated to the target column can be truncated

Oracle unicode source column values replicated to the target column can be truncated because the XML length is incorrect. To avoid this issue ensure that you use the following JDBC driver versions:

For Oracle 11g use 11.2.0.4 and above

Metabase Install

In order to do a metabase install for Oracle, Connect CDC Director must connect as user SYS, however, you need to create a password file with the orapwd command to allow remote connections as user SYS. Please see Oracle documentation for information about the orapwd command.

Handling NUMBER (no p,s) as FLOAT or INTEGER

The omnient.ini file [Director] section controls whether the Connect CDC Director assumes a NUMBER, no p,s, is defined internally as a FLOAT or an INTEGER:

OracleNUMBERWithoutPrecisionOrScaleIsINTEGER=<1 or 0>

If this is set to 1, NUMBER is NUMBER(38).
If this is set to 0, NUMBER is FLOAT.

The table must be refreshed in order for this NUMBER interpretation to be in effect.

Change Selector

Due to the order in which rows are processed using Oracle, Oracle Change Selector capture does not handle FK constraints that have “on delete cascade” set. The order of processing may not be as expected.

Possible solutions are:

Use the trigger on the target to filter out the child table deletes.
If individual child table rows are not deleted (that is, they always use cascading delete), then the deletes could be filtered out in any expression.
Use the replication setting “Target correct, ignore” for unprotected collision resolution and set for delete.

Locking

During a model update, if you are using Oracle and have a heavy update environment, you may encounter the error:

ORA-00054: resource busy and acquire with NOWAIT specified.

If this occurs, you must give Connect CDC exclusive access to the table for a short period of time. Connect CDC must block out users and execute a table lock while it waits for any pending updates to complete and blocks any new updates until after the log group is created. This avoids getting the Oracle error.