==========================
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
REPLICAT RUNNING REP_P_11 00:00:00 00:00:08
REPLICAT RUNNING REP_P_12 00:00:00 00:00:05
REPLICAT RUNNING REP_P_13 00:00:00 00:00:00
REPLICAT ABENDED REP_P_14 00:00:50 1858:05:19
REPLICAT RUNNING REP_P_15 00:00:00 00:00:04
REPLICAT RUNNING REP_S_01 00:00:00 00:00:02
REPLICAT RUNNING REP_S_02 00:00:00 00:00:03
The REP_P_14 cannot be restarted.
What to check?
==========================
What to check
==========================
One can try these commands to restart REP_P_14
INFO REP_P_14
STATUS REP_P_14
STOP REP_P_14
START REP_P_14
CLEANUP REP_P_14
KILL REP_P_14
To see the real issues:
VIEW REPORT REP_P_14
In the output there is the actual error:
2018-01-21 10:16:58 ERROR OGG-01296 Error mapping from MEX_MOVQQ_IPNQQ.DB_PROC_PURGE_HOMEBOUND_TEMP to MEX_MOVQQ_IPNQQ.DB_PROC_PURGE_HOMEBOUND_TEMP.
==========================
By Example
==========================
GGSCI (esp-tel-1-dbu-2) 3> info REPLICAT REP_P_14
REPLICAT REP_P_14 Last Started 2018-01-21 09:54 Status ABENDED
Checkpoint Lag 00:00:50 (updated 1858:07:04 ago)
Log Read Checkpoint File /software/ogg/1212/dirdat/14/in/ep000204
2017-11-04 23:50:37.060216 RBA 6086003
GGSCI (esp-tel-1-dbu-2) 7> STATUS REP_P_14
REPLICAT REP_P_14: ABENDED
GGSCI (esp-tel-1-dbu-2) 8> STOP REP_P_14
REPLICAT REP_P_14 is already stopped.
GGSCI (esp-tel-1-dbu-2) 9> START REP_P_14
Sending START request to MANAGER ...
REPLICAT REP_P_14 starting
GGSCI (esp-tel-1-dbu-2) 7> STATUS REP_P_14
REPLICAT REP_P_14: ABENDED
GGSCI (esp-tel-1-dbu-2) 3> VIEW REPORT REP_P_14
********************************************************************
Oracle GoldenGate Delivery for Oracle
Version 12.1.2.0.0 17185003 OGGCORE_12.1.2.0.0_PLATFORMS_130924.1316_FBO
Linux, x64, 64bit (optimized), Oracle 11g on Sep 25 2013 00:50:41
Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.
Starting at 2018-01-21 10:09:04
********************************************************************
Operating System Version:
Linux
Version #1 SMP Sun Nov 10 22:19:54 EST 2013, Release 2.6.32-431.el6.x86_64
Node: esp-tel-1-dbu-2
Machine: x86_64
soft limit hard limit
Address Space Size : unlimited unlimited
Heap Size : unlimited unlimited
File Size : unlimited unlimited
CPU Time : unlimited unlimited
Process id: 47121
Description:
********************************************************************
** Running with the following parameters **
********************************************************************
2018-01-21 10:09:04 INFO OGG-03059 Operating system character set identified as UTF-8.
2018-01-21 10:09:04 INFO OGG-02695 ANSI SQL parameter syntax is used for parameter parsing.
REPLICAT rep_p_14
setenv (ORACLE_SID="igt")
2018-01-21 10:09:04 INFO OGG-02095 Successfully set environment variable ORACLE_SID=igt.
setenv (ORACLE_HOME="/software/oracle/112")
2018-01-21 10:09:04 INFO OGG-02095 Successfully set environment variable ORACLE_HOME=/software/oracle/112.
setenv (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
2018-01-21 10:09:04 INFO OGG-02095 Successfully set environment variable NLS_LANG=AMERICAN_AMERICA.AL32UTF8.
ASSUMETARGETDEFS
USERID ogg, PASSWORD ******************************** ENCRYPTKEY DEFAULT
REPERROR (-1, IGNORE)
CACHEMGR CACHESIZE 256M
DISCARDFILE /software/ogg/1212/dirdat/14/in/disc14p.txt, append,
include ./dirprm/mapPROVtables_14.inc
MAP MEX_MOVQQ_IPNQQ.ADPE, TARGET MEX_MOVQQ_IPNQQ.ADPE;
MAP MEX_MOVQQ_IPNQQ.ADPE_FLOW_RULES, TARGET MEX_MOVQQ_IPNQQ.ADPE_FLOW_RULES;
MAP MEX_MOVQQ_IPNQQ.ADPE_IF, TARGET MEX_MOVQQ_IPNQQ.ADPE_IF;
MAP MEX_MOVQQ_IPNQQ.ALARMER, TARGET MEX_MOVQQ_IPNQQ.ALARMER;
MAP MEX_MOVQQ_IPNQQ.ALARM_EVENTS, TARGET MEX_MOVQQ_IPNQQ.ALARM_EVENTS;
MAP MEX_MOVQQ_IPNQQ.ALARM_FILE_CONFIG, TARGET MEX_MOVQQ_IPNQQ.ALARM_FILE_CONFIG;
MAP MEX_MOVQQ_IPNQQ.ALARM_MAP, TARGET MEX_MOVQQ_IPNQQ.ALARM_MAP;
opened trail file /software/ogg/1212/dirdat/14/in/ep000204 at 2018-01-21 10:09:05
2018-01-21 10:09:05 INFO OGG-03522 Setting session time zone to source database time zone 'GMT'.
2018-01-21 10:09:05 INFO OGG-03506 The source database character set, as determined from the trail file, is UTF-8.
MAP resolved (entry MEX_MOVQQ_IPNQQ.DB_PROC_PURGE_HOMEBOUND_TEMP):
MAP "MEX_MOVQQ_IPNQQ"."DB_PROC_PURGE_HOMEBOUND_TEMP", TARGET MEX_MOVQQ_IPNQQ.DB_PROC_PURGE_HOMEBOUND_TEMP;
2018-01-21 10:09:05 WARNING OGG-06439 No unique key is defined for table DB_PROC_PURGE_HOMEBOUND_TEMP. All viable columns will be
used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
Using following columns in default map by name:
NETWORK_ID, PROFILE_ID, PURGE_COUNT_GSM, PURGE_COUNT_LTE,
PURGE_COUNT_VOICE, PURGE_COUNT_DATA
Using the following key columns for target table MEX_MOVQQ_IPNQQ.DB_PROC_PURGE_HOMEBOUND_TEMP: NETWORK_ID, PROFILE_ID, PURGE_COUNT_G
SM, PURGE_COUNT_LTE, PURGE_COUNT_VOICE, PURGE_COUNT_DATA.
2018-01-21 10:09:05 WARNING OGG-01004 Aborted grouped transaction on 'MEX_MOVQQ_IPNQQ.DB_PROC_PURGE_HOMEBOUND_TEMP', Database erro
r 1403 (OCI Error ORA-01403: no data found, SQL <DELETE FROM "MEX_MOVQQ_IPNQQ"."DB_PROC_PURGE_HOMEBOUND_TEMP" WHERE "NETWORK_ID" =
:b0 AND "PROFILE_ID" = :b1 AND "PURGE_COUNT_GSM" = :b2 AND "PURGE_COUNT_LTE" is NULL AND "PURGE_COUNT_VOICE" is NULL AND "PURGE_COUN
T_DATA" is NULL AND ROWNUM = 1>).
2018-01-21 10:09:05 WARNING OGG-01003 Repositioning to rba 6086003 in seqno 204.
2018-01-21 10:09:05 WARNING OGG-01154 SQL error 1403 mapping MEX_MOVQQ_IPNQQ.DB_PROC_PURGE_HOMEBOUND_TEMP to MEX_MOVQQ_IPNQQ.DB_PROC_PURGE_HOMEBOUND_TEMP
OCI Error ORA-01403: no data found, SQL <DELETE FROM "MEX_MOVQQ_IPNQQ"."DB_PROC_PURGE_HOMEBOUND_TEMP" WHERE
"NETWORK_ID" = :b0 AND "PROFILE_ID" = :b1 AND "PURGE_COUNT_GSM" = :b2 AND "PURGE_COUNT_LTE" is NULL AND "PURGE_COUNT_VOICE" is NULL
AND "PURGE_COUNT_DATA" is NULL AND ROWNUM = 1>.
2018-01-21 10:09:05 WARNING OGG-01003 Repositioning to rba 6086003 in seqno 204.
==========================
Checking the Table in question
==========================
After checking the table DB_PROC_PURGE_HOMEBOUND_TEMP - it is not clear why this table failed in Replication
The table on both sites is identical and empty.
Table DB_PROC_PURGE_HOMEBOUND_TEMP got no Primary Key on both Instances.
Table DB_PROC_PURGE_HOMEBOUND_TEMP is identical on both Instances.
Even after running TRUNCATE TABLE DB_PROC_PURGE_HOMEBOUND_TEMP on both sites, the issue still remains.
As a workaround this table was excluded from the tables list for Replication.
A more general solution would have been to use ALLOWNOOPUPDATES parameter.
But since this was a production environment, and this table should not have been replicate in the first place, a more conservative solution was chosen.
==========================
REPLICAT Parameters File
==========================
Per VIEW REPORT REP_P_14 command output:
DISCARDFILE /software/ogg/1212/dirdat/14/in/disc14p.txt, append,
include ./dirprm/mapPROVtables_14.inc
Checking ./dirprm/mapPROVtables_14.inc file, it got such references:
MAP MEX_MOVQQ_IPNQQ.ADPE, TARGET MEX_MOVQQ_IPNQQ.ADPE;
MAP MEX_MOVQQ_IPNQQ.ADPE_FLOW_RULES, TARGET MEX_MOVQQ_IPNQQ.ADPE_FLOW_RULES;
MAP MEX_MOVQQ_IPNQQ.ADPE_IF, TARGET MEX_MOVQQ_IPNQQ.ADPE_IF;
MAP MEX_MOVQQ_IPNQQ.ALARMER, TARGET MEX_MOVQQ_IPNQQ.ALARMER;
MAP MEX_MOVQQ_IPNQQ.ALARM_EVENTS, TARGET MEX_MOVQQ_IPNQQ.ALARM_EVENTS;
MAP MEX_MOVQQ_IPNQQ.ALARM_FILE_CONFIG, TARGET MEX_MOVQQ_IPNQQ.ALARM_FILE_CONFIG;
MAP MEX_MOVQQ_IPNQQ.ALARM_MAP, TARGET MEX_MOVQQ_IPNQQ.ALARM_MAP;
...
...
MAP MEX_MOVQQ_IPNQQ.DB_PROC_PURGE_HOMEBOUND_TEMP, TARGET
MEX_MOVQQ_IPNQQ.DB_PROC_PURGE_HOMEBOUND_TEMP;
As a workaround, the reference to MEX_MOVQQ_IPNQQ.DB_PROC_PURGE_HOMEBOUND_TEMP was deleted from tables list, and REP_P_14 restarted.
Same was done on both instances.
And at this point the REP_P_14 was started without errors.
./ggsci
GGSCI (esp-tel-2-dbu-1) 2> STOP REP_P_14
ending STOP request to REPLICAT REP_P_14 ...
Request processed.
GGSCI (esp-tel-2-dbu-1) 2> START REP_P_14
Sending START request to MANAGER ...
REPLICAT REP_P_14 starting
GGSCI (esp-tel-2-dbu-1) 3> STATUS REP_P_14
REPLICAT REP_P_14: RUNNING
==========================
OGG-01296 Error mapping Error
==========================
Per Oracle Documentation:
Replicat Abends With Error "OGG-01296 Oracle GoldenGate Delivery for Oracle, repggt10.prm: Error mapping from CW.XYZ to CW.XYZ" (Doc ID 2295315.1)
APPLIES TO:
Oracle GoldenGate - Version 11.2.1.0.14 and later
Information in this document applies to any platform.
SYMPTOMS
Replicat process abends with following mapping errors
2017-07-16 19:52:12 WARNING OGG-00869 Oracle GoldenGate Delivery for Oracle, repggt10.prm: No unique key is defined for table 'XYZ'. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2017-07-16 19:52:12 ERROR OGG-01296 Oracle GoldenGate Delivery for Oracle, repggt10.prm: Error mapping from CW.XYZ to CW.XYZ.
2017-07-16 19:52:12 ERROR OGG-01668 Oracle GoldenGate Delivery for Oracle, repggt10.prm: PROCESS ABENDING.
CAUSE
Table doesn't have PK/UI and Replicat encounters a no-up update
Add ALLOWNOOPUPDATES or APPLYNOOPUPDATES parameter to the Replicat
What are ALLOWNOOPUPDATES or APPLYNOOPUPDATES parameters?
ALLOWNOOPUPDATES and NOALLOWNOOPUPDATES
ALLOWNOOPUPDATES and NOALLOWNOOPUPDATES to control how Replicat responds to a no-op operation.
A no-op operation is one in which there is no effect on the target table.
NOALLOWNOOPUPDATES is the default value.
The following are some examples of how this can occur:
A. The source table has a column that does not exist in the target table, or it has a column that was excluded from replication (with a COLSEXCEPT clause).
In either case, if that source column is updated, there will be no target column name to use in the SET clause within the Replicat SQL statement.
B. An update is made that sets a column to the same value as the current one.
The database does not log the new value, because it did not change. However, Oracle GoldenGate captures the operation as a change record because the primary key was logged, but there is no column value for the SET clause in the Replicat SQL statement.
By default (NOALLOWNOOPUPDATES), Replicat abends with an error because these types of operations do not update the database.
With ALLOWNOOPUPDATES, Replicat ignores the operation instead of abending.
APPLYNOOPUPDATES
You can use the internal parameter APPLYNOOPUPDATES to force the UPDATE to be applied.
APPLYNOOPUPDATES overrides ALLOWNOOPUPDATES.
If both are specified, Replicat applies updates for which there are key columns for the source and target tables.
ALLOWNOOPUPDATES usage
The parameter ALLOWNOOPUPDATES should be added to the prm file and replicat process restarted.
For example:
/software/ogg/1212/dirprm>% less rep_p_14.prm
REPLICAT rep_p_14
setenv (ORACLE_SID="igt")
setenv (ORACLE_HOME="/software/oracle/112")
setenv (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")
ASSUMETARGETDEFS
ALLOWNOOPUPDATES
USERID ogg, PASSWORD AACAAAAAAAAAAAIAZEDCPHICXGPEQCED ENCRYPTKEY DEFAULT
REPERROR (-1, IGNORE)
CACHEMGR CACHESIZE 256M
DISCARDFILE /software/ogg/1212/dirdat/14/in/disc14p.txt, append,
include ./dirprm/mapPROVtables_14.inc
