Oracle GoldenGate: Skipping Erroneous Transactions

How would the Replicat behave when a DDL or DML transaction is run on the source database but the equivalent table does not exist on the target database?

If the table is not found in the target database the Replicat process will ABEND.

What options do you have if you are required to resume data processing immediately to the existing tables?

One way to get around this issue is to either create the required table in the target database or “skip” this transaction altogether.

This article deals with the skipping of such records which are in the trail file but cannot be processed on the target.

Skipping of the transactions can be achieved using one of the following methods.

    • Reset RBA using Logdump
    • SKIPTRANSACTION Parameter
    • HANDLECOLLISIONS Parameter
    • Exceptional handling

Using logdump

Here we skip the transaction that caused the error, by re-starting the Replicat from the next non-erroneous transaction position, by moving forward the RBA in the trail file.

Using the logdump we first identify the next transaction number, then we use this information to reset the Replicat to the new starting position (RBA) in the trail file.

Here is how to work around the problematic transaction.

1. Using GGSCI first determine the Replicat details.

ggsci> INFO REPLICAT rep_trg DETAIL


2. From the output above, get the filename and path.

Here the last two digits of the file name represents the Sequence Number. for example:

“/u01/oracle/goldengate/dirdat/se000005”


3. Also note down the RBA of the OGG trail file. This is the current location of the Replicat process where it is failing.

4. Then using logdump open up the trail file.

logdump>open /u01/oracle/goldengate/dirdat/se000005


5. Go to the RBA noted above.

logdump>pos <<RBA>>


6. To identify the next RBA, enter “n” at the logdump prompt. Run this command again if the RBA shown is the same as the one noted earlier.

logdump>n


Assume the noted RBA as X.

7. Now issue the following command from ggsci to set the new RBA.

By moving the Replicat to the new RBA, the Replicat process will start processing trail data from this point onward.

ggsci>ALTER REPLICAT rep_trg extseqno <<SEQNO>> extrba X


8. Then startup the Replicat process.

If the error still persists, check the RBA with the command given in step 1. If it is X, it means that the next transaction is also erroneous.

Repeat the steps 6-8, till the Replicat runs successfully by moving the trail file position to the next position.

If all the RBA in file is corrupt then use next available trail using the STEP 2.

SKIPTRANSACTION

The START REPLICAT SKIPTRANSACTION command causes the Replicat to start the processing of the records in the trail file by skipping the current erroneous transaction. It is used to specify the next logical recovery position, after the error.

With this the Replicat is re-positioned, to skip the erroneous transaction or transactions, with the understanding that the skipped error will not be Replicated to the target.

1. Use the command as given in the below example:

ggsci>START REPLICAT rep_trg SKIPTRANSACTION

HANDLECOLLISIONS

This HANDLECOLLISIONS parameter is used for the automatic handling of Replicat errors.

The purpose of this option is to make sure the Replicat is able to keep on running with the understanding that the records may be skipped or some data might not be Replicatd.

Use the HANDLECOLLISIONS by using either of the following methods:

1. Turn on the Handle collisions option for REPLICAT, from GGSCI prompt.

ggsci>SEND REPLICAT rep_trg, HANDLECOLLISIONS


2. Mention the HANDLECOLLISIONS parameter in the Replicat parameter file. As shown in the below example:

ggsci>HANDLECOLLISIONS MAP src.tbl_t1, TARGET hr.tbl_t1;


Apply this parameter at the end of the Replicat file, along with other map statements.

Also after the successful replication of the data, we need to remove this parameter or disable the HANDLECOLLISIONS parameter.

Disable the HANDLECOLLISIONS by using either of the following methods:

1. Turn off the Handle collisions option for REPLICAT, from GGSCI prompt.

ggsci>SEND REPLICAT rep_trg, NOHANDLECOLLISIONS


2. Remove the HANDLECOLLISIONS parameter from the Replicat parameter file, that we added in the previous steps.

Exceptional handling

This method involves the mapping of the erroneous transaction to a separate table, so that the rather than ignoring the transaction we can save its information for review.

1. Create the required table in the schema used for goldengate, with the below command.

CREATE TABLE tabExceptions
(
ID                     varchar2(100),
GroupName              varchar2(8),
TableName              varchar2(61),
ErrorNo                number,
dbErrorMsg             varchar2(4000),
optype                 varchar2(20),
Errtype                varchar2(20),
logrba                 number,
logposition            number,
committimestamp        timestamp );


