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:\Oracle\network\admin\tnsnames.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:\Oracle\network\admin\sqlnet.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 firstname.lastname@example.org. If Scott had a NAMES.DEFAULT_DOMAIN value of internet.com, could he connect to the dbaspt database by using email@example.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.