ORA-12519的错误和解决

曾经终败给现在 2023-06-13 08:52 1阅读 0赞

家中领导提了个问题，某系统和数据库交互的时候，提示ORA-12519，

![640?wx\_fmt=png][640_wx_fmt_png]

ORA-12519的解释就是找不到合适的服务句柄，当前注册到监听的实例无法接收连接请求，

SQL> !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.

Troubleshooting Guide TNS-12519 TNS-12516 ORA-12519 ORA-12516 (Doc ID 552765.1)介绍，12516/12519错误最常见的原因就是到达了PROCESSES/SESSIONS参数指定的最大值限制，PMON进程会会告诉监听器当前配置的限制，有空闲连接时，才会允许新的连接进来，

> One of the most common reasons for the TNS-12516 and/or TNS-12519 errors being reported is the configured maximum number of PROCESSES and/or SESSIONS limitation being reached. When this occurs, the service handlers for the TNS listener become "Blocked" and no new connections can be made. Once the TNS Listener receives an update from the PMON process associated with the Database instance telling the TNS Listener the thresholds are below the configured limit, and the database is now accepting connections connectivity resumes.
> 
> 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: ora-12519 or ora-12516

我们看下当前系统中的processes和sessions参数值，分别是150和247，

SQL> show parameter processes
    NAME                     TYPE    VALUE
    ------------------------------------ ----------- ------------------------------
    processes                 integer     150
    
    SQL> show parameter sessions
    NAME                     TYPE    VALUE
    ------------------------------------ ----------- ------------------------------
    sessions                 integer     247

为了验证，我们减少processes的值，因为scope=spfile，所以需要重启数据库，注意虽然设置了processes=10，但实际允许processes=40，相应地，sessions会被设置成新值，

SQL> alter system set processes=10 scope=spfile;
    System altered.
    
    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    
    SQL> startup;
    ORACLE instance started.
    Total System Global Area  521936896 bytes
    Fixed Size            2254824 bytes
    Variable Size          331352088 bytes
    Database Buffers      184549376 bytes
    Redo Buffers            3780608 bytes
    Database mounted.
    Database opened.
    
    SQL> show parameter processes
    NAME                     TYPE    VALUE
    ------------------------------------ ----------- ------------------------------
    processes                 integer     40
    
    SQL> show parameter sessions
    NAME                     TYPE    VALUE
    ------------------------------------ ----------- ------------------------------
    sessions                 integer     82

检索v$process和v$session视图，了解当前状态下，连接到数据库的进程总数，分别是23和20，

SQL> select count(*) from v$process;
      COUNT(*)
    ----------
        23
    
    SQL> select count(*) from v$session;
      COUNT(*)
    ----------
        20

通过打开不同的sqlplus窗口，让连接到数据库的进程数，增加到临近processes的值，此时还剩1个空闲，

SQL> select count(*) from v$process;
      COUNT(*)
    ----------
        39
    
    SQL> select count(*) from v$session;
      COUNT(*)
    ----------
        36

如果用Oracle NET的方式登陆数据库，则会提示ORA-12516，

[oracle@travelsky Desktop]$ sqlplus hr/oracle@test
    SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 12 13:57:44 2019
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    ERROR:
    ORA-12516: TNS:listener could not find available handler with matching protocol
    stack

如果用操作系统认证的方式登陆，则会提示ORA-00020，显式指出了processes的值是40，

[oracle@travelsky Desktop]$ sqlplus / as sysdba
    SQL*Plus: Release 11.2.0.4.0 Production on Tue Nov 12 13:57:53 2019
    Copyright (c) 1982, 2013, Oracle.  All rights reserved.
    ERROR:
    ORA-00020: maximum number of processes (40) exceeded

从提示消息看，ORA-00020比ORA-12516更具可读性。但无论是什么，都和processes/sessions的限制，有些关系。

因此这个问题，解决方案有很多，例如重启，可以清除多余的连接，可以从应用层减少多余的连接，或者增加系统processes/sessions参数值。

[640_wx_fmt_png]: https://ss.csdn.net/p?https://mmbiz.qpic.cn/mmbiz_png/a1q64ic86Pamicwbg1aYdmhFTjZAkpqk9yNFL5RjrCeibsSkjiahsarOuicZKq5l9rGqbO08J0dECaic6O83ic1p44xWg/640?wx_fmt=png