Shell - Monitor Goldengate trail file sequence threshold limit

## ----------------------------------------------------------------

## File Name    : /home/oracle/scripts/seq_monitor.sh

## Description: Monitor trail file sequence limit

## Call Syntax  : sh /home/oracle/scripts/seq_monitor.sh ORACLE_SID

## Last Modified: 3/15/2017

## ----------------------------------------------------------------

## Revision history:

## ----------------------------------------------------------------

PATH=$PATH:/usr/local/bin

export PATH

export ORACLE_SID=$1

export ORACLE_HOME/u03/app/oracle/product/12.1.0

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/ggs

export GG_HOME=/u01/ggs

export SCRIPT_HOME=/home/oracle/scripts

TO="aj@gmail.com"

THRESHOLD11G=980000

THRESHOLD12C=980000000

PROCESS="NONE"

log_dir=/home/oracle/scripts/logs

# Script

if [ "$#" -ne 1 ]; then

  echo "Usage: sh seq_monitor.sh ORACLE_SID"

  exit 1

fi

# check if log directory exist

if [ ! -d "$log_dir" ]; then

echo "Log directory $log_dir does not exist"

exit 1

fi

# remove if old log file exist

if [[ -f $log_dir/infostat.txt ]]; then

rm $log_dir/infostat.txt

fi

cd $GG_HOME

./ggsci << EOF > $log_dir/infostat.txt

info all

EOF

log_file=$log_dir/infostat.txt

ext_name=`cat $log_dir/infostat.txt |grep EXTRACT | awk '{print $3}'`

rep_name=`cat $log_dir/infostat.txt |grep REPLICAT | awk '{print $3}'`

counte=(`echo $ext_name | wc -w`)

countr=(`echo $rep_name | wc -w`)

if [[ $counte > 0 ]]; then

PROCESS="EXTRACT"

fi

if [[ $countr > 0 ]]; then

PROCESS="REPLICAT"

fi

# checking for Extract

mail=0

if [[ "$PROCESS"="EXTRACT" ]]; then

for ext_chk in $ext_name

do

if [[ -f $log_dir/extinfo.txt ]]; then

rm $log_dir/extinfo.txt

fi

flag=0

./ggsci << EOF > $log_dir/extinfo.txt

info $ext_chk showch

EOF

cwp=`cat $log_dir/extinfo.txt |grep -ni "current write position" |cut -f1 -d:`

cwp=$(echo "$cwp + 1" | bc)

seqno=`head -$cwp $log_dir/extinfo.txt |tail -1 | awk -F':' '{print $2+0}'`

length=(`echo $seqno | wc -m`)

if [[ length -gt 7 ]]; then

THRESHOLD=$THRESHOLD12C

else

THRESHOLD=$THRESHOLD11G

fi

seqno=$(echo "$seqno + 0" | bc)

if [[ $seqno -gt $THRESHOLD ]]; then

flag=1

mail=1

MSG="$PROCESS $ext_chk with Sequence $seqno about to reach threshold"

fi

done

fi

# check if any extract trail sequence is about to reach threshold

if [[ $mail == 1 ]]; then

if [[ $counte > 0 ]]; then

/bin/mailx -s "ACTION REQUIRED: $MSG on `hostname`" "$TO" < $log_file

fi

fi

# checking for REPLICAT

mail=0

if [[ "$PROCESS"="REPLICAT" ]]; then

for rep_chk in $rep_name

do

if [[ -f $log_dir/repinfo.txt ]]; then

rm $log_dir/repinfo.txt

fi

flag=0

./ggsci << EOF > $log_dir/repinfo.txt

info $rep_chk

EOF

trailfile=(`cat $log_dir/repinfo.txt |grep File | awk '{print $5}'`)

seqno=$(echo $trailfile | rev | cut -d "/" -f1 | rev  | grep -o -E '[0-9]+')

length=(`echo $seqno | wc -m`)

if [[ length -gt 7 ]]; then

THRESHOLD=$THRESHOLD12C

else

THRESHOLD=$THRESHOLD11G

fi

seqno=$(echo "$seqno + 0" | bc)

if [[ $seqno > $THRESHOLD ]]; then

flag=1

mail=1

MSG="$PROCESS $rep_chk with Sequence $seqno about to reach threshold"

fi

done

fi

# check if any replicat trail sequence is about to reach threshold

if [[ $mail == 1 ]]; then

if [[ $countr > 0 ]]; then

/bin/mailx -s "ACTION REQUIRED: $MSG on `hostname`" "$TO" < $log_file

fi

fi

The detail of issue has been explained in previous post.