Wednesday, November 2, 2011

ORA-12520: TNS:listener could not find available handler for requested type of server

Today I encountered a strange intermittent database connection problem when trying to connect to a database via SQL*Plus, the error I got is:
ORA-12520: TNS:listener could not find available handler for requested type of server

According to this Oracle note: Intermittent TNS-12516 or TNS-12519 Errors When Connecting Via Oracle Net [ID 240710.1] , it's likely that lack of server processes is the cause of the problem.  So I checked the system parameters settings of this database:
SQL> show parameters process;


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     150        


SQL> select * from v$resource_limit where resource_name in ('processes','sessions');


RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION                       LIMIT_VALUE
------------------------------ ------------------- --------------- ---------------------------------------- ----------------------------------------
processes                                       78             150        150                                      150
sessions                                        93             172        264                                      264


The process value is set to the default one by another DBA who created this database.  And also at the same time, there is an import running in the database, and it's using parallelism of 8. Do the settings of parallel related parameters have impact on generating this error? I don't know, I need to do more research about that.

SQL> show parameters parallel


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
fast_start_parallel_rollback         string      LOW
parallel_adaptive_multi_user         boolean     TRUE
parallel_automatic_tuning            boolean     FALSE
parallel_degree_limit                string      CPU
parallel_degree_policy               string      MANUAL
parallel_execution_message_size      integer     16384
parallel_force_local                 boolean     FALSE
parallel_instance_group              string
parallel_io_cap_enabled              boolean     FALSE
parallel_max_servers                 integer     135
parallel_min_percent                 integer     0


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_min_servers                 integer     0
parallel_min_time_threshold          string      AUTO
parallel_server                      boolean     FALSE
parallel_server_instances            integer     1
parallel_servers_target              integer     384
parallel_threads_per_cpu             integer     2
recovery_parallelism                 integer     0


Anyway, it seems the low value of "process" parameter a likely cause of this problem.







Labels: , ,

1 Comments:

At March 17, 2012 at 1:10 PM , Anonymous Anonymous said...

True comment, we faced the same issue in our Production... All app servers started and one of the App server giving the below error

Caused by: java.sql.SQLException: Listener refused the connection with the following error:
ORA-12520, TNS:listener could not find available handler for requested type of server

The Connection descriptor used by the client was:
(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xxx-vip1.xxx.ca)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=xxx-vip1.xxxx.ca)(PORT=1521))(LOAD_BALANCE=on)(FAILOVER=on)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=xxxPROD.WORLD)(FAILOVER_MODE=(TYPE=session)(METHOD=basic)(RETRIES=10)(DELAY=1))))

Process parameter increased from 300 to 400.
Current utilization was showing 267 in one node and 261 in another node. But start of the last app server wanted another 80+ processes to be started.. which was not adequate with the Process parameter having 300.
Increasing process to 400 and updating SPFILE in ASM and bouncing the db again and starting all App servers, issue got resolved

SQL> select * from gv$resource_limit where resource_name in ('processes','sessions');
INST_ID RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
---------- ------------------------------ ------------------- --------------- ---------- ----------
2 processes 267 26 400 400
2 sessions 288 289 640 640
1 processes 356 358 400 400
1 sessions 381 38 640 640


Robertson
Oracle SME
+91-9886321339

 

Post a Comment

Subscribe to Post Comments [Atom]

<< Home