GoldenGate

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Column Mapping Error on Dissimilar databases - Golden Gate for Big Data

3505428Jul 26 2017 — edited Oct 12 2017

Hi,

I'm working on a data replication project using Golden Gate between Oracle and Cassandra and I could achieve a simple replication of employee table with 2 columns - employeeid, name - between Oracle and Cassandra with exact table structures. Now, I shifted to a slightly complex use case where table structures on Oracle and Cassandra databases are different due to which I've to utilise COLMAP function. However, I'm encountering Error in COLMAP clause. Unrecognised clause or element.

Detail description is given below:

Golden Gate Version on Source side: 12.2.0.1

Golden Gate for Big Data Version: 12.3.0.1.0

Oracle Database Version: 12c Release 1 Enterprise Edition

Cassandra DSE software: 3.9.0

DDL for Oracle (source) table:

pulse_mesage ddl:

CREATE TABLE "OGGSRC"."PULSE_MESSAGE"

( "EVENT_ID" VARCHAR2(36 BYTE),

"FIELD_001" VARCHAR2(4 BYTE),

"FIELD_002" VARCHAR2(19 BYTE),

"FIELD_003" VARCHAR2(6 BYTE),

"FIELD_004" VARCHAR2(12 BYTE),

"FIELD_005" VARCHAR2(12 BYTE),

"FIELD_006" VARCHAR2(12 BYTE),

"FIELD_007" VARCHAR2(10 BYTE),

"FIELD_008" VARCHAR2(8 BYTE),

"FIELD_009" VARCHAR2(8 BYTE),

"FIELD_010" VARCHAR2(8 BYTE),

"FIELD_011" VARCHAR2(6 BYTE),

"FIELD_012" VARCHAR2(6 BYTE),

"FIELD_013" VARCHAR2(4 BYTE),

"FIELD_014" VARCHAR2(4 BYTE),

"FIELD_015" VARCHAR2(4 BYTE),

"FIELD_016" VARCHAR2(4 BYTE),

"FIELD_017" VARCHAR2(4 BYTE),

"FIELD_018" VARCHAR2(4 BYTE),

"FIELD_019" VARCHAR2(3 BYTE),

"FIELD_020" VARCHAR2(3 BYTE),

"FIELD_021" VARCHAR2(3 BYTE),

"FIELD_022" VARCHAR2(4 BYTE),

"FIELD_023" VARCHAR2(3 BYTE),

"FIELD_024" VARCHAR2(3 BYTE),

"FIELD_025" VARCHAR2(2 BYTE),

"FIELD_026" VARCHAR2(2 BYTE),

"FIELD_027" VARCHAR2(1 BYTE),

"FIELD_028" VARCHAR2(9 BYTE),

"FIELD_029" VARCHAR2(9 BYTE),

"FIELD_030" VARCHAR2(9 BYTE),

"FIELD_031" VARCHAR2(9 BYTE),

"FIELD_032" VARCHAR2(11 BYTE),

"FIELD_033" VARCHAR2(11 BYTE),

"FIELD_034" VARCHAR2(28 BYTE),

"FIELD_035" VARCHAR2(37 BYTE),

"FIELD_036" VARCHAR2(104 BYTE),

"FIELD_037" VARCHAR2(12 BYTE),

"FIELD_038" VARCHAR2(6 BYTE),

"FIELD_039" VARCHAR2(2 BYTE),

"FIELD_040" VARCHAR2(3 BYTE),

"FIELD_041" VARCHAR2(8 BYTE),

"FIELD_042" VARCHAR2(15 BYTE),

"FIELD_043" VARCHAR2(40 BYTE),

"FIELD_044" VARCHAR2(27 BYTE),

"FIELD_045" VARCHAR2(79 BYTE),

"FIELD_046" VARCHAR2(255 BYTE),

"FIELD_047" VARCHAR2(255 BYTE),

"FIELD_048" VARCHAR2(255 BYTE),

"FIELD_049" VARCHAR2(3 BYTE),

"FIELD_050" VARCHAR2(3 BYTE),

"FIELD_051" VARCHAR2(3 BYTE),

"FIELD_052" VARCHAR2(16 BYTE),

"FIELD_053" VARCHAR2(16 BYTE),

"FIELD_054" VARCHAR2(120 BYTE),

"FIELD_055" VARCHAR2(255 BYTE),

"FIELD_056" VARCHAR2(255 BYTE),

"FIELD_057" VARCHAR2(255 BYTE),

"FIELD_058" VARCHAR2(255 BYTE),

"FIELD_059" VARCHAR2(255 BYTE),

"FIELD_060" VARCHAR2(255 BYTE),

"FIELD_061" VARCHAR2(255 BYTE),

"FIELD_062" VARCHAR2(255 BYTE),

"FIELD_063" VARCHAR2(255 BYTE),

"FIELD_064" VARCHAR2(64 BYTE),

"FIELD_065" VARCHAR2(64 BYTE),

"FIELD_066" VARCHAR2(1 BYTE),

"FIELD_067" VARCHAR2(2 BYTE),

"FIELD_068" VARCHAR2(3 BYTE),

"FIELD_069" VARCHAR2(3 BYTE),

"FIELD_070" VARCHAR2(3 BYTE),

"FIELD_071" VARCHAR2(4 BYTE),

"FIELD_072" VARCHAR2(4 BYTE),

"FIELD_073" VARCHAR2(6 BYTE),

"FIELD_074" VARCHAR2(10 BYTE),

"FIELD_075" VARCHAR2(10 BYTE),

"FIELD_076" VARCHAR2(10 BYTE),

"FIELD_077" VARCHAR2(10 BYTE),

"FIELD_078" VARCHAR2(10 BYTE),

"FIELD_079" VARCHAR2(10 BYTE),

"FIELD_080" VARCHAR2(10 BYTE),

"FIELD_081" VARCHAR2(10 BYTE),

"FIELD_082" VARCHAR2(12 BYTE),

"FIELD_083" VARCHAR2(12 BYTE),

"FIELD_084" VARCHAR2(12 BYTE),

"FIELD_085" VARCHAR2(12 BYTE),

"FIELD_086" VARCHAR2(16 BYTE),

"FIELD_087" VARCHAR2(16 BYTE),

"FIELD_088" VARCHAR2(16 BYTE),

"FIELD_089" VARCHAR2(16 BYTE),

"FIELD_090" VARCHAR2(42 BYTE),

"FIELD_091" VARCHAR2(1 BYTE),

"FIELD_092" VARCHAR2(2 BYTE),

"FIELD_093" VARCHAR2(5 BYTE),

"FIELD_094" VARCHAR2(7 BYTE),

"FIELD_095" VARCHAR2(42 BYTE),

"FIELD_096" VARCHAR2(64 BYTE),

"FIELD_097" VARCHAR2(17 BYTE),

"FIELD_098" VARCHAR2(25 BYTE),

"FIELD_099" VARCHAR2(11 BYTE),

"FIELD_100" VARCHAR2(11 BYTE),

"FIELD_101" VARCHAR2(17 BYTE),

"FIELD_102" VARCHAR2(28 BYTE),

"FIELD_103" VARCHAR2(28 BYTE),

"FIELD_104" VARCHAR2(510 BYTE),

"FIELD_105" VARCHAR2(255 BYTE),

"FIELD_106" VARCHAR2(255 BYTE),

"FIELD_107" VARCHAR2(255 BYTE),

"FIELD_108" VARCHAR2(255 BYTE),

"FIELD_109" VARCHAR2(255 BYTE),

"FIELD_110" VARCHAR2(255 BYTE),

"FIELD_111" VARCHAR2(255 BYTE),

"FIELD_112" VARCHAR2(255 BYTE),

"FIELD_113" VARCHAR2(255 BYTE),

"FIELD_114" VARCHAR2(255 BYTE),

"FIELD_115" VARCHAR2(255 BYTE),

"FIELD_116" VARCHAR2(255 BYTE),

"FIELD_117" VARCHAR2(255 BYTE),

"FIELD_118" VARCHAR2(255 BYTE),

"FIELD_119" VARCHAR2(255 BYTE),

"FIELD_120" VARCHAR2(255 BYTE),

"FIELD_121" VARCHAR2(255 BYTE),

"FIELD_122" VARCHAR2(255 BYTE),

"FIELD_123" VARCHAR2(510 BYTE),

"FIELD_124" VARCHAR2(255 BYTE),

"FIELD_125" VARCHAR2(510 BYTE),

"FIELD_126" VARCHAR2(255 BYTE),

"FIELD_127" VARCHAR2(255 BYTE),

"FIELD_128" VARCHAR2(64 BYTE),

"LOG_DATE" DATE,

"SWITCH_NAME" VARCHAR2(10 BYTE),

"PSEUDO_PAN" NUMBER(17,0),

"INSERT_DATE" DATE DEFAULT sysdate

/* GOLDENGATE_DDL_REPLICATION */,

"MODIFIED_DATE" DATE,

CONSTRAINT "PK_PULSE_MESSAGE_GG" PRIMARY KEY ("EVENT_ID"),

);

