StarQuest Technical Documents

SQDR Plus: Tips for Oracle Data Replication

Last Update: 13 May 2019
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL022

Abstract

This technical document contains tips and troubleshooting hints hints when using SQDR Plus for incremental data replication to or from an Oracle database.

The following technical documents provide additional information:

Contents:

Managing Oracle Log Files

SQDR Plus uses Oracle LogMiner to extract incremental changes from redo log files. If a log file is deleted prematurely (before it has been examined by SQDR Plus), all incremental subscriptions will be flagged as requiring new baselines.

An Oracle administrator can use the following information to determine which log files can be removed without affecting SQDR Plus incremental replications.

The following SQL statement returns a list of log files that can be deleted safely:

select AL.SEQUENCE#,AL."NAME" from V$ARCHIVED_LOG AL, SQDR.SQ_READERS R where AL."NAME" IS NOT NULL and AL."NEXT_CHANGE#" <= R.SCN

ROWID derived column name (Oracle destination only)

When replicating a table without a primary key from an Oracle source, SQDR will use the ROWID in place of the primary key. The default name of the derived column in the destination table will be ROWID. This succeeds if the destination is a database other than Oracle. However, if the destination is also Oracle, the CREATE TABLE statement will fail with the following error because ROWID is a reserved keyword in Oracle:

SQLSTATE 42S22, native error 904
[Oracle][ODBC][Ora]ORA-00904: "ROWID": invalid identifier

Soution: use regedit to change the registry entry HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\StarQuest\SQDRSVC\rowidName to another value (e.g. RRN) and restart the SQDR service. Future subscriptions will create a derived column named RRN rather than ROWID.

Timezone region error

The following error was encountered when creating or running a Staging Agent for an Oracle source in the CAT (Central African Time) timezone. This error may occur in other timezones, substitute the appropriate timezone name.

Unable to validate connection parameters.
ORA-00604: error occurred at recursive level 1
ORA-01882: timezone region not found

Solution:

Create the following two files:

C:\ProgramData\StarQuest\sqdrplus\conf\wrapper-local.conf
(/var/sqdrplus/conf/wrapper-local.conf on Linux)

containing this line:

wrapper.java.additional.3=-Duser.timezone=CAT

C:\ProgramData\StarQuest\sqdrplus\jetty\wrapper-local.conf
(/var/sqdrplus/jetty/wrapper-local.conf on Linux)

containing this line:

wrapper.java.additional.5=-Duser.timezone=CAT

After creating the files, restart the jetty and capagent services.

Virtual Columns

If you are performing incremental replication from an Oracle table containing virtual columns:

  • Use SQDR Plus 4.97 or later (previous versions of SQDR Plus will flag the table as needing a baseline whenever a row is inserted/updated).
  • Do not include such columns in your subscription, since changes to these columns are not reported by LogMiner. Instead, define a derived column in Data Replicator Manager to explicitly calculate the column value. The defintion should use SQL syntax that is acceptable to both Oracle and DB2 LUW.

Mapping a key column from floating point to NUMBER

When replicating from certain source systems (e.g. SQL Server, DB2 for i) to Oracle, floating point columns on the source are mapped to the NUMBER data type on the Oracle destination.

When such a column is used as the primary key of the table (as is typical in a JD Edwards application) and you are using the SQDR bundled ODBC driver for Oracle, best performance is obtained by adding FPP=1 (FloatingPointParmeters) to the connection string or the ODBC data source.

ORA-03137: malformed TTC packet

Issue: When using the SQDR Oracle 8 ODBC driver for an Oracle destination, the following error occurred:

Insert failed at destination. [StarQuest][ODBC Oracle Wire Protocol driver][Oracle]ORA-39776: fatal Direct Path API error loading table "MYSCHEMA"."MYTABLE"
ORA-03137: malformed TTC packet from client rejected: [klaprs_11] [109] [512] [3] [4] [110]

Solution: This error results from using Bulk Load (default) when replicating tables with more than 255 columns.

Either disable Bulk Load by adding EBL=0 to the connection string, or reduce the number of columns being replicated.



DISCLAIMER

The information in technical documents comes without any warranty or applicability for a specific purpose. The author(s) or distributor(s) will not accept responsibility for any damage incurred directly or indirectly through use of the information contained in these documents. The instructions may need to be modified to be appropriate for the hardware and software that has been installed and configured within a particular organization.  The information in technical documents should be considered only as an example and may include information from various sources, including IBM, Microsoft, and other organizations.