Analyze and fix Core Advanced Replication problems

This interactive script is intended to analzye, report and fix problems associated with Oracle Core Advanced Replication.

Corrected 10 January 2002

This script -- created in the Bourne shell on HP-UX 11.0 -- is intended to analzye, report and fix problems associated with Core Advanced Replication. It is an interactive script that can fix and report the status of advanced replication on a one-way basis. The list of replication directions you can fix is dynamically updated depending on how many databases you have in your repgroup.

It fixes:

  • broken push jobs
  • Jobs_running: It will kill the process of jobs running more than 6 hours
  • database links: It will repair broken database links

It reports on:

  • deferred errors
  • deferred transactions
  • listener errors
  • database status
                
#!/bin/sh

# ----------------------------------------------------------------------------
#
# Creator:	Kevin Nicholson
#		Symbol Technologies
#
# Name:		fix_rep.sh
#
# Purpose:   	Analzye, Report and Fix problems associated with Core Advanded
#	    	Replication
#		Fixes problems associated with DBA_JOBS, DBA_DB_LINKS and 
#		DBA_JOBS_RUNNING
#		It informs the user about problems with DEFERRCOUNT and 
#		DBA_ADMINDEFTRAN
#
# Usage:   
#
# Input		Sources .profile		
# Parameters:	Interactive interface, requires input from users.
#
# Comments:
#
# How to use: search and replace user_name with lower case username
#    	      search and replace USER_NAME with upper case username
#	      search and replace password with password
#
# REVISION HISTORY
#
#@(#) Rev 1.0 - Finished script (KN/JK)
#
# ----------------------------------------------------------------------------
#
# ----------------------------------------------------------------------------
#
#               SET VARIABLES AND ASK USER TO VERIFY ENVIRONMENT
#
# ----------------------------------------------------------------------------

# ----------------------------------------------------------------------------
# Source .profile
#

. $HOME/.profile

# . profile contains
# export ORACLE_OWNER=db
# export ORACLE_HOME=/db/PRD/apps/oracle/8.0.5
# export ORACLE_BASE=/db/PRD/apps
# export ORACLE_SID=TESTPRD1
# export SHLIB_PATH=/db/PRD/apps/oracle/8.0.5/lib
# export ALERTDIR=$ORACLE_HOME/rdbms/log
# export TRACEDIR=$ORACLE_BASE/admin/$ORACLE_SID/udump
# export PFILEDIR=$ORACLE_HOME/dbs
# export ARCHIVEDIR=/db/PRD/archive.log/archive
# export EXPORTDIR=/db/PRD/archive.log/export
# export CRONDIR=/db/PRD/apps/cronjobs
# export TNS_ADMIN=/listener/apps/oracle/product/8.0.6/network/admin

# ----------------------------------------------------------------------------
# Initializing Variables
#

export HOSTNAME=`hostname`
export DATE=`date`

export TMP=/tmp
export TOOLS=/secure/backups
export DBA_LIB=$TOOLS/lib
export LOGGER_FILE=$CRONDIR/rep_fix.log

export DEFTRAN=$CRONDIR/rep_deftran.tmp
export DEFERR=$CRONDIR/rep_deferr.tmp
export JOB=$CRONDIR/rep_defjob.tmp
export JOBRUN=$CRONDIR/rep_jobsrun.tmp
export LINKS=$CRONDIR/rep_links.tmp
export SOURCEDB_FILE=$CRONDIR/rep_sourcedb_file.tmp
export DESTDB_FILE=$CRONDIR/rep_destdb_file.tmp
export KILL_FILE=$CRONDIR/rep_kill.tmp
export BATCH_FILE=$CRONDIR/rep_batch.tmp
export INTERVAL_FILE=$CRONDIR/rep_interval.tmp
export DEST_DB_UPDOWN_SPOOL=$CRONDIR/rep_testdest_updown.tmp
export SOURCE_DB_UPDOWN_SPOOL=$CRONDIR/rep_testsource_updown.tmp

