10.1 Testing Connectivity to the Server The most common manifestation of a Net8 problem is the inability to run a program, connect to a database server, and log in to a database. There are many reasons why you might not be able to do that. When faced with such a problem, it's important to follow the physical and logical chain of connectivity, testing each point of failure, until you've isolated the problem. We usually follow a process that looks somewhat like this: -
Review the specific error messages received. -
Test the underlying network connectivity between the client and the server. -
Verify that the net service name is being resolved correctly. -
Test the Net8 connectivity between the client and the Net8 listener running on the server. -
Look in Net8 log files. -
Generate and examine Net8 trace files. You won't need to go through all these steps for each problem. There's no need to generate trace files, for example, if looking at how a net service name is being resolved leads to a solution. When faced with a tough problem, though, it pays to be rigorous and to proceed in a methodical fashion. Keep in mind the path that your connection must takethat's key to solving connectivity problems. 10.1.1 Connectivity-Related Error Messages Pay attention to the error messages that you get, even though sometimes they are frustratingly vague. It would be nice if every time an error occurred, you received an error message that explained exactly what the problem was and told you how to fix it. Unfortunately, life is not quite that good. Still, the error messages that you receive can help you narrow the scope of your investigations. Table 10.1 lists some common error messages that you might receive when trying to connect to a database and discusses the most likely underlying causes. Table 10.1. Common Connectivity-Related Error Messages | Error Message | General Cause | | ORA-12154: TNS:could not resolve service name | You used a net service name that Net8 did not recognize. This is a name resolution problem. The name may not be defined in your tnsnames.ora file. Your Oracle Names server may not be running or it may not have the net service name defined. Your default domain setting could be getting in the way. In general, focus your investigations on the manner and process by which your Net8 client resolves net service names . | | ORA-12541: TNS:no listener | This message indicates that Net8 recognized your net service name, attempted to contact a Net listener, and failed. In response to this message, you should look to see if the Net8 listenerthe one you are trying to contactis actually running. You should also verify that the net service name is pointing to the correct server, and that the protocol address for the net service name matches an address that the listener is monitoring. | | ORA-12538: TNS:no such protocol adapter | This message usually indicates that the definition of a net service name calls for the use of a network protocol that is not installed on your machine. You could, for example, be attempting an SPX connection from a client that supports only TCP/IP. You can also get this message when using an installed protocol, if that protocol was installed subsequent to the installation of Net8. If that's the case, you may need to uninstall and reinstall Net8 in order to get the needed adapter. | | ORA-12514: TNS:listener could not resolve SERVICE_NAME given in connect descriptor | This message indicates that you've contacted a Net8 listener, but the listener is not aware of the database service to which you are connecting. It's possible that no instances of the database service are running. It's possible that your net service name definition specifies the wrong name for the database server. It's also possible that an instance of the service is running, but that it hasn't registered with the Net8 listener. | | ORA-12564: TNS:connection refused | If you're using Connection Manager, this is the message that you'll get if Connection Manager can't complete the next hop. It's not a very helpful message either. Essentially, you've successfully connected to Connection Manager, but for whatever reason, Connection Manager was unable to make contact with the next protocol address in the list. | You can look up the definitions and suggested actions for any Oracle error message in the Oracle8i Error Messages manual. If you don't have access to that document and you are on a Unix or Linux system, you can also use the oerr command as follows : [oracle@donna oracle]$ oerr ora 12154 12154, 00000, "TNS:could not resolve service name" // *Cause: The service name specified is not defined correctly in the // TNSNAMES.ORA file. // *Action: Make the following checks and correct the error: // - Verify that a TNSNAMES.ORA file exists and is in the proper // place and accessible. See the operating system specific manual // for details on the required name and location. // - Check to see that the service name exists in one of the // TNSNAMES.ORA files and add it if necessary. // - Make sure there are no syntax errors anywhere in the file. // Particularly look for unmatched parentheses or stray characters. // Any error in a TNSNAMES.ORA file makes it unusable. See // Chapter 4 in the SQL*Net V2 Administrator's Guide. If // possible, regenerate the configuration files using the Oracle // Network Manager. There are two parameters that you need to pass to oerr . The first is the facility name, and the second is the error number. In this example, we retrieved information about the ORA-12154 error.  | Unfortunately, Oracle does not make the oerr command available on Windows NT systems. | | Chapter 11, talks about some specific causes and solutions for the error messages listed in Table 10.1. The rest of this chapter talks about techniques and features that you can use to find out more details about a specific problem. 10.1.2 The Connectivity Path The more that you know about networking in general, and about Net8 specifically , the easier it is to track down and diagnose a connection problem. One of the key things to keep in mind is that there are a lot of hardware and software components that need to work together for a connection to occur. Those typically involved are illustrated in Figure 10.1. If Connection Manager is involved, things can become even more complex. Figure 10.1. The path to Net8 connectivity  If you can't make a connection to a database, the problem will be in one of the layers or components shown in Figure 10.1. Your diagnostic work then involves testing each layer and component separately until you've isolated the problem. Chapter 1, provides more information about how all the different pieces of Net8 need to come together for a connection to work. In order to know when a specific configuration is incorrect, you need to know what a correct configuration looks like. You should be familiar with how Net8 is configured in your environment. If you're not, earlier chapters in this book provide configuration information that may be helpful. 10.1.3 Testing Network Connectivity If you work from the bottom up with respect to Figure 10.1, one of the first things that you should do is to verify that you have basic network connectivity to the server using the protocol over which you want to communicate. The TCP/IP protocol is by far the most commonly used protocol, and you can test basic TCP/IP connectivity using the ping utility. For example: E:\> ping donna.gennick.org Pinging donna.gennick.org [10.11.12.16] with 32 bytes of data: Reply from 10.11.12.16: bytes=32 time<10ms TTL=255 Reply from 10.11.12.16: bytes=32 time<10ms TTL=255 Reply from 10.11.12.16: bytes=32 time<10ms TTL=255 Reply from 10.11.12.16: bytes=32 time<10ms TTL=255 The ping utility is only useful for testing TCP/IP connectivity. If you are using some other network protocol such as DECnet or SPX, you'll have to use a method appropriate for that protocol. 10.1.4 Verifying Name Resolution Names resolution is the process whereby Net8 takes a net service name that you specify, looks it up in some sort of repository, and matches it with a network protocol, network address, and database service name. The repository may be something as simple as a tnsnames.ora file, or it may be an Oracle Names server or an Oracle Internet Directory server. Whatever the case, if Net8 can't resolve a net service name, you'll receive the annoying "ORA-12154: TNS:could not resolve service name" error. For example: [oracle@donna admin]$ sqlplus system/manager@donna_ded_t SQL*Plus: Release 8.1.6.0.0 - Production on Tue Aug 22 20:01:24 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. ERROR: ORA-12154: TNS:could not resolve service name These errors are usually easy to solve. It's just a matter of your taking the time to rigorously follow the same path that Net8 does in order to resolve the net service name. When you're looking at name resolution, be sure to look at the protocol address and connect data details that are associated with the service name. When multiple name resolution methods are used, it's possible to have the same name defined different ways in different places. It's not only important that a net service name be resolved, it's also important that it be resolved correctly. 10.1.4.1 Naming method and location of configuration files Before you begin to verify that a net service name is defined, be sure that you know the naming method (or methods) that you are using. You can get that information by looking in your sqlnet.ora file, but first you need to know where that file is located. On most systems, the default location for sqlnet.ora is in your $ORACLE_HOME /network /admin directory. This default location can be overridden using the TNS _ ADMIN environment variable. On Windows NT, TNS _ ADMIN may additionally be set in the registry on an Oracle Home-specific basis. See Appendix E, for details. Multiple Homes on Windows NT Locating the correct set of Net8 configuration files ( sqlnet.ora and tnsnames.ora ) can be particularly tricky on Windows NT systems with multiple Oracle Home directories. If you're running an Oracle database instance, an Oracle Portal (formerly WebDB) listener, and Oracle Enterprise Manager on the same box, you'll have an Oracle Home directory for each. This is true at least for current releases of those products. The issue with multiple Oracle Homes on Windows NT is that the binaries associated with a particular Oracle Home will use the Net8 configuration files that are specific to that same home. Thus, you can have a net service name defined in your tnsnames.ora file under your database Oracle Home that allows you to connect to your database using SQL*Plus from the same Oracle Home, and yet still find yourself unable to connect using Enterprise Manager's SQLPlus Worksheet. In that case, you would also need to define the same net service name in the tnsnames.ora file in Enterprise Manager's Oracle Home directory. If you want one common set of Net8 configuration files to be used regardless of which Oracle Home is involved, you can use the TNS_ADMIN environment variable to point all Oracle Homes to the same directory. | Having found the correct set of configuration files, look in the sqlnet.ora file for a NAMES.DIRECTORY_PATH entry. For example: NAMES.DIRECTORY_PATH = (ONAMES, TNSNAMES) NAMES.DIRECTORY_PATH tells you which naming services Net8 will use to resolve a net service name, and in which order. According to the path shown here, Net8 will first attempt name resolution using an Oracle Names server. Then, if that fails, Net8 will look in your tnsnames.ora file. Your job, if faced with a name that can't be resolved, is to look in those places yourself. | | The default setting for NAMES.DIRECTORY_PATH is (TNSNAMES, ONAMES, HOSTNAME). | | 10.1.4.2 Default domain Your default Net8 domain is set using the NAMES.DEFAULT_DOMAIN parameter in sqlnet.ora , and it's appended onto the end of any unqualified net service names that you use. The following line sets the default domain to gennick.org : NAMES.DEFAULT_DOMAIN = gennick.org The default domain setting, or the lack thereof, can sometimes be the cause of a failure to resolve a net service name. Consider the following tnsnames.ora entry: donna_t.gennick.org = (DESCRIPTION = (ADDRESS_LIST = As long as your default domain is gennick.org , you can reference this net service name simply by using donna_t . The gennick.org part is implied . Problems can arise, however, if your default domain is something else, or if the tnsnames.ora entry does not include the domain as expected. One way to eliminate the default domain as a possible problem is to connect using the fully qualified net service name. For example: sqlplus system/manager@donna_t.gennick.org sqlplus system/manager@donna_t. The first example explicitly specifies a domain of gennick.org . The second example uses a trailing period to explicitly specify no domain at all. If you can connect when you explicitly specify a domain matching that in tnsnames.ora , but not otherwise , then you have a mismatch between your default domain and the one in which the net service name has been defined. | | It's not necessary for all your net service names to be defined in the default domain. You just need to be aware of which ones are and which ones aren't. | | 10.1.4.3 tnsnames.ora If you're using tnsnames.ora files to resolve service names, then it's very easy to verify that a name is defined. Just look in the file to see if it's really there. As mentioned before, be sure that you're looking in the correct file. Also be sure to take the default domain into account. Finally, be sure to check that the protocol address and connect data are correct. | | Watch for stray leading spaces in your tnsnames.ora files. A leading space just in front of a net service name will render that name invisible to Net8. | | 10.1.4.4 Oracle Names If you're using Oracle Names, there are two questions that you need to ask. Can you contact the Oracle Names server? Is the net service name that you are using defined? You can answer these questions using the Names Control utility or by using the Net8 Assistant. See Chapter 7, for information on using Net8 Assistant to check the status of a Names server or to query for a net service name definition. From the command line, you can use Names Control (usually invoked using the namesctl command) to query a Names server for a net service name definition. The command in the following example queries for the net service name donna.gennick.org : NAMESCTL> QUERY donna.gennick.org a.smd Total response time: 0 seconds Response status: normal, successful completion Authoritative answer: yes Number of answers: 1 TTL: 1 day Answers: data type is "a.smd" Syntax is ADDR: ...(DESCRIPTION=(SOURCE_ROUTE=OFF)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP) (HOST=donna.gennick.org)(PORT=1523)))(CONNECT_DATA=(SERVICE_NAME=donna.gennick. org)(SRVR=))) The a.smd parameter to the QUERY command tells Names Control that you are querying for a net service name definition. Don't ask us why that particular constant was chosen over something more intuitive and user friendlywe don't know the answer to that question. Your default domain applies when Oracle Names is used just as it does when a tnsnames.ora file is being used. So if your default domain was GENNICK.ORG, you could omit the domain from the QUERY command and specify the following instead: QUERY donna a.smd instead Be sure to check the description returned by Oracle Names for a net service name. You should also make sure that the protocol address (or addresses) and connect data are all defined correctly. 10.1.5 Testing Net8 Connectivity If you have verified the underlying network connectivity, your net service name is being resolved, and you are certain that it's defined correctly, your next step should be to verify that the proper Net8 connectivity exists. For this, you can use Oracle's tnsping utility. 10.1.5.1 tnsping The tnsping utility functions similarly to the Unix ping utility, except that you pass it a net service name rather than a TCP/IP address. In the following example, tnsping is being used to verify connectivity to the service named donna_t : [oracle@donna admin]$ tnsping donna_t TNS Ping Utility for Linux: Version 8.1.6.0.0 - Production on 23-AUG-2000 12:21:22 (c) Copyright 1997 Oracle Corporation. All rights reserved. Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=donna.gennick.org)(PORT=1523)) OK (10 msec) The tnsping utility only verifies contact with the remote listener, which is enough to ensure that the service name is being resolved correctly and that the underlying connectivity is in place. It also verifies that Net8 is working correctly on both nodes. A successful tnsping does not imply that the remote database instance is actually up and running. When you tnsping a net service name, understand that only the first address in the list is contacted. If you are using Connection Manager, that means that only the connection from your client to the Connection Manager instance is tested . To verify connectivity from the Connection Manager node to the next address in the list, you would need to sign on to that node and issue a tnsping command from there. You'd need to create a new net service name specifically for that purpose too, because you would not want that second tnsping to go through the same Connection Manager instance again. 10.1.5.2 tnsping options There are a couple of useful options to the tnsping utility that you should know about. First, it's possible to specify a protocol address instead of a net service name on the tnsping command. This allows you to use tnsping to test connectivity not only to a listener, but also to a Names server or Connection Manager instance. In the following example, tnsping is being used to test connectivity to Connection Manager: $ tnsping '(ADDRESS=(PROTOCOL=TCP)(HOST=JONATHAN.GENNICK.ORG)(PORT=1630))' TNS Ping Utility for Linux: Version 8.1.6.0.0 - Production on 25-AUG-2000 11:15:44 (c) Copyright 1997 Oracle Corporation. All rights reserved. Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=JONATHAN.GENNICK.ORG) (PORT=1630)) OK (20 msec) When you use a protocol address as a tnsping parameter, you may need to enclose that address within quotes. This is because some operating systems assign special meanings to characters in the address such as parentheses, periods, and equal signs. On Linux and Unix systems you'll need quotes, while on Windows NT you can generally get away without them. It's also possible to specify a repeat count to cause tnsping to repeatedly test the connection to a remote listener. For example: [oracle@donna oracle]$ tnsping donna_t 3 TNS Ping Utility for Linux: Version 8.1.6.0.0 - Production on 25-AUG-2000 11:09:12 (c) Copyright 1997 Oracle Corporation. All rights reserved. Attempting to contact (ADDRESS=(PROTOCOL=TCP)(HOST=DONNA.GENNICK.ORG)(PORT=1523)) OK (0 msec) OK (0 msec) OK (10 msec) A repeat count may be specified regardless of whether the first parameter is a net service name or a protocol address. 10.1.6 Verify That the Database Is Open If you've gotten this far and you still can't connect even though the Net8 connectivity looks good, then you should check to be sure that the database service or instance to which you are trying to connect is really up and running. Check to be sure that the database is open and that the service has been properly registered with the listener. 10.1.6.1 Verifying that the database is open Log on to the server and connect without using Net8 in order to verify that a database instance is running, and that it has the database open. For example: [oracle@donna oracle]$ export ORACLE_SID=donna [oracle@donna oracle]$ sqlplus system/manager SQL*Plus: Release 8.1.6.0.0 - Production on Fri Aug 25 11:19:28 2000 (c) Copyright 1999 Oracle Corporation. All rights reserved. ... It's also possible to issue a command such as the following: ps -ef grep sid_name The results will tell you whether an instance is running, but not whether it has the database open or mounted. 10.1.6.2 Verifying that the service is registered To verify that a database service is registered with the listener, you can use the Listener Control utility's SERVICES command. For example: [oracle@donna oracle]$ lsnrctl SERVICES prodlistener LSNRCTL for Linux: Version 8.1.6.0.0 - Production on 25-AUG-2000 11:21:13 (c) Copyright 1998, 1999, Oracle Corporation. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=donna.gennick.org) (PORT=1523))) Services Summary... donna has 2 service handler(s) DEDICATED SERVER established:2 refused:0 LOCAL SERVER DISPATCHER established:0 refused:0 current:0 max:10 state:ready D000 <machine: donna.gennick.org, pid: 553> (ADDRESS=(PROTOCOL=tcp)(HOST=donna.gennick.org)(PORT=1027)) The command completed successfully Here you see that the listener has registered a database service named donna . One thing to be aware of is that the listener does not show the domain that goes with the service name. For that, you'll have to look at the DB_DOMAIN parameter in the instance's parameter file. 10.1.7 Using Log and Trace Files If you've gone through all the steps described so far and still can't resolve a connection problem, then it's time for some more drastic measures. The various Net8 log files sometimes contain helpful information, and you can enable tracing in order to get a lot of detail about what's going wrong with a connection. The next two sections in this chapter talk about both those features. | 
