[Case] ORA-12519: TNS:no appropriate service handler found

I. problem phenomenon

Today, some AP users reported that sometimes the database is connected, sometimes it cannot be connected, and the following error is reported:

ORA-12519: TNS:no appropriate service handler found

Second, the reason analysis

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.

The first reason may be that Instance is not registered with Listener

The second reason may be that the current number of connections on the database has exceeded the maximum it can handle.

III. Solutions

According to step 2, check two places:

1. Check whether Instance is registered with 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...

Confirm that the "MTH" Instance has been registered with listener

2. The current number of connections on the database has exceeded the maximum it can handle.

(1) View the current number of connections

SQL > select count (*) from v$process

View the maximum number of connections allowed in the database

SQL > show parameter processes

(2) increase the value of processes appropriately.

Alter system set processes = 300scope = spfile

(3) restart the database

(4) check which users are currently using data.

SELECT osuser, a.userame.cputated timestamp executions1000000000 | |'s encoding, sql_fulltext,machine |

From v$session a, v$sqlarea b

Where a.sql_address = b.address order by cpu_time/executions desc

IV. Summary of problems

The above is the direction of thinking and dealing with the problem. The second method is to modify the parameter processes.