OGG-01296 Error mapping OGG-01154 SQL Error 1722 mapping Goldengate Error Solution

Hi,

I will explain how to solve OGG-01296 Error mapping OGG-01154 SQL Error 1722 mapping Goldengate Error in this article.

 

Details of error are in the report file as follows.

 

2020-03-17 18:22:59 WARNING OGG-01004 Aborted grouped transaction on MSD.TARGET_TABLE, Database error 1722 (OCI Error ORA-01722: invalid number (status = 1722), SQL <UPDATE "E
XTRACT_GG"."CCB_ICCI_SIM_POOL" x SET x."CARD_TYPE" = :a1,x."CAPACITY_ID" = :a2,x."TOOLKIT_IND" = :a3,x."ORDER_NO" = :a4,x."WH_EXIT_DATE" = :a5,x."ORDER_ITEM_NO" = :a6,x."ADVANCE_IND" = :a7,
x."OWNERSHIP_DATE" = :a8,x."PROGRAMMABLE" = :a9,x."MANUFACTURE_ID" = :a10,x."CARD_PRICE" = :a11,x."ADVANCE_AMOUNT" = :a12,x."USAGE_REASON_CODE" = :a13,x."ICCI_STATUS_CODE" = :a14,x."WH_EXIT
_SHOP_CODE" = :a15,x."OWNER_SHOP_CODE" = :a16,x."CAMPAIGN_CODE" = :a17,x."PERSONALIZATION_DATE" = :a18,x."GEN_TYPE" = :a19,x."SIM_PROFILE_ID" = :a20,x."SIM_ART_WORK_FILE_ID" = :a21,x."PINPU
K_ARTWORK_FILE_ID" = :a22,x."SIM_TECH_SPEC_ID" = :a23,x."PINPUK_TECH_SPEC_ID" = :a24,x."GEN_ORDER_NO" = :a25,x."CR_DATE" = :a26,x."CR_USER" = :a27,x."UPD_DATE" = :a28,x."UPD_USER" = :a29,x.
"UPD_SEQ" = :a30,x."SIM_TYPE_ID" = :a31,x."SIM_CUST_ID" = :a32,x."VISUALIZE_ID" = :a33,x."LOCK_IND" = :a34,x."SHOP_CODE_TO_APPROVE" = :a35,x."COMPANY_ID" = :a36,x."LOCK_TYPE" = :a37,x."MNP_
REUSE" = :a38,x."GG_TRANS_DATE" = :a39,x."ESIM_FLAG" = :a40,x."MATCHING_ID" = :a41,x."CONFIRMATION_CODE" = :a42,x."EID" = :a43,x."PROFILETYPE" = :a44,x."PROFILEDOWNLOAD_TIMESTAMP" = :a45,x.
"NOTIFICATIONPOINTID" = :a46,x."NOTIFICATIONPOINTSTATUS" = :a47,x."SUBJECTCODE" = :a48,x."REASONCODE" = :a49,x."SUBJECTIDENTIFIER" = :a50,x."MESSAGE" = :a51 WHERE x."ICCI" = :b0 AND ROWNUM
= 1>).

2020-03-17 18:22:59 WARNING OGG-01003 Repositioning to rba 225845259 in seqno 49.

