Re: orasrv hangs once in a while

On Wed, 19 Jul 1995, Richard Scholz wrote:

> Hi folks
>
> I'm experiencing a strange behaviour of orasrv once in a while:
> Sometimes it just stops working. All clients which try to get a new connection
> to the database via sql*net v1 then hang to. This happens sometimes once or
> twice a day, sometimes not at all.
> After killing the orasrv, and restarting it afterwards, everything
> works fine, at least for some time.
>
> I'm experiencing this which all versions of oracle. It occurred
> with 7.0.12, 7.0.16, 7.1.3 and 7.1.4. Didn't try 7.1.6 yet.
>
> The problem occurs in a net with a SUN Server running Solaris 2.3 and 2.4, and
> Clients running WfW with Sunselect PCNFS and SQL*NET V1.

Are the clients running ODBC-based applications to connect through SQL*Net? One major reason for orasrv hangups that we have identified is from an ODBC-based application suffering a crash at the same time that it is attempting a connection to the server. For example, we had a third-party report tool that was suffering GPF's while attempting to connect to the oracle server. Each time this happened, the orasrv process would hang.

Now, as for solutions, Oracle offered none for being able to prevent the hang up. However, we did resolve how to deal with it. NOW, I CAN'T SAY THIS WILL WORK FOR OTHER FOLKS AS PLATFORMS, ETC VARY. We are a HP unix server environment with MS Windows clients.

Fixing a hung orasrv process and minimizing impact

1. When a client connects to orasrv, the main orasrv process (parent) spins off a child process.
2. The client and child process continue to talk for the duration of the session. From this point on, this connection does not need the "parent" orasrv process.
3. If the parent orasrv process hangs, then its children connections that were active at the moment of hang, continue as normal. HOWEVER, NEW CONNECTIONS are ignored.
4. We first confirm that the orasrv process is hung by attempted a TCPCTL STATUS. If the process never responds, its dead. WHAT WE THEN DO is kill the parent orasrv process, but leave the children processes alone. ON OUR CONFIGURATION, this works. Prior connections continue to function, even though the parent is dead.
5. We now start a new parent orasrv process by doing a TCPCTL START.

Sorry for all the disclaimers, but I have been informed that this does not work in all environments. For example, I have been told that in the VAX/VMS environment, all of the children have to be killed after the hung parent is killed. This is not the case for the HP environment we use.

Detecting a hung session.

Once we learned the above, we knew that we just needed to minimize the time that the orasrv process remained hung. What I then did was write a very small VB/ODBC app that simply sits as an smiley/frowney icon on my screen (I'm using it now). It does the following:

1. wakes up every five minutes
2. attempts to connect to oracle
3. with successful connect:
4. updates the icons information (number of successful connects, number of user's accessing oracle, number of minutes to next connection (which counts down)).
5. DISCONNECTS from oracle.
6. begins count down to next connection attempt.
7. with FAILURE to connect:
8. sets off an alarm and delivers various error messages.
9. waits for situation to be resolved.

When the alarm goes off or the error messages, appear, I access the server and do as I listed above.

Hope this helps!

Guerry A. Semones                 |  Phone: 919-269-1391 
guerry_at_iluvatar.tip.duke.edu      |  Pager: 919-737-8894
----------------------------------+---------------------
My thoughts are not my company's  |  All generalizations 
thoughts, saith the disclaimer.   |      are false.