您好,登錄后才能下訂單哦!
今天有AP用戶反映,數(shù)據(jù)庫(kù)有時(shí)候連的上,有時(shí)候連不上,報(bào)如下的錯(cuò)誤:
ORA-12519: TNS:no appropriate service handler found
二、原因解析
oracle@C01TEST03:/home/oracle>oerr ORA 12519
12519, 00000, "TNS:no appropriate service handler found"
// *Cause: The listener could not find any available service handlers that
// are appropriate for the client connection.
// *Action: Run "lsnrctl services" to ensure that the instance(s) have
// registered with the listener, and are accepting connections.
第一個(gè)原因可能是Instance沒(méi)有注冊(cè)到Listener;
第二個(gè)原因可能是數(shù)據(jù)庫(kù)上當(dāng)前的連接數(shù)目已經(jīng)超過(guò)了它能夠處理的最大值。
三、解決方法
根據(jù)第二步,檢查兩個(gè)地方:
1、查看Instance是否注冊(cè)到Listener
oracle@C01TEST03:/home/oracle>lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 03-NOV-2017 15:29:45
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=C01TEST03)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 25-AUG-2017 13:37:06
Uptime 70 days 1 hr. 52 min. 38 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /oracle/product/11.2.0/network/admin/listener.ora
Listener Log File /oracle/product/diag/tnslsnr/C01TEST03/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=C01TEST03)(PORT=1521)))
Services Summary...
Service "MTH" has 1 instance(s).
Instance "MTH", status READY, has 1 handler(s) for this service...
確認(rèn)到"MTH" Instance已經(jīng)注冊(cè)到listener里了;
2、數(shù)據(jù)庫(kù)上當(dāng)前的連接數(shù)目已經(jīng)超過(guò)了它能夠處理的最大值。
(1)查看當(dāng)前連接數(shù)
SQL> select count(*) from v$process;
查看數(shù)據(jù)庫(kù)允許最大的連接數(shù)
SQL> show parameter processes;
(2)適當(dāng)加大processes的值
alter system set processes = 300 scope = spfile;
(3)重啟數(shù)據(jù)庫(kù)
(4)查看當(dāng)前有哪些用戶正在使用數(shù)據(jù)
SELECT osuser, a.username,cpu_time/executions/1000000||'s', sql_fulltext,machine
from v$session a, v$sqlarea b
where a.sql_address =b.address order by cpu_time/executions desc;
四、問(wèn)題總結(jié)
以上便是該問(wèn)題的思考及處理方向。處理方法以第二種,修改參數(shù)processes的方法為主。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。