2020-03-17 18:22:59 WARNING OGG-01154 SQL error 1722 mapping MSD.SOURCE_TABLE to MSD.TARGET_TABLE OCI Error ORA-01722: invalid number (status = 1722), SQL <UPDATE "EXTRAC
T_GG"."CCB_ICCI_SIM_POOL" x SET x."CARD_TYPE" = :a1,x."CAPACITY_ID" = :a2,x."TOOLKIT_IND" = :a3,x."ORDER_NO" = :a4,x."WH_EXIT_DATE" = :a5,x."ORDER_ITEM_NO" = :a6,x."ADVANCE_IND" = :a7,x."OW
NERSHIP_DATE" = :a8,x."PROGRAMMABLE" = :a9,x."MANUFACTURE_ID" = :a10,x."CARD_PRICE" = :a11,x."ADVANCE_AMOUNT" = :a12,x."USAGE_REASON_CODE" = :a13,x."ICCI_STATUS_CODE" = :a14,x."WH_EXIT_SHOP
_CODE" = :a15,x."OWNER_SHOP_CODE" = :a16,x."CAMPAIGN_CODE" = :a17,x."PERSONALIZATION_DATE" = :a18,x."GEN_TYPE" = :a19,x."SIM_PROFILE_ID" = :a20,x."SIM_ART_WORK_FILE_ID" = :a21,x."PINPUK_ART
WORK_FILE_ID" = :a22,x."SIM_TECH_SPEC_ID" = :a23,x."PINPUK_TECH_SPEC_ID" = :a24,x."GEN_ORDER_NO" = :a25,x."CR_DATE" = :a26,x."CR_USER" = :a27,x."UPD_DATE" = :a28,x."UPD_USER" = :a29,x."UPD_
SEQ" = :a30,x."SIM_TYPE_ID" = :a31,x."SIM_CUST_ID" = :a32,x."VISUALIZE_ID" = :a33,x."LOCK_IND" = :a34,x."SHOP_CODE_TO_APPROVE" = :a35,x."COMPANY_ID" = :a36,x."LOCK_TYPE" = :a37,x."MNP_REUSE
" = :a38,x."GG_TRANS_DATE" = :a39,x."ESIM_FLAG" = :a40,x."MATCHING_ID" = :a41,x."CONFIRMATION_CODE" = :a42,x."EID" = :a43,x."PROFILETYPE" = :a44,x."PROFILEDOWNLOAD_TIMESTAMP" = :a45,x."NOTI
FICATIONPOINTID" = :a46,x."NOTIFICATIONPOINTSTATUS" = :a47,x."SUBJECTCODE" = :a48,x."REASONCODE" = :a49,x."SUBJECTIDENTIFIER" = :a50,x."MESSAGE" = :a51 WHERE x."ICCI" = :b0 AND ROWNUM = 1>.

Source Context :
SourceModule : [er.replicat.errors]
SourceID : [/scratch/aime/adestore/views/aime_adc2280123/oggcore/OpenSys/src/app/er/replicat/reperrors.cpp]
SourceMethod : [repError]
SourceLine : [1726]
ThreadBacktrace : [15] elements
: [/goldengate/ggate18c/libgglog.so(CMessageContext::AddThreadContext())]
: [/goldengate/ggate18c/libgglog.so(CMessageFactory::CreateMessage(CSourceContext*, unsigned int, ...))]
: [/goldengate/ggate18c/libgglog.so(_MSG_QualTableName_QualTableName(CSourceContext*, int, ggs::gglib::ggapp::CQualDBObjName<(DBObjType)1> const&, ggs::gglib::ggap
p::CQualDBObjName<(DBObjType)1> const&, CMessageFactory::MessageDisposition))]
: [/goldengate/ggate18c/replicat(ggs::er::ReplicatContext::repError(short, int, char const*, extr_ptr_def*, ggs::gglib::gglcr::CommonLCR const*, std_rec_hdr_def*,
char*, ObjectMetadata*, bool))]
: [/goldengate/ggate18c/replicat()]
: [/goldengate/ggate18c/replicat(ggs::er::ReplicatContext::processRecord(ggs::gglib::gglcr::CommonLCR const*, ggs::gglib::gglcr::CommonLCR*, extr_ptr_def*&, extr_p
tr_def*&, bool&, int&, bool, RepCsn&))]
: [/goldengate/ggate18c/replicat(ggs::er::ReplicatContext::processReplicatLoop())]
: [/goldengate/ggate18c/replicat(ggs::er::ReplicatContext::run())]
: [/goldengate/ggate18c/replicat()]
: [/goldengate/ggate18c/replicat(ggs::gglib::MultiThreading::MainThread::ExecMain())]
: [/goldengate/ggate18c/replicat(ggs::gglib::MultiThreading::Thread::RunThread(ggs::gglib::MultiThreading::Thread::ThreadArgs*))]
: [/goldengate/ggate18c/replicat(ggs::gglib::MultiThreading::MainThread::Run(int, char**))]
: [/goldengate/ggate18c/replicat(main)]
: [/lib64/libc.so.6(__libc_start_main)]
: [/goldengate/ggate18c/replicat()]

2020-03-17 18:22:59 ERROR OGG-01296 Error mapping from MSD.SOURCE_TABLE to MSD.TARGET_TABLE.

***********************************************************************
* ** Run Time Statistics ** *
***********************************************************************

2020-03-17 18:22:59 INFO OGG-02333 Reading /goldengate/ggate18c/dirdat/i7000000049, current RBA 225,845,259, 0 records, m_file_seqno = 49, m_file_rba = 225,846,077.

 

