Ora-12514 tns listeners cannot identify the Service requested in the connection descriptor, ora-12514tns

Ora-12514 tns listeners cannot identify the Service requested in the connection descriptor, ora-12514tns

Today, when you remotely connect to a linux server, this error occurs: tnsnames. ora and listener. ora file configuration is not wrong, but is not connected to tnsping is also normal, but with plsql connection is always reported when the error ora-12514 tns listeners cannot identify the connection descriptor request service, the listener should be okay because there is no problem connecting to the local database. Next, check tnsnames. ora found a problem, in fact, the error message clearly indicates that the service request in the connection descriptor cannot be identified. The SID and service name are different. I changed SERVICE_NAME to SID and restarted the listener to connect successfully.

The following is the tnsnames. ora file when the connection fails:

Yanzhao_orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 220.194.59.37) (PORT = 1521 ))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

Modified tnsnames. ora File

Yanzhao_orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = 220.194.59.37) (PORT = 1521 ))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID = orcl)
)
)

Tnsping

Once written here, I checked the relationship between SID and server_name on the Internet.

In oracle, SID must be equal to instance_name (Instance name). Here, we can simply remember that SID is the Instance name.

1. SID indicates the Instance name and Instance name indicates the name of the database management system used to respond to a database operation. The instance name is determined by the instance_name parameter of the initialization parameter file. If this parameter is not specified (instance_name is not specified as any value), the Instance name is determined by the user's environmental variable ORACLE_SID (note that this parameter is in upper case. On windows, the oracle_sid value in the registry is determined.

2. SERVICE_NAME refers to the global database name in listener: this name is determined by the GLOBAL_DBNAME parameter in listener. ora. This name indicates the value of the SERVICE_NAME parameter in tnsnames. ora when the client connects to the database.

3. When configuring tnsname. ora in the connection string, you can use SID or SERVICE_NAME. Note that these two values are not necessarily the same, depending on the configuration in the database server. The problem lies in the third point here, if SID and service name is the same of course, the connection will not go wrong, but if not the same, is to report ora-12514 this specific also depends on the server configuration information.
Two articles about the differences between instance_name/SID/server_name are provided for your reference. 1. http://blog.chinaunix.net/uid-23072872-id-35661.html 2. http://www.zhetao.com/content240 Thank you ~~