ORA-00020: maximum number of processes: cannot connect as sysdba?!

Database reach max process or sessions, son no new login is available, first your sysadmin cant  access too for solve the problem seting new process value, is a production  enviroment so we dont want to kill the instance as a first step to solve the problem .

In linux will be easy make room for enter killing one session but in windows
OS we have a little problem. In windows we cant see session in threads, all are in one process oracle.exe, so no solution? only restarting instance? no.

One easy way to solve the issue, in windows in services.msc you can stop
OracleDBConsole[SID] give you the chance of conect without reboot instance service.

Imagine on windows we cant enter on databsase and service not work for restart instance, we cant  kill SMON as in linux,PMON realizes that SMON is missing and aborts the database.

Beyond oracle 10g oracle introduced a preliminary connection, to bypass the creation of a new session in cases where the database is hung and not accepting traditional sqlplus connections, this type of conection database allow sysadmin to shutdown abort the database, but take care and first dump some traces
set oracle_sid=YOUR_ORA_SID

sqlplus -prelim sys/oracle as sysdba

or

sqlplus /nolog
@> set _prelim on
@> conn sys/oracle as sysdba

We can’t query regular tables nor even V$ views, because we aren’t really logged on! Because of the prelim option we do not have all the structures for query execution set up.

How to dump traces:

sqlplus system/mypass -prelim /nolog

oradebug setmypid
oradebug hanganalyze 12
oradebug dump systemstate 10
oradebug tracefile_name C:\oracle\admin\orcl\udump\orcl_ora_5598.trc

We can now check this dump to take note of  details of the problem:
os id: 789
process id: 27, oracle@orcl (TNS V1-V3)
session id: 77
session serial #: 56789

killing  that session, will solve the problem.

windows: orakill

linux: kill -9

 

Advertisements

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s