This error is related with SQL 1722 error, which is The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal.

 

ORA-01722 invalid number
Cause: The attempted conversion of a character string to a number failed because the character string was not a valid numeric literal. Only numeric fields or character fields containing numeric data may be used in arithmetic functions or expressions. Only numeric fields may be added to or subtracted from dates.

Action: Check the character strings in the function or expression. Check that they contain only numbers, a sign, a decimal point, and the character "E" or "e" and retry the operation.

 

To solve this problem, fix the conversion of a character string to a number manually.

Or sometimes this error can be fake, when you check your table data, if there is no problem with your data, then ignore this error.

 

 

 

You can add the following line to the parameter file of related goldengate replicat process, then start it again.

 

reperror (-1722, IGNORE)

 

GGSCI (msddb01.deveci.local) 32> edit params r01msd


REPLICAT r01msd
...........
...........

reperror (-1722, IGNORE)
...........
...........

save and quit via wq!



start replicat process.

GGSCI (msddb01.deveci.local) 33> start r01msd

Sending START request to MANAGER ...
REPLICAT r01msd starting


GGSCI (msddb01.deveci.local) 34> info r01msd

REPLICAT   r01msd    Last Started 2020-03-17 19:40   Status RUNNING
Checkpoint Lag       05:12:06 (updated 00:02:24 ago)
Process ID           330618
Log Read Checkpoint  File /goldengate/ggate18c/dirdat/i7000000049
                     2020-03-17 14:26:00.001859  RBA 225530070


GGSCI (msddb01.deveci.local) 35>


GGSCI (msddb01.deveci.local) 49> !
info r01msd

REPLICAT   r01msd    Last Started 2020-03-17 19:40   Status RUNNING
Checkpoint Lag       04:44:46 (updated 00:00:23 ago)
Process ID           330618
Log Read Checkpoint  File /goldengate/ggate18c/dirdat/i7000000049
                     2020-03-17 14:57:14.014155  RBA 653509805
					 
GGSCI (msddb01.deveci.local) 53> info r01msd

REPLICAT   r01msd    Last Started 2020-03-17 19:40   Status RUNNING
Checkpoint Lag       04:06:43 (updated 00:00:00 ago)
Process ID           330618
Log Read Checkpoint  File /goldengate/ggate18c/dirdat/i7000000049
                     2020-03-17 15:36:21.035674  RBA 951869708


GGSCI (msddb01.deveci.local) 54>					 


GGSCI (msddb01.deveci.local) 55> info r01msd

REPLICAT   r01msd    Last Started 2020-03-17 19:40   Status RUNNING
Checkpoint Lag       00:00:00 (updated 00:00:02 ago)
Process ID           330618
Log Read Checkpoint  File /goldengate/ggate18c/dirdat/i7000000049
                     2020-03-17 19:43:44.000504  RBA 1016566766


GGSCI (msddb01.deveci.local) 56>

 

Now Replication is running properly.

 

Do you want to learn Oracle Goldengate from scratch, then read the following Goldengate Tutorial articles.

https://ittutorial.org/goldengate-tutorials-oracle-for-beginners/

 158 views last month,  1 views today

Mehmet Salih Deveci

I am Founder of SysDBASoft IT and IT Tutorial and Certified Expert about Oracle & SQL Server database, Goldengate, Exadata Machine, Oracle Database Appliance administrator with 10+years experience.I have OCA, OCP, OCE RAC Expert Certificates I have worked 100+ Banking, Insurance, Finance, Telco and etc. clients as a Consultant, Insource or Outsource.I have done 200+ Operations in this clients such as Exadata Installation & PoC & Migration & Upgrade, Oracle & SQL Server Database Upgrade, Oracle RAC Installation, SQL Server AlwaysOn Installation, Database Migration, Disaster Recovery, Backup Restore, Performance Tuning, Periodic Healthchecks.I have done 2000+ Table replication with Goldengate or SQL Server Replication tool for DWH Databases in many clients.If you need Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS Consultancy and Training you can send my email adress mehmetsalih.deveci@outlook.com.-                                                                                                                                                                                                                                                 -Oracle DBA, SQL Server DBA, APPS DBA,  Exadata, Goldengate, EBS ve linux Danışmanlık ve Eğitim için  mehmetsalih.deveci@outlook.com a mail atabilirsiniz.

Leave a Reply