After finishing Server side configuration, Client has to be configured for connections to Server.
It is a DBA's requirement to understand the network needs of the Database, type of connection required and two-tier/multi-tier connectivity.
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. You should only be casually familiar with these two naming resolution options.
Next, we will discuss the following issues:
This method is useful in: Small networks with few Oracle servers to manage. to reduce the amount of configuration work necessary. It saves from configuring on the clients. Prerequisites: Protocol to be used : TCP/IPis Advanced networking features such as Oracle Connection Manager must not be used. 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.
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.
This parameter can be confitured using the Oracle Net Manager. To do this, 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 the configuration is saved, Oracle updates the sqlnet.ora file with the changes made.
The hostnaming and the Oracle Easy Connect Naming methods do not require any client-side configuration files.
TCP/IP connectivity can be checked 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 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.
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.
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 Sam with the password ora10g wants to connect to a database residing on machine machine1 , he enters Sqlplus sam/ora10g@machine1.
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:\winnt\system32\drivers\etc. 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
# Additionally, comments (such as these) may be
# inserted on individual
# lines or following the machine name denoted
# by a '
# For example:
# 220.127.116.11 rhino.acme.com
# source server
# 18.104.22.168 x.acme.com
# x client host 127.0.0.1 localhost 10.2.0.91 mweishan-dell
# Oracle Database Server
HOSTS file If you have a small TCP/IP network and a names resolution method such as DNS is not used or available, you can use the HOSTS file to resolve network service names. Even in larger networks that use DNS or other names resolution methods, the HOSTS file can be a handy tool when troubleshooting connections. For example, if a client is having a connection problem, try configuring a local HOSTS file on the client that points to the IP address of the server to which you want to connect. When a HOSTS file is configured and the hostname is contained within the file, the client can use it for hostname resolution. If you are successful in pinging or connecting to the server using the local HOSTS, chances are the problem lies somewhere in the routing information within one of the network devices on your network. It could be a bad route statement or a mislabeled hostname within the routing table of the network device.
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.
Hostnaming connection process: 1. The client contacts the DNS server or local HOSTS file. 2. The client contacts the Oracle server. 3. 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. 4. The server passes connection information back to the client. 5. The client is now in direct contact with the server process or dispatcher.
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:
# 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.)
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 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.
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 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.
Certain conditions required to be met using the Oracle Easy Connect Naming method: • 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. Examples of connecting 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:
1. Choose Local Profile pane in the Navigator pane. 2. Select Naming from the panel on the right. 3. Select the Methods tab. 4. Select EZCONNECT in the Selected Methods list. You can click the promote arrows to move EZCONNECT to the top of the Selected Methods list. 5. 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)
The Localnaming Method The localnaming method is 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%\network\admin 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 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.
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.
The Oracle Net Manager starts the Net Service Name wizard, which guides you through the process of creating the Net Service Names definition.
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. Click the Next button to continue.
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. The list of protocols depends on your platform. Click the Next button to continue.
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.
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
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.
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%\rdbms\admin on Windows.
After you complete all this, save your changes by choosing File Save Network Configuration. This creates and saves the tnsnames.ora file.
Contents and Structure of the tnsnames.ora File
# tnsnames.ora Network Configuration File: D: \oracle\ora10g\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools. ORCL = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = mweishan-dell)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = ORCL) ) )
|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.|
|PORT||An IP address can also be specified in TCP/IP.|
|CONNECT_DATA||Specifies the listening location of the listener specific to TCP/IP.|
|SERVICE_NAME||Starts the services section for this net 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.|
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. Choose Local Naming from the Administer drop-down list and click Go to open the Local Naming screen.
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.
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.
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.
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, ping a computer with the hostname of "production" as follows:
Pinging mweishan-dell.corp.goprod.net [22.214.171.124] with 32 bytes of data:
Reply from 126.96.36.199: bytes=32 time<10ms TTL=128
Reply from 188.8.131.52: bytes=32 time<10ms TTL=128
Reply from 184.108.40.206: bytes=32 time<10ms TTL=128
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 220.127.116.11 (18.104.22.168) 1 ms 3 ms 0 ms 2 trx001.mweishan.com (10.15.9.11) 1 ms 1 ms 1 ms This 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 22.214.171.124. 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.
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 126.96.36.199
2 <1 ms <1 ms <1 ms rep02.cgnsmadisono.com [10.20.0.39]
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:\oracle\ora10g\NETWORK\ADMIN>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:
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.
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.
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.
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:
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
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.
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.
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/casey@PROD.WORLD. 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.
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.
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.
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.