StarQuest Technical Documents

SQDR Plus: Tips for Oracle Data Replication

Last Update: 10 September 2021
Product: SQDR Plus
Version: 4.50 and later
Article ID: SQV00PL022


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:


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:


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 the SQDR Service Properties application to change the advanced setting rowidName to another value (e.g. RRN). Future subscriptions will create a derived column named RRN rather than ROWID.

See Advanced Settings in the Reference chapter of the SQDR help file (drmgr.chm) for details.

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


Create the following two files:

(/var/sqdrplus/conf/wrapper-local.conf on Linux)

containing this line:

(/var/sqdrplus/jetty/wrapper-local.conf on Linux)

containing this line:

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.

  • 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


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:

Unexpected native error: 99999: ORA-01291: missing logfile
ORA-06512: at "SYS.DBMS_LOGMNR", line 58
ORA-06512: at line 1


* 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;


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



XML or LOB-only Changes not Detected

Due to LogMiner limitations, changes involving only an XML or a LOB column are not detected. It is necessary to also update a non-XML (or non-LOB) field, either in the update statement or by trigger.

Use Primary Key (rather than ROWID) if source table contains LOB column

Incremental replications of tables that contain LOB data should be referenced using a primary key rather than ROWID. Failing to do so will result in the error:

Error processing LOB change data for subscription MYTAB.
ODBC message: SQLSTATE HY000, native error 1410, [StarQuest][ODBC Oracle Wire Protocol driver][Oracle]ORA-01410: invalid ROWID

Amazon RDS for Oracle

Amazon RDS for Oracle is a managed DBMS service and restricts access to some system procedures and tables that require advanced privileges.

The following considerations apply when using AWS RDS for Oracle as an incremental source:

  • Before creating the agent, use rdsadmin_util functions to enable supplemental logging and set the archivelog retention to a non-zero value.

rdsadmin.rdsadmin_util.alter_supplemental_logging(p_action => 'ADD');
rdsadmin.rdsadmin_util.set_configuration(name => 'archivelog retention hours', value => '24');

  • If you are using the Add Agent wizard of SQDR Control Center, the following grants will fail. These errors appear in the jetty wrapper log.

grant execute on SYS.DBMS_LOGMNR to SQDR;
insufficient privileges
grant select on V_$DATABASE to SQDR;
ORA-00942: table or view does not exist

Use the following procedures to perform these grants (run as the RDS master user)

rdsadmin.rdsadmin_util.grant_sys_object(p_obj_name => 'V_$DATABASE', p_grantee => 'SQDR', p_privilege => 'SELECT');
exec rdsadmin.rdsadmin_util.grant_sys_object('DBMS_LOGMNR','SQDR','EXECUTE');

Alternatively, you can use the scripts described in SQDR Plus Oracle User Authorities to create users and issue grants before creating the Agent.


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.