DDL for the Cassandra (target) table

CREATE TABLE IF NOT EXISTS switch_message_by_id (

event_id text,

switch_name text,

pseudo_pan text,

request_date timestamp,

request_type text,

account_type text,

transaction_amount int,

is_pin_based boolean,

mcc text,

track1 text,

track2 text,

merchant_name text,

response_date timestamp,

response_message text,

available_balance int,

PRIMARY KEY (event_id)

)

Replicat file:

REPLICAT cas

TARGETDB LIBFILE ggjava.dll SET property=dirprm/cassandra.properties

--SOURCEDEFS ./dirdef/dbo.def

getEnv (JAVA_HOME)

getEnv (LD_LIBRARY_PATH)

getEnv (PATH)

DDL INCLUDE ALL

GROUPTRANSOPS 1000

--MAPEXCLUDE dbo.excludetable

MAP oggsrc.PULSE_MESSAGE_GG, TARGET test.switch_message_by_id,

COLMAP(event_id = EVENT_ID, response_message = 'SUCCESS', transaction_amount = FIELD_004, track1 = FIELD_045, track2 = FIELD_035, switch_name = SWITCH_NAME, pseudo_pan = @STRNUM(PSEUDO_PAN));

Error description:

2017-07-26 18:27:29 INFO OGG-02243 Opened trail file dirdat/pn000000000 at 2017-07-26 18:27:29.300000.

