oracle11g "The ORA-12514 listener is currently unable to identify the service requested by the connection descriptor" error resolution process record

1. Installation Environment

Windows7/windows10/windows Server R2

Standard Edition/Enterprise Edition

2. Operation procedure

(1) Follow the installation process to complete the installation;

(2) After the installation is complete, a service ORCL is configured by default, and a listener listener. The service hostname defaults to Localhost,listener's listening location address host defaults to localhost, testing the service in Net Manager and testing success!

(3) Since it is a database service, naturally requires the client to access (production environment, the natural existence of database server), then we serve the host and listener host should be defined by the network IP. OK, modify it directly in Net Manager, XXX.XXX.XXX.XXX.

(4) Next, according to the Oracle website, the configuration in Net Manager requires a restart of the service. OK, restart the Oracleoradb11g_homextnslistener, the ORACLESERVICEORCL also restart the bar ~ ~ ~

(5) Testing services, Scott ... However, this test was unsuccessful, error:The ORA-12514 listener currently does not recognize the service requested in the connection descriptor.

------------------------------------------------------------begin to solve the problem-------------------------------------------------

　　For a technical person, the error event will be triggered almost every day, but also may be recursive 100 times!!!

So do not have to panic, we first to analyze (not recommended direct "degree Niang" ah, search out n a post forum solution, try each time, in fact, there are a lot of this practice. But each person encountered different problems, each server deployment environment is not the same, forum post author in a specific environment can be resolved, does not mean that will be able to solve your problem! Of course, try to solve the probability is quite large, after all, the global practitioners, a large database software configuration problems encountered by the number of people.

(1) First of all, the error indicates that the listener does not recognize the service, then we first to understand the Oracle Listener program and service relationship:

In Oracle's official documentation, there are:

The listener is a separate process, runs on the database server computer. IT receives incoming client connection requests and manages the traffic of these requests to the database server. This chapter describes how to configure the listener to accept client connections.

and

A Listener is configured with one or more listening protocol addresses, information about supported services, and Paramete Rs. control its runtime behavior. The listener configuration is stored in a configuration file named listener.ora .

(2) from the document can be seen that the listener configuration file is listener.org, it is easy to find the file directory: installation directory \app\admin\product\11.2.0\dbhome_1\network\admin

Open File:

# Listener.ora Network Configuration File:e:\app\admin\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

LISTENER =
(Description_list =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.111) (PORT = 1521))
)
)

Adr_base_listener = E:\app\admin

If you are an Oracle technology bull and know all the configuration parameters of listener, it should be easy to solve the problem. But I also know that there are a large part of the people, not interested in these configuration files, configuration error, search online, solve OK, can be used after n years.

But I don't know much about Oracle, but I want to guide others to solve problems. In this case, it is recommended to first on the official website of the listener configuration parameters of the document to see again, I believe that these minutes than in the "degree Niang" search more efficient.

(3) Traverse the official documents to get a clue:

Global_dbname

Purpose

Use the parameter to GLOBAL_DBNAME identify the database service.

while processing a client connection request, the listener tries to match the value of thi s parameter with the value of the , service_name  parameter in the client connect descriptor. If The client connect descriptor uses the  SID  parameter, then the listener does isn't attempt to map The values. This parameter was primarily intended for configurations with Oracle8 release 8.0 or ORACLE7 databases (where dynamic servi Ce registration is not supported for dedicated servers). This parameter may also is required for use with Oracle9i and Oracle8 database services by some Configurations and management tools.

The value for this parameter are typically obtained from the combination of the DB_NAME and DB_DOMAIN parameters ( DB_NAME . DB_DOMAIN ) in The initialization parameter file, but the value can also contain any valid name used by clients to identify the service.

Embed This parameter under the SID_DESC parameter.

Example

Sid_list_listener_name= (  sid_list= (sid_desc= (    global_dbname=sales.us.example.com)    ( Sid_name=sales)    (oracle_home=/usr/oracle)))

From this we can see that there is a connection description when the client connects the request, and the value of Service_Name is included in the connection description. And Global_dbname is used to define the database service. Combined error: The listener does not recognize the service requested in the connection descriptor. It may be inferred that the service is undefined.
We increase global_dbname in accordance with example, as follows:

# Listener.ora Network Configuration File:e:\app\admin\product\11.2.0\dbhome_1\network\admin\listener.ora
# Generated by Oracle configuration tools.

Sid_list_listener =
(Sid_list =
(Sid_desc =
(Global_dbname = ORCL)

(Sid_name = ORCL)

(Oracle_home = E:\app\admin\product\11.2.0\dbhome_1)
)
)

LISTENER =
(Description_list =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC) (KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP) (HOST = 192.168.1.111) (PORT = 1521))
)
)

Adr_base_listener = E:\app\admin

(4) Restart Listener service, test success!

Although this may be a small white problem, but also spent time to learn, to solve the problem.