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.
posted by linyi @ 4:20 PM
1 Comments
1 Comments:
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