ORA-12519: TNS:no appropriate service handler found

ORA-12519: TNS: no appropriate service handler found

Cause: Number of session and process reached the MAX_UTILIZATION level when listener find 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.

Solution: Check the value of process parameter and increase the value of the parameter.


[grid@DB ~]$ lsnrctl services

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 24-SEP-2017 13:32:38

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
  Instance "+ASM", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:1158 refused:0 state:ready
         LOCAL SERVER
Service "SPFTLDB" has 1 instance(s).
  Instance "SPFTLDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "DEDICATED" established:73880 refused:0 state:blocked
         LOCAL SERVER
Service "SPFTLDBXDB" has 1 instance(s).
  Instance "SPFTLDB", status READY, has 1 handler(s) for this service...
    Handler(s):
      "D000" established:0 refused:0 current:0 max:1022 state:ready
         DISPATCHER <machine: DB.spftl.com, pid: 18815>
         (ADDRESS=(PROTOCOL=tcp)(HOST=db.spftl.com)(PORT=43078))
The command completed successfully

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
aq_tm_processes                      integer     1
db_writer_processes                  integer     3
gcs_server_processes                 integer     0
global_txn_processes                 integer     1
job_queue_processes                  integer     1000
log_archive_max_processes            integer     4
processes                            integer     150
SQL> ALTER SYSTEM SET processes=1000 SCOPE=SPFILE;

System altered.

SQL>

Bounce the database.