REPERROR (-1, DISCARD)

[Roshan]

Oracle DB 12.1.0.2
Solaris 6.4

Hello Erman,

is it ok if I add the following parameter permanently

REPERROR (-1, DISCARD)

in my parameter file if ever I get the error below?

OCI Error ORA-00001: unique constraint (CBSOGG.WF_TODOITEM_WID_$PK) violated (status = 1), SQL <INSERT INTO "CBSOGG"."WF_TODOITEM" ("WORK_ID_N","WRKAGENT_ID_N","NOTES_V","EXPIRY_TIME_D","STATUS_V","ASSIGNMENT_DT_D","WORK_COMPLETION_DT_D","EVENT_TYPE_N","EVENT_REFNO_V","USER_ID_N","TIMES_DIFFERED_N","ACTIVITY_NAME_V","ASSIGNMENT_DT_D_1") VALUES (:a0,:a1,:a2,:a3,:a4,:a5,:a6,:a7,:a8,:a9,:a10,:a11,:a12)>
Operation failed at seqno 686 rba 182835455
Discarding record on action DISCARD on error 1
Problem replicating CLM_TT.WF_TODOITEM to CBSOGG.WF_TODOITEM.
Mapping problem with insert record (target format) SCN:29.29.51717...

or is it necessary to comment after replicat is resuming normally?

Regards,

Roshan

[ErmanArslansOracleBlog]

Send me the description of the tables both from the source and target.

desc CLM_TT.WF_TODOITEM
desc CBSOGG.WF_TODOITEM

You may have a configuration problem.. It seems our target table has a primary key .. Lets see if you have that key in your source table too..

[Roshan]

Hi,

please find attached DDL from source and target:

DDL.txt

[Roshan]

Both source and target have primary keys.

[ErmanArslansOracleBlog]

In order to answer this properly, I need the following answers;
Probably both your source and target gets inserts, right? Is your GG cofiguration bidirectional?
How did you do the initial load?
How does this record be present in target? (it is in a PK column bytheway)
There should be a cause for having the PK data already present in your target.

As for this unique constratint error, there are fixes.
HANDLECOLLISIONS is one of them. REPERROR is another solution.
It seems HANDLECOLLISIONS is not recommended, so you should go with REPERROR (-1, DISCARD).

However; you are asking if you should set it permenantly.
It depends.
Normally, you shouldn't. I mean, If you don't have any data integrity issues between your source and target , you shouldn't...
Please answer the above questions and describe your configuration briefly..

[Roshan]

No, replication is downstream from source to target. Redo logs are being shipped from source to target and mined on target.
expdp using flashback scn. I stopped replicat before starting expdp. The I imported the tables and started replicat. The tables had this parameter FILTER ( @GETENV ('TRANSACTION', 'CSN') > flashback scn) ; before starting replicat.
I did not check the record when the replicat was abended but I think the record already existed. The record with WORK_ID_N = 1783548 exists only once on both source and target.

[ErmanArslansOracleBlog]

Seems good. your flashback based method is correct.
This may be normal, as your replicat was running, while you were getting the required SCN.. So your replicat may have already replicated some records between your SCN query and your expdp command.
Anyways; then this is a one time error and you could go with  REPERROR (-1, DISCARD).