Sunday, March 15, 2015

Error : ORA-00020: maximum number of processes (%s) exceeded

Error : ORA-00020: maximum number of processes (%s) exceeded

Cause
Maximum number of processes is specified in the initiatlization parameter PROCESSES is reached, no more requests are processed. User will get above error. New connection will not establish.

Solution

Some of the Rogue “zombie” connections  to Oracle that are idle (not working). 

To fix this,
If you are already connected with sys user,
use the ALTER SYSTEM KILL command. 

Once you reach maximum number of processes, you cannot connect to database with sys user also unless you already connected to your database. In this scenario you need to kill some processes at the OS level.

On Unix/Linux
$ kill -9

To fix the this issue permanent, increase the processes parameter value in init.ora file.

SQL> alter system set processes= scope=spfile;
SQL>shutdown;
SQl>startup;


Some FAQs,

Q. Why ORA-00020  occur and what should be the ideal value?
Ans. Maximum number of processes is specified in the initiatlization parameter PROCESSES is reached, no more requests are processed. User will get above error. New connection will not establish.


Q. Is backup jobs need separate process I.e RMAN or import/export?
Ans. Yes, RMAN and import/export are connecting to oracle hence new process is assigned to those connections.
It is recommended that such backup jobs (i.e. RMAN or others) should run on non-business hours. So that required process can available.

Q. How to identify how many processes need to be set with process parameter ?
Ans. Please check the Current used and Max used number of PROCESSES from the view gv$resource_limit and decide for the number of PROCESSES accordingly.

Use the following SQL query:

select RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION from gv$resource_limit where RESOURCE_NAME = 'processes';

Increase the size accordingly.

Or you can calculate like this also,
Processes = Oracle background process (20) + your user sessions (number of shared servers/dedicated servers) + number of job processes/aq processes you want to permit + parallel execution servers if any.


Q. Can one change process parameter run time ?
Ans. No, process parameter is initialization parameter hence it cannot be change run time. It requires database restart.

Q. Steps to Change process parameter?
Ans.
Step 1: Login with sys user
Step 2: run below command
            sql> alter system set process=2000 scope=spfile;
            sql> shutdown immediate;
            sql> startup

Here I am assuming you are using SPFILE. If not then you need to modify process parameter in PFILE manually and need to start database with PFILE.



Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert

Monday, March 9, 2015

Dead Connection in Oracle, ORA-00020

Dead Connection in Oracle


Reason 1

There is an application running and connecting to the database . The connection against the database is failing.
A likely cause is that the application tries a connect using an incorrect username and password.

Failed connections from an application are creating dead processes on the database server.

This might lead to large number of process generated on the server side which would eventually cause
the error ORA-00020 for any new connections.

Further analysis you can see the many processes in the view V$PROCESS may exist without having a related session in the view V$SESSION, so that V$SESSION has much less entries compared to V$PROCESS.

At the  O/S level,  there are numerous processes being generated with a LOCAL=NO description.

Reason 2


When the application is not used for a significant amount of time, the firewall setting between database & application server closes physical connection between application & database.

This will invalidate the connection in connection pool.


Reason 3

These are previously valid connections with the database but the connection between the client and server processes has terminated abnormally.
Examples of a dead connection:
A user reboots/turns-off their machine without logging off or disconnecting from the database.
A network problem prevents communication between the client and the server.


What happens if above situation arise,

This will become major problem as stated below,
     * The database exhausts PROCESSES and gives ORA-20 maximum number of processes exceeded
     * The OS can become exhausted due to the unneeded resources consumed by such oracle processes

SYMPTOMS are,
     * The database view V$PROCESS will have no corresponding V$SESSION entry
ñ  An OS process / thread still exists for the SNIPED session

Immediate solution or Workaround

The solutions to this scenario can are to cleanup the OS processes ... after which the V$PROCESS entries should be removed automatically


Dead Connection Detection (DCD)
Dead Connection Detection (DCD) is a feature of SQL*Net 2.1 and later
DCD detects when a partner in a SQL*Net V2 client/server
or server/server connection has terminated unexpectedly, and flags the dead session
so PMON can release the resources associated with it.
How DCD works,

When DCD is enabled, Net8 (server-side) sends a packet to the client. If the client is active, the packet is discarded. If the client has terminated, the server will receive an error and Net8 (server-side) will end that session.

DCD is well explained on Oracle Metalink Note 151972.1


Solution
Enable Resource Limits & DCD.

See my Note and FAQs how to enable resource Limit.
DCD can be implemented by adding SQLNET.EXPIRE_TIME = to the sqlnet.ora file.

As per Oracle, It is strongly recommended that both DCD and Resource Limits with Profiles be implemented in order to clean up resources at both the database and OS level


FAQs

Q. Why dead connection happens?
Ans. Explained three scenarios above.

Q. What happen when we kill those inactive/dead connections?
Ans. Once kill, process in the database go away. PMON will release all the resources.

Here one thing need to understand that, The blocked session in all cases has to wait for the resources to be freed. If you alter system kill the session, they will be released after the session rolls back.
If the session died an unexpected death, it'll be after pmon does the same.

Q. How to kill session manually if situation arise like ora-00020 ?
Ans. Below are some methods you can use for some immediate remedy,

ALTER SYSTEM DISCONNECT SESSION ',' IMMEDIATE
OR
On Unix/Linux at OS level kill -9
OR
orakill  


Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert

TAG:- Dead Connection in Oracle, Dead Connection Detection (DCD), ora-00020

Sunday, March 1, 2015

Automatic Disconnect Ideal Session

How to automatic disconnect Ideal sessions or INACTIVE Sessions ?


FAQs.

What are Inactive session ?
These are sessions that remain connected to the database with a status in v$session of INACTIVE.

For example,
    - A user starts a program/session, then leaves it running and idle for an extended period of time.

Q. How to overcome this situation?
Ans. Follow below steps to resolve the issue.

Step 1: Enable the feature
Set RESOURCE_LIMIT = TRUE in the database startup parameter file (spfile or pfile)
Example:
- alter system set resource_limit = true scope=spfile;

Step 2: Create the profile for monitoring idle time:
- create profile profile_name limit idle_time no_of_minutes;
Example:
- create profile idletime limit idle_time 10;

Step 3. Attach the profile to desired user:
- alter user user_name profile profile_name;
Example:
-        alter user scott profile idletime;
-         
Above example shows that if user session have been inactive for greater than no_of_minutes i.e. 10 min in example, to be disconnected from the database.

Q. What happens if transactions are uncommitted?
Ans. Any uncommitted transactions will be rolled back.

Q. Will user get any notification or error once it disconnected?
Ans. Yes, Next time when user enters any command, user will receive and ORA-02396 exceeded maximum idle time, please connect again.


Thanks & Regards,
Chandan Tanwani
Oracle Performance Tuning Certified Expert




TAG :  ORA-02396 , inactive session , ideal session