# ----------------------------------------------------------------------------
# Clear the screen
#

clear

# ----------------------------------------------------------------------------
# Listing environment variables for user verification
#

echo ""
echo HOSTNAME = $HOSTNAME
echo DATE = $DATE
echo ORACLE_SID = $ORACLE_SID
echo ORACLE_HOME = $ORACLE_HOME
echo ""

# ----------------------------------------------------------------------------
# User prompt for y/n input
#

echo "Are you sure this is the correct environment [y/n]? \c" 
read ANSWER

# ----------------------------------------------------------------------------
# Set variable GO_ON with the user input
#

case "$ANSWER" in
	y | Y | yes | YES ) GO_ON=TRUE ;;
	n | N | no | NO ) GO_ON=FALSE ;;
	* )  { echo "Wrong Key, Please Run Again"; exit; } ;;
esac

# ---------------------------------------------------------------------------
# Exit out if the user gives a 'NO' answer
#

if [ $GO_ON = "FALSE" ]
then
	echo
	echo "Please check and/or fix your environment before proceeding!"
	exit
fi

# ----------------------------------------------------------------------------
# Implement this section after you get the library functions setup
#
# Runs test_db_updown script to test the status of the database
# It returns:
#
# 0=Db is Down
# 1=Db is in Nomount mode
# 2=Db is in Mount mode
# 3=Db is Open

#$DBA_LIB/test_db_updown

#if [ "$?" -ne 3 ]
#then

# echo "$ORACLE_SID needs to be open in order for it to export, Please open DB" 
# exit

#fi

# --------------------------------------------------------------------------
# Connect to the Source DB and do a simple select statment to test if the DB
# is open or not
#

(
svrmgrl << EOF

        connect user_name/password;

        spool $SOURCE_DB_UPDOWN_SPOOL;

        select * from V\$DATABASE;

        spool off;

        exit;
EOF
) > /dev/null 2>&1

# --------------------------------------------------------------------------
# Grep for ORA- messages in the spool file from the simple query we just ran.
# If no errors exist, then the DB is open and we proceed.
# If there are errors the program exits.  We cannot run this tool with the 
# source DB being down.
#

export TEST_DB_UPDOWN=`grep ORA- $SOURCE_DB_UPDOWN_SPOOL`
if [ "$TEST_DB_UPDOWN" ]
then
       echo The Source Database must be started!!
	exit 
else
        export SOURCE_DB_IS=UP
fi

# ---------------------------------------------------------------------------
# The SOURCE query, retrieves data about the Replication Source Databases 
# which will be used to build a menu later in the script
#
# The DBFIX query, retrieves data about the Replication Destination Databases
# which will be used to build a menu later in the script
#

(
svrmgrl << EOF6

	connect internal;

	spool $SOURCEDB_FILE;

	select 'SOURCE~' || GLOBAL_NAME || '~' "DATABASE NAME" 
	from global_name;

	spool off;

	spool $DESTDB_FILE;

	select 'DBFIX~' || dblink || '~' "SITE TO FIX" from dba_repsites where dblink ! = (select * from global_name);

	spool off;

	exit;

EOF6
) > /dev/null 2>&1

# -----------------------------------------------------------------------------
# Set the variable $SOURCEDB_FILE_TEMP
# Take the data that is in a file and put it in the $SOURCEDB variable
# If there is no data in the file, the script will exit
#

export SOURCEDB_FILE_TEMP=$CRONDIR/rep_sourcedb_file1.tmp
grep SOURCE $SOURCEDB_FILE > $SOURCEDB_FILE_TEMP
if [ -s $SOURCEDB_FILE_TEMP ]
then
	
	while read line
	do
		export SOURCEDB=`echo $line |cut -f2 -d "~"`

	done < $SOURCEDB_FILE_TEMP
else
	echo "The DB is down or not setup correctly"
	exit
