Goldengate for Kafka : how to send Tokens to Kafka from Goldengate

Requirement -

Recently i got one request from client to provide one unique identifier of the Source table to Kafka mostly Source Table name. 

As we do not have any Column in the table which can provide this information to the Kafka by simple Goldengate Mapping. 

To full-fill this requirement i Used Goldengate Tokens to give Source Table name with each generated topic to Kafka. 

for this configuration below changes are required. 



Source Side (Goldengate)

we need to add Token to the goldengate extract parameter file.  

TABLE Hr.Emp , tokens (SOURCE_TABLE = "Emp") ;




Target Side (Kafka)

we need to make below changes to kafka.props file at target goldengate configuration side 

gg.handler.kafkahandler.includeTokens=true



Note: before implementing above solution in to production please do testing of your requirement by your own in non production ENV . above solution is based on my requirement and experience. 

OGG-03528 | OGG-15052 - Goldengate For Kafka : Replicate stopped after Linux latest OS kernel updates

Situation - 

After installing Linux latest OS kernel updates, Oracle Goldengate replicate process are stopped and showing below in report file. 

GGSCI > View report <Replicate name>

Report file show like below

2017-07-04 00:16:01  INFO    OGG-03528  The source database character set, as determined from the table definition file, is ISO-8859-1.

REPLICAT INR28

TARGETDB LIBFILE libggjava.so SET property=/goldengate/ggadmin/12.3.0/dirprm/kafka.props

2017-07-04 00:16:02  INFO    OGG-15052  Using Java class path: /goldengate/ggadmin/12.3.0/ggjava/ggjava.jar:/goldengate/ggadmin/12.3.0/dirprm:ggjava/resources/lib/op

tional/log4j-1.2.17.jar:ggjava/resources/lib/optional/slf4j-log4j12-1.7.6.jar.

#

# A fatal error has been detected by the Java Runtime Environment:

#

#  SIGBUS (0x7) at pc=0x00007f0369010eac, pid=48662, tid=139653265958272

#

# JRE version:  (8.0_66-b17) (build )

# Java VM: Java HotSpot(TM) 64-Bit Server VM (25.66-b17 mixed mode linux-amd64 compressed oops)

# Problematic frame:

# j  sun.reflect.ReflectionFactory.<clinit>()V+0

#

# Failed to write core dump. Core dumps have been disabled. To enable core dumping, try "ulimit -c unlimited" before starting Java again

#

# An error report file with more information is saved as:

# /goldengate/ggadmin/12.3.0/hs_err_pid48662.log

#

# If you would like to submit a bug report, please visit:

Reason - 
After Linux Kernel updates JVMController will not start and core dump is generated. 


Solution - 
As per Oracle Document Doc ID 2280962.1

we need to change below parameter values 

at GG_HOME/dirprm directory in kafka.props file 


BEFORE VALUE

