Oracle ORA-12154: TNS: cocould not resolve service name error Solution

Yesterday, a colleague reported that when Oracle9i was used, the service name was successfully configured in the net assistant, but it was always unable to connect when SQL * Plus and other client tools were used.

· First, check whether the service name exists and find that the service name does exist and can be connected and tested in the net assistant.

· Next with SQL * Plus connection error, error: ORA-12154: TNS: cocould not resolve service name

· Error in testing with Oracle tnsping command: TNS-03505: failed to break down name

I was puzzled. I only had to go online to Google. After searching for multiple times, I finally found an article that found the same situation as I did. The following is an excerpt:

(Respect Original: Author blog address: http://blog.chinaunix.net/u1/44757/index.html)

The following content is a reprinted part:
Certificate ----------------------------------------------------------------------------------------------------------------------------------------
ORA-12154: TNS: cocould not resolve service name

The error indicates that the customer cannot find the services listed in the tnsnames. ora file.

Display error codes:

ORA-12154: TNS: cocould not resolve service name
Cause: Oracle Net cocould not locate the net service name specified in the tnsnames. ora configuration file.

Action: perform these steps:
1. Verify that a tnsnames. ora file exists. (See also: "localized configuration file support" For Configuration File Location Information)

2. Verify that there are not multiple copies of the tnsnames. ora file.

3. In the tnsnames. ora file, verify that the net service name specified in your connect string is mapped to a connect descriptor.

4. verify that there are no duplicate copies of the sqlnet. ora file.

5. if you are using domain names, verify that your sqlnet. ora file contains a names. default_domain parameter. if this parameter does not exist, you must specify the domain name in your connect string.

6. If you are not using domain names, and this parameter exists, delete it or disable it by commenting it out.

7. If you are connecting from a Login Dialog Box, verify that you are not placing an "@" symbol before your connect net service name.

8. Activate client tracing and repeat the operation.

Cause: Oracle Net cocould not locate the Database Service name or net service name specified in the Directory Server.

Action: perform these steps:
1. Verify that the database service or net service name entry exists in the directory that this computer was configured to use.
(See also: Chapter 8, "setting up directory server usage" for directory setup instructions)

2. Verify that the sqlnet. ora file contains des the following entry: names. directory_path = (LDAP, other_naming_methods)
Check whether the service name in the tnsnames. ora file is correctly configured according to the instructions in step 3. (1.2 is correct). Check (SERVICE_NAME = hatest)
Then, follow step 4.5 of the document to locate the problem at Step 6th. The sqlnet. ora file references the domain (Note: We didn't use the domain here)
[Oracle @ localhost admin] $ cat sqlnet. ora
# Sqlnet. ora network configuration file:/home/Oracle/orahome1/Network/admin/sqlnet. ora
# Generated by Oracle configuration tools.
Names. default_domain = localdomain
Names. directory_path = (tnsnames, onames, hostname) -- No problem

Okay. What are you waiting for to edit the file and kill the domain. After editing, hurry up with tnsping hatest
[Oracle @ localhost admin] $ tnsping hatest
TNS Ping utility for Linux: Version 9.2.0.4.0-production on 26-apr-2007 16:33:39
Copyright (c) 1997 Oracle Corporation. All rights reserved.
Used parameter files:
/Home/Oracle/orahome1/Network/admin/sqlnet. ora
Used tnsnames adapter to resolve the alias
Attempting to contact (description = (address_list = (address = (Protocol = TCP) (host =
192.168.248.155) (Port = 1521) (CONNECT_DATA = (SID = hatest) (Server = dedicated )))
OK (30 msec)

No problem. Connect to the database again

[Oracle @ localhost admin] $ sqlplus hangan/hangan @ hatest
SQL * Plus: Release 9.2.0.4.0-production on Thu Apr 26 16:33:49 2007
Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.
Connected:
Oracle9i Enterprise Edition Release 9.2.0.4.0-Production
With the partitioning, OLAP and Oracle Data Mining options
Jserver release 9.2.0.4.0-Production

SQL> exit
Certificate ----------------------------------------------------------------------------------------------------------------------------------------

After reading the above description, I checked the sqlnet. ora file step by step, and the problem came out. It turns out that this colleague's sqlnet. ora file is like this:

# Sqlnet. ora network configuration file: C:/Oracle/ora81/Network/admin/sqlnet. ora
# Generated by Oracle configuration tools.

Names. default_domain = eapac. Ericsson. se

Sqlnet. authentication_services = (CNT)

Names. directory_path = (onames)

We can see that only onames is used to find the service, but its service is configured in tnsnames. in the ora file, when connecting to SQL * Plus, Oracle does not search by tnsname, but cannot find the service.

The problem is found. Change names. directory_path:
Names. directory_path = (tnsnames, hostname, onames)

Test the connection and solve the problem.

I would like to thank the bloggers of the blog post and feel that the DBA in the article has a clear idea of solving the problem. It seems that I still have a long way to go from a qualified dBA and continue to work hard.