fi

# -----------------------------------------------------------------------------
# Set the variable $DESTDB_FILE_TEMP and the counter $n
# Take the data that is in a file and put it 
# in the DBFIX1, DBFIX2, DBFIX3 .... variable
# If there is no data in the file, the script will exit
# 

export DESTDB_FILE_TEMP=$CRONDIR/rep_destdb_file1.tmp
export n=0
grep DBFIX $DESTDB_FILE > $DESTDB_FILE_TEMP

if [ -s $DESTDB_FILE_TEMP ]
then
	while read line
	do
		n=`expr $n + 1`
		export DBFIX${n}=`echo $line |cut -f2 -d "~"`

	done < $DESTDB_FILE_TEMP

else
	echo "Replication is not setup correctly, Contact a DBA"
	exit
fi

# ----------------------------------------------------------------------------
# Clear the screen
#

clear

# ----------------------------------------------------------------------------
# This is the top of the selection menu
#

echo '           This tool only fixes Replication in one direction'
echo ' Run this tool on the other DB to fix Replication in the other direction'
echo ""
echo ""

# ----------------------------------------------------------------------------
# This sets the selection menu options
#

export d=0
while [ $n -gt 0 ]
do
	n=`expr $n - 1`
	d=`expr $d + 1`
	eval echo "\${d}')'  \$SOURCEDB '-->' \$DBFIX$d"
done

export d=`expr $d + 1`
echo "$d) Quit"
echo ""

# ----------------------------------------------------------------------------
# User prompt for 1,2,3 ... input
#

echo "Please select the Replication Group to be Fixed [1,2,3..] \c"
read ANSWER

# ----------------------------------------------------------------------------
# If they choose a letter or the Quit option, the script will exit
#

case "$ANSWER" in
	[a-z] | [A-Z] ) { echo "Please choose a number, Run Again"; exit; } ;;
	${d} ) exit ;;
	* ) echo "" ;;

esac

# ----------------------------------------------------------------------------
# If they choose a number greater than the highest possible number
# the script will exit
#

if [ "$ANSWER" -gt "$d" ]
then
	echo "That is not a valid selection, Run Again"
	exit
fi

# ----------------------------------------------------------------------------
# This sets $n, for readability 
#

export n=${ANSWER}

# ----------------------------------------------------------------------------
# This sets whatever Destination database they choose as, $DBFIX
#

case "$ANSWER" in
	"$n" ) { eval DBFIX="\$DBFIX${n}"; export DBFIX; } ;;
esac

# ----------------------------------------------------------------------------
# Clear the screen
#

clear

# ----------------------------------------------------------------------------
# Shows the user what they have selected
#

echo "You Have Chosen ${n}) $SOURCEDB --> $DBFIX"
echo ""

# ------------------------------------------------------------------------
#
#                                 ANALZYE
#
# ------------------------------------------------------------------------

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

# ---------------------------------------------------------------------------
# This query is preformed to check if the Destination DB is up.
# If checks this by using the DBLINKS.
# It is intended to report the status back to the user, in the reporting stage.
#

echo Making sure DEST. DB is up ... 
(
svrmgrl << EOF
 
        connect user_name/password;

        spool $DEST_DB_UPDOWN_SPOOL;

        select * from V\$DATABASE@$DBFIX;

        spool off;

        exit;
EOF
) > /dev/null 2>&1

# ---------------------------------------------------------------------------
# The DEFTRANS query, retrieves the Deferred Transaction count for the 
# source DB to the $DBFIX
#

echo "Extracting data from DBA_ADMINDEFTRAN ...."

(
svrmgrl << EOF1

	connect internal;


	spool $DEFTRAN;

	select 'DEFTRANS~' || to_char(trn_cnt) || '~' || dst_lnk "DBA_ADMINDEFTRAN" 
	from sys.dba_admindeftran 
	where dst_lnk = '$DBFIX';

	spool off;
	

	exit;
EOF1
) >/dev/null 2>&1

