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
-- --Original Message-- -- From: Boivin, Patrice J [mailto:BoivinP@(protected)] 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.
