A couple of months ago one of my customers had a failed data ware house report. There was a ORA-12592 (TNS) error message generated.
I turned out not to be the only TNS error. During a couple of weeks similar TNS-errors were generated. Not only the ORA-12592 error but also ORA-12514 and ORA-12571 errors.
We did some extensive sqlnet tracing but we didn’t find the cause there.
Finally the cause turned out to be a very simple and stupid one: the maximum number of processes was reached…. But strangely there was no ora-00020 error generated and found in the alert.log. I should expect such an error in the alert.log when this happens. But it seems that Oracle is not always doing that… (?)
Oracle has a parameter called processes. By default this is set on 150 during creation of the database. You can of course set another value during database creation. If you set this parameter on 200 then if there are more than 200 processes then a new connection can not be made with the database and the user gets some sort of TNS error like mentioned above.
How can you find out that this is the case? You can find out using the following query:
SQL> select * from v$resource_limit where resource_name = ‘processes’;
If you get results like these:
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
—————————— ——————- ————— ———- ———-
processes 194 200 200 200
then you will get will probably TNS errors.
You can see that the max_utilization is 200 which is the same as the limit_value and the processes parameter. This means you have reached at least one time the maximum number of processes. If also the current_utilization is the same as the limit_value or nearby the limit_value then new users will get TNS errors during login. To solve this you should set the processes parameter on a higher value.
You can do that by the following command:
alter system set processes=300 scope=spfile;
and then restart the database.
The processes parameter is not a dynamic parameter so you are not able to change this parameter without a restart of the database.
So next time when I get TNS errors I will take a look in the v$resource_limit view first, before starting with extensive sqlnet tracing…