# ----------------------------------------------------------------------------
# The DEFERRCOUNT query retrieves the Deferred Error Count for the Source DB.
#

echo 'Extracting data from DEFERRCOUNT ....'

(
svrmgrl << EOF2

	connect internal;


	spool $DEFERR;

	select 'DEFERRCOUNT~' || to_char(errcount) || '~' || destination "ERRCOUNT" 
	from deferrcount 
	where destination ='$DBFIX';

	spool off;


	exit;

EOF2
) > /dev/null 2>&1

# -----------------------------------------------------------------------------
#  The DEFJOB query, retrieves data about the Job#, Broken status, and what the 
#  job is. It uses Union and RPAD to format the length and quantity of data
#  The push job uses $DBFIX to make sure it is selecting the one the user wants
#
#  The BATCH query, finds the batch count if one exist.  It uses a complex query
#  to find the string 'transaction_count=>' and moves over 19 characters.
#  The is captures the number given until it reaches the comma.
#  If no transaction count is found 0 is used.

#  The INTERVAL query, finds the push job, then it finds the difference
#  between the last_date and next_date the job is supposed to run and 
#  to converts that into minutes.
#

echo 'Extracting data from DBA_JOBS ....'

(
svrmgrl << EOF3

	connect internal;

	
	spool $JOB;

	select 'DEFJOB~' || job || '~' || decode(broken, 'Y', 'YES', 'N', 'NO') || '~' || RPAD(substr(what, 1), 53) "JOBS" 
	from dba_jobs 
	where instr(what, 'repcat', 1) > 0

	union

	select 'DEFJOB~' || job || '~' || decode(broken, 'Y', 'YES', 'N', 'NO') || '~' || RPAD(substr(what, 40), 53) "JOBS" 
	from dba_jobs 
	where upper(instr(what, '$DBFIX', 1)) > 0

	union

	select 'DEFJOB~' || job || '~' || decode(broken, 'Y', 'YES', 'N', 'NO') || '~' || RPAD(substr(what, 40), 24) "JOBS" 
	from dba_jobs 
	where instr(what, 'purge', 1) > 0;

	spool off;

	spool $BATCH_FILE;

	select 'BATCH~' || decode(instr(what,'transaction_count',1), 0, 0,
		substr(what,
	        instr(what,'transaction_count',1) + 19,
		instr(what, ',', instr(what,'transaction_count',1)) - (instr(what,'transaction_count',1) + 19)
		     )
	             ) "Blah"
	from dba_jobs 
	where instr(what,'push',1) > 0;

	spool off;

	spool $INTERVAL_FILE

	select  'INTERVAL~' || ABS((LAST_DATE - NEXT_DATE ))  * 1440 "BLAH" 
	from dba_jobs
	where upper(instr(what, '$DBFIX', 1)) > 0;

	spool off;


	exit;
EOF3
) > /dev/null 2>&1
	

# ------------------------------------------------------------------------------
# The KILL query, retrieves the UNIX PID's number of any push job that has been
# running for over 6 hours
#

echo 'Extracting data from DBA_JOBS_RUNNING ....'

(
svrmgrl << EOF4

	connect internal;


	spool $KILL_FILE;

	select 'KILL~' || p.spid "PID" from  V\$session s,V\$process p 
	where s.paddr=p.addr and s.sid in (select SID "DBA_JOBS_RUNNING" 
	from dba_jobs_running 
	where sysdate-6/24 >= this_date 
	and JOB IN (select job from dba_jobs where upper(instr(what, '$DBFIX', 1)) > 0));
	spool off;


	exit;
EOF4
) > /dev/null 2>&1

# ------------------------------------------------------------------------------
# The LINKS, query retrieves data about the public and private Database links
# where the DB_LINK matchs the variable $DBFIX
#

echo 'Extracting data from DBA_DB_LINKS ....'

