Back to Main

PDF VERSION

Oracle GoldenGate Discard Alerts

Author: Vladimir Grigorian

Review Date: March 20, 2015

Last Updated: March 20, 2015

Version: 2.0

Table of Contents

Document Control ………………………………………………………………………. 3

Introduction ……………………………………………………………………………… 4

Overview of GoldenGate Discards Alerts Creation ……………………………….. 5

1. GoldenGate Discards Scripts Functionality and Purpose……………………… 8

2. Implementation …………………………………….…………………………… 10

APPENDIX ……………………………………………………………………………….. 16

Document Control

Change Record

Date	Author	Version	Change Reference
March 10, 2011	Vladimir Grigorian	1.0	First Draft

Reviewers

Name

Position

Distribution

Copy No.	Name	Location
1
2
3

This publication may not be reproduced, stored in a retrieval system, or transmitted in whole or in part, in any form, or by any means, electronic, mechanical, photocopying, recording or otherwise, without the prior written permission of Cognizant/ Xerox Company.

Introduction

Purpose

The purpose of this document is document is to outline a procedure for automation of discard file creation by GoldenGate.

Usually a discard file requires a DBA’s attention, if not an intervention of some kind. Some GoldenGate administrators check for discards daily, others only check the discard directory only when an issue occurs. Unfortunately if the discard method is set to purge or rewrite, an older discard record may have been unavailable.

The following script is designed to automate a discard file directory check daily at 11:58PM. If it finds a discard file it checks for the following conditions:

1) Is the discard file new? Was it created within the past 24 hours?

2) Does the discard file contain more than just a “opening a new discard file on such a day and such time”? That header is written to a discard file on each process startup and doesn’t indicate a problem. So, we need the file not only to be fresh, but also big enough to contain actual discarded transaction information.

Only if both conditions are met an alert is sent notifying of the new file. This way only when there is a valid discard file is created a DBA needs to review it.

Audience

This document is intended for:

· Oracle DBAs and GoldenGate Administrators.

Assumptions

Following assumptions have been verified prior to installing and configuring Oracle GoldenGate.

A discard directory had been created, a discard file is specified in the process parameter and there is an issue that actually triggers a discard

Overview of GoldenGate Discard Alerts

The following outlines the intended setup for this configuration.

Table 1. Server Information (Due to Hardware Constraint this POC was executed on a Single Server)

	Source	Destination
Hostname	Oralinux6	Oralinux6
OS	3.8.13-55.1.2.el7uek.x86_64	3.8.13-55.1.2.el7uek.x86_64
GoldenGate Home	/u02/gg/source	/u02/gg/target
Oracle Instance	PBMP	STBMP
Oracle Version	12.1.0.2.0	12.1.0.2.0
GoldenGate version	11.2.1.0.27	11.2.1.0.27

Table 2. GoldenGate Processes Information

	Primary	Standby
Manager Port	7809	7810
DataPump	ESNDRT01	ESNDRT01
Failover Scripts in	/u02/gg/source/scripts	/u02/gg/source/scripts
Trails	/u02/gg/source/dirdat/s1	/u02/gg/source/dirdat/s1
GoldenGate Admin	GGATE	GGATE
User schemas	SENDER	SENDER

Fig 1. Regular DataGuard and GoldenGate Operation

Fig 2. GoldenGate Configuration

1. GoldenGate Failover Scripts Functionality and Purpose

Script Name	Purpose	Function
find_discards_run.ksh	Check	Send alert that a discard is found

#!/bin/ksh

############################################

# Name: ggs_lag.ksh #

# Author: grigorianvlad@yahoo.com

# PURPOSE: TO MONITOR LAG OF GOLDEN GATE #

# NOTE: THIS SCRIPT CALLS /opt/app/gg/scripts/find_discards.ksh #

# ONLY FOR FOR EXT AND PMP PROCESS GROUP #

###########################################

export GGATE=/goldengate

alias gate='clear;cd $GGATE;./ggsci'

#!/bin/ksh

############################################

# Name: ggs_lag.ksh #

# Author: vladimir.grigorian@acs-in.com

# PURPOSE: TO MONITOR LAG OF GOLDEN GATE #

# NOTE: THIS SCRIPT CALLS /opt/app/gg/scripts/find_discards.ksh #

# ONLY FOR FOR EXT AND PMP PROCESS GROUP #

###########################################

export GGATE=/goldengate

alias gate='clear;cd $GGATE;./ggsci'

export PATH=/goldengate/target:/usr/sbin:/usr/lib/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/usr/lib:/goldengate/target:

LOGDIR=/goldengate/target/log

EMAILFile=/tmp/ggs_stats.log

today=`date '+%Y_%m_%d__%H_%M_%S'`;

BOX=$(uname -a | awk '{print $2}')

##########################################################################

# RUNNING SCRIPT TO GET GOLDEN GATE DISCARDS INFORMATION #

##########################################################################

#sed -i '1i These non-empty discard files newer than 24 hours found' $LOGDIR/ggs_discards.log

/goldengate/target/scripts/find_discards.ksh > $LOGDIR/ggs_discards.log

#printf "\n" >> $LOGDIR/ggs_discards.log

#printf "++++++++++++++++++++++++++++++\n" >> $LOGDIR/ggs_discards.log

#echo 'These non-empty discard files newer than 24 hours found' >> $LOGDIR/ggs_discards.log

##########################################################

## SENDING EMAIL IF ERRORS ARE IN LOGFILE ###

##########################################################

if find $LOGDIR/ggs_discards.log -type f -size +3c 2>/dev/null | grep -q .; then

printf "\n" >> $LOGDIR/ggs_discards.log

printf "++++++++++++++++++++++++++++++\n" >> $LOGDIR/ggs_discards.log

echo 'These non-empty discard files newer than 24 hours found in /goldengate/target/discard' >> $LOGDIR/ggs_discards.log

#echo "ERRORS FOUND"

mailx -s "Discards in Production DSS (Oracle=>Oracle) OS+ GoldenGate Found on $BOX at $(date)" grigorianvlad@yahoo.com < $LOGDIR/ggs_discards.log

else

echo "Discards parsed" >> /tmp/ggs_stats.log

fi

##########################################################

## Empty the discards log ###

##########################################################

truncate -s 0 $LOGDIR/ggs_discards.log

################# SCRIPT END ######################

2. An Example of a Discard Alert

Cron entry example: 58 23 * * * /goldengate/target/scripts/find_discards_run.ksh 2> /tmp/ggs_discards_run.log

END of DOCUMENT