gg.classpath=/goldengate/ggadmin/12.3.0/dirprm:/dgt/kfk/kafka/kafka_2.10-0.10.1.0/libs/*

javawriter.bootoptions=-Xmx512m -Xms32m -Djava.class.path=/goldengate/ggadmin/12.3.0/ggjava/ggjava.jar:/goldengate/ggadmin/12.3.0/dirprm

Value After Fix

gg.classpath=/goldengate/ggadmin/12.3.0/dirprm:/dgt/kfk/kafka/kafka_2.10-0.10.1.0/libs/*

javawriter.bootoptions=-Xss2m -Xms512m -Xmx1024m -Djava.class.path=/goldengate/ggadmin/12.3.0/ggjava/ggjava.jar:/goldengate/ggadmin/12.3.0/dirprm

Note: Values may change with environment and versions in use .  Before considering changes in Production Environment please validate the same in Non-production or Testing environment.  

Move LOB column of a partition Table to a new Tablespace Oracle || dba_lob_partitions

To move LOB column of a partition table to a new Tablespace in the same Database use bellow approach.

alter table Schema_name.Table_name move partition Partition_ name lob(Column_name) store as (tablespace Tablespace_name); 


Replace red marked names with Object names in your ENV. 

Schema_name = Schema of the table of which you want to move the Lob column to new Tablespace.
Table_Name = Table name of which you want to move the Lob column to new Tablespace.
Partition_name= Partition name of the Table if you just want to move column of a specific partition.
Tablespace_name= Name of the table space where you want to move the Lob column.


To check the tablespace name after column move user below.

select TABLE_NAME,COLUMN_NAME,PARTITION_NAME,LOB_PARTITION_NAME,TABLESPACE_NAME from  dba_lob_partitions where  table_name='TABLE_NAME';

Goldengate Memory Utilization - Script

Automated Script to get Goldengate process Memory Utilization. 

Below script is a Plug and Play tool for getting memory Utilization of Goldengate Process running on HP-UX or Linux Machine. 

For Creating Script in your Environment do vi GG_MemoUtilization.ksh copy and pest below code


#!/bin/ksh

###########################################################
# +----------------------------------------------------------------------------+
# | Technology : Oracle Goldengate                                                 |
# | Orther      : Kamlesh Parmar (Kamleshparmar21@gmail.com)       |
# | FILE          : GG_MemoUtilization.ksh                                         |
# | PURPOSE   : This Script is Useful to get the Memory Utilization of   |
# |               Goldengate Extract and Replicat Process                     |
# | PARAMETERS   : None                                                              |
# | EXAMPLE      : sh GG_MemoUtilization.ksh                                   |
# +----------------------------------------------------------------------------+
###########################################################

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

case "$OSNAME" in
  "HP-UX")
    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/ggmem.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 | grep -v grep`

if [ -z "$FLAG" ]
  then
    echo
    echo
    echo
    echo "#####################################"
    echo "#---No $PROCESS processes found-----#"
    echo "#####################################"
    echo
    echo
    echo
 else
    echo
    echo
    echo "###########################################################"
    echo "#-------Individual $PROCESS Process Memory Usage--------- #"
    echo "###########i###############################################"
    echo
    case "$OSNAME" in
      "HP-UX")
        UNIX95=1 ps -e -o user,pid,vsz,sz,etime,args | grep -w $PROCESS |grep -v grep > $TMPFILE
        cat $TMPFILE | grep $PROCESS | awk '{ printf "%3.4f %s\n" , $3/1024/1024,"GB   "$8}' | sort -r
        ;;
      "Linux")
        ps -C $PROCESS -O rss > $TMPFILE
        cat $TMPFILE | grep $PROCESS | awk '{print $2/1024/1024, "GB", $12}' | sort -k 2
        ;;
      "*")
        echo "This script has not been verified on $OSNAME"
        exit 1
        ;;
    esac


    echo
    echo
    echo
    echo "#####################################"
    echo "#---Total $PROCESS Process Usage----#"
    echo "#####################################"
    echo
    case "$OSNAME" in
      "HP-UX")
        cat $TMPFILE | grep $PROCESS | awk '{count ++; sum=sum+$3; } END \
          { print "Number of processes      =",count; \
          print "AVG Memory usage/process =",sum/1024/1024/count, "GB"; \
          print "Total memory usage       =", sum/1024/1024,   "GB"}'
        ;;
      "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/1024/count, "GB"; \
          print "Total memory usage       =", sum/1024/1024,  " GB"}'
        ;;
      "*")
        echo "This script has not been verified on $OSNAME"
        exit 1
        ;;
    esac
    rm -f $TMPFILE
  fi
done
echo
echo
exit 0 

Memory Utilization of Goldengate Process

Why GG process consumes memory ?

The Oracle redo log files contain both committed as well as uncommitted changes but GoldenGate only replicates committed transactions. So it needs some kind of cache where it can store the operation of each transaction until it receives a commit or rollback for that transaction. This is particularly significant for both large as well as long-running transactions.

This cache is a virtual memory pool or global cache for all the extract and replicate processes and sub-pools are allocated for each Extract log reader thread or Replicate trail reader thread as well as dedicated sub-pools for holding large data like BLOBs.

Documentation states: “While the actual amount of physical memory that is used by any Oracle GoldenGate process is controlled by the operating system, the cache manager keeps an Oracle GoldenGate process working within the soft limit of its global cache size, only allocating virtual memory on demand.”

Parameters to control memory usage in GG

CACHEMGR CACHESIZE {size}

CACHEMGR CACHEDIRECTORY {path} {size}

CACHEMGR CACHEBUFFERSIZE {size}

The CACHEMGR parameter controls the amount of virtual memory and temporary disk space that is available for caching uncommitted transaction data.

The CACHEMGR CACHESIZE parameter controls the virtual memory allocations and in GoldenGate versions 11.2 onwards for a 64-bit system the CACHESIZE by default is 64 GB.

While the CACHESIZE parameter controls the Virtual Memory, if that is exceeded then GoldenGate will swap data to disk temporarily and that is by default being allocated in the dirtmp sub-directory of the Oracle GoldenGate installation directory.

The dirtmp location will contain the .cm files. The cache manager assumes that all of the free space on the file system is available and will use it to create the .cm files until it becomes full. To regulate this we can use the CACHEMGR CACHEDIRECTORY parameter and provide both a size as well assign a directory location where these .cm files will be created.

The sizes of the initial and incremental buffers are controlled by the CACHEBUFFERSIZE option of CACHEMGR.

To know the Memory Utilization of Goldengate Process in your Environment use below Automated Script

Automated Script to know Memory Utilization of Goldengate Process