StarQuest Technical Documents

SQDR Plus: Tips for Oracle Data Replication

Last Update: 10 September 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.

Unsupported Data Types (Oracle incremental source)

LogMiner does not support the following data types and table storage attributes. If a table contains columns having any of these unsupported data types, then the entire table is ignored by LogMiner, and SQDR Plus is unable to perform incremental replication.

  • BFILE
  • Nested tables
  • Objects with nested tables
  • Tables with identity columns
  • Temporal validity columns
  • PKREF columns
  • PKOID columns
  • Nested table attributes and stand-alone nested table columns
  • Tables containing columns with names > 30 characters (column name length limit was increased from 30 to 128 characters in Oracle v12.2)

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.

ORA-01291: missing logfile

Issue:

A customer updated several Oracle source systems from Oracle 11 to Oracle 18. On most systems, no problems were encountered; however one system encountered the following symptoms:


* SQDR Plus agent is failing with the following error:

ReplicationWorkerFactory.performWorkerHealthCheck:
com.starquest.sqdr.capture.ReplicationException:
Unexpected native error: 99999: ORA-01291: missing logfile
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 1
at com.starquest.sqdr.capture.ora.OracleLogMiner.next(OracleLogMiner.java:316)

 

* The following command shows that V$ARCHIVED_LOG contains old/stale entries, dating from prior to the update, and referencing REDO log filenames that no longer exist:

select * from V$ARCHIVED_LOG AL where name is not null;

Solution:

Issue the following command to clear the invalid entries from the $archived_log view:

“EXECUTE SYS.DBMS_BACKUP_RESTORE.RESETCFILESECTION( 11 );



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.