(
svrmgrl << EOF5

	connect internal;

	
	spool $LINKS;

	select 'LINKS~' || OWNER || '~' || DB_LINK "DBA_DB_LINK" 
	from dba_db_links 
	where DB_LINK ='$DBFIX';

	spool off;
	

	exit;
EOF5
) > /dev/null 2>&1


# --------------------------------------------------------------------------
#
#                        PARSE FILES INTO VARIABLES
#
# --------------------------------------------------------------------------

# --------------------------------------------------------------------------
# Grep for ORA- errors in the spool file where we tried to do a simple select
# statement on the Destination DB.
# If errors exist the DB is not open.
# If errors do not exist the DB is not open.
# UP or DOWN is put into the variable $DEST_DB_IS 
#

export TEST_DB_UPDOWN=`grep ORA- $DEST_DB_UPDOWN_SPOOL`
if [ "$TEST_DB_UPDOWN" ]
then
	export DEST_DB_IS=DOWN
else
	export DEST_DB_IS=UP
fi

# --------------------------------------------------------------------------
# Cut the .world off the variable $DBFIX and $SOURCEDB and put field 1 
# into $DBFIX_CUT and $SOURCEDB_CUT respectively
#

export DBFIX_CUT=`echo $DBFIX | cut -f1 -d "."`
export SOURCEDB_CUT=`echo $SOURCEDB | cut -f1 -d "."`


# --------------------------------------------------------------------------
# Put Data from the DEFTRANS query (DBA_ADMINDEFTRAN) into variables
#
# Search the $DEFTRAN file for the string DEFTRANS and put it into the
# variable $TRANS, if the variable $TRANS exist cut the fields into the 
# appropriate variables
#

export TRAN=`grep DEFTRANS "$DEFTRAN"` 
if [ "$TRAN" != "" ]
then
	export TRN_CNT=`echo $TRAN |cut -f2 -d "~"`
	export DST_LNK=`echo $TRAN |cut -f3 -d "~"`
else
	echo "Replication it not setup correctly, Contact DBA" 
	exit
fi

# ----------------------------------------------------------------------------
# Put Data from the DEF_JOB query ($DBA_JOBS) into variables
#
# Set the $JOB_TEMP variable, set $n as a counter.
# Search the file $JOB for DEFJOB and output to $JOB_TEMP, if $JOB_TEMP
# exist, the file is read one line at a time through a loop and the 
# appropriate varables are filled

export JOB_TEMP=$CRONDIR/rep_defjob1.tmp
export n=0
grep DEFJOB $JOB > $JOB_TEMP
if [ -s $JOB_TEMP ]
then
	while read line
	do
		n=`expr $n + 1`
		export JOBNUM${n}=`echo $line |cut -f2 -d "~"`
		export BROKEN${n}=`echo $line |cut -f3 -d "~"`
		export WHAT${n}="`echo $line |cut -f4 -d "~"`"
	done < $JOB_TEMP
else
	echo "Replication is not set up correctly, Contact DBA"
	exit
fi


# --------------------------------------------------------------------------
# Set the $JOB_COUNTER variable to the amount of times the last while statemnt
# looped.  That is how many jobs exist.
# It then sets JOBERROR to 0, just in case there are no JOBERRORS, this is
# so it doesn't error out when I use the variable later.
# If their are broken jobs it counts them up and puts the number in the 
# variable $JOBERROR
#

export JOB_COUNTER=${n}
export JOBERROR=0
while [ $n -ne 0 ]
do
	export TEMP=`eval echo "\\$BROKEN${n}"`
	export n=`expr $n - 1`
	if [ "$TEMP" = YES ]
	then
		export JOBERROR=`expr $JOBERROR + 1`
	fi
done