2. Create the primary key on that table.

sqlplus>ALTER TABLE tabExceptions ADD (
CONSTRAINT PK_CTS PRIMARY KEY
(logrba, logposition, committimestamp));


3. To make sure the added table is excluded from DDL replication. Add the name of the table in DDL exclude parameter as shown in example.

DDL INCLUDE ALL, EXCLUDE OBJNAME tabExceptions


4. To add the exception handling feature in oracle goldengate use the below parameter as shown in example.

REPERROR (default, EXCEPTION)


5. Add the following parameters after the map statement in Replicat file. As shown in below example.

With these parameters we will map the tablename, error number, error message, operation type error type, time etc.

Also most importantly replace the [primary key] with primary key column name, [schemaName] with replicating schema, [tableName] with the name of the replicating table and [processName] with the replicat name.

MAP [statement];[codenew] INSERTALLRECORDS
MAP [SchemaName].[TableName], TARGET tabExceptions,
EXCEPTIONSONLY,
COLMAP (
USEDEFAULTS,
ID = [primary key],
GroupName = ”[ProcessName]”,
ErrorNo = @GETENV (“lasterr”, “dberrnum”),
[email protected] (“ggheader”, “tablename”),
dbErrorMsg = @GETENV (“lasterr”, “dberrmsg”),
optype = @GETENV (“lasterr”, “optype”),
errtype = @GETENV (“lasterr”, “errtype”),
logrba = @GETENV (“ggheader”, “logrba”),
[email protected] (“ggheader”,“logposition”),
committimestamp = @GETENV (“ggheader”, “committimestamp”)
),
INSERTALLRECORDS,
EXCEPTIONSONLY;


6. Then after running the Replicat all the error transactions are saved in this table.

These steps allow the Replicat to continue processing the other trail records.

Troubleshooting Series

GoldenGate: Skipping Transactions
GoldenGate: Finding Open Database Transactions
GoldenGate: How to use handle Collisions correctly?




Comments

  1. reyaan26@gmail.com'anwaar says

    Hi Ameen,

    Nice article .
    In our case we haveI am doing replication from oracle to teradata where oracle is having below charter in one column.
    ->->->
    ACSII value for this char is 26 .
    I need to replace it with null . In the charmap we have mentioned like:-
    SOURCECHARSET AL32UTF8
    TARGETCHARSET UTF-16
    \xef\xbf\xbd \x20

    and included CHARMAP in the parameter file but do not know why this is not happening .
    I have to mannually update/insert the record in the teradata target Database each time when this
    collision happens .Any suggestions over it.

    Also I need to confirm below, our replicat is having two tables.Say A and B.
    We are looking at the RBA in report which is causing problem and then skipping that rba for table A.
    Say at rba 1100 this is occurring so we have skipped rba 1100 by altering replicat .I need to know whether doing
    this we are losing a record for table B.

    We are using Version 12.2.0.1.0 for OGG

    • Natik Ameen says

      Anwaar, glad you like the article.

      For the first issue it will need further research and a Teradata database.

      On your second question whether you will miss other transactions by resetting the RBA that will depend on how long the transaction that you are trying to skip is.

      So what you could have done is that in logdump you should first position yourself to the RBA of the transaction you want to skip.

      Then using ENDTRANS command in logdump you move to the end of this transaction.

      After that you would then need to use the NEXTRANS command in logdump to move forward to the beginning of the very next transaction.

      Note this RBA and then set the Replicat process to begin processing from this point onwards.

      This way you can be sure that no other transaction was missed.

      Let me know how that goes.

  2. alinaveed786@gmail.com'ALI NAVEED ERSHAD says

    Does DDL INCLUDE ALL, EXCLUDE OBJNAME tabExceptions needs to be added to both extract and replicat param files ?

  3. markfury47@gmail.com'Mark Fury says

    HI, Nice article.
    Consider database SRC replicating to TRGT using OGG, i want both to be in sync.
    Meanwhile I used the skiptransaction [or any above mentioned method].
    Then, how can I say it is in sync? or any other method to include those changes too in to the TRGT database.?

Leave a Reply

Your email address will not be published.