TNS-12520 TNS-12519 连接错误处理

TNS-12520 TNS-12519 连接错误处理  2010-07-15 11:43:11

分类： Linux

TNS-12520 连接错误处理

今天巡检客户数据库时，发现有个taf自动offline,手工启动后，再过一段时间又自动offline
$ crs_stat -t
Name           Type           Target    State     Host        
------------------------------------------------------------
ora....B1.lsnr application    ONLINE    ONLINE    db1     
ora....db1.gsd application    ONLINE    ONLINE    db1     
ora....db1.ons application    ONLINE    ONLINE    db1     
ora....db1.vip application    ONLINE    ONLINE    db1     
ora....B2.lsnr application    ONLINE    ONLINE    db2     
ora....db2.gsd application    ONLINE    ONLINE    db2     
ora....db2.ons application    ONLINE    ONLINE    db2     
ora....db2.vip application    ONLINE    ONLINE    db2     
ora.gzdb.db  application    ONLINE    ONLINE    db2     
ora....c1.inst application    ONLINE    ONLINE    db1     
ora....c2.inst application    ONLINE    ONLINE    db2     
ora....dcdb.cs application    ONLINE    ONLINE    db2     
ora....dc1.srv application    ONLINE    OFFLINE               
ora....dc2.srv application    ONLINE    ONLINE    db2  

监听日志报如下错误：

13-JUL-2010 13:27:18 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)(SERVICE_NAME=gzdcdb)(FAILOVER_MODE=(type=select)(method=basic))) * (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.1)(PORT=61530)) * establish * db * 13520
TNS-12520: TNS:listener could not find available handler for requested type of server

客户端连接也不正常

$ sqlplus system/abcdefg@gzdc1

SQL*Plus: Release 10.1.0.4.0 - Production on Wed Jul 14 16:03:15 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

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

介入处理：

show parameter processes

processes    150                            ----默认值  150

 

select * from gv$resource_limit

   INST_ID RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION   LIMIT_VALUE
---------- ------------------------------ ------------------- --------------- -------------------- --------------------
         1 processes                                      149             150        150                  150
         1 sessions                                       152             156        170                  170
         1 enqueue_locks                                  175             481       2462                 2462
         1 enqueue_resources                              168             342        968            UNLIMITED
         1 ges_procs                                      149             149        151                  151
         1 ges_ress                                     48789           54428       4656            UNLIMITED
         1 ges_locks                                    58379           66558       6695            UNLIMITED
         1 ges_cache_ress                                1586            2304          0            UNLIMITED
         1 ges_reg_msgs                                   123            2560        730            UNLIMITED
         1 ges_big_msgs                                    53              99        730            UNLIMITED
         1 ges_rsv_msgs                                     0               0        300                  300

   INST_ID RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION   LIMIT_VALUE
---------- ------------------------------ ------------------- --------------- -------------------- --------------------
         1 gcs_resources                               523835          553818     631953               631953
         1 gcs_shadows                                 275118          281683     631953               631953
         1 dml_locks                                        2              50        748            UNLIMITED
         1 temporary_table_locks                            0               8  UNLIMITED            UNLIMITED
         1 transactions                                     2              10        187            UNLIMITED
         1 branches                                         0               0        187            UNLIMITED
         1 cmtcallbk                                        0               1        187            UNLIMITED
         1 sort_segment_locks                              10              14  UNLIMITED            UNLIMITED
         1 max_rollback_segments                           11              11        187                65535
         1 max_shared_servers                               1               1  UNLIMITED            UNLIMITED
         1 parallel_max_servers                             1               7        120                 3600

   INST_ID RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION   LIMIT_VALUE
---------- ------------------------------ ------------------- --------------- -------------------- --------------------
         2 processes                                       70             115        150                  150
         2 sessions                                        77             125        170                  170
         2 enqueue_locks                                  164             286       2462                 2462
         2 enqueue_resources                              166             249        968            UNLIMITED
         2 ges_procs                                       70             114        151                  151
         2 ges_ress                                     69809           80946       4656            UNLIMITED
         2 ges_locks                                    80635           90200       6695            UNLIMITED
         2 ges_cache_ress                                5042            5230          0            UNLIMITED
         2 ges_reg_msgs                                    89            3063        730            UNLIMITED
         2 ges_big_msgs                                    32             538        730            UNLIMITED
         2 ges_rsv_msgs                                     0               0        300                  300

   INST_ID RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_ALLOCATION   LIMIT_VALUE
---------- ------------------------------ ------------------- --------------- -------------------- --------------------
         2 gcs_resources                                66883          500751     500751               500751
         2 gcs_shadows                                 228595          500751     500751               500751
         2 dml_locks                                        2              71        748            UNLIMITED
         2 temporary_table_locks                            0               8  UNLIMITED            UNLIMITED
         2 transactions                                     7              21        187            UNLIMITED
         2 branches                                         0               0        187            UNLIMITED
         2 cmtcallbk                                        0               2        187            UNLIMITED
         2 sort_segment_locks                               4              17  UNLIMITED            UNLIMITED
         2 max_rollback_segments                           12              13        187                65535
         2 max_shared_servers                               1               1  UNLIMITED            UNLIMITED
         2 parallel_max_servers                             4               7        120                 3600

44 rows selected.

 

可以看到外面连接数已经达到设置的processes参数值了

通过查metalink文档ID为240710.1

Cause
By way of instance registration, PMON is responsible for updating the listener 
with information about a particular instance such as load and dispatcher 
information. Maximum load for dedicated connections is determined by the 
PROCESSES parameter. The frequency at which PMON provides SERVICE_UPDATE 
information varies according to the workload of the instance. The maximum 
interval between these service updates is 10 minutes.

The listener counts the number of connections it has established to the instance 
but does not immediately get information about connections that have terminated. 
Only when PMON updates the listener via SERVICE_UPDATE is the listener 
informed of current load. Since this can take as long as 10 minutes, there can be 
a difference between the current instance load according to the listener 
and the actual instance load.

When the listener believes the current number of connections has reached maximum 
load, it may set the state of the service handler for an instance to "blocked" 
and begin refusing incoming client connections with either of the following 
errors:

TNS-12516 TNS:listener could not find instance with matching protocol stack 
TNS-12519 TNS:no appropriate service handler found

Additionally, an ORA-12520 error may appear in the listener log.

The output of the LSNRCTL services command will likely show that the service handler is "blocked".

e.g. '"DEDICATED" established:1 refused:0 state:blocked'

Solution
Increase the pfile or spfile setting for PROCESSES

 

把参数加大后，重启数据库
SQL> alter system set processes=500 scope=spfile sid='*';

System altered.

SQL>

问题解决