# ---------------------------------------------------------------------------
# Put Data from the DEFERRCOUNT query (DEFERRCOUNT) into variables
#
# Set the $DEFERR_TEMP variable, set $n as a counter.
# Search the file $DEFERR for DEFERRCOUNT and output to $DEFERR_TEMP, if 
# $DEFERR_TEMP exist, the file is read one line at a time through a loop
# and the appropriate varables are filled

export DEFERR_TEMP=$CRONDIR/rep_deferr1.tmp
export n=0
grep DEFERRCOUNT $DEFERR > $DEFERR_TEMP

if [ -s $DEFERR_TEMP ]
then
	read line < $DEFERR_TEMP
	export ERRCOUNT=`echo $line |cut -f2 -d "~"`
	export DESTINATION=`echo $line |cut -f3 -d "~"`

else
	export ERRCOUNT=0
fi


# ----------------------------------------------------------------------------
# Put Data from the PUBLIC and USER_NAME query (DBA_DB_LINKS) into variables
#
# Search the $LINKS file for the string PUBLIC and USER_NAME and put the data
# into the variable $PUBLIC and $PRIVATE respectively, if the variables $PUBLIC
# or $PRIVATE exist cut the field into the appropriate variables
# Then add the PRIVATE and PUBLIC link errors to see how many you have

export n=0
export PUBLIC=`grep PUBLIC "$LINKS"`
export PRIVATE=`grep USER_NAME "$LINKS"`
if [ "$PUBLIC" != "" ]
then
	export OWNER1=`echo $PUBLIC |cut -f2 -d "~"`
	export DB_LINK1=`echo $PUBLIC |cut -f3 -d "~"`
	export LINK_ERRORS_PUBLIC=0
else
	export LINK_ERRORS_PUBLIC=1
fi

if [ "$PRIVATE" != "" ]
then
	export OWNER2=`echo $PRIVATE |cut -f2 -d "~"`
	export DB_LINK2=`echo $PRIVATE |cut -f3 -d "~"`
	export LINK_ERRORS_PRIVATE=0
else

	export LINK_ERRORS_PRIVATE=1
fi

export LINK_ERRORS=`expr $LINK_ERRORS_PRIVATE + $LINK_ERRORS_PUBLIC`

# ------------------------------------------------------------------------
# Put Data from the $KILL query (DBA_JOBS_RUNNING) into variables
#
# Set the $KILL_FILE_TEMP variable, set $n as a counter.
# Search the file $KILL_TEMP for KILL and output to $KILL_FILE_TEMP, if 
# $KILL_FILE_TEMP exist, the file is read one line at a time through a loop
# and the appropriate varables are filled
# 

export KILL_FILE_TEMP=$CRONDIR/rep_kill1.tmp
export n=0
grep KILL $KILL_FILE > $KILL_FILE_TEMP

if [ -s $KILL_FILE_TEMP ]
then
	while read line
	do
		n=`expr $n + 1`
		export KILL${n}=`echo $line |cut -f2 -d "~"`
	done < $KILL_FILE_TEMP
fi

# ---------------------------------------------------------------------------
# Set $KILL_COUNTER to be used in the fixing stage
# Set JOBFRZ to be used in the reporting (menu) stage
#

export KILL_COUNTER=${n}
export JOBFRZ=${n}


# --------------------------------------------------------------------------
# Calculation for DEFFERED TRANSACTIONS
#
# Put Data from the BATCH query (DBA_JOBS) into variables 
#

export BATCH1=`grep BATCH $BATCH_FILE`
if [ "$BATCH1" != "" ]
then
	export BATCH=`echo $BATCH1 |cut -f2 -d "~"`
else
	echo "Batch Query is not setup correctly, Contact programmer" 
	exit
fi

# --------------------------------------------------------------------------
# Calculation for DEFFERED TRANSACTIONS
#
# Put Data from the INTERVAL query (DBA_JOBS) into variables 
#


export INTERVAL=`grep INTERVAL $INTERVAL_FILE`
if [ "$INTERVAL" != "" ]
then
	export INTERVAL=`echo $INTERVAL |cut -f2 -d "~"`
