ORA-12520 TNS: Listener count not find available handler for requested type of server error Solution

A vpc process is a one-to-one ing with the client process, which is generated in real time and disappears instantly. Put user session data and cursor status in PGA

A Shared Server process already exists. It can be divided into two parts: a dispatch process and a shared server process.
The dispatch process establishes a connection with the user process, puts user requests in the Request queue of the SGA, shares the process to retrieve requests for processing, and puts them in the SGA response queue after processing, the dispatch process extracts the processing result to the user process. A dispatch can be connected to 250 or 300 client processes. One request queue and multiple response queues. The user session data and cursor status are placed in SGA.

Possible causes and solutions for ORA-12520 errors
ORA-12520 listeners cannot find available handlers for the requested server type:
1. The database is a dedicated server, but the connection mode is set to shared in the tnsname. ora configuration. In this case, enable tnsname. ora,
Change (Server = shared) to (Server = dedicate)

2. The database is set to the shared mode, but the service configured by the parameter dispatchers = '(Protocol = TCP) (Service = orcl)' is different from the SERVICE_NAME specified in the local TNS, here, service = orcl indicates that when you connect to the database through the orcl service, dispatch to a shared server process.

If you configure a corresponding TNS, you can connect to the database in shared mode.

Orcls =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = PC-6753184) (Port = 1522 ))
)
(CONNECT_DATA =
(Server = shared)
(SERVICE_NAME = orcl)
)
)

Assume that there are other services in the database (you can check the status of lsnrdbms) as follows, and orclb is the service name. These services can be used in the TNS configuration, but it cannot be set to the shared mode connection. This means that some connections in the database are in dedicated mode and some are in shared mode. If you connect in shared mode, the service specified by SERVICE_NAME can only use the dynamically registered service name.

Services summary...
Service "orcia" has 1 instance (s ).
Instance "orcl", status ready, has 1 handler (s) for this service...
Service "orclb" has 1 instance (s ).
Instance "orcl", status ready, has 1 handler (s) for this service...

If a TNS is configured as follows:

Orlinoleic =
(Description =
(Address_list =
(Address = (Protocol = TCP) (host = PC-6753184) (Port = 1522 ))
)
(CONNECT_DATA =
(Server = shared)
(SERVICE_NAME = orcia)
)
)

In this case, because dispatchers = '(Protocol = TCP)
(Service = orcl) 'configuration is different from the SERVICE_NAME here, with sqlplus test/test @ orlinoleic connection will get a ORA-12520 error.

The solution is to modify SERVICE_NAME.
= Orcl, or modify dispatchers = '(Protocol = TCP)'. All connections are in shared mode. We recommend that you use the former.

(In this way, you can use the sharing mode for the TNS connection configured for a certain service, such as the olta type, to connect some services in the dedicated mode)

After connecting to the database, View

Select
* From v $ session where Sid in (select distinct (SID) from V $ mystat)
If the server value is dedicated, it indicates private mode and shared mode.

3) it is caused by insufficient process.

View current connections
Select count (*) from V $ process;

Set the maximum number of connections (default value: 150)
Select value from V $ parameter where name = 'processs ';

Modify the maximum number of connections
Alter system set processes = 300 scope = spfile

Restart

Select * from V $ shared_server view information about the sharing process, which is being executed and waiting for requests.

Select * from V $ dispatcher to view the information of the shared scheduler. If dispatchers = (dispatchers = quantity) is configured, you can see the number of data entries.

Select * from V $ Circuit
Check the session connected to the dispatcher, and the server process is serving it.