Tip

Analyze and fix Core Advanced Replication problems

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

    Requires Free Membership to View

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.