4.5. Configuring Oracle Net for the Client
Once the Oracle server is properly configured, you can focus on configuring the clients to allow for connectivity to the Oracle server. It is important to understand how to configure Oracle clients. Without proper knowledge of how to configure the client, you are limited in your connection choices to the server. As a DBA, you must understand the network needs of the organization, the type of connectivity that is required, and client/server connections versus n-tier connectivity, for example, in order to make the appropriate choices about client-side configuration. This section should help clarify the client-side connectivity options available to you and show you how to troubleshoot client connection problems
4.5.1. Client-Side Names Resolution Options
When a client needs to connect to an Oracle server, the client must supply three pieces of information: their user ID, password, and net service name. The net service name provides the necessary information, in the form of a connect descriptor, to locate an Oracle service in a network.
This connect descriptor describes the path to the Oracle server and its service name, which is an alias for an Oracle database. The location where this information is kept depends on the names resolution method you choose. The five methods of net service name resolution are Oracle Internet Directory, External Naming, hostnaming, Oracle Easy Connect, and localnaming. Normally, you choose just one of these methods, but you can use any combination.
Oracle Internet Directory is advantageous when you are dealing with complex networks that have many Oracle servers. When you choose this method, you can configure and manage Net Service Names and connect descriptor information in a central location.
External Naming uses a non-Oracle facility to manage and resolve Oracle service names. For example, if an organization is using an external names resolution method such as Network Information Service (NIS), the database service information could be stored in this external location and used by clients to resolve service names.
NOTE
You should only be casually familiar with these two naming resolution options. For a more detailed description of how to configure External Naming and how to configure and use this method, please consult the Oracle Database Net Services Administrator's Guide 10g Release 1 (10.1) Part Number B10775-01.
In the following sections, we will take a closer look at the hostnaming, Oracle Easy Connect, and localnaming methods.
4.5.2. The Hostnaming Method
In small networks with few Oracle servers to manage, you can take advantage of the hostnaming method. Hostnaming is advantageous when you want to reduce the amount of configuration work necessary. Hostnaming saves you from configuring on the clients, although it does have limitations. There are four prerequisites to using hostnaming:
You must use TCP/IP as your network protocol.
You must not use any advanced networking features, such as Oracle Connection Manager.
You must have an external naming service, such as DNS, or a HOSTS file available to the client.
The listener must be set up with the GLOBAL_DBNAME parameter equal to the name of the machine.
Now let's discuss how to configure this naming method.
4.5.2.1. Configuring the Hostnaming Method
By default, Oracle attempts to use the hostnaming method from the client only after it attempts connections using localnaming. To override this default search path for resolving names, set the NAMES.DIRECTORY_PATH parameter in the sqlnet.ora file on the client so that it searches for hostnaming only. You can configure this parameter using the Oracle Net Manager (see Figure 4.24).
Figure 4.24. Oracle Net Manager sqlnet.ora Naming screen
To configure the parameter using Oracle Net Manager, choose Profile from the Local tab and select Naming from the drop-down list at the top of the screen to open a list of naming methods. The Selected Methods list displays the naming methods being used and the order in which they are used to resolve service names. The Available Methods list displays the methods not included in the Selected Methods list.
To change the list of available methods, highlight a method name and click the arrow key (>) to include it in the Selected Methods list. To remove a name, select it and click the other arrow key (<). You can also change the order of the list. Select a name from the Selected Methods list and click the Demote button to move the name down the list or click the Promote button to move the name up the list. Make sure that HOSTNAME shows up in the Selected Methods column.
Once you save the configuration, Oracle updates the sqlnet.ora file with the changes you made.
The following is an example of the sqlnet.ora file.
# SQLNET.ORA Network Configuration File:
D:oracleora10g
etworkadminsqlnet.ora
# Generated by Oracle configuration tools.
NAMES.DEFAULT_DOMAIN = mjw.com
NAMES.DIRECTORY_PATH= (TNSNAMES,EZCONNECT,HOSTNAME)NOTE
The hostnaming and the Oracle Easy Connect Naming methods do not require any client-side configuration files. We'll discuss these connection methods later in this section.
You can check TCP/IP connectivity from the client using the TCP/IP utility ping. Ping attempts to contact the server by sending a small request packet. The server responds in kind with an acknowledgment.
The following code shows an example of how ping works and the speed of the round-trip from client to server and back:
C:>ping mil02ora Pinging mil02ora [10.1.5.210] with 32 bytes of data: Reply from 10.1.5.210: bytes=32 time<10ms TTL=128 Reply from 10.1.5.210: bytes=32 time<10ms TTL=128 Reply from 10.1.5.210: bytes=32 time<10ms TTL=128 Reply from 10.1.5.210: bytes=32 time<10ms TTL=128
The server must be configured with a listener running TCP/IP, and the listener must be listening on the default port of 1521. If the instance has not been dynamically registered with the listener, you must configure the listener with the GLOBAL_DBNAME parameter. The following code shows what the listener.ora file looks like when it is configured with this parameter.
In this example, the hostname is mweishan-dell, the name of the physical machine on which the listener process is running.
# listener.ora Network Configuration File:
D:oracleora10g
etworkadminlistener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = mweishan-dell)
(PORT = 1521))
(PROTOCOL_STACK =
(PRESENTATION = TTC)
(SESSION = NS)
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = mweishan-dell) − machine listener is on
(ORACLE_HOME = d:oracleora10g)
(SID_NAME = orcl)
)
)Figure 4.25 shows the Oracle Net Manager Database Services screen. Each database that the listener will be serving is created as a separate entry. Provide the global database name, Oracle Home directory, and Oracle SID information. This completes the configuration work for the database portion of listener configuration.
4.5.2.2. The Connection Process When Using Hostnaming
When you use hostnaming, the client must supply a user ID and password along with the name of the machine to which they want to connect. For example, if the user matt with the password casey wants to connect to a database residing on machine mweishan-dell, he enters Sqlplus matt/casey@mweishan-dell.
The hostname is resolved either by a HOSTS file or by an external naming environment, such as DNS. External naming methods, such as DNS, are preferred over a HOSTS file because they facilitate centralized management of hostnames. The following code contains an example of a HOSTS file from a Windows environment. The default location for the HOSTS file on a Unix system is in the /etc directory. On Windows, the default location is c:winntsystem32driversetc. Once the hostname is resolved, the connection is made to the machine.
# Copyright (c) 1993-1999 Microsoft Corp. #
# This is a sample HOSTS file used by Microsoft # TCP/IP for Windows NT. # # This file contains the mappings of IP addresses # to hostnames. Each # entry should be kept on an individual line. # The IP address should # be placed in the first column followed # by the corresponding hostname. # The IP address and the hostname should be separated # by at least one # space. # # Additionally, comments (such as these) may be # inserted on individual # lines or following the machine name denoted # by a '#' symbol. # # For example: # # 102.54.94.97 rhino.acme.com # source server # 38.25.63.10 x.acme.com # x client host 127.0.0.1 localhost 10.2.0.91 mweishan-dell # Oracle Database Server
Figure 4.25. The Oracle Net Manager listener.ora setup for hostnaming
The listener receives the request and looks for a matching GLOBAL_DBNAME. If it is found, the connection is established as a dedicated, or dispatched, connection depending on the configuration of the Oracle server.
Figure 4.26 illustrates the following hostnaming connection process:
The client contacts the DNS server or local HOSTS file.
The client contacts the Oracle server.
The server spawns a dedicated process and redirects the connection to the newly spawned process or redirects the connection to a dispatched process when you are using the Oracle Shared Server.
The server passes connection information back to the client.
The client is now in direct contact with the server process or dispatcher.
4.5.2.3. Configuring Multiple Services on the Same Host Using Hostnaming
If you have multiple Oracle servers on the same machine, it is possible to continue using the hostnaming method. To do so, you must have separate hostname address entries in your HOSTS file or in your external naming service for each of the separate Oracle services. For example, if you have two Oracle services, one called DBA and one called PROD, on a machine with an IP address of 10.2.0.91, you can configure your HOSTS name with the following entry:
10.2.0.91 DBA # Alias for MACH1 server for DBA DBA 10.2.0.91 PROD # Alias for MACH1 server for PROD PROD
Notice that each of these names resolve to the same IP address. You also need to configure your listener with two entries, one for DBA and one for PROD, both with the GLOBAL_DBNAME parameter set to DBA and PROD, respectively. (If you are using the hierarchical naming model with domain names, include the domain name on the GLOBAL_DBNAME parameter.)
4.5.3. The Oracle Easy Connect Naming Method
The Oracle Easy Connect Naming method is a new connection resolution technique introduced in Oracle 10g. This method is similar to the hostnaming method described in the previous section but adds parameters that allow for a port and service name specification. By default, the Oracle Easy Connect names resolution method is configured when Oracle Net is installed.
Figure 4.26. Hostnaming connection summary
Like the hostnaming method, the Oracle Easy Connect Naming method eliminates the need for any connection information to be configured on the client. This makes for less setup and administrative work. It enhances the hostnaming method by allowing for a port and service specification. Remember from the previous section that the hostnaming method requires a listener to be listening on the default port of 1521. Allowing a port specification addresses one of the limitations of the hostnaming method. Using the Oracle Easy Connect Naming method requires that certain conditions be met:
Oracle Net Services 10g must be installed on the client.
Oracle Net TCP/IP services must be enabled and supported on both the client and the server.
No advanced connection descriptor features are allowed such as connection pooling or external procedure calls.
The following describes the connect descriptor components when you are using the Oracle Easy Connect Naming method:
| Syntax Component | Description |
|---|---|
| // | Optional: Used when you are connecting via a URL. |
| Host | Required: The host or IP address to connect to. |
| Port | Optional: The port to connect to. The default is 1521. |
| Service Name | The service name for the database. The default is the hostname of the computer on which the database resides. If the database name is different from the hostname, enter the service name. |
Here are a few examples of how to connect to a database using the Easy Connect method:
CONNECT scott/tiger@mweishan-dell:1522/orcl.com CONNECT scott/tiger@//mweishan-dell/orcl
The first example shows how a user connects to a database service orcl.com that is running on the mweishan-dell server and has an Oracle listener listening for TCP/IP connections on port 1522. The second example shows how you can use the Easy Connect method with the default port via JDBC or a URL-type connection. This type of connection requires a double slash (//) between the password and server descriptor.
As stated previously, this method is configured automatically when you install Oracle Net. If you want the Oracle Easy Connect Naming method to be the first method chosen by a client when a connection request is made, you can modify the NAMES.DIRECTORY_PATH parameter in the sqlnet.ora file. The following discussion shows how to do this.
You can use the Oracle Net Manager tool to configure the Easy Connect method as the default names resolution method. Start the Oracle Net Manager tool, then follow these steps:
Choose Local
Profile pane in the Navigator pane.Select Naming from the panel on the right.
Select EZCONNECT in the Selected Methods list. You can click the promote arrows to move EZCONNECT to the top of the Selected Methods list.
Choose File
Save Network Configuration to save your changes.
When you check your sqlnet.ora file, you should see the following entry:
NAMES.DIRECTORY_PATH=(EZCONNECT,TNSNAMES)
4.5.4. The Localnaming Method
The localnaming method is probably the most widely used and well-known method for resolving net service names. Most users know this method as the tnsnames.ora method because it uses the tnsnames.ora file.
To use the localnaming method, you must configure the tnsnames.ora file, which can be in any location as long as the client can get to it. The default location for the tnsnames.ora file and the sqlnet.ora file is %ORACLE_HOME% etworkadmin on Windows and $ORACLE_ HOME/network/admin on Unix systems. If you want to change the location of this file, set the environmental variable TNS_ADMIN. In Unix-based systems, you can export TNS_ADMIN to the user's shell environment or in the user's profile. In Windows, this setting is in the Registry. The Windows Registry key that stores the TNS_ADMIN depends on your particular setup. Generally, it is somewhere under Hkey_local_machine/software/oracle, but it may be at a lower level depending on your configuration.
Most installations probably keep the files in these default locations on the client and server. Some users create shared disks and place the tnsnames.ora and sqlnet.ora files in this shared location to take a centralized approach to managing these files. If server-to-server communication is necessary, these files need to be on the server. The default location on the server is the same as the default location on the client.
Now that you have an understanding of the localnaming method, we will discuss how to configure this method using Oracle Net Manager.
4.5.4.1. Configuring the Localnaming Method Using Oracle Net Manager
To configure the localnaming method, you use Oracle Net Manager. To start this configuration, open the Net Manager and select Service Naming on the Local tab. Click the plus sign on the left side of the screen, or choose Edit 
Create (see Figure 4.27).
The Oracle Net Manager starts the Net Service Name wizard, which guides you through the process of creating the Net Service Names definition.
4.5.4.1.1. Choosing a Net Service Name
When you configure a client to use the localnaming method, you must first choose a net service name. This is the name that users enter when they are referring to the location to which they want to connect. The name you supply here should not include the domain portion if you are using the hierarchical naming mode. Figure 4.28 shows an example of choosing the net service name. Click the Next button to continue.
Figure 4.27. The Oracle Net Manager title screen with the Service Naming option chosen
Figure 4.28. Choosing a net service name
4.5.4.1.2. Choosing a Network Protocol
The next step is to enter the type of protocol that the client should use when they connect to the server for this net service name. By default, TCP/IP is chosen (see Figure 4.29). The list of protocols depends on your platform. Click the Next button to continue.
4.5.4.1.3. Choosing the Hostname and the Port
This step depends on the protocol you chose in the previous step. If you chose TCP/IP, you are prompted for the hostname and the port number. The hostname is the name of the machine on which the listener process is running. The port number is the listening location for the listener. The default port is 1521 (see Figure 4.30).
Figure 4.29. Choosing a network protocol
Figure 4.30. Choosing a hostname and a port
4.5.4.1.4. Choosing the Service Name
The next step is to define the service name. For Oracle 10g, the service name does not have to be the same as the ORACLE_SID because a database can have multiple service names. In Oracle 10g, the service name is normally the same as the global database name. This is the service name that is supplied to the listener, so the listener has to be listening for this service. You can also choose whether this service is for Oracle8i or later databases or Oracle8 and previous databases. You can also select the connection type from one of these choices:
Database Default
Shared Server
Dedicated Server
Figure 4.31 shows an example of the Oracle Net Manager service name screen.
4.5.4.1.5. Testing the Net Service Name Connection
The last step is to test the net service name and verify that all the connection information entered is correct. Click the Test button to test the network connection. Figure 4.32 displays an example of the Oracle Net Manager test network connection screen.
NOTE
By default, the test connection tries to connect to the database with a username of scott and the password tiger. If your connection fails, check to see if you have a scott/tiger user. You can change which login to test with by clicking the Change Login button in the test connection screen. You can also create the user scott by running a script called scott.sql located in the $ORACLE_HOME/ rdbms/admin directory on Unix or %ORACLE_HOME% dbmsadmin on Windows.
Figure 4.31. Choosing the service name
Figure 4.32. The Oracle Net Manager test network connection screen
If everything is correct, you should see a result similar to Figure 4.33.
After you complete all this, save your changes by choosing File 
Save Network Configuration. This creates and saves the tnsnames.ora file.
4.5.4.2. Contents and Structure of the tnsnames.ora File
Here is an example of the tnsnames.ora file:
# tnsnames.ora Network Configuration File:
D:oracleora10gNETWORKADMIN nsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = mweishan-dell)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = ORCL)
)
)Figure 4.33. The Oracle Net Manager tnsnames.ora wizard test result screen
Table 4.6 summarizes the parameters in the tnsnames.ora file.
| Parameter | Description |
|---|---|
| DESCRIPTION | Starts the connect descriptor section of the file. |
| ADDRESS_LIST | Starts a list of all connect descriptor address information. |
| ADDRESS | Specifies the connect descriptor for the net service name. |
| PROTOCOL | Specifies the protocol used, such as TCP/IP. |
| HOST | Specifies the name of the machine on which the listener is running. An IP address can also be specified in TCP/IP. |
| PORT | Specifies the listening location of the listener specific to TCP/IP. |
| CONNECT_DATA | Starts the services section for this net service name. |
| SERVICE_NAME | Replaces the SID parameter from older releases of Oracle. Defines which service to connect to, which can be the same as the ORACLE_SID or the global database name. Databases can now be referred to by more than a single service name. |
4.5.4.3. Configuring Localnaming Using Enterprise Manager
You can also use Oracle Enterprise Manager to configure localnaming. You do so from the Net Services Administration screen as described in the "Adding a Listener Using Enterprise Manager Database Control" section earlier in this chapter. You will see the screen shown in Figure 4.17. Choose Local Naming from the Administer drop-down list and click Go to open the Local Naming screen, as shown in Figure 4.34.
Figure 4.34. Using Enterprise Manager to configure localnaming
Click the Create button to open the Create Net Service Name page. Here you can enter the Unique Service Name that you want users to use to connect to this Oracle Service. This can also be the Oracle SID. You can select the type of connection to use for this service: dedicated server, shared server, or the database default. The address information also needs to be specified. This includes the protocol, port, and host used by the service being connected to. Click the Add button under addresses to open the Add Address screen to fill in the appropriate information (see Figure 4.35).
On the Create New Service Name page, there is a section to configure failover and load balancing options. The earlier section in this chapter, "Additional Configurations When Using Multiple Listeners" discusses the concepts of failover and load balancing. Five choices are listed under the Connect Time Failover and Load Balancing section. Table 4.7 summarizes these prompts. If you have multiple listeners listening for this service or are using Oracle Connection Manager, you can select from this list. The default is to use the first address only; this is the case where a single listener is being used.
Figure 4.35. The Add Address screen
| Option | Advanced Feature |
|---|---|
| Try each address, in order, until one succeeds. | Failover |
| Try each address, randomly, until one succeeds. | Failover Load Balancing |
| Try one address, selected at random. | Load Balancing |
| Use each address in order until you reach the destination. | Source Routing |
| Use only the first address. | None |
NOTE
Source Routing is used with Oracle Connection Manager. Oracle passes control from the first address listed to the next address and so on until the ultimate destination is reached. Every address listed is used in the case of source routing.
4.5.5. Troubleshooting Client-Side Connection Problems
Connection problems can also occur from the Oracle client. A number of areas affect the ability of a client to connect successfully to the server. The client must be able to contact both the computer on which the Oracle server is located and the listener listening for connections to the Oracle server. The client must also be able to resolve the net service name. Let's take a look at the checks to perform on the client to verify connectivity to the Oracle server and to detect and troubleshoot client-side connection problems. Use the following list to systematically check various aspects of the client connection process:
Verify that the client can contact the server.
Determine the network route that the client is taking to the server.
Verify localnaming configuration files.
Check for multiple-client network configuration files.
Check network file locations.
Check the NAMES.DIRECTORY_PATH parameter.
Check the NAMES.DEFAULT_DOMAIN parameter.
Check the client protocol adapters installed.
Check for any common client-side error codes.
4.5.5.1. Checking Client/Server Contact
Make sure that the client can see the host computer. If you are using TCP/IP, you can attempt to ping the host computer. Simply type ping and the name of the host. If the host is not found, try using the IP address of the host computer instead of the hostname. If this works, the problem may be that your HOSTS file is not correct or your DNS server does not recognize the host computer name. For example, you can ping a computer with the hostname of mweishan-dell as follows:
D:oracleora10gNETWORKADMIN>ping mweishan-dell Pinging mweishan-dell.corp.goprod.net [211.209.111.50] with 32 bytes of data: Reply from 211.209.111.50: bytes=32 time<10ms TTL=128 Reply from 211.209.111.50: bytes=32 time<10ms TTL=128 Reply from 211.209.111.50: bytes=32 time<10ms TTL=128
4.5.5.2. Determining the Network Route that the Client Is Using to Get to the Server
If you are using a TCP/IP network, you can see a summary of the network path or "hops" made from a client to some network destination using the tracert (on Windows) or traceroute (on Unix) utility. This utility is helpful because it displays the route taken by a TCP/IP connection from one location to another. It can help to diagnose situations in which some users are experiencing network problems or slowness while others are not having the problem. It is also helpful if new hardware such as routers have been added to the network and you want to see if the new hardware is being used to route the IP traffic from your client to the server. You and your network team can use this information to ensure that appropriate network routes are being used to connect to databases.
Here is an example of using the traceroute utility:
Cupx001:/home/oracle>traceroute 10.15.9.11
traceroute to 10.15.9.11 (10.19.5.11), 30 hops max, 20 byte packets
1 211.118.131.20 (211.118.131.20) 1 ms 3 ms 0 ms
2 trx001.mweishan.com (10.15.9.11) 1 ms 1 ms 1 msThis example shows a traceroute being performed from a server called Cupx001. The user is attempting to see which network route is being taken to connect to the TCP/IP destination 10.15.9.11. You can see that an initial network hop is made to 211.118.131.20. This is possibly a router in the network. The connection was established in about 1 millisecond, as you can see in the first numeric column.
From there, the TCP/IP packet was sent to our destination of 10.15.9.11. This also was done in about 1 millisecond. Long network delays or many hops could indicate where and why network problems are occurring.
In the following example, we can see an example of using the tracert utility on a Windows platform.
E:>tracert 10.20.0.39 Tracing route to rep02.cgnsmadison.com [10.20.0.39] over a maximum of 30 hops: 1 <1 ms <1 ms <1 ms 211.139.222.26 2 <1 ms <1 ms <1 ms rep02.cgnsmadisono.com [10.20.0.39] Trace complete. E:>
4.5.5.3. Checking Client/Listener Contact
Next, check to see if the client can contact the listener. You can use a utility called tnsping to verify this. The tnsping Oracle utility attempts to contact an Oracle listener. It works similarly to ping in that you can enter a net service name and the number of times to contact the listener. This utility verifies that the client can contact the listener. However, it does not verify that the client can actually connect to the Oracle server. You can also specify a number of attempts. In the following example, two attempts are made to contact the ORCL database:
D:oracleora10gNETWORKADMIN>tnsping orcl 2 TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 10-JUN-2004 10:18:05
Copyright (c) 1997, 2003, Oracle. All rights reserved. Used parameter files: D:oracleora10g etworkadminsqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = mweishan-dell)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = ORCL))) OK (20 msec) OK (10 msec)
The tnsping utility shows how long the round-trip took to contact the listener. This information can also assist you in uncovering the connection problem, as discussed in the following sidebar.
4.5.5.4. Verifying Localnaming Configuration Files
If the client is using the localnaming method for net service name resolution, check the entries in the tnsnames.ora file. Make sure that the entries are syntactically correct and that there is an entry for the net service name. Also make sure that the protocol is correct.
4.5.5.5. Looking for Multiple Client Network Configuration Files
Make sure that all copies of these files are identical. Normally there should be only one copy of the client-side networking files such as tnsnames.ora and sqlnet.ora. In some situations, such as when you are using other Oracle tools (such as Oracle Developer), these products are installed on a client in a separate ORACLE_HOME directory and have their own copies of the network files. This can make it necessary for a client to have more than one copy of the networking files.
4.5.5.6. Checking Network File Locations
One of the most common problems encountered is clients moving network files and not setting the TNS_ADMIN environmental variable to the new file location. Oracle expects the tnsnames.ora and sqlnet.ora files to be in the default location. If it cannot locate the files and you have not set TNS_ADMIN, you receive an ORA-12154 error message. You also receive this error if the supplied net service name is invalid. The following codes shows an example of this error message:
D:oracleora10gNETWORKADMIN>sqlplus scott/tiger@ora SQL*Plus: Release 10.1.0.2.0 - Production on Thu Jun 10 10:21:22 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. ERROR: ORA-12154: TNS:could not resolve the connect identifier specified
NOTE
If you decide to move network files, be sure to set the TNS_ADMIN environmental variable to the location of the files. Oracle first searches the default location for the files and then searches the TNS_ADMIN location for the files.
4.5.5.7. Checking NAMES.DIRECTORY_PATH
Make sure that the client has the proper names resolution setting. The NAMES.DIRECTORY_PATH parameter in the sqlnet.ora file controls the order in which the client resolves net service names. If the parameter is not set, the default is localnaming, OID, and then hostnaming.
If this parameter is set incorrectly, the client may never check the appropriate names resolution type. For example, if you are using localnaming and the parameter is set to HOSTNAMES, the tnsnames.ora file will never be used to resolve the net service name. You will receive an ORA-12154 "Cannot Resolve Connect Identifier Specified" error message.
4.5.5.8. Checking NAMES.DEFAULT_DOMAIN
NAMES.DEFAULT_DOMAIN is another common error. It was more common in older releases of Oracle because the parameter defaulted to the value WORLD. Check the client sqlnet.ora file to see if the parameter is set. If the parameter has a value and you are using unqualified net service names, the parameter value is appended to the end of the net service name. An unqualified service name is a service name that does not contain domain information.
For example, if you entered Sqlplus matt/casey@PROD, and the NAMES.DEFAULT_DOMAIN is set to WORLD, Oracle appends .WORLD to the net service name; as a result, Oracle passes the command as sqlplus matt/[email protected]. You will receive an ORA-12154 "Cannot Resolve Connect Identifier Specified" error message if the service name should not include the .WORLD domain extension. You use this parameter only if you are using a hierarchical naming convention.
4.5.5.9. Checking Client Protocol Adapters
Verify that the appropriate protocol adapters are installed on the client. You can invoke the Universal Installer and check the client setup. Look for the listing of client protocol adapters installed.
4.5.5.10. Checking for Client-Side Error Codes
You should next check for client-side error codes. Here is a summary of some of the common client-side Oracle error messages that you might encounter. They are discussed in detail in the following sections.
ORA-12154 "TNS: could not resolve connect identifier specified" ORA-12198 "TNS: could not find path to destination" ORA-12203 "TNS: Unable to connect to destination" ORA-12533 "TNS: illegal address parameters" ORA-12541 "TNS: No listener"
ORA-12154 Indicates that the client cannot find the service listed in the tnsnames.ora file. Some of the causes of this are described previously, such as the file is not in the proper directory or the TNS_ADMIN variable is not specified or specified incorrectly.
ORA-12198 and ORA-12203 Indicate that the client found an entry for the service in the tnsnames.ora file but the service specified was not found. Check to make sure that the service specified in the tnsnames.ora file actually points to a valid database service.
ORA-12533 Indicates that you have configured the ADDRESS section of the tnsnames.ora file incorrectly. Check to make sure the syntax is correct or re-create the definition using the Oracle Net Manager tool.
ORA-12541 Indicates that the client contacted a server that does not have a listener running on the specified port. Make sure that the listener is started on the server and that the listening port specifications on the client and the server match.