AW: db link hangs

Hi!

We have the same problem using database links between two 8.1.6 instances on NT.
The frequency it happens ( from 2-3 times a day to weekly ) seems to depend on the amount of data affected on the remote site. As we use DBMS_JOB for regularly starting our tasks we encounter no max processes overflow, but the bad thing is, that the local session keeps waiting forever for a response from the remote partner session, thereby preventing the next scheduled job to start.

This was quite embarrassing, because we're using the dblink to load a huge amount of data into a data warehouse, and the hangs occurred mostly during the weekends, when nobody was in.

Our DBAs contacted Oracle, who told us, they could neither reproduce this strange behaviour, nor provide a solution.

Our quick and dirty approach uses a "watchdog" ( a shell script using SQL*PLUS and PL/SQL ) started periodically by the cron scheduler on a Linux machine. ( Sure, you on MS platforms you could use the AT command. Even a special Oracle job could be used for this, but we wanted to avoid the risk of getting the "watchdog" procedures/packages
invalid / paralyzing our little guard. The Linux machine has got an uninterruptible
power supply too and
<LINUX HYPE> keeps running and running and running stable </LINUX HYPE> )

Using v$session ,v$sesstat and v$statname we collect and compare each job's individual execution count statistics. If a statistic value remains unchanged ( indicating a "hang" in our case) over a certain period ( loop with delay using DBMS_LOCK.sleep) , we use the hanging job's SID and SERIAL# to execute an ALTER SYSTEM KILL SESSION. To ensure that no other (e.g.: interactive idle ) session will be killed, we tagged the jobs with a special string ( e.g. '@@@watchdog@@@' ) using the Oracle supplied package DBMS_APPLICATION_INFO procedures to get and set the tags for those "watched" sessions.

So eventually after a timeout period, the hanging jobs get killed, thereby enabling the next scheduled job to be started.

In case someone is interested in getting the script, just reply and I'll post it.

HTH
  Andreas
> ----------
> Von: blair_at_pjm.com[SMTP:blair_at_pjm.com]
> Gesendet: Mittwoch, 18. April 2001 14:45
> An: Multiple recipients of list ORACLE-L
> Betreff: db link hangs
>
> AIX 4.3
> RDBMS 8.1.6.x
>
> We have a problem using a db link. The listener-side server port (1521)
> is
> hanging. This happens about once a week - and ends up crashing the far
> database
> because queries through the link just hang and don't return - then we die
> with a
> max processes error.
>
> Has anyone else had any problems with this? How did you solve it? Can I
> (should I) get the Oracle listener to listen on more than one port? What
> are
> some other ways to solve the problem?
>
> PS: I can connect and query the table when logged in locally. Other
> connections (not using 1521) work to the server. netstat (and telnet to
> 1521)
> shows only connections thru 1521 are a problem.
>
> thanks,
>
> ..tom
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author:
> INET: blair_at_pjm.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Haunschmidt Andreas VASL/FAS
  INET: Andreas.Haunschmidt_at_voest.co.at

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).