else
	echo "Interval Query is not setup correctly, Contact programmer" 
	exit
fi

# --------------------------------------------------------------------------
# Calculation for DEFFERED TRANSACTIONS
#
# If the DB doesn't have the transaction_count setup then it will only divide
# the $TRN_CNT/ 116 and set that variable.  If is does have it, it will do 
# all the other calculations in the if statement
#

export TIME_TO_FIX=`expr $TRN_CNT / 116`

if [ "$BATCH" -ne 0 ]
then
	export TIME_TO_FIX2=`expr $TRN_CNT / $BATCH`
	export TIME_TO_FIX3=`echo "$TIME_TO_FIX2 * $INTERVAL" | bc`
	export TIME_TO_FIX4=`echo "$TIME_TO_FIX + $TIME_TO_FIX3" | bc`
	export TIME_TO_FIX=`awk "BEGIN { print int($TIME_TO_FIX4) }"`
fi

# --------------------------------------------------------------------------
# Calculation for DEFFERED TRANSACTIONS
#
# This just sets what $TIME_TO_FIX will say when it is used with echo
#

if [ $TIME_TO_FIX -eq 0 ]
then
	export TIME_TO_FIX2="Less than 1 minute"
else
	export TIME_TO_FIX2="$TIME_TO_FIX minute(s)"
fi

# --------------------------------------------------------------------------
#     Listener check
#
 
export LISTENER_ERROR=0 
export LISTENER_SOURCE=0
export LISTENER_DEST=0
export LISTENER_SOURCE=`lsnrctl status | grep $SOURCEDB_CUT`
 
if [ -z $LISTENER_SOURCE ]
then
        export LISTENER_ERROR=1
fi

export LISTENER_DEST=`tnsping $DBFIX | grep OK`

if [ -z $LISTENER_DEST ]
then
        export LISTENER_ERROR=`expr $LISTENER_ERROR + 1`
fi

# -----------------------------------------------------------------------------
# Clean up all the temporary files that were generated
# and files that should be there
#

rm rep_*.tmp
rm $LOGGER_FILE 2>&1 /dev/null

# ----------------------------------------------------------------------
#
#                                REPORTING
#
# ----------------------------------------------------------------------

clear

# -------------------------------------------------------------------------
# This is the reporting menu that gives you all your results
# The if statment is used, so if there are any errors, the person running this
# script would contact a DBA.  If no errors exist, I don't want the user to 
# see that message, so it is only shown if errors exist.
#

(
echo ---------------------------------------------------------------------------
echo ""
echo '                             Replication Status                         '
echo ""
echo ---------------------------------------------------------------------------
echo ""
echo "1) JOBS: There were $JOBERROR errors"
echo ""
echo "2) JOBS_RUNNING: There were $JOBFRZ Jobs running for longer than 6 hours"
echo ""
echo "3) DBLINKS: There were $LINK_ERRORS errors"
echo ""
echo "4) DEFERRED ERRORS: There were $ERRCOUNT errors"

if [ $ERRCOUNT -gt 0 ]
then
	echo "                    Contact DBA to fix these errors"
fi

echo ""
echo "5) DEFERRED TRANSACTIONS: There were $TRN_CNT transactions"
echo "                          Estimated transfer time: $TIME_TO_FIX2 "
echo ""
echo "6) LISTENER: There were $LISTENER_ERROR errors"

if [ -z $LISTENER_SOURCE ]
then
        echo "Reload or start the listener on $SOURCEDB"
fi
if [ -z $LISTENER_DEST ]
then
        echo "Reload or start the listener on $DBFIX"
fi

echo ""
echo "7) DB STATUS: Destination Database - $DBFIX is $DEST_DB_IS"
if [ "$DEST_DB_IS" = "DOWN" ]
then
	echo "The DEST. DB needs to be started.  Please start $DBFIX"
	echo "Or the DBLINK to this DB is no setup, please run this tool again"
fi
echo "              Source Database - $SOURCEDB is $SOURCE_DB_IS"
echo ""
) | tee $LOGGER_FILE
echo "8) The log file for script can be found at $LOGGER_FILE"
echo ""

