Re: AW: db link hangs

I don'k like comment without trace files. But, I know a bug. Workaround is:

• don't use tns alias in dblink definition. Create dblink with "(DESCRIPTION = ...".

Haunschmidt Andreas VASL/FAS wrote:

> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Danisment Gazi Unal (Unal Bilisim)
  INET: danisment.unal_at_unal-bilisim.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).