Monthly Archives: December 2013

Mapping problem with compressed update record – Key column missing from update

Here’s an example of an update statement failing on the target database on a table without a primary or unique key.

Since there are no keys defined on the table, the replicat will consider all the columns as key columns.

The update statement that was processed by the extract only logged the column values that were changed along with the columns defined by the keycols parameter in the table statement, in this case the objectid column.

The replicat ABENDed since it did not have values for allt he columns. Here’s the discard file which show the specifics on the columns and the error message.

Oracle GoldenGate Delivery for Oracle process started, group RWPC discard file opened: 2013-09-08 11:21:51

Key column ROV_RNAME (354) is missing from update on table WPC.WPCTBL
Key column A_RCPT_DATE (355) is missing from update on table WPC.WPCTBL
Key column A_SCAN_TIME (356) is missing from update on table WPC.WPCTBL
Key column DOCTYPE (357) is missing from update on table WPC.WPCTBL
Key column ORIGDATE (358) is missing from update on table WPC.WPCTBL
Key column STRACKING (359) is missing from update on table WPC.WPCTBL
Key column M_IND (360) is missing from update on table WPC.WPCTBL
Key column NCOB (361) is missing from update on table WPC.WPCTBL
Key column RET_CBT (362) is missing from update on table WPC.WPCTBL
Key column M_COUNTER (363) is missing from update on table WPC.WPCTBL
Key column IS_DOC (364) is missing from update on table WPC.WPCTBL
Missing 11 key columns in update for table WPC.WPCTBL.
Current time: 2013-09-08 11:21:53
Discarded record from action ABEND on error 0

Aborting transaction on /u01/app/oracle/product/golden_gate/dirdat/wP beginning at seqno 76 rba 695700
                         error at seqno 76 rba 973932
Problem replicating WPC.WPCTBL to WPC.WPCTBL
Mapping problem with compressed update record (target format)...
*
CATEGORYID = 3000
OBJECTID = 1892683845

Doing some research on My Oracle Support turned up the following two notes that were used to come up with a solution.

Replicat Abending With Mapping Error and discard file shows Missing Key Columns (Doc ID 1276538.1)
Can I Use A Wildcard In For All Of The Tables Except One so that I may describe it separately? (Doc ID 970419.1)

Two parameters were put in the replicat parameter file to workaround the issue.

DYNAMICRESOLUTION - enables fast process startup when there are numerous tables specified in TABLE or MAP statements

WILDCARDRESOLVE IMMEDIATE - objects are processed at startup

Here’s a snippet of the replicat parameter file with the new mappings. The replicat is able to process the transaction after the change since the KEYCOLS parameter specified objectid to be the table’s key column. Hence, GG updates the columns in the trail, leaving those mentioned before in the discard file unchanged.

> cat rwpc.prm 
REPLICAT rwpc
...
-- ------------------------------------------------------------------
-- mappings
-- ------------------------------------------------------------------
WILDCARDRESOLVE IMMEDIATE
MAP WPC.WPCTBL, TARGET WPC.WPCTBL, KEYCOLS (objectid);
DYNAMICRESOLUTION
MAPEXCLUDE WPC.WPCTBL;
MAP WPC.*, TARGET WPC.*;

GoldenGate Process Memory Usage

From the GG Installation documentation:

Each Extract and Replicat process needs approximately 25-55 MB of memory, or more depending on the size of the transactions and the number of concurrent transactions.

So let’s see some example of how much memory is being used. I wrote a short script to show the memory usage by using “ps” and have two examples, one on Linux and one on Solaris.

The first example ( on Linux ) shows the usage of GoldenGate extract and replicat processes configured to process a heartbeat table, so not very much activity.

The second example ( on Solaris ) show the usage of GoldenGate processes on a live system with lots of activity.

Due to differences on how ps behaves between Linux and Solaris, I display either the group name itself, or the parameter file of the group, which contains the group name. The unix script can be found at the bottom of this blog.

Here’s the example running on Linux. This configuration does not have very many transactions.


> ./gg_memory_usage.sh
OSNAME = Linux

#####################################
# Individual extract Process Usage #
#####################################
48.3516 MB CGGMONX
48.6094 MB CGGMONY
31.6055 MB PGGMONX
31.6055 MB PGGMONY

#####################################
#   Total extract Process Usage    #
#####################################
Number of processes      = 4
AVG Memory usage/process = 40.043 MB
Total memory usage       = 160.172  MB

#####################################
# Individual replicat Process Usage #
#####################################
29.8398 MB RGGMONX
29.832 MB RGGMONY

#####################################
#   Total replicat Process Usage    #
#####################################
Number of processes      = 2
AVG Memory usage/process = 29.8359 MB
Total memory usage       = 59.6719  MB



Here’s the example running on Solaris. This configuration has processed over 11,000 transactions per second at peak times, but normally runs around 5000 transactions per second on average.


oracle@sadcsim1db1[CSOTADB1] $ ./gg_memory_usage.sh
OSNAME = SunOS

#####################################
# Individual extract Process Usage #
#####################################
7565.41 MB /ggs/dirprm/e1.prm
5435.1 MB /ggs/dirprm/e2.prm
160.758 MB /ggs/dirprm/p1.prm
156.758 MB /ggs/dirprm/p2.prm
164.758 MB /ggs/dirprm/p3.prm
160.758 MB /ggs/dirprm/p4.prm
160.758 MB /ggs/dirprm/p5.prm
156.758 MB /ggs/dirprm/p6.prm
164.758 MB /ggs/dirprm/p7.prm
160.758 MB /ggs/dirprm/p8.prm
160.758 MB /ggs/dirprm/p9.prm
156.758 MB /ggs/dirprm/p10.prm
164.758 MB /ggs/dirprm/p11.prm
160.758 MB /ggs/dirprm/p12.prm
160.758 MB /ggs/dirprm/p13.prm
156.758 MB /ggs/dirprm/p14.prm
164.758 MB /ggs/dirprm/p15.prm
160.758 MB /ggs/dirprm/p16.prm

