Oracle GG Golden Gate Replicat Process is Abended in UPDATE. What to check.

==========================
General
==========================
The issue: One of the Oracle GG (Golden Gate) REPLICAT processes is in ABENDED status

>%cd /software/ogg/1212
/software/ogg/1212/>% ./ggsci
info all

EXTRACT     RUNNING     EXT_S_01    00:00:07      00:00:06
EXTRACT     RUNNING     EXT_S_03    00:00:06      00:00:06
EXTRACT     RUNNING     EXT_S_04    00:00:05      00:00:09
EXTRACT     RUNNING     EXT_S_05    00:00:06      00:00:01
REPLICAT    RUNNING     REP_I_01    00:00:00      00:00:08
REPLICAT    RUNNING     REP_I_03    00:00:00      00:00:04
REPLICAT    RUNNING     REP_I_04    00:00:00      00:00:09
REPLICAT    RUNNING     REP_I_05    00:00:00      00:00:08
REPLICAT    RUNNING     REP_P_01    00:00:00      00:00:08
REPLICAT    RUNNING     REP_P_03    00:00:00      00:00:08
REPLICAT    RUNNING     REP_P_04    00:00:02      00:00:03
REPLICAT    ABENDED     REP_P_05    6208:22:23    00:44:39
REPLICAT    RUNNING     REP_P_06    00:00:00      00:00:08
REPLICAT    RUNNING     REP_S_01    00:00:00      00:00:03
REPLICAT    RUNNING     REP_S_03    00:00:00      00:00:01
REPLICAT    RUNNING     REP_S_04    00:00:00      00:00:00


==========================
Error
==========================

When checking error log for the Replicat process, there are these errors

2018-07-30 14:23:27  WARNING OGG-01004  Oracle GoldenGate Delivery for Oracle, rep_p_05.prm:  
Aborted grouped transaction on 'BEL_BELGA_MOCOQ.GA_PRODUCT_TREE_CONF', Database error 1403 (OCI Error ORA-01403: no data found, SQL <UPDATE "BEL_BELGA_MOCOQ"."GA_PRODUCT_TREE_CONF" x SET x."ITEM_ID" = :a1 WHERE x."ITEM_ID" = :b0>).

2018-07-30 14:23:27  WARNING OGG-01154  Oracle GoldenGate Delivery for Oracle, rep_p_05.prm: 
SQL error 1403 mapping BEL_BELGA_MOCOQ.GA_PRODUCT_TREE_CONF 
to BEL_BELGA_MOCOQ.GA_PRODUCT_TREE_CONF OCI Error ORA-01403: no data found, SQL <UPDATE "BEL_BELGA_MOCOQ"."GA_PRODUCT_TREE_CONF" x SET x."ITEM_ID" = :a1 WHERE x."ITEM_ID" = :b0>.

2018-07-30 14:23:27  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, rep_p_05.prm:  Error mapping from BEL_BELGA_MOCOQ.GA_PRODUCT_TREE_CONF to BEL_BELGA_MOCOQ.GA_PRODUCT_TREE_CONF.
2018-07-30 14:23:27  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, rep_p_05.prm:  PROCESS ABENDING.



==========================
SKIPTRANSACTION
==========================
The syntax:

START REPLICAT <group_name> SKIPTRANSACTION

Maybe need to repeat this step several times, since several transactions should be skipped.

SKIPTRANSACTION causes Replicat to skip the first transaction after its expected startup position in the trail. 

All operations from that first transaction are excluded.

If the MAXTRANSOPS parameter is also being used for this Replicat, it is possible that the process will start to read the trail file from somewhere in the middle of a transaction. 

In that case, the remainder of the partial transaction is skipped, and Replicat resumes normal processing from the next begin-transaction record in the file. 

The skipped records are written to the discard file if the DISCARDFILE parameter is being used; 
Otherwise, a message is written to the report file that is similar to:

User requested START SKIPTRANSACTION. 
The current transaction will be skipped. 


Transaction ID txid, position Seqno seqno, RBA rba


==========================
Fix for error
==========================

The solution is to reset the Replicat Process on BOTH nodes.

ALTER REPLICAT REP_P_05, BEGIN NOW
REPLICAT altered

START REPLICAT REP_P_05
Sending START request to MANAGER ...
REPLICAT REP_P_05 starting

INFO ALL

