Re: Oracle orphan sessions after server to Windows 2003R2

On Nov 14, 2007 5:20 PM, Reardon, Bruce (RTABBAY) < Bruce.Reardon_at_riotinto.com> wrote:

> We have 8.1.7.4 databases running on Windows 2003R2 SP2 Standard
> Edition.
> We previously had these same databases running on Windows 2000 SP4 but
> have now moved to new servers.
>
> Our clients are a mix of Forms 4.5 (on XP SP2 + Citrix), VB6 using
> Oracle 8.1.7.4 / Win XP SP2, ODBC via 9.2.0.1 client and .Net clients
> via 9.2.0.1 client (last 2 being on same Windows 2003R2 SP2 servers).
>
> We are now experiencing a problem where we have orphaned Oracle sessions
> (ie threads) on the server with no corresponding process on the clients.
>
> This is occurring even when the client shuts the program down cleanly.
> The SPID is present in v$process for the orphaned sessions.
> v$session.process lists the client process ID, but that process does not
> exist on the client PC.
>
> This did not happen when the server was Windows 2000 - nothing else has
> changed, we are on the exact same patch version on the server and
> clients.
> We can't reproduce on demand - we did make occur for 1 session out of
> thousands of Forms sessions started via a program.
> It is not happening for our client programs that use a 9.2.0.5 client.
>
> The problem is that this causes the memory allocated to the oracle.exe
> process to increase until we reach the 2 GB limit (for Windows standard
> 32 bit), as occurred the first time we found this problem, and then no
> new connections are accepted. This actually occurred when oracle.exe
> was reported as using 1.46 GB of virtual memory (eg by pslist) - see
> Metalink 46001.1 for explanation of this.
>
> DCD is designed to detect and delete these (you can refer Metalink notes
> 165659.1 / 151972.1), but we have had serious problems with this
> previously so I'm loath to turn it on without significant testing.
>
> For now, I've created a script to identify orphaned sessions (based on
> login age and session inactivity - don't mind that it will detect
> non-orphans that haven't been used for a long time) and creating the
> orakill commands to delete these sessions.
>
> Has anyone else experienced these orphan sessions?
> I'm open to ideas for determining the root cause - note that an upgrade
> of Oracle clients & server is intended to happen next year.
>
> Thanks,
> Bruce Reardon
> mailto:bruce.reardon_at_riotinto.com
>
>
> NOTICE
> This e-mail and any attachments are private and confidential and may
> contain privileged information. If you are not an authorised recipient, the
> copying or distribution of this e-mail and any attachments is prohibited and
> you must not read, print or act in reliance on this e-mail or attachments.
> This notice should not be removed.
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>

Bruce,

I've also seen this on MS Win32 with 10g R1 and the 10.1.0.4 patchset. It was apparently fixed in the 10.2.0.3 patchset.

Whacking the zombies won't recover the lost memory. Perhaps a pass with orastack.exe against the oracle.exe and lsnrctl.exebinaries may get you a little more headroom.

I didn't get to "root cause" as a fix was available. DCD was also supposed to be working in 10.2.0.3 for MS Win32 but I didn't have to enable it.

hth.

Paul

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