Avoiding the "ORA-12154: TNS: could not resolve service name" error
Where were you when Oracle9i was released? Okay, so it wasn’t
that memorable of an event for most users, DBA’s, and the rest of the world.
However, given that Oracle9i was released over two years ago, it is interesting
to note that its new features are still being announced, highlighted, debated,
and unfortunately, debugged. The RDBMS component of a new release typically
attracts all of the attention, relegating other Oracle components to the
sidelines. This article discusses a common error frequently seen in one of
those seemingly unimportant bit players, namely, Oracle Net Services.
Oracle Net Services, formerly known as Net8, formerly known
as SQL*Net, is what allows client-based users to connect to a database, where
connecting is important for obvious reasons. To be more precise, Oracle Net, a
component of Oracle Net Services, provides this functionality. In certain
cases, it is important to differentiate between Oracle Net Services and Oracle
Net. This is analogous to differentiating between database and instance. Most
of the time, everyone knows what is meant by "the database is up." To
a large degree, most people can use Oracle Net Services and Oracle Net interchangeably.
The change in the network component name leads into this
observation – there are two things you can count on with each new release of
Oracle: bugs, and name changes related to parameters, utilities, and features.
Some of the changes are best viewed using a "good news, bad news"
approach. For example, in Oracle9i, the good news is Oracle Names (ONAMES)
became a deprecated feature. The bad news, for those of you seeking Oracle
Certified Professional (OCP) status for Oracle8i, is that the networking exam
still asks quite a few questions about ONAMES. In some sort of twisted logic,
you have to learn Oracle Names so you can forget about it later.
Another change concerns the protocol.ora file. If you were
used to using it in prior releases, the bad news is that it’s going, going,
gone, but the good news is that its contents have been moved to the sqlnet.ora
file. The good news about Oracle Net? It is a lot like Net8. The bad news? If
you have not mastered how to configure tnsnames.ora and sqlnet.ora under Net8, you
are going to continue to have the same problems today. Why is it important to
master configuring these files? If not done correctly, connecting with Oracle
does not happen. One of the most common errors related to improper
configuration is the "ORA-12154: TNS: could not resolve service name"
error. This error can be infuriatingly frustrating if you do not understand how
Oracle resolves a service name. It is not uncommon for new users of Oracle to
spend hours upon hours trying to figure out what their mistake is when
ORA-12154 appears.
One of the (bad?) side effects of reading the new features
summary is that you realize how little you knew about the old features the new
ones are replacing! This begs the question of how to avoid the side effects,
and the answer is not "don’t read about the new features." It helps
to focus on what has stayed the same and to leverage that knowledge into
gaining the most mileage out of what you already know how to do. Although not
directly related to data in a database, problems with network-related
configuration files are an impediment to productivity. The bad news is obvious:
no connection means no access to your data. The good news is that whatever
mistakes you make in configuration rarely affect your data. The following
discussion applies equally to the Net8 and Oracle Net configuration files of
tnsnames.ora and sqlnet.ora.
Let’s start with a simple example of what happens when you
enter username/password@SID. We will use the ubiquitous scott/tiger setup, and the
name of our database is dbaspt. After entering username/password@SID, Oracle
stops by the sqlnet.ora file to pick up the NAMES.DEFAULT_DOMAIN value, if it
exists. When "Scott" installed his Oracle software and created the
seed database, he was prompted to enter a domain name for his database. Suppose
Scott entered "company.com" as his domain name. Oracle appends the
NAMES.DEFAULT_DOMAIN value of company.com to dbaspt, resulting in a string of
dbaspt.company.com. Oracle’s next stop is the tnsnames.ora file.
The "tns" in "tnsnames.ora" stands for
transparent network substrate. Without going into what all the network layers
are, the operative word here is transparent. For the most part, you really do
not need to know or care how Oracle networking works. The details are
transparent to you, so to speak. What you do need to know is how to
successfully create at least one Oracle Netrelated file (not counting the
listener.ora file on the server). If you can do this on one platform, you can
do it on any platform. So what does Oracle look for in the tnsnames.ora file?
Shown below are typically configured tnsnames.ora and
sqlnet.ora files that our user Scott would see after the seed database has been
created.
# TNSNAMES.ORA Network Configuration File: D:Oraclenetworkadmintnsnames.ora
# Generated by Oracle configuration tools.EXTPROC_CONNECTION_DATA.COMPANY.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)DBASPT.COMPANY.COM =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = yourpc)(PORT = 1521))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = dbaspt)
)
)# SQLNET.ORA Network Configuration File: D:Oraclenetworkadminsqlnet.ora
# Generated by Oracle configuration tools.NAMES.DEFAULT_DOMAIN = company.com
SQLNET.AUTHENTICATION_SERVICES= (NTS)
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
The second "paragraph," or stanza, in the
tnsnames.ora file begins with DBASPT.COMPANY.COM. If DBASPT.COMPANY.COM does
not appear exactly as shown, Scott will receive the ORA-12154 error. If the
first line had only "DBASPT" (no default domain name), Scott would
still receive the error. What about the case where there is no
NAMES.DEFAULT_DOMAIN value, and the tnsnames.ora file stays the same (as shown
above)? Scott would still receive the ORA-12154 error because Oracle is looking
for DBASPT, not DBASPT.COMPANY.COM.
In the case where no default domain name is used in the
sqlnet.ora file, but the tnsnames.ora file contains the SID "dot"
NAMES.DEFAULT_DOMAIN entry, Scott could connect to the database by using a
fully qualified net service name. The string represented by NAMES.DEFAULT_DOMAIN
appended to SID is a fully qualified net service name. Scott could connect to
the database by using scott/tiger@dbaspt.company.com. If Scott had a
NAMES.DEFAULT_DOMAIN value of internet.com, could he connect to the dbaspt
database by using scott/tiger@dbaspt.company.com? The answer is yes! Anytime Scott
(or you) uses a service name string with a period in it, Oracle considers that
string to be a fully qualified net service name. If a fully qualified net
service name is used, Oracle bypasses the sqlnet.ora file and goes straight to
tnsnames.ora to resolve the service name.
In a plain vanilla, generic environment, you do not even
need the sqlnet.ora file. There is no requirement to use NAMES.DEFAULT_DOMAIN.
The NAMES.DIRECTORY_PATH shown in the sample file reflects Oracle’s default
name resolution search path, so if you are using the default order, there is no
requirement to list it. Because the sample sqlnet.ora was based on a Windows
environment, Oracle included the SQLNET.AUTHENTICATION_SERVICES parameter, and
again, even that is not required.
If you were using one and only one database, no external
procedure calls, all you would need in the tnsnames.ora file is this:
DBASPT =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = yourpc)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = dbaspt)
)
)
Scott and other users would only need to use
username/password@dbaspt to connect to the database. The "SERVICE_NAME"
entry could be changed to "SID" because the use of "SID = your_database_name"
is still supported, but the Net8 and beyond Oracle-recommended standard is "SERVICE_NAME
= your_database_name."
Like many other things in Oracle, there is more than one way
to accomplish whatever it is you are trying to do, and Oracle Net configuration
is no exception. Barring the issue of firewalls and other more sophisticated
networking tools, Oracle provides you a failsafe method of fixing errors in
your Oracle Net (and Net8) configuration files. The failsafe method lies within
the use of a GUI assistant, and one of the benefits of using it is that you
will consistently get the same output (files created for you) given that you
input the same information. Do not be afraid to experiment with these files on
a test database. You won’t harm any data, and you can always start over, and
that’s a situation you don’t always find yourself in when using Oracle.