2017-07-26 18:29:31 INFO OGG-03506 The source database character set, as determined from the trail file, is UTF-8.

2017-07-26 18:29:31 INFO OGG-06505 MAP resolved (entry oggsrc.PULSE_MESSAGE_GG): MAP "OGGSRC"."PULSE_MESSAGE_GG", TARGET test.switch_message_by_id, COLMAP(event_id = EVENT_ID, response_message = 'SUCCESS', transaction_amount = FIELD_004, track1 = FIELD_045, track2 = FIELD_035, switch_name = SWITCH_NAME, pseudo_pan = @STRNUM(PSEUDO_PAN)).

2017-07-26 18:29:31 INFO OGG-02756 The definition for table OGGSRC.PULSE_MESSAGE_GG is obtained from the trail file.

...d = EVENT_ID, response_message...

Error in COLMAP clause. Unrecognized clause or element.

Source Context :

SourceModule : [er.mapping]

SourceID : [er/mapping.cpp]

SourceFunction : [get_map_entry]

SourceLine : [2836]

ThreadBacktrace : [18] elements

: [C:\Users\Samarth\Softwares\gg_big_data_cassandra\gglog.dll(??1CContextItem@@UEAA@XZ+0x2ab6) [0x00007FF87509F106]]

: [C:\Users\Samarth\Softwares\gg_big_data_cassandra\gglog.dll(?CreateMessage@CMessageFactory@@QEAAPEAVCMessage@@PEAVCSourceContext@@IZZ+0x561) [0x00007FF87509F751]]

