StarQuest Technical Documents

SQDR Plus: Tips for Oracle Data Replication

Last Update: 18 October 2022
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:

• Quick Start Guide to Using SQDR Plus on Windows with Oracle
• SQDR Plus Oracle User Authorities
• SQDR Plus: Oracle XStream Support

Contents:

• Managing Oracle Log Files
• ROWID derived column name (Oracle destination only)
• Timezone region error
• Virtual columns
• Unsupported Data Types
• Mapping a key column from floating point to NUMBER (Oracle destination)
• ORA-03137: malformed TTC packet (Oracle destination)
• ORA-01291: missing logfile
• XML or LOB-only changes not detected
• Use Primary Key (rather than ROWID) if source table contains LOB column
• Amazon RDS for Oracle
• Specifying Query Parallelism
• Disable the Parent Agent (PDB Envrionment)
• Oracle Native Network Encryption (NNE)

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

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

 

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.
SQL attempted: 'SELECT "MYCOL" FROM "MYSCHEMA"."MYTAB" WHERE ROWID= ? '.
Keys: '{"ROWID": "AAAAAAAAAAAAAAAAAA"}'.
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.

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

• 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)

begin
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');
end;
/
commit;

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

Specifying Query Parallelism

Enabling parallelism for the user being used by SQDR (Tier 3). can potentially improve performance for baselines from an Oracle source or when writing to an Oracle destination.

Note that this does not provide any benefit for LogMiner/XStream connections from Tier 2.

Refer to the Oracle documentation for details.

For example:

create or replace TRIGGER SYSTEM."LOGON_SQDR_TRIGGER" AFTER
LOGON ON DATABASE
declare
begin
IF sys_context('USERENV','SESSION_USER') LIKE '%SQDRUSER%' THEN
EXECUTE IMMEDIATE('alter session set optimizer_features_enable="19.1.0"');
EXECUTE IMMEDIATE('ALTER SESSION SET PARALLEL_DEGREE_LIMIT=2');
EXECUTE IMMEDIATE('ALTER SESSION SET PARALLEL_DEGREE_POLICY=LIMITED');
END IF;
end logon_clarity_trigger;

or

create or replace TRIGGER SYSTEM."LOGON_SQDR_TRIGGER" AFTER
LOGON ON DATABASE
declare
begin
IF sys_context('USERENV','SESSION_USER') = 'MYSQDRUSER' THEN
EXECUTE IMMEDIATE('alter session set optimizer_features_enable="19.1.0"');
EXECUTE IMMEDIATE('ALTER SESSION SET PARALLEL_DEGREE_LIMIT=2');
EXECUTE IMMEDIATE('ALTER SESSION SET PARALLEL_DEGREE_POLICY=LIMITED');
END IF;
end logon_clarity_trigger;

Disable the Parent Agent (PDB environment)

If you are connecting to an Oracle (12c & later) multitenant container database (CDB), you will see two or more agents: a parent agent that connects to the root container, and a subagent for each PDB (pluggable database) that you wish to connect to. Disable the parent agent; you will only be using the PDB subagent.

Oracle Native Network Encryption (NNE)

Support for Oracle Native Network Encryption (aka Oracle Advanced Security) is implemented by the SQDR Oracle 8 ODBC driver using the properties EncryptionLevel, EncryptionTypes, DataIntegrityLevel, and DataIntegrityTypes.

Symptom:
Using the SQDR Oracle 8 ODBC driver to connect to an Oracle system configured for Native Network Encryption (NNE, aka Advanced Security) fails with the error:

[Oracle]ORA-12656: Cryptographic checksum mismatch

Solution:
Update to SQDR_ODBC 8.0.1.54 (May 2022) or later. The SQDR_ODBC (bundled drivers) package can be updated independently of SQDR. Stop the SQDR service before updating SQDR_ODBC.