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

Dig Deeper

PRO+

Content

Find more PRO+ content and other member only offers, here.

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