: [C:\Users\Samarth\Softwares\gg_big_data_cassandra\gglog.dll(?_MSG_ERR_DB_CLAUSE_ERROR@@YAPEAVCMessage@@PEAVCSourceContext@@PEBDW4MessageDisposition@CMessageFactory@@@Z+0x3d) [0x00007FF87503A8CD]]

: [C:\Users\Samarth\Softwares\gg_big_data_cassandra\replicat.exe(ERCALLBACK+0x2d752) [0x00007FF629F38BC2]]

: [C:\Users\Samarth\Softwares\gg_big_data_cassandra\replicat.exe(ERCALLBACK+0x108c8c) [0x00007FF62A0140FC]]

: [C:\Users\Samarth\Softwares\gg_big_data_cassandra\replicat.exe(ERCALLBACK+0x10bc39) [0x00007FF62A0170A9]]

: [C:\Users\Samarth\Softwares\gg_big_data_cassandra\replicat.exe(ERCALLBACK+0x27d84) [0x00007FF629F331F4]]

: [C:\Users\Samarth\Softwares\gg_big_data_cassandra\replicat.exe(ERCALLBACK+0x7314) [0x00007FF629F12784]]

: [C:\Users\Samarth\Softwares\gg_big_data_cassandra\replicat.exe(ERCALLBACK+0x4fee3) [0x00007FF629F5B353]]

: [C:\Users\Samarth\Softwares\gg_big_data_cassandra\replicat.exe(ERCALLBACK+0x47851) [0x00007FF629F52CC1]]

: [C:\Users\Samarth\Softwares\gg_big_data_cassandra\replicat.exe(ERCALLBACK+0xa11dd) [0x00007FF629FAC64D]]

: [C:\Users\Samarth\Softwares\gg_big_data_cassandra\replicat.exe(_ggTryDebugHook+0x6793) [0x00007FF62A056C03]]

: [C:\Users\Samarth\Softwares\gg_big_data_cassandra\replicat.exe(_ggTryDebugHook+0x595b) [0x00007FF62A055DCB]]

: [C:\Users\Samarth\Softwares\gg_big_data_cassandra\replicat.exe(_ggTryDebugHook+0x663d) [0x00007FF62A056AAD]]

: [C:\Users\Samarth\Softwares\gg_big_data_cassandra\replicat.exe(ERCALLBACK+0xa124b) [0x00007FF629FAC6BB]]

: [C:\Users\Samarth\Softwares\gg_big_data_cassandra\replicat.exe(CommonLexerNewSSD+0x2b842) [0x00007FF62A0F04C2]]

: [C:\WINDOWS\System32\KERNEL32.DLL(BaseThreadInitThunk+0x14) [0x00007FF8923E8364]]

: [C:\WINDOWS\SYSTEM32\ntdll.dll(RtlUserThreadStart+0x21) [0x00007FF893B070D1]]

2017-07-26 18:29:31 ERROR OGG-00919 Error in COLMAP clause.

***********************************************************************

* ** Run Time Statistics ** *

**********************************************************************

Reading dirdat/pn000000000, current RBA 11764, 0 records, m_file_seqno = 0, m_file_rba = 11764

Report at 2017-07-26 18:29:31 (activity since 2017-07-26 18:29:31)

DDL replication statistics:

Operations: 0

Mapped operations: 0

Unmapped operations: 0

Other operations: 0

Excluded operations: 0

Errors: 0

Retried errors: 0

Discarded errors: 0

Ignored errors: 0

No records were replicated.

This post has been answered by 3505428 on Jul 28 2017

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Nov 9 2017

Added on Jul 26 2017

#goldengate

6 comments

1,099 views