Oracle ORA12514 Listener currently does not recognize the service requested in the connection descriptor

In connection with the database, sometimes encountered a "ORA12514: Listener currently does not recognize the service requested by the connection descriptor" error, which is actually the database dynamic registration (about dynamic registration will be explained later) does not take effect, causing the listener does not recognize the service name provided in the client connector, The error message of the database connection times is rejected, so the listener configuration needs to be modified.

Here, it is also necessary to refine the problem, and sometimes may find that in the beginning of the listener when the error occurs, but after a while to connect will not error, this is actually because the dynamic registration takes time, and just opened the listener, the database has not registered to the listener, resulting in an error, This is not within the scope of this article.

The Listener.ora file is stored in $oracle_home/network/admin (for example, my Oracle11 is D:\Oracle\product\11.2.0\dbhome_1\NETWORK\ADMIN), This file is stored in the listener configuration, the listener will read the file at startup, let us first understand this file.

The approximate configuration of the file is as follows

, the Sid_list_listener parameter is database registration, dynamic registration or static registration (registering is registering the database as a service to the listener. The client does not need to know the database name and the instance name, only need to know that the database provides the service name can request to connect to the database, the dynamic registration is in the Listener configuration file not explicitly declares the database instance and the service name, but when the database starts the database automatically registers to the listener, Static registration is the explicit declaration of the DB instance and service name in the listener configuration file. ), the listener parameter is the configuration of the listener, where the protocol parameter is the protocol name, usually the Tcp,host parameter is the address, can write the IP address, server name, localhost, 127.0.0.1,port parameter is the port number, the default is 1521.

Since the problem is caused by dynamic registration, the simplest solution is to change from dynamic registration to static registration, such as

In the red box is the static registration, the Sid_name parameter is the database instance name, the Global_dbname parameter is the global database name (when configuring the client's Local service name "service name" to be consistent with the global database name), after configuration, restart the listener, you can connect normally.

Modification to static registration seems simple, but there are shortcomings, first of all, to modify the static registration needs to be more familiar with the configuration files and parameters, otherwise it is likely to configure the error, second, when the value of the parameter changes must be re-modified configuration.

Another way is to modify the value of the host parameter, the host parameter can have four kinds of values: IP address, server name, localhost, 127.0.0.1, because the desirable value has become more, resulting in a solution also with the cause of the problem has diversified up, Various situations may have different solutions, you can try to modify between the four values, it should be noted that when using localhost and 127.0.0.1, the client's local service name cannot be configured with IP address and server name, otherwise it will be reported "ORA12541: No Listener" Error, and the listener will be restarted after the change.

The above solution is only my own summary of this error, may not be able to solve all the situation, welcome to discuss.

Oracle ORA12514 Listener currently does not recognize the service requested in the connection descriptor