GoldenGate Discard file into Table

				
					#!/bin/bash
USER="ggadmin"
PWD="Password_123"
DBNAME="atpdb_high"
CURLUSER="oggadmin"
CURLPASS="Password_123"
OCIGG_URL="https://xyz123456.deployment.goldengate.us-ashburn-1.oci.oraclecloud.com"
FILEPATH="/DATA/OCIGG_FILES"
REPNAME="RPSWING RPHIST"
dt=`date +\%Y-\%m-\%d-\%H-\%M-\%S`

##################################
export HOME=/home/opc
ORACLE_HOME=`cat $HOME/.bash_profile|grep $ORACLE_HOME|cut -d= -f2 | head -1`
export ORACLE_HOME
export TNS_ADMIN=$ORACLE_HOME/network/admin/ATP
###################################


if [[ -z "$ORACLE_HOME" ]]; then
    echo "ORACLE_HOME path needs to be set in .bash_profile" 1>&2
    exit 1
fi

if [[ -z "$FILEPATH" ]]; then
    echo "FILEPATH needs to be set in the script" 1>&2
    exit 1
fi

if [[ -z "$CURLUSER" ]]; then
    echo "CURLUSER needs to be set in the script" 1>&2
    exit 1
fi

if [[ -z "$OCIGG_URL" ]]; then
    echo "OCIGG_URL needs to be set in the script" 1>&2
    exit 1
fi

if [[ -z "$REPNAME" ]]; then
    echo "REPLICAT NAME needs to be set in the script" 1>&2
    exit 1
fi

PATH=$PATH:$ORACLE_HOME/bin

for p in $REPNAME
do
curl -s -u $CURLUSER:$CURLPASS -X  GET $OCIGG_URL/services/v2/replicats/$p/info/reports/$p.dsc | jq > $FILEPATH/$p.out
#echo $FILEPATH/$p.out
curl -s -u $CURLUSER:$CURLPASS -X  GET $OCIGG_URL/services/v2/replicats/$p/info/reports/"$p"0.dsc | jq > $FILEPATH/"$p"0.out
#echo $FILEPATH/"$p"0.out
done



RNAME=`awk '/lines/{y=1;next}y' $p.out | grep -oP '(?<=group )\w+'`
TABLE_INFO=`awk '/lines/{y=1;next}y' $p.out | grep "Problem replicating" | sed 's/\"//g' | sed 's/\,//g'`
ABEND_ERROR=`awk '/lines/{y=1;next}y' $p.out | grep Discarded | sed 's/\"//g' | sed 's/\,//g'`
FULL_DISCARD=`awk '/lines/{y=1;next}y' $p.out`
TIME_STAMP=`awk '/lines/{y=1;next}y' $p.out | grep -oP '(?<=opened: ).*' | tr -d ',"'`
#echo $TIME_STAMP


if awk '/lines/{y=1;next}y' $p.out | grep -oh "\w*update\w*" ; then
    DML_OP=UPDATE
elif
    awk '/lines/{y=1;next}y' $p.out | grep -oh "\w*delete\w*"; then
    DML_OP=DELETE
elif
    awk '/lines/{y=1;next}y' $p.out | grep -oh "\w*insert\w*"; then
    DML_OP=INSERT
fi


DB_VALUES=`$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF
connect $USER/$PWD@$DBNAME
set pagesize 200
set linesize 121
set heading off feedback off verify off
SET TERMOUT OFF
select REPLICAT_NAME, TIME_STAMP, UPPER(DML_OPERATION) from GGADMIN.DISCARD_TABLE order by TIME_STAMP desc fetch first 1 row only;
exit
EOF`

DB_RNAME="`echo $DB_VALUES|awk '{ print $1}'`"
DB_TIME_STAMP="`echo $DB_VALUES|awk '{ print $2" " $3}'`"
DB_DML_OP="`echo $DB_VALUES|awk '{ print $4}'`"


if [[ -z "$TABLE_INFO" ]]; then
    REPDISCFILE0=0
    for p in $REPNAME
        do

                REPDISCFILE0=`awk '/lines/{y=1;next}y' "$p"0.out | grep -E "Mapping problem"`
                  if [[ -z "$REPDISCFILE0" ]]; then
                    exit 0
                  else
                        C_RNAME=`awk '/lines/{y=1;next}y' "$p"0.out | grep -oP '(?<=group )\w+'`
                        C_TABLE_INFO=`awk '/lines/{y=1;next}y' "$p"0.out | grep "Problem replicating" | sed 's/\"//g' | sed 's/\,//g'`
                        C_ABEND_ERROR=`awk '/lines/{y=1;next}y' "$p"0.out | grep Discarded | sed 's/\"//g' | sed 's/\,//g'`
                        C_FULL_DISCARD=`awk '/lines/{y=1;next}y' "$p"0.out`
                        C_TIME_STAMP=`awk '/lines/{y=1;next}y' "$p"0.out | grep -oP '(?<=opened: ).*' | tr -d ',"'`
                            if awk '/lines/{y=1;next}y' "$p"0.out | grep -oh "\w*update\w*" ; then
                                 C_DML_OP=UPDATE
                               elif
                              awk '/lines/{y=1;next}y' "$p"0.out | grep -oh "\w*delete\w*"; then
                                 C_DML_OP=DELETE
                               elif
                               awk '/lines/{y=1;next}y' "$p"0.out | grep -oh "\w*insert\w*"; then
                                 C_DML_OP=INSERT
                             fi
                fi
        if [ "$C_RNAME" == "$DB_RNAME" ] && [ "$C_TIME_STAMP" == "$DB_TIME_STAMP" ] && [ "$C_DML_OP" == "$DB_DML_OP" ]; then
        exit 0
         else
                $ORACLE_HOME/bin/sqlplus -s /nolog <<EOF
                connect $USER/$PWD@$DBNAME
                set pagesize 200
                set linesize 121
                set heading off feedback off verify off
                SET TERMOUT OFF
                insert into GGADMIN.DISCARD_TABLE values (SYS_GUID(), ('$C_TIME_STAMP'), ('$C_RNAME'), ('$C_DML_OP'), ('$C_TABLE_INFO'), ('$C_ABEND_ERROR'), ('$C_FULL_DISCARD'));
                commit;
                exit 1;
EOF
        fi
done
fi

if [[ -n "${TABLE_INFO}" ]]; then
    if [ "$RNAME" == "$DB_RNAME" ] && [ "$DB_DML_OP" == "$DML_OP" ] && [ "$TIME_STAMP" == "$DB_TIME_STAMP" ];
   then
   # echo "NOT RUNNING"
    exit 1;
else
echo "INSERTING"
$ORACLE_HOME/bin/sqlplus -s /nolog <<EOF
connect $USER/$PWD@$DBNAME
set pagesize 200
set linesize 121
set heading off feedback off verify off
SET TERMOUT OFF
insert into GGADMIN.DISCARD_TABLE values (SYS_GUID(), ('$TIME_STAMP'), ('$RNAME'), ('$DML_OP'), ('$TABLE_INFO'), ('$ABEND_ERROR'), ('$FULL_DISCARD'));
commit;
exit
EOF
fi
fi