Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

Thanks for the question.

Asked: February 26, 2020 - 10:14 am UTC

Last updated: February 28, 2020 - 10:15 am UTC

Version: 12.1.0.2.0

Viewed 10K+ times! This question is

You Asked

Hi Team,
Today around 03:35:36 2020, oracle database went into hung state and we were not able to login database with ORA-00018: maximum number of sessions exceeded .

This is RAC database running on 2 nodes. DBA restarted nodes one by one and later we were able to login.
Now to identify the cause of this issue, DBA is not able to trace which user sessions caused this issues not they are unable to get any ASH/AWR report for this.

Could you please assist if there is way to identity DB went down due to sessions threshold issue.

and Chris said...

There was too many sessions connected to the instance. It's not the fault of any one particular session - it's that the total is over the limit set by the SESSIONS parameter.

There are various steps you can take to stop this happening again:

- Increase the value for SESSIONS
- Use the resource manager/profiles to limit the activity for users and/or remove dead/inactive sessions
- Check the application to ensure it's not creating excessive numbers of sessions

Rating

(3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

and monitoring...

pete, February 27, 2020 - 11:41 am UTC

I would also recommend that you enable auditing for logins. This would allow you to look back later and confirm exactly how many sessions were logging in (and not out!) over time and identify any trends related to particular users or client systems to further aid troubleshooting.

February 27, 2020 - 1:28 pm UTC

Good suggestion.

How can I enable auditing

A reader, February 27, 2020 - 2:32 pm UTC

Hi,
Thanks for the reply.
Is there any way we can enable login auditing?
Please suggest the steps.

February 27, 2020 - 3:35 pm UTC

Assuming you have unified auditing enabled:

create audit policy conn_pol 
  actions logon, logoff;
  
audit policy conn_pol;

select * from UNIFIED_AUDIT_TRAIL 
where  action_name in ( 'LOGON', 'LOGOFF' );

Dead Connect Detection

lh, February 28, 2020 - 7:49 am UTC

Hi

One possibility is that there are dead connections consuming processess on the database server side.

sqlnet.expire_time=1

might help.

lh

February 28, 2020 - 10:15 am UTC

Yep, worth looking into.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database

Breadcrumb

Question and Answer