ArcSDE - Oracle Processes Issue

[User35489]

Dear Admins,

When a user initiates a connection through ArcGIS Desktop application. It creates a process in Oracle RDBMS. as user disconnects or exits the application, the Oracle process must quit i beleive, but it is remaining back in Oracle RDBMS as Orphaned process leading to  hang the system as at a point it is reaching maximum number of processes allowed.

Please help me out with your great ideas and experiences.

Environment: ORACLE RDBMS 11g, ArcSDE 9.3, RHEL 5, TCPKEEPALIVE=TRUE

Thanks in advance
Abdullah

[VinceAngelo]

If you are not using 9.3.1 SP2, you should first install that.

Are you using Direct Connect or an application server?

If ArcGIS disconnects and exits, and Oracle doesn't exit, that sounds like an
Oracle misconfiguration issue, not anything related to ArcGIS.  You should
probably speak with Tech Support about it anyway.

- V

[EmadAl-Mousa]

This issue is "known" for ArcSDE 9.3 release, i have personally faced it and i remember there was a pl/sql script that cleans ArcSDE orphaned sessions in Oracle. i don't remember specifically which ArcSDE "service pack" have fixed this issue. what is your current service pack applied ?

you can check this link: http://support.esri.com/en/knowledgebase/techarticles/detail/34258

whatever script you want to do, you need to base it on the following:

compare your PROCESS_INFORMATION SDE system table (which holds ONLY the current active connections) with the current OS connections, if the OS connections are more then this is an indication that you have orphaned processes.

since you are using linux server you can use "grep" command to check your current "gsrvr" processes (assuming you are using application server architecture).

[User35489 · Accepted solution]

Thanks all for excellent support. The issue behind the scene was related to oracle user's expired credentials. It was continiously trying to connect through a service leading to  large number of process creation in Oracle.
As there was no successful connection, no information entry found in Process_Information table.
Resetting the password worked out and processes got back to normal.

-Abdullah Saleh