Shaun's Wiki

This page is read only. You can view the source, but not change it. Ask your administrator if you think this is wrong.
===== Oracle GoldenGate =====

==== Cache Memory Usage ====
<code>
./ggsci
dblogin USERIDALIAS ggadmin
SEND EXTRACT ext_mr CACHEMGR CACHESTATS
SEND REPLICAT rep_file CACHEMGR CACHESTATS
</code>

==== Statistics ====
<code>
./ggsci
STATS REPLICAT rep_mr, TOTALSONLY *.*, REPORTRATE MIN
STATS EXTRACT ext_mr, TOTALSONLY *.*, REPORTRATE MIN
</code>

==== Process Info ====
<code>
INFO REPLICAT *, ALLPROCESSES
INFO REPLICAT *, TASKS
INFO EXTRACT *, TASKS
</code>

==== Network Stats ====
<code>
send extract dp_mr gettcpstats
send extract dp_mr resettcpstats
</code>

==== Stats from within DB ====
<code sql>
/* Extract side */
SELECT capture_name, 86400 *(available_message_create_time - capture_message_create_time) latency_in_seconds FROM GV$GOLDENGATE_CAPTURE;

/* Replicat side */
SELECT r.apply_name, 86400 *(r.dequeue_time - c.lwm_message_create_time) latency_in_seconds FROM GV$GG_APPLY_READER r, GV$GG_APPLY_COORDINATOR c WHERE r.apply# = c.apply# and r.apply_name = c.apply_name;
</code>

==== Heartbeat Table ====
Show heartbeat for all extracts and replicats
<code sql>
select * from ggadmin.gg_heartbeat;
</code>
Show lag history 
<code sql>
set pagesize 200 linesize 200
col heartbeat_received_ts format a30
col incoming_path format a24
col incoming_lag format 999,999.999
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';
SELECT heartbeat_received_ts, incoming_path, incoming_lag FROM ggadmin.gg_lag_history;
</code>

==== Tuning STREAMS pool ====
Set the STREAMS_POOL_SIZE initialization parameter for the database to the following value:
**(MAX_SGA_SIZE * # of integrated Extracts) + 25% head room**

<code sql>
select current_size from v$sga_dynamic_components where component = 'streams pool';

/* Streams pool advice */
COLUMN STREAMS_POOL_SIZE_FOR_ESTIMATE  HEADING 'Streams Pool|Size for Estimate'
  FORMAT 999999999999
COLUMN STREAMS_POOL_SIZE_FACTOR HEADING 'Streams Pool|Size|Factor' FORMAT 99.9
COLUMN ESTD_SPILL_COUNT HEADING 'Estimated|Spill|Count' FORMAT 99999999
COLUMN ESTD_SPILL_TIME HEADING 'Estimated|Spill|Time' FORMAT 99999999.99
COLUMN ESTD_UNSPILL_COUNT HEADING 'Estimated|Unspill|Count' FORMAT 99999999
COLUMN ESTD_UNSPILL_TIME HEADING 'Estimated|Unspill|Time' FORMAT 99999999.99
 
SELECT STREAMS_POOL_SIZE_FOR_ESTIMATE,
       STREAMS_POOL_SIZE_FACTOR, 
       ESTD_SPILL_COUNT, 
       ESTD_SPILL_TIME, 
       ESTD_UNSPILL_COUNT,
       ESTD_UNSPILL_TIME
  FROM V$STREAMS_POOL_ADVICE;

</code>

==== Monitor Dependency Waits ====
If there are many wait dependencies when using integrated Replicat, try reducing the value of BATCHTRANSOPS.
<code sql>
select APPLY_NAME, STATE, ACTIVE_SERVER_COUNT, TOTAL_WAIT_DEPS, TOTAL_WAIT_COMMITS from V$GG_APPLY_COORDINATOR;
</code>



==== Process Memory Usage ====
<code bash gg_memory_usage.sh>
#!/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 | grep -v grep`
  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
</code>

=== Output ===
<code bash>
OSNAME = Linux

#####################################
# Individual extract Process Usage #
#####################################
41.7109 MB DP_C
31.9805 MB DP_F
29.9297 MB DP_M
37.3672 MB DP_M_O
37.8438 MB DP_S
29.1562 MB DP_SV
1516.69 MB EXT_C
74.8516 MB EXT_F
556.352 MB EXT_M
246.926 MB EXT_M_O
68.7773 MB EXT_S
91.5234 MB EXT_SV

#####################################
#   Total extract Process Usage    #
#####################################
Number of processes      = 12
AVG Memory usage/process = 230.259 MB
Total memory usage       = 2763.11  MB
No replicat processes found
</code>

==== Apply Server Details ====
<code sql>
select APPLY_NAME, APPLY#, SERVER_ID, STATE, TOTAL_MESSAGES_APPLIED from V$GG_APPLY_SERVER ORDER BY APPLY_NAME, SERVER_ID;
</code>

==== Apply Coordinator Detail ====
<code sql>
SELECT APPLY_NAME, STATE, TOTAL_APPLIED, TOTAL_ASSIGNED, TOTAL_ROLLBACKS, TOTAL_ERRORS, ACTIVE_SERVER_COUNT, TOTAL_WAIT_DEPS, TOTAL_WAIT_COMMITS FROM V$GG_APPLY_COORDINATOR;
</code>

==== Queue Status ====
<code sql>
SELECT DST_QUEUE_SCHEMA, DST_QUEUE_NAME, TOTAL_MSGS, SPID, STATE from V$PROPAGATION_RECEIVER;
</code>

==== Apply Latency ====
<code sql>
SELECT r.apply_name, 86400 *(r.dequeue_time -c.lwm_message_create_time) latency_in_seconds FROM GV$GG_APPLY_READER r, GV$GG_APPLY_COORDINATOR c WHERE r.apply# = c.apply# and r.apply_name = c.apply_name;
</code>

==== Trace Monitoring ====
under OGG installation home directory, create an xml file named gglog-<EXT_OR_REP_NAME>.xml
gglog-<EXT_OR_REP_NAME>.xml contents:
<code xml>
<?xml version="1.0"?>
<configuration reset="true">
<appender name="myTraceFile" class="FileAppender">
<param name="File" value="myTrace-%I.log"/>
<param name="Append" value="true"/>
<layout class="PatternLayout"/>
</appender>
<logger name="ggstd.ptrace">
<level value="all"/>
</logger>
</configuration>
</code>
Shaun's Wiki

User Tools

Site Tools

Page Tools
