Ora-12514:tns:listener does not currently know the service requested in Connect

192.168.5.231 Remote Connection 192.168.6.51 ORCL instance, error:

Ora-12514:tns:listener does not currently know the service requested in Connect

To view this error:

[ORACLE@OCP ~]$ oerr ora 12514
12514, 00000, "Tns:listener does not currently know of service requested in Connect descriptor"
*cause:the Listener received a request to establish a connection to a
Database or other service. The Connect descriptor received by the listener
Specified a service name for a service (usually a database service)
That is either has not yet dynamically registered with the listener or has
Not been statically configured for the listener. This May is a temporary
Condition such as after the listener has started, but before the database
Instance has registered with the listener.
*action:
-Wait a moment and try to connect a second time.
-Check which services are currently known by listener by:
LSNRCTL Services <listener name>
-Check that the service_name parameter in the Connect descriptor of the
NET service name used specifies a service known by the listener.
-If A Easy Connect naming connect identifier is used, check that
The service name specified is a service known by the listener.
-Check for a event in the Listener.log file.

The cause of this error is two:

①: Tnsnames.ora files are not configured correctly on the client

②: Service not registered in server-side listening

For the 1th, after inspection, found that there is no problem:

View on 192.168.6.51:

Sql> Show parameter service;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Service_names string ORCL

The contents of the Tnsnames.ora configuration file in 192.168.5.231 are as follows:

ORCL =
(DESCRIPTION =
(address = (PROTOCOL = TCP) (HOST = 192.168.6.51) (PORT = 1521))
(Connect_data =
(SERVER = dedicated)
(service_name = ORCL)
)
)

For the 2nd:

Check that the server-side service is registered in the Monitor:

[ORACLE@OCP ~]$ lsnrctl service LISTENER #注意: LISTENER is the listener name

Lsnrctl for linux:version 11.2.0.1.0-production on 07-sep-2017 09:28:51

Copyright (c) 1991, 2009, Oracle. All rights reserved.

Connecting to (description= address= (protocol=tcp) (host=10.0.9.121) (port=1521))
The listener supports no services
The command completed successfully

Found that there was no service in the monitor.

Solution:

Modify the Listener.ora file and add at the end of the file:

Sid_list_listener=
(sid_list=
(sid_desc=
#BEQUEATH CONFIG
(GLOBAL_DBNAME=ORCL)
(SID_NAME=ORCL)
(oracle_home=/u01/app/oracle/product/11.2.0/dbhome_1)
#PRESPAWN CONFIG
(PRESPAWN_MAX=20)
(prespawn_list=
(Prespawn_desc= (PROTOCOL=TCP) (pool_size=2) (timeout=1))
)
)
)

Then, restart the listening service:

Lsnrctl stop

Lsnrctl start

#查看下服务是否注册到了监听里:

Lsnrctl Service LISTENER

Lsnrctl for linux:version 11.2.0.1.0-production on 07-sep-2017 10:19:34
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (description= address= (protocol=tcp) (host=192.168.6.51) (port=1521))
Services Summary ...
Service "ORCL" has 1 instance (s).
Instance "ORCL", Status UNKNOWN, has 3 handler (s) for the This service ...
Handler (s):
"Dedicated" Established:6 refused:0
Local SERVER
"Prespawn" established:0 refused:0 current:0 max:0 state:ready
"Prespawn" established:0 refused:0 current:0 max:0 state:ready
The command completed successfully

Remote connection:

Sqlplus DANDAN/DANDAN@ORCL

No more errors.