# ----------------------------------------------------------------------------
# User prompt for y/n input
#

echo "Would you like to correct these errors [y/n]? \c" 
read ANSWER

# ----------------------------------------------------------------------------
# Set variable GO_ON with the user input
#

case "$ANSWER" in
	y | Y | yes | YES ) GO_ON=TRUE ;;
	n | N | no | NO ) GO_ON=FALSE ;;
	* )  { echo "Wrong Key, Please Run Again"; exit; } ;;
esac

# ---------------------------------------------------------------------------
# Exit out if the user gives a 'NO' answer
#

if [ $GO_ON = "FALSE" ]
then
	echo
	echo "GOODBYE!"
	exit
fi

# --------------------------------------------------------------------------
#
#                                  FIXES
#
# --------------------------------------------------------------------------

# --------------------------------------------------------------------------
# If the public link has errors or doesn't exist, it is droped and created
#

if [ $LINK_ERRORS_PUBLIC -eq 1 ]
then

echo Correcting Public Database Link ...
(
svrmgrl << EOF

	connect user_name/password;

	drop public database link $DBFIX;
	create public database link $DBFIX using $DBFIX_CUT;

	exit;
EOF
) > /dev/null 2>&1
fi


# --------------------------------------------------------------------------
# If the private link has errors or doesn't exist, it is droped and created
#

if [ $LINK_ERRORS_PRIVATE -eq 1 ]
then

echo Correcting Private Database Link ...
(
svrmgrl << EOF

	connect user_name/password;

	drop database link $DBFIX;
	create database link $DBFIX
	connect to user_name identified by password;
	
	exit;
EOF
) > /dev/null 2>&1
fi

# --------------------------------------------------------------------------
# Change $JOB_COUNTER to $n for readability
#

export n=$JOB_COUNTER

# --------------------------------------------------------------------------
# Depending on how many jobs there are (3 for us), this code will loop that
# many times.  It then puts the current array variables into temporary
# variables that can be used in the code. (eval command does not work well 
# in if statements, and would not work in SQL).
# The code this determines if the job is broken, if it is it fixes that job
# number.  Then it decreases the counter $n
#

while [ $n -ne 0 ]
do
	export BROKEN=`eval echo "\\$BROKEN${n}"`
	export JOBNUM=`eval echo "\\$JOBNUM${n}"`
	export WHAT=`eval echo "\\$WHAT${n}"`

	if [ $BROKEN = YES ]
	then
	echo Now Fixing the $WHAT Job
	(
	svrmgrl <<- EOF

		connect user_name/password;

		execute DBMS_JOB.BROKEN($JOBNUM, FALSE);

		exit;
	EOF
	) > /dev/null 2>&1
	fi
	export n=`expr $n - 1`
done




# --------------------------------------------------------------------------
# Change $KILL_COUNTER to $n for readability
#

export n=$KILL_COUNTER

# --------------------------------------------------------------------------
#
# For as many jobs that have been running longer than 6 hours, It then puts 
# the current array variables into temporary variables that can be used in 
# the code. (eval command does not work well in if statements, and would 
# not work in SQL).
# It then uses kill -9 to kill the PID of the run away job
#

while [ $n -ne 0 ]
do
	export KILL=`eval echo "\\$KILL${n}"`

	kill -9 $KILL

	export n=`expr $n - 1`
done


# -----------------------------------------------------------------------------
# Clean up all the temporary files that were generated
#

rm rep_*.tmp > /dev/null 2>&1

For More Information

  • What do you think about this tip? E-mail the Editor at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

This was first published in December 2001
This Content Component encountered an error

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close