Re: Profile limit concurrent sessions for a user not limiting as expected

All what Jonathan explained,
plus: if you are using SCAN listeners (which is common in current RAC environments) you need to understand the load balancing mechanism of SCAN: it always try to route the connection to the node which "had recently the least load". The load value will be communicated by LREG process to the SCAN listeners - normally about every 60 seconds. [disclaimer: I have no proof for this interval]. This leads to some funny effects: Within a minute all your sessions are routed to one node. it reaches the SESSIONS_PER_USER limit and the application gets a proper error. At the same time (due to the additional active sessions) the load increases which is communicated to the SCAN listeners. New connections are now routed to another node which has ample sessions until it also reaches SESSIONS_PER_USER. listener.log and alert.log doesn't tell you about it. It's a huge fun to debug.
I'm not sure if *ORA-02391* is even audited, but still I'd recommend audit on LOGON - if not for security reasons then for operational analysis.

hth,
 Martin

Am Do., 18. Jan. 2024 um 11:14 Uhr schrieb Jonathan Lewis < jlewisoracle_at_gmail.com>:

>
> The RAC thing makes sense. If your logon attempt is checking v$session
> then every instance can get the limit; and the fact that you use "alter
> system" rather than alter database to set resource_limit to TRUE hints at
> the idea it's an instance-level behaviour; and the pattern you reported
> suggests that the first failure was trying to connect to the same instance
> as the first two, then connected to the second instance when the
> application retried..
>
>
> Be careful with setting sessions_per_user to 1. You may find that when you
> (or Oracle internally) query a gv$ view the query crashes with ora-12850
> (Could not allocate slaves on all specified instances) as a side effect of
> how RAC starts up PX processes to execute gv$ (and many x$) result sets.
> This could also have an impact on your use of datapump (as in, it will
> crash).
>
> Of course you're okay so long as the users with that profile aren't
> allowed to query gv$, run datapump, or run queries or DML in parallel.
>
> Regards
> Jonathan Lewis
>
>
> On Wed, 17 Jan 2024 at 21:14, Jack van Zanen <jack_at_vanzanen.com> wrote:
>
>> I think it is because the limit is per node and I have a 2 node RAC so
>> setting it to 2 actually limits the user to 4
>> setting this to 1 limits them to 2.
>>
>>
>> Need to do some more testing, but that is what I think is happening
>>
>>
>>
>> Jack van Zanen
>>
>>
>> -------------------------
>> This e-mail and any attachments may contain confidential material for the
>> sole use of the intended recipient. If you are not the intended recipient,
>> please be aware that any disclosure, copying, distribution or use of this
>> e-mail or any attachment is prohibited. If you have received this e-mail in
>> error, please contact the sender and delete all copies.
>> Thank you for your cooperation
>>
>>
>> On Wed, Jan 17, 2024 at 8:40 PM Jonathan Lewis <jlewisoracle_at_gmail.com>
>> wrote:
>>
>>> It certainly sounds wrong. Have you (if you're allowed) checked what
>>> happens if you try to get 3 SQL*Plus (or similar) sessions with direct
>>> connections. I'm wondering if it's something to do with connection pooling
>>> (are the other two sessions still alive, or has one of them - for example -
>>> been flagged in v$session as "sniped"), or maybe something to do with the
>>> way that the application code handles exceptions and the connect mechanism
>>> it uses. Are the three sessions (v$sessions) associated with three
>>> difference processes (v$process), or has one process managed to spawn
>>> multiple sessions - maybe something like that could bypass the profile
>>> limit.
>>>
>>> Regards
>>> Jonathan Lewis
>>>
>>>
>>>
>>>
>>> On Wed, 17 Jan 2024 at 02:58, Jack van Zanen <jack_at_vanzanen.com> wrote:
>>>
>>>> Hi
>>>>
>>>>
>>>> We have an application and want to limit the number of sessions a user
>>>> can open so are experimenting with the profile and setting "Concurrent
>>>> sessions(Per user)" set to 2
>>>>
>>>> When I start the application I can see one session, so far so good. I
>>>> can start another one and that works fine too..However on the third one is
>>>> where things do not behave as I expected. I correctly get the pop up that
>>>> the sessions have exceeded the value for concurrent max sessions. But when
>>>> I click "ok" I get an application logon error popup. When I click that it
>>>> starts a third session and logs into the application just fine and leaves
>>>> me with 3 working sessions in the database for my username. I was hoping to
>>>> get just the error popup and that would be it.
>>>>
>>>> Can anyone explain under what conditions does oracle allow me to have 3
>>>> sessions when my max is set to 3?
>>>>
>>>> database 12.2
>>>>
>>>> Regards
>>>>
>>>>
>>>> Jack van Zanen
>>>>
>>>>
>>>> -------------------------
>>>> This e-mail and any attachments may contain confidential material for
>>>> the sole use of the intended recipient. If you are not the intended
>>>> recipient, please be aware that any disclosure, copying, distribution or
>>>> use of this e-mail or any attachment is prohibited. If you have received
>>>> this e-mail in error, please contact the sender and delete all copies.
>>>> Thank you for your cooperation
>>>>
>>>

-- 
Martin Berger                Oracle ♠
martin.a.berger_at_gmail.com _at_martinberx <https://twitter.com/martinberx>
^∆x      http://berxblog.blogspot.com

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