Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Script to ping all db links

RE: Script to ping all db links

From: Mercadante, Thomas F <thomas.mercadante_at_labor.state.ny.us>
Date: Fri, 11 Feb 2005 08:33:21 -0500
Message-ID: <C9995D8C5E0DDA4A8FF9D68EE666CE0702A97337@exchsen0a1ma>


Patrice,

The following unix script checks a single database link for us. If the tnsping is successful, it runs the check_dmv procedure which performs a simple select using the db link to make sure that there is data in the db table on the remote machine. If there is no data, we turn access to the remote db off (using a local table that the applications check before they do anything).

If the test fails we update the same local table (again) to turn access to the remote database off.

This works great for us. We run this in a cron every minute during the normal working hours of the day (7AM-5PM).

Hope this helps.

Tom

SQLSCR=$DBA_TEMP/line_test.sql
STATUS=`$ORACLE_HOME/bin/tnsping dmvdb |grep OK` if [ $? = 0 ]; then

  echo set feedback off          >  $SQLSCR
  echo exec tcc_pkg.check_dmv\;  >> $SQLSCR
  echo exit                      >> $SQLSCR
else
  echo set feedback off          >  $SQLSCR
  echo exec tcc_pkg.not_found\;  >> $SQLSCR
  echo exit                      >> $SQLSCR
fi

  $ORACLE_HOME/bin/sqlplus -S $RICUSR/$RICPWD @$SQLSCR   rm $SQLSCR

-----Original Message-----

From: Boivin, Patrice J [mailto:BoivinP_at_mar.dfo-mpo.gc.ca] Sent: Thursday, February 10, 2005 3:28 PM To: oracle-l
Subject: Script to ping all db links

Does anyone have a script that tnspings all db links to produce a list of the invalid ones?

I did one in sqlplus, which hosts to the command line to run tnsping, but the output never appears in the spool file.

Patrice.
--

http://www.freelists.org/webpage/oracle-l
--

http://www.freelists.org/webpage/oracle-l Received on Fri Feb 11 2005 - 08:36:09 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US