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@xxxxxxxxx>:
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@xxxxxxxxxxxx> 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@xxxxxxxxx>
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@xxxxxxxxxxxx> 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
