Re: processes not released, got ora-00020 error

"Gary YU" <gary.yu_at_istark.com> wrote in message news:5483713a.0111051336.3c9716db_at_posting.google.com...
> Hi experts,
>
> I got some problem with the oracle processes recently, all new
> connection request through TNS are rejected(ora-00020 error). I
> checked my init<sid>.ora file, the 'process' parameter was 200, so I
> doubled it to 400. But I soon ran into the same problem 2 days after.
>
> I checked v$process, there're 397 processes running, and I checked
> v$session, there're 40-50 sessions. It seemed that some process are
> not exit/released, I also checked in the Unix command line(HP UX
> 11.0), ' ps -ef|grep oracle', there are more than 400 process named
> 'oraclebvprod'(bvprod is my SID) like this:
>
> oracle 23784 1 0 20:58:51 ? 0:00 oraclebvprod
> (DESCRIPTION=(LOCAL =no)(ADDRESS=(PROTOCOL=BEQ))
>
> we are running BroadVision web-server and eGate(a middle ware) as
> oracle client.
>
> my question is: should the count in v$process matches the count in
> v$session, and what reason may cause the process keep growing. Also
> what are these 'oracle<sid>' processes, and is there a safe way to
> manully release these process without re-bounce the oracle server.
>
> Thanks a lot!

Broadvision, lol. Known to me as e-commerce software without using Oracle RI. I had many discussions with Broadvision consultants about this 'feature'

As to your question: unless you are using multithreaded server (which you are probably not as Broadvision has it own mechanisms for that, _no_ client process (ie browser) ever _directly_ connects to Oracle) the number of processes should match the number of sessions. You see the number of processes grow as the connection doesn't terminate with an explicit disconnect, they just leave the executable and that's it. As Oracle is fully two task, there is always a session being served by dedicated process, that are the oracle<sid> process entries you are seeing. You can kill the session with status inactive and last_call_et non-zero or high in v$session.
You could enable dead connection detection by including the line sqlnet.expire_time = 10
in sqlnet.ora, but my own experience is this doesn't work. As I in 'my' Broadvision production installation never saw such an excessive amount of defunct processes, one should ask whether the Broadvision software has been correctly configured.
But then again at the site I am referring to they didn't have a proper firewall license, so IIRC they couldn't even go beyond 50 connections because of firewall problems.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address