Skip to Main Content
  • Questions

  • DB down after "Resource temporarily unavailable" occurs

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Kitti.

Asked: January 21, 2016 - 5:16 am UTC

Last updated: January 21, 2016 - 8:58 am UTC

Version: 11.2.0.3

Viewed 1000+ times

You Asked

Hello Tom,

I have two databases in my linux server. I found in alert logs in two databases that the databases abnormally shutdown after these errors occur
ORA-27300: OS system dependent operation:fork failed with status: 11
ORA-27301: OS failure message: Resource temporarily unavailable
ORA-27302: failure occurred at: skgpspawn3

The root cause that i knew is OS processes exceed maximum number quota of oracle user.
But i would like to know which SQL Statements cause the a lot of processes in my system.


ps. This is configuration in my /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536


Thank you
K.lee

and Connor said...

Its not SQL, its more likely to be sessions.

Classic example would be a program not releasing connections, so

a- it connects
b- does it works
c- *thinks* that it has disconnected
d- goto A

and very quickly you spiral out of control.

A simple means to collect some data here would be to issue: AUDIT SESSION

and you'll get a row in the audit trail every time someone logs in and when they log off.

Hope this helps.

Rating

  (2 ratings)

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

Comments

Kitti Leelahasin, January 21, 2016 - 8:46 am UTC

Thank you Connor.
I would like to know historical information of the incident such as historical number of oracle processes on system, which sql or operations that made a lot of processes (including recursive).

Ps. I have AWR and ASH report covering that period.

Thank you and Best regards,
K.Lee
Connor McDonald
January 21, 2016 - 8:58 am UTC

Maybe see if DBA_HIST_SERVICE_STAT yields anything useful for you.

AWR/ASH I think are unlikely to give you the actual session information you are after to know exactly what was happening.

If you're lucky and all the connections did a lot of work - you might see them in ASH (v$Active_session_history or DBA_HIST_ACTIVE_SESS_HISTORY).



Kitti Leelahasin, January 21, 2016 - 8:57 am UTC

Thank you for your reply.
But I would like historical information of the incident such as number of processes belong to oracle user that running in that period, How to find SQL or operations that made a lot of processes (including recursive, parallel, background).

Ps. I have AWR and ASH report covering that period.

Thank you in advance.
Best regards,
K.lee
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.