ORA-12516: TNS: The Listener cannot find the available handler that meets the protocol stack requirements

Author: skate
Time: 2009/03/10

 

ORA-12516: TNS: The Listener cannot find the available handler that meets the protocol stack requirements

 

 

Today's test colleagues found that the error reported ora-12516, based on this analysis, generally there are two reasons, one is the number of sessions is not enough
Another cause is the unstable connection between the client and the server. My current server is Oracle10g R2, and
The client is oracle11i. As a result, when the number of connections is large, the ora-12516 error will be reported.

 

Solution: 1. The first reason is to modify the connection limit.
SQL> alter system set processes = 300 #### change the value

Then reboot the database to make it take effect

2. For the second reason, my personal solution is to change dynamic registration of TNS to static registration (my current error is solved in this way)
Write global_name and sid_name to linster. Ora File

 

 

Note:

 

 

1. What is registration?

Registration is to register the database as a service to the listener. The client does not need to know the Database Name and Instance name.
The provided service name can be applied to connect to the database. The service name may be the same or different from the instance name.

 

When the database server is started, the database server registers the corresponding service with the listener (whenever a database is started, the default
Register two messages to the listener: The instance and service corresponding to the database server .)

 

As a result, there is a listener (listener) between the database server and the client. In the listener, the corresponding database is recorded.
Service name (a database may have multiple service names). When the client needs to connect to the database, you only need to provide the service name to create a customer
The connection between the user and the server.

 

Ii. Static Registration

Static registration is to read the configuration of the listener. ora file when the instance starts, and register the instance and service to the listener. Whenever a database is started,
By default, two messages are registered to the listener: The instance and service corresponding to the database server.

During static registration, global_dbname in listener. ora provides the service name externally, and sid_name in listener. ora provides the registered Instance name.

When static registration is adopted, the content in listener. ora is as follows:

Sid_list_listener =
(Sid_list =
(Sid_desc =
(Sid_name = plsextproc)
(ORACLE_HOME = D: oracleproduct10.2.0db _ 1)
(Program = EXTPROC)
)
(Sid_desc =
(Global_dbname = orcl)
(ORACLE_HOME = D: oracleproduct10.2.0db _ 1)
(Sid_name = orcl)
)
(Sid_desc =
(Global_dbname = orcl1)
(ORACLE_HOME = D: oracleproduct10.2.0db _ 1)
(Sid_name = orcl)
)
)

This file indicates that the database is a single instance and the Instance name is orcl. It provides two external services: orcl and orcl1.

 

3. Dynamic Registration

Dynamic Registration is performed by the pmon process when the instance is started according to the instance_name and service_names parameters in init. ora.
The service dynamically registers to listener.

 

First, specify the instance_name and service_names values in init. ora. Use show parameter servi under sqlplus
Ce_names and show parameter instance_name can be used to view the values of these two parameters.

 

The instance value registered to the listener is obtained from the instance_name parameter in the init. ora file. If this parameter is not set, it will take the ini
The value of db_name in the T. ora file.

 

The service value registered to the listener is obtained from the service_names parameter in the init. ora file. If this parameter is not set, the database concatenates
You can use the values of db_name and db_domain In the NIT. ora file to register yourself. If you choose to provide the service_names value, you can use the fully qualified
Name (such as orcl.oracle.com) or abbreviated name (such as orcl ). If you select the abbreviated name and set the db_domain parameter, register
The service in the listener is the combination of SERVICE_NAME and db_domain values. For example, the following settings will cause the service orcl.oracle.com to be registered to the listener:

Db_domain = oracle.com

Service_names = orcl;

When dynamic registration is adopted, the content in listener. ora is as follows:

Sid_list_listener =
(Sid_list =
(Sid_desc =
(Sid_name = plsextproc)
(ORACLE_HOME = D: oracleproduct10.2.0db _ 1)
(Program = EXTPROC)
)
)

You can specify multiple service values in the service_names parameter, which are separated by commas (,). This is useful for sharing server configurations.

 

By default, dynamic registration is only registered to the default listener (the name is listener, the port is 1521, and the protocol is TCP). To register with a non-default listener, you need to configure the local_listener parameter! If the service_names and instance_name values are not explicitly set, dynamic registration only occurs when the database is started after the listener is running. In this case, if the listener is restarted later, dynamic Registration Information will be lost. Obviously, it is best to start the listener before all databases are started. This will avoid the loss of Dynamic Registration Information caused by restarting the listener if the values of service_names and instance_name are not explicitly set.

 

Setting an explicit value for the initialization parameters service_names and instance_name is recommended. Because if the listener needs to be restarted during database operation. when the values of service_names and instance_name are explicitly set in the ora file, the pmon process of each database completes dynamic registration within a short period of time.

4. query whether a service is static registration or Dynamic Registration

You can run the lsnrctl status Command to check whether a service is registered statically or dynamically.

When the instance status is unknown, it indicates that this service is set for static registration. The listener is used to indicate that it does not know any information about the instance.
When the user sends a connection request, it checks whether the instance exists.

Dynamically registered databases are indicated by status ready or status blocked (for a backup database) in the status information. No matter when the database is closed
Status registration databases are dynamically deregistered from the listener, and the related information will disappear from the status list. In this way, whether the database is running or
The listener always knows its status. This information will be used to connect to the request's back-to-back (fallback) and load balancing.

 

----- End ----