#####################################
#   Total extract Process Usage    #
#####################################
Number of processes      = 18
AVG Memory usage/process = 865.147 MB
Total memory usage       = 15572.6  MB

#####################################
# Individual replicat Process Usage #
#####################################
148.688 MB /ggs/dirprm/r1.prm
144.688 MB /ggs/dirprm/r2.prm
144.688 MB /ggs/dirprm/r3.prm
148.688 MB /ggs/dirprm/r4.prm
144.688 MB /ggs/dirprm/r5.prm
144.688 MB /ggs/dirprm/r6.prm
144.688 MB /ggs/dirprm/r7.prm
144.688 MB /ggs/dirprm/r8.prm
144.688 MB /ggs/dirprm/r9.prm
144.688 MB /ggs/dirprm/r10.prm
144.688 MB /ggs/dirprm/r11.prm
144.688 MB /ggs/dirprm/r12.prm
148.688 MB /ggs/dirprm/r13.prm
144.688 MB /ggs/dirprm/r14.prm
144.688 MB /ggs/dirprm/r15.prm
148.688 MB /ggs/dirprm/r16.prm
144.688 MB /ggs/dirprm/r17.prm
144.688 MB /ggs/dirprm/r18.prm
144.688 MB /ggs/dirprm/r19.prm
144.688 MB /ggs/dirprm/r20.prm
144.688 MB /ggs/dirprm/r21.prm
144.688 MB /ggs/dirprm/r22.prm
144.688 MB /ggs/dirprm/r23.prm
144.688 MB /ggs/dirprm/r24.prm
148.688 MB /ggs/dirprm/r25.prm
144.688 MB /ggs/dirprm/r26.prm
144.688 MB /ggs/dirprm/r27.prm
148.68 MB /ggs/dirprm/r28.prm
144.68 MB /ggs/dirprm/r29.prm
144.68 MB /ggs/dirprm/r30.prm
144.68 MB /ggs/dirprm/r31.prm
144.68 MB /ggs/dirprm/r32.prm
144.68 MB /ggs/dirprm/r33.prm
144.68 MB /ggs/dirprm/r34.prm
144.68 MB /ggs/dirprm/r35.prm
144.68 MB /ggs/dirprm/r36.prm

#####################################
#   Total replicat Process Usage    #
#####################################
Number of processes      = 36
AVG Memory usage/process = 145.352 MB
Total memory usage       = 5232.68  MB

Big difference! If you have a busy system, make sure you have memory. You’ll probably have to do testing since the memory requirements will vary depending on both the number of transactions and the size of the transactions.

Here’s the script :


#!/bin/bash

###############################
# determine the OS type
###############################
OSNAME=`uname`

case "$OSNAME" in
  "SunOS")
    echo "OSNAME = $OSNAME"
    ;;
  "Linux")
    echo "OSNAME = $OSNAME"
    ;;
  "*")
    echo "This script has not been verified on $OSNAME"
    exit 1
    ;;
esac

###############################
# set the temp file
###############################
TMPFILE=/tmp/pmem.tmp
if [ -f $TMPFILE ]
then
  rm -f $TMPFILE
fi

################################
# loop over the gg process types
################################
PROCESSES="extract replicat"

for PROCESS in $PROCESSES
do
  FLAG=""
  FLAG=`ps -ef | grep $PROCESS`
  if [ -z "FLAG" ]
  then
    echo "No $PROCESS processes found"
  else
    echo
    echo "#####################################"
    echo "# Individual $PROCESS Process Usage #"
    echo "#####################################"
    case "$OSNAME" in
      "Linux")
        ps -C $PROCESS -O rss > $TMPFILE
        cat $TMPFILE | grep $PROCESS | awk '{print $2/1024, "MB", $12}' | sort -k 2
        ;;
      "SunOS")
        ps -efo vsz,uid,pid,ppid,pcpu,args | grep -v grep | grep $PROCESS > $TMPFILE
        cat $TMPFILE | grep $PROCESS | awk '{print $1/1024, "MB", $8}' | sort -k 2
        ;;
      "*")
        echo "This script has not been verified on $OSNAME"
        exit 1
        ;;
    esac
    rm -f $TMPFILE

    echo
    echo "#####################################"
    echo "#   Total $PROCESS Process Usage    #"
    echo "#####################################"
    case "$OSNAME" in
      "Linux")
        ps -C $PROCESS -O rss > $TMPFILE
        cat $TMPFILE | grep $PROCESS | awk '{count ++; sum=sum+$2; } END \
          { print "Number of processes      =",count; \
          print "AVG Memory usage/process =",sum/1024/count, "MB"; \
          print "Total memory usage       =", sum/1024,  " MB"}'
        ;;
      "SunOS")
        ps -efo vsz,uid,pid,ppid,pcpu,comm | grep -v grep | grep $PROCESS > $TMPFILE
        cat $TMPFILE | awk '{count ++; sum=sum+$1; } END \
          { print "Number of processes      =",count; \
          print "AVG Memory usage/process =",sum/1024/count, "MB"; \
          print "Total memory usage       =", sum/1024,  " MB"}'
        ;;
      "*")
        echo "This script has not been verified on $OSNAME"
        exit 1
        ;;
    esac
    rm -f $TMPFILE
  fi
done

exit 0