EXTRACT     RUNNING     EXT_S_01    00:00:07      00:00:06
EXTRACT     RUNNING     EXT_S_03    00:00:06      00:00:06
EXTRACT     RUNNING     EXT_S_04    00:00:05      00:00:09
EXTRACT     RUNNING     EXT_S_05    00:00:06      00:00:01
REPLICAT    RUNNING     REP_I_01    00:00:00      00:00:08
REPLICAT    RUNNING     REP_I_03    00:00:00      00:00:04
REPLICAT    RUNNING     REP_I_04    00:00:00      00:00:09
REPLICAT    RUNNING     REP_I_05    00:00:00      00:00:08
REPLICAT    RUNNING     REP_P_01    00:00:00      00:00:08
REPLICAT    RUNNING     REP_P_03    00:00:00      00:00:08
REPLICAT    RUNNING     REP_P_04    00:00:09      00:00:04
REPLICAT    RUNNING     REP_P_05    00:00:00      00:00:08
REPLICAT    RUNNING     REP_S_01    00:00:00      00:00:03
REPLICAT    RUNNING     REP_S_03    00:00:00      00:00:01
REPLICAT    RUNNING     REP_S_04    00:00:00      00:00:00
REPLICAT    RUNNING     REP_S_05    00:00:00      00:00:04

==========================
Deep Fix for error
==========================
Need to add SCHEMATRANDATA or TRANDATA on the mapping schemas.

ADD SCHEMATRANDATA <schema_name>

ADD TRANDATA <schema_name>.*

If possible, use the ADD SCHEMATRANDATA command rather than the ADD TRANDATA command. 
The ADD SCHEMATRANDATA command ensures replication continuity should DML occur on an object for which DDL has just been performed.


ADD SCHEMATRANDATA

Example:
DBLOGIN...
ADD SCHEMATRANDATA scott

Use ADD SCHEMATRANDATA to enable schema-level supplemental logging for a table. 
ADD SCHEMATRANDATA acts on all of the current and future tables in a given schema to automatically log a superset of available keys that Oracle GoldenGate needs for row identification.


ADD SCHEMATRANDATA is valid for both integrated and classic capture and does the following:

- Enables Oracle supplemental logging for new tables created with a CREATE TABLE.

- Updates supplemental logging for tables affected by an ALTER TABLE to add or drop columns.

- Updates supplemental logging for tables that are renamed.


- Updates supplemental logging for tables for which unique or primary keys are added or dropped.

ADD SCHEMATRANDATA is not straight forward:
1. Need to apply Oracle Patch 10423000 to the source database if the Oracle version is earlier than 11.2.0.2.

2. Oracle strongly encourages putting the source database into forced logging mode and enabling minimal supplemental logging at the database level when using Oracle GoldenGate. 

ADD TRANDATA

Example:
DBLOGIN...
ADD TRANDATA scott.my_table

Use ADD TRANDATA to enable Oracle GoldenGate to acquire the transaction information that it needs from the transaction records.

By default, ADD TRANDATA for Oracle enables the unconditional logging of the primary key and the conditional supplemental logging of all unique key(s) and foreign key(s) of the specified table.

If possible, use the ADD SCHEMATRANDATA command rather than the ADD TRANDATA command. 

The ADD SCHEMATRANDATA command ensures replication continuity should DML occur on an object for which DDL has just been performed.







==========================
Alternative Fix for error

==========================

Another solution, which I did not test myself, would be to follow Oracle TechNote:

"Replicat Abends with "OGG-01154 SQL error 1403 mapping" (Doc ID 1329189.1)"

Symptoms
Replicat abending with the below error:
2011-05-31 22:42:22  WARNING OGG-01004  Aborted grouped transaction on 'STG_SAP.HRP1000', Database error 100 (retrieving bind info for query).
2011-05-31 22:42:22  WARNING OGG-01003  Repositioning to rba 21070779 in seqno 15.
2011-05-31 22:42:22  WARNING OGG-01154  SQL error 1403 mapping SAPSR3.HRP1000 to STG_SAP.HRP1000.
2011-05-31 22:42:22  WARNING OGG-01003  Repositioning to rba 21070779 in seqno 15.

Cause
The cause is not entirely identified 

Solution
Deleted trandata for all the tables and add them again
GGSCI (Sapzax04) 1> DBLOGIN USERID GGSUSER PASSWORD ****
GGSCI (Sapzax04) 2> DELETE TRANDATA XXXX.YYYY

GGSCI (Sapzax04) 2> ADD TRANDATA XXXX.YYYY

==========================

Reference

==========================

Approach to Troubleshoot an Abended OGG Process

http://www.oracle-scn.com/approach-to-troubleshoot-an-abended-ogg-process/