Active-active replicate MAP update/delete/insert conflict resolution - GoldenGate

I'm new to Golden Gate and working on a proof of concept project using Golden Gate for an active active configuration. I got active active to work, but I have trouble understanding how to code the conflict resolution routines.
I'd like to have a Replicat configuration that does the following:
1. for a certain table, (e.g oauth_consumer)
detect update conflicts and resolve based on timestamp
discard delete actions, if the target record is already missing
2. for all the other cases, insert a record in the exceptions table
I created a replicat with this configuration. I can see the update conflicts being resolved based on timestamp (and discard table being populated), and see exceptions added to the exceptions table, but I don't see any deletes in the discard or exception table. Is this the right way to configure the conflict resolution for the same table, but different opperations.
Many thanks!!!
REPLICAT rep2
ASSUMETARGETDEFS
USERID bla, PASSWORD bla
DISCARDFILE /usr/local/ogg/discard.out APPEND
-- This starts the macro
MACRO #exception_handler
BEGIN
, TARGET bla.exceptions
, COLMAP ( rep_name = "REP2"
, table_name = #GETENV ("GGHEADER", "TABLENAME")
, errno = #GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = #GETENV ("LASTERR", "DBERRMSG")
, optype = #GETENV ("LASTERR", "OPTYPE")
, errtype = #GETENV ("LASTERR", "ERRTYPE")
, logrba = #GETENV ("GGHEADER", "LOGRBA")
, logposition = #GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = #GETENV ("GGHEADER", "COMMITTIMESTAMP"))
, INSERTALLRECORDS
, EXCEPTIONSONLY;
END;
-- This ends the macro
REPERROR (DEFAULT, EXCEPTION)
REPERROR (DEFAULT2, ABEND)
REPERROR (-1, EXCEPTION)
IGNOREINSERTS
IGNOREDELETES
GETUPDATES
MAP my.oauth_consumer, TARGET my.*,
REPERROR (21000, DISCARD),
SQLEXEC (ID lookup, ON UPDATE,
QUERY "select count(*) cnf from my.oauth_consumer where consumer_key = :cid and DATE_MODIFIED > :ts",
PARAMS (cid = consumer_key, ts = DATE_MODIFIED),
BEFOREFILTER, ERROR REPORT, TRACE ALL),
FILTER (lookup.cnf = 0, ON UPDATE, RAISEERROR 21000);
IGNOREINSERTS
GETDELETES
IGNOREUPDATES
MAP my.oauth_consumer, TARGET my.*,
REPERROR (22000, DISCARD),
SQLEXEC (ID lookup, ON DELETE,
QUERY "select count(*) cnf from my.oauth_consumer where consumer_key = :cid",
PARAMS (cid = consumer_key),
BEFOREFILTER, ERROR REPORT, TRACE ALL),
FILTER (lookup.cnf > 0, ON DELETE, RAISEERROR 22000);
GETINSERTS
GETDELETES
GETUPDATES
MAP my.*, TARGET my.*;
MAP my.*, #exception_handler() 

The config file is missing this parameter:
ALLOWDUPTARGETMAP

Related

exceptions

Hi Experts, We have setup gg between two oracle database , also created exception table to handle the exceptions , exception records are captured and stored in to exception table, But, here our requirement is when ever any exception happening the error message needs to be inserted in to exception table at the same time process also should be abended .In our current setup , the error messages are stored in to exception table but process is not abending , since we have used reperror(default, exceptions) , if we tried reperror(default, abend) the process will be ablended but no exception records inserted in to exception table. when ever any error(like no data found, or unique constraint) replicat proceess should be abended and also exception table should have the exception records.   Can any one help on this to achive ? thanks in advance replic prm MACRO #exception_handlerBEGIN, TARGET exceptions, COLMAP ( rep_name = "RTARGET1", table_name = #GETENV ("GGHEADER", "TABLENAME"), errno = #GETENV ("LASTERR", "DBERRNUM"), dberrmsg = #GETENV ("LASTERR", "DBERRMSG"), optype = #GETENV ("LASTERR", "OPTYPE"), errtype = #GETENV ("LASTERR", "ERRTYPE"), logrba = #GETENV ("GGHEADER", "LOGRBA"), logposition = #GETENV ("GGHEADER", "LOGPOSITION"), committimestamp = #GETENV ("GGHEADER", "COMMITTIMESTAMP")), INSERTALLRECORDS, EXCEPTIONSONLY;END; REPERROR (DEFAULT, ABEND) ---this parameter tried but exception record is not inserted in to exception table but process went abended before that REPERROR (DEFAULT , EXCEPTION) -- this one also tried but process is not abended but exception record captured , but we need exception detail at the same time process also should be abended. map SRC.TEST , target TGT.TEST;MAP SRC.TEST #exception_handler(); Simply, We have to store the error detail in exception table when replicat process is abend. Please help.  Thanks,AT
Can any one please help on this ? to capture the error details in to exception table when process is going to abend status. Like can we use both REPERROR (DEFAULT, ABEND) and REPERROR (DEFAULT, EXCEPTION) or REPERROR (1403, ABEND) and REPERROR (1403, EXCEPTION) We dont want to allow the process to continue when any errors  but at the same time we need capture the error details in exception table.   Thanks.AT
Experts, Any tricks or help on this to achieve? please help , we are in last stage in the configuration your help is very much helpful.  Thanks.
Hi , Did you try the TRANSEXCEPTION? Please refer the doc https://docs.oracle.com/goldengate/1212/gg-winux/GWURF/gg_parameters126.htm Regards,Veera
Hi Veera ,  Thanks for your response, I have tried the below options with TRANSEXCEPTION but no luck  1. REPERROR (DEFAULT, ABEND)REPERROR (1403, TRANSEXCEPTION)with map src.TEST , target tgt.TEST, &MAPEXCEPTION (TARGET exceptions, INSERTALLRECORDS, COLMAP ( rep_name = "RTARGET1",  table_name = #GETENV ("GGHEADER", "TABLENAME"), errno = #GETENV ("LASTERR", "DBERRNUM"), dberrmsg = #GETENV ("LASTERR", "DBERRMSG"), optype = #GETENV ("LASTERR", "OPTYPE"), errtype = #GETENV ("LASTERR", "ERRTYPE"), logrba = #GETENV ("GGHEADER", "LOGRBA"),logposition = #GETENV ("GGHEADER", "LOGPOSITION"), committimestamp = #GETENV ("GGHEADER", "COMMITTIMESTAMP")) ); Its inserted the 1403 err record in to exception table but not abended  2. tried only with REPERROR (DEFAULT, TRANSEXCEPTION) --no luck  MACRO #exception_handlerBEGIN, TARGET exceptions, COLMAP ( rep_name = "RTARGET1",  table_name = #GETENV ("GGHEADER", "TABLENAME"), errno = #GETENV ("LASTERR", "DBERRNUM"), dberrmsg = #GETENV ("LASTERR", "DBERRMSG"), optype = #GETENV ("LASTERR", "OPTYPE"), errtype = #GETENV ("LASTERR", "ERRTYPE"), logrba = #GETENV ("GGHEADER", "LOGRBA"),logposition = #GETENV ("GGHEADER", "LOGPOSITION"), committimestamp = #GETENV ("GGHEADER", "COMMITTIMESTAMP")), INSERTALLRECORDS, EXCEPTIONSONLY;--END; MAP src.TEST #exception_handler();map src.TEST , target GES_OWNER.TESTtried with exception only and wothout exception only also   Its also inserted the row into excetion table but not abended 3. tried with REPERROR (DEFAULT, TRANSEXCEPTION)REPERROR (DEFAULT, ABEND)   Its abended but exception record not captured.  Pleas suggest
Hi,Additional info: TRANSEXCEPTION will result into the exception record getting inserted in the exceptions table but all data (all tokens) do NOT get captured correctly. DBERRNO, DBERRMSG, OPTYPE and ERRTYPE will not get captured or will be invalid. This is bug 18696641  btw, why do you really want to do both? Did you check if the record is getting recorded in the discard file? If yes, then why not allow the process to abend and get the record/values from the discard file? If you still want to achieve what you are trying to achieve, the foll. may help: map twice. Once to the actual table and then again to the exceptions table.Pls see MOS ID 1382092.1 and 1590023.1 HTH
Thanks for this , for that two map option also not working  here only we can achieve any one scenario at a time , like1. if exception record is inserted then the process is not abended ,reperror(default, exception)2. if process is abended then exception table is not updated reperror(default,abend) this is a generic exception handling mechanism, when ever there is any error ,the error details should be recorded in to exception table then process should be abended  but in gg we cant configure this. Is there any possibility to use eventaction parameter for this?like , if any error , the error details will be inserted in to exception table and process should abend ? we tried with below one,reperror (default, exception)map src.test, target exceptions , eventactions(abort)  here also the exception table is not updated but process is abended , the error details is stored in discard file, instead of storing that info in to discard we need to insert into database exception table. , the reason is we have email alert mechanism , when ever gg process is stopped or abended we will get alert mail , in the same time if we can able to see the exception details in database table that would be very much helpful, instead of go and check the discard file every time, just if its in table then we can query it and resolve the issue with minimal time.  Thanks,Annamalai. 
I think you have an ordering issue and what to do at different error points.  On the first error, you want the error sent to the exception table. On the second time,then abend.  You need to use the default and default2 options of REPERROR. Try replacing your REPERROR with these: REPERROR(default, exception);REPERROR(default2, abend); Let me know if this whats as you think it should. Thanks Bobby
Hi Bobby, Thanks, But here the use of these two parameters is different , REPERROR(default, exception); --- This one will capture the error message and sent to exception table but allow to continue the data flow, it will not stop the process. REPERROR(default2, abend); Specifies a capture all details for any unanticipated Oracle errors that may occur other than default,  like permission issue, space issue that time processes will be abended but errors are not captured in exception table, But our requirement is if any error then the process should abend at the same time exception table also should have entries. like , repoerror (exception,abend)  or evenaction (exception, abend) -- but in ogg we can't use like this.
How are you calling your exception handling?   Bobby

replicat abended

Hi,
im my active -active replication setup, today i'm getting the below error message , can you please help me to resolve this ,
2011-06-15 13:30:09 GGS ERROR 101 Oracle GoldenGate Delivery for Oracle,
REP_TAR2.prm: Must be IGNORE, DISCARD, ABEND, EXCEPTION, TRANSABORT or RETRYOP
Where i need to set the above parameters, please find below the map statements which is configured in both replicat prm files
MAP replica.test, TARGET replica.item_descr, &
REPERROR ( DEFAULT , EXCEPTION ) , &
SQLEXEC ( ID detect_conflict, &
ON UPDATE, &
QUERY "SELECT 0 conflict FROM replica.test WHERE batch_id=:p_batch_id AND total=:p_total_before", &
PARAMS(p_batch_id=batch_id,p_total_before=BEFORE.total), &
ALLPARAMS REQUIRED , &
BEFOREFILTER, &
EXEC MAP, &
TRACE ALL, &
ERROR RAISE );
also i have written a procedure on both side for mismatch,
create or replace PROCEDURE ggadmin.h (
p_batch_id IN NUMBER
, p_total_after IN NUMBER
, p_total_before IN NUMBER)
IS
BEGIN
UPDATE replica.test
SET total = total + (p_total_after - p_total_before)
WHERE batch_id = p_batch_id;
END h;
/
Please help to resolve the 2011-06-15 13:30:09 GGS ERROR 101 Oracle GoldenGate Delivery for Oracle,
REP_TAR2.prm: Must be IGNORE, DISCARD, ABEND, EXCEPTION, TRANSABORT or RETRYOP
when i start the replicat process both side it goes ABENDED status... below are the full replicat process entries please advice...
-- Replicat process
REPLICAT REP_SRC1
-- Environment Settings
USERID ggadmin, PASSWORD ggadmin
-- Discard file path
DISCARDFILE E:\ggs\dirrpt\rep1_dsc.rpt, append
-- Grneral Parameters
ASSUMETARGETDEFS
-- Truncate parameter
--GETTRUNCATES
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP
--HANDLECOLLISIONS
APPLYNOOPUPDATES
-- Mapping parameters
GETAPPLOPS
IGNOREREPLICATES
-- This starts the macro
--MAP replica.*, target replica.*;
MAP replica.test , TARGET replica.test, &
REPERROR ( DEFAULT, EXCEPTION ), &
SQLEXEC ( ID detect_conflict, &
ON UPDATE, &
QUERY "SELECT 0 conflict FROM replica.test WHERE batch_id=:p_batch_id AND total=:p_total_before", &
PARAMS(p_batch_id=batch_id,p_total_before=BEFORE.total), &
ALLPARAMS REQUIRED, &
BEFOREFILTER, &
EXEC MAP, &
TRACE ALL, &
ERROR RAISE );
MACRO #exception_handler
BEGIN
, TARGET ggadmin.exceptions
, EXCEPTIONSONLY
, SQLEXEC ( SPNAME ggadmin.h, &
PARAMS(p_batch_id = batch_id, &
p_total_after = total, &
p_total_before = BEFORE.total)
, EXEC MAP
, TRACE ALL
)
, COLMAP ( rep_name = "rep_src1"
, table_name = #GETENV ("GGHEADER", "TABLENAME")
, errno = #GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = #GETENV ("LASTERR", "DBERRMSG")
, optype = #GETENV ("LASTERR", "OPTYPE")
, errtype = #GETENV ("LASTERR", "ERRTYPE")
, logrba = #GETENV ("GGHEADER", "LOGRBA")
, logposition = #GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = #GETENV ("GGHEADER", "COMMITTIMESTAMP")
, batch_id=batch_id
, batch_id_before = BEFORE.batch_id
--, total_after = total
, total_before = BEFORE.total
, record_image = #GETENV ("GGHEADER", "BEFOREAFTERINDICATOR"))
, INSERTALLRECORDS
;
END;
-- This ends the macro
Edited by: Atp on Jun 15, 2011 5:49 PM 
any update please? waiting for that 
Try removing the comma after the REPERROR clause.
From:
REPERROR ( DEFAULT , EXCEPTION ) , &
To:
REPERROR ( DEFAULT , EXCEPTION ) & 
Hi Steven,
Thanks for your reply,
the problem is space between ( and DEFAULT also between EXCEPTION and ) after removing the space its working fine....
REPERROR (DEFAULT, EXCEPTION), &

exception table getting dropped

Hi,
when i dropped a table from the source, the exception table at the target also getting dropped and a table created at the source will also create/override exception table with the new table's structure. So i am wonding if anyone has any idea or
have gone through similar situation.
Here is my replicat setup:
-- Identify the Replicat group:
REPLICAT CEPREP1
-- Use HANDLECOLLISIONS while Source is Active
--HANDLECOLLISIONS
-- State that source and target definitions are identical:
ASSUMETARGETDEFS
-- Specify database login information as needed for the database:
USERID goldengate_owner, PASSWORD *******
-- This starts the macro for exception handler
MACRO #exception_handler
BEGIN
, TARGET goldengate_owner.exceptions
, COLMAP ( rep_name = "CEPREP1"
, table_name = #GETENV ("GGHEADER", "TABLENAME")
, errno = #GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = #GETENV ("LASTERR", "DBERRMSG")
, optype = #GETENV ("LASTERR", "OPTYPE")
, errtype = #GETENV ("LASTERR", "ERRTYPE")
, logrba = #GETENV ("GGHEADER", "LOGRBA")
, logposition = #GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = #GETENV ("GGHEADER", "COMMITTIMESTAMP"))
, INSERTALLRECORDS
, EXCEPTIONSONLY;
END;
-- This ends the macro for exception handler
-- Specify error handling rules:
REPERROR (DEFAULT, EXCEPTION)
REPERROR (DEFAULT2, ABEND)
REPERROR (-1, EXCEPTION)
REPERROR (-1403, EXCEPTION)
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP
MAP GGATE_T1.*, TARGET GGATE_T1.*;
MAP GGATE_T1.* #exception_handler()
MAP GGATE_T2.*, TARGET GGATE_T2.*;
MAP GGATE_T2.* #exception_handler()
MAP OPS$ORACLE.*, TARGET OPS$ORACLE.*;
MAP OPS$ORACLE.* #exception_handler()
Edited by: pbhand on Jun 29, 2011 4:22 PM 
You have "DDL INCLUDE ALL", which means any "drop table" on the source DB will be replicated to the target DB.
If you don't want to replicate DDL, then just omit this statement altogether (DDL replication is not enabled by default).
If you want to replicate DDL but not for specific objects, then use "DDL INCLUDE {what_you_want}" or "DDL INCLUDE ALL, EXCLUDE {what_you_dont_want}". See the GG Admin guide (ch 14) for how DDL synchronization can be configured[1]...
For example,
<pre>
DDL INCLUDE ALL, EXCLUDE OBJNAME "goldengate_owner.exceptions”
</pre>
or,
<pre>
DDL INCLUDE OBJNAME "goldengate_owner.*", EXCLUDE OBJNAME "goldengate_owner.exceptions”
</pre>
Note: it could be the formatting of the message, but "MAP GGATE_T1., TARGET GGATE_T1.;" doesn't look right. You should always have "schema.tablename", where tablename could be a wildcard... perhaps it was supposed to be => MAP GGATE_T1.*, TARGET GGATE_T1.*;" ?
Cheers,
-Michael
[1] http://download.oracle.com/docs/cd/E18101_01/index.htm 
Thanks MikeN.
The following exclude option helped me and not any more my exceptions table getting deleted.
DDL INCLUDE ALL, EXCLUDE OBJNAME "goldengate_owner.exceptions”

data wipped out on target table

Hi All,
It is very strange that in my Active-Active GG setup, i was reported that all the data in a table in the target wipped out except one record.
Schema and table name: RELEASE_APP2.PORT_INST
IDEV1 has 98 records
IDEV2 has 1 record only
Here are the steps i performed to troubleshoot this:
1. checked 'View report <extract>/<replicat>, found no activities regarding the data lost
2. checked stats: stats <extract>/<replicat> name, table <owner.table>
and there is no sign of any operations performed on that particular table.
3. I have exception tables setup on both source and target server to trap errors but i can't find any errors logged on the exception table.
here is my replicat setup
-- Identify the Replicat group:
REPLICAT CEPREP1
-- Use HANDLECOLLISIONS while Source is Active
--HANDLECOLLISIONS
-- State that source and target definitions are identical:
ASSUMETARGETDEFS
-- Specify database login information as needed for the database:
USERID goldengate_owner, PASSWORD xxxxxx
-- This starts the macro for exception handler
MACRO #exception_handler
BEGIN
, TARGET goldengate_owner.exceptions
, COLMAP ( rep_name = "CEPREP1"
, table_name = #GETENV ("GGHEADER", "TABLENAME")
, errno = #GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = #GETENV ("LASTERR", "DBERRMSG")
, optype = #GETENV ("LASTERR", "OPTYPE")
, errtype = #GETENV ("LASTERR", "ERRTYPE")
, logrba = #GETENV ("GGHEADER", "LOGRBA")
, logposition = #GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = #GETENV ("GGHEADER", "COMMITTIMESTAMP"))
, INSERTALLRECORDS
, EXCEPTIONSONLY;
END;
-- This ends the macro for exception handler
-- Specify error handling rules:
--REPERROR (<error>, <response>)
REPERROR (DEFAULT, EXCEPTION)
REPERROR (DEFAULT2, ABEND)
REPERROR (-1, EXCEPTION)
REPERROR (-1403, EXCEPTION)
DDL INCLUDE ALL, EXCLUDE OBJNAME "GOLDENGATE_OWNER.*"
DDLERROR DEFAULT IGNORE RETRYOP
MAP GGATE_T1.*, TARGET GGATE_T1.*;
MAP GGATE_T1.* #exception_handler()
MAP RELEASE_APP2.*, TARGET RELEASE_APP2.*;
MAP RELEASE_APP2.* #exception_handler()
Any help as to how dig into this issue would be appreciated.
Thanks,
pbhand. 
Someone/something other than the table owner or the GG user did it - where the transaction from that user would be ignored, because both ends would be the same. Or, the operation was set to be ignored by extract. 
Hi,
As it is a Active-Active bi-directional evnrionment, please try using 'TRACETABLE' parameter in Extract and Replicat. Trace table is used for bidirectional synchronization to identify Replicat transactions to Extract. The new OGG code (v11) does not require a TRACETABLE but I sincerely suggest try using to avoid any data inconsistencies. If used, TRACETABLE must appear in both the Extract and Replicat parameter files.
In the Replicat parameter file, TRACETABLE causes Replicat to write an operation to the trace table at the beginning of each transaction.
In the Extract parameter file, TRACETABLE causes Extract to identify as a Replicat transaction any transaction that begins with an operation on the trace table.
Thanks & Regards
SK 
Thanks Sonthosh for your suggestions.
currently i have enabled the following options for extract to ignore any transaction by REPLICAT User goldengate_owner.
"TRANLOGOPTIONS EXCLUDEUSER goldengate_owner"
if i added TRACETABLE would that make much differences?
again your inputs/suggestions always appreciated
Thanks,
pbhand

Oracle Golden gate default exception handler

Hi,
I am setting bi-direction DML replication using GG. I am using exception handling for prevent the replicat process abend. My code REPERROR (DEFAULT, EXCEPTION) is working find for error id 1403 but it is geting failed for Error ORA-00001: unique constraint. So here my question
1. Is there any default opting available in GG for handle all kinds of error?
2. Why replicat process is failing in case ORA-00001 error but it is not failing in ORA-1403. Does REPERROR (DEFAULT, EXCEPTION) handle only ORA-1403 error?
Please help. 
1. Is there any default opting available in GG for handle all kinds of error? No such default standard exceptions handler.
2. Why replicat process is failing in case ORA-00001 error but it is not failing in ORA-1403. Does REPERROR (DEFAULT, EXCEPTION) handle only ORA-1403 error? Not sure but you can catch 00001 by REPERROR (-1, EXCEPTION) 
The problem is DBERRMSG column will store the error,the error description, and the complete SQL up to 4000bytes. But In my case my DBERRMSG is so big more then 4000.I think that is the reason my REPERROR (-1, EXCEPTION). I am using the following code.
create table ggs_admin.exceptions
( rep_name varchar2(8)
, table_name varchar2(61)
, errno number
, dberrmsg varchar2(4000)
, optype varchar2(20)
, errtype varchar2(20)
, logrba number
, logposition number
, committimestamp timestamp
);
GGSCI (db1) 1> edit params rep1
-- Start of the macro
MACRO #exception_handler
BEGIN
, TARGET ggs_admin.exceptions
, COLMAP ( rep_name = "rep1"
, table_name = #GETENV ("GGHEADER", "TABLENAME")
, errno = #GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = #GETENV ("LASTERR", "DBERRMSG")
, optype = #GETENV ("LASTERR", "OPTYPE")
, errtype = #GETENV ("LASTERR", "ERRTYPE")
, logrba = #GETENV ("GGHEADER", "LOGRBA")
, logposition = #GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = #GETENV ("GGHEADER", "COMMITTIMESTAMP"))
, INSERTALLRECORDS
, EXCEPTIONSONLY;
END;
-- End of the macro
REPERROR (DEFAULT, EXCEPTION)
REPERROR (DEFAULT2, ABEND)
REPERROR (-1, EXCEPTION)
MAP SRC.ORDERS, TARGET TGT.ORDERS;
MAP SRC.ORDERS #exception_handler()
In case of order table has more the 60 columns and when I am trying to insert duplicate rows the error message is crossing 4000 byte I think that is the reason REPERROR(-1,EXCEPTION) is failing. Is there any way to resolve this issue.

Categories

Resources