ORA-12519引出的問題

看到這篇文章提到一個ORA-12519的問題處理：

oerr的解釋：

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.

文章上的做法是查看了lsnrctl service，instance登入，狀態是ready。

MOS的240710.1說明了這個問題，指出：

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.

採用執行個體註冊的方法，PMON進程負責更新監聽的資訊(負載、分發等)。最大負載由PROCESSES參數決定。PMON提供服務更新的頻率依據執行個體負載的不同有所區別。這些服務更新之間的最大間隔是10分鐘。

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.

監聽會計算已經串連到此執行個體的connection數，但不會立即知道終止進程的資訊。只有當PMON通過SERVICE_UPDATE更新監聽的時候，監聽才知道當前的負載。既然間隔有10分鐘，那就存在監聽當前知道的負載與實際負載的差異。

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'

當監聽認為當前串連數已經到達最大負載，就將每執行個體的service handler狀態設定為“blocked”，開始拒絕新進來的用戶端串連請求，報錯：

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

SPFILE 錯誤導致資料庫無法啟動(ORA-01565)

ORA-01172、ORA-01151錯誤處理

ORA-00600 [2662]錯誤解決

ORA-01078 和 LRM-00109 報錯解決方案

ORA-00471 處理方法筆記

1 2 下一頁