TNS-12520 TNS-12519 連線錯誤處理
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
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>
問題解決
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/7199859/viewspace-668073/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- TNS-12520 TNS-12519 連線錯誤處理
- 遠端連線錯誤程式碼及處理
- SQL Server 連線常見錯誤及其處理方法SQLServer
- 前端的水平線，錯誤處理和除錯前端除錯
- 錯誤處理
- 兄弟連go教程（15）函式 - 錯誤處理Go函式
- PHP 錯誤處理PHP
- php錯誤處理PHP
- Go 錯誤處理Go
- Swift錯誤處理Swift
- Zabbix錯誤處理
- mysqldump錯誤處理MySql
- 寬頻連線錯誤678 寬頻連線錯誤691錯誤的解決辦法
- 錯誤處理：如何通過 error、deferred、panic 等處理錯誤？Error
- perl連線Oracle錯誤Oracle
- PHP錯誤處理和異常處理PHP
- go的錯誤處理Go
- Python錯誤處理Python
- 異常錯誤資訊處理
- PHP 核心特性 - 錯誤處理PHP
- 常用模組 PHP 錯誤處理PHP
- laravel9 錯誤處理Laravel
- 淺談前端錯誤處理前端
- Oracle異常錯誤處理Oracle
- ORACLE 異常錯誤處理Oracle
- 15-錯誤處理（Error）Error
- 學習Rust 錯誤處理Rust
- axios 的錯誤處理iOS
- Go語言之錯誤處理Go
- GOLANG錯誤處理最佳方案Golang
- Objective-C:錯誤處理Object
- javascript之處理Ajax錯誤JavaScript
- 搭建dataguard時，錯誤處理
- Oracle錯誤處理思路(一)Oracle
- COM的錯誤處理 (轉)
- 使用PHP錯誤處理 (轉)PHP
- openGauss 處理錯誤表
- Python錯誤處理和異常處理（二）Python