Even if it seems that you have configured Oracle server-side components correctly, network errors can still occur that will require troubleshooting. You can experience a connection problem for a variety of reasons:
The client, middle-tier, or Oracle server is not configured properly.
The client cannot resolve the net service name.
The underlying network protocol is not active on the server: for example, the TCP/IP process on the server is not running.
The user enters an incorrect net service name, user ID, or password.
You can diagnose and correct these types of errors.
When a client has a connection problem that is up to you to fix, it is helpful to first gather information about the
situation. Make sure you record the following information:
The Oracle error that the client received.
The location of the client.
Is the client connecting from a remote location, or is the client connected directly to the server?
The name of the Oracle server to which the client is attempting to connect.
Check to see if other clients are having connection problems. If so, are these clients in the same general location?
Ask the user what is failing. Is it the application being used or the connection?
We will now look at the particular network areas to check and the methods used to further diagnose connection problems from the Oracle server. We will also look at the Oracle error codes that will help identify and correct the problems.
You can perform several server-side checks if a connection problem occurs. Before running such checks, be sure that the machine is running, that the Oracle server is available, and that the listener is active. In the following sections, we’ll summarize the checks to perform on the server.
Make sure that the server machine is active and available for connections. On some systems, it is possible to start a system in a restricted mode that allows only supervisors or administrators to log in to the computer. Make sure that the computer is open and available to all users. On a TCP/IP network, you can use the ping utility to test for connectivity to the server. Here is an example of using ping to test a network connection to a machine called matt:
C:\users\default>ping matt Pinging cupira03.cmg.com [10.69.30.113] with 32 bytes of data: Reply from 10.69.30.113: bytes=32 time=10ms TTL=248 Reply from 10.69.30.113: bytes=32 time=10ms TTL=248 Reply from 10.69.30.113: bytes=32 time<10ms TTL=248 Reply from 10.69.30.113: bytes=32 time=10ms TTL=248 The reply indicates that the machine can be seen on the network.
Make sure that the database is running. Connect to the Oracle server and log in to the database using a tool such as SQL*Plus. First attempt a local connection, which does not use the Oracle listener.
To connect to the Oracle server using a local connection, set your ORACLE_SID environmental variable to the name of the Oracle instance that you want to connect to. Then, attempt to connect to SQL*Plus.
The following example is a connection sequence on Windows that fails because the database is not running. For example, if the database that you are attempting to connect to is named MJW, you can use the following code example in a Windows environment for your test:
D:\oracle\ora10g\BIN>sqlplus system/manager SQL*Plus: Release 10.1.0.2.0 Production on Thu Jun 10 10:08:16 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. ERROR: ORA-01034: ORACLE not available ORA-27101: shared memory realm does not exist An ORA-01034 error indicates that the Oracle instance is not running. You need to start the Oracle instance. The ORA- 27101 error indicates that no instance is currently available to connect to for the specified ORACLE_SID.
You can open a database in restricted mode. This means that only users with restricted mode access can use the system. This is not a networking problem, but it will lead to clients being unable to connect to the Oracle server.
Here is an example of a connection that fails because the user does not have the restricted session privilege.
D:\>sqlplus scott/tiger@ORCL SQL*Plus: Release 10.1.0.2.0 Production on Thu Jun 10 10:09:19 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. ERROR: ORA-01035: ORACLE only available to users with RESTRICTED SESSION privilege As we can see, the user Scott is attempting to connect to the ORCL service. The error message tells us that user Scott does not have the restricted session privilege and cannot log in until the DBA either grants this privilege to Scott or takes the database out of restricted session mode.
Make sure that the user attempting to establish the connection has been granted the CREATE SESSION privilege to the database. This privilege is needed for a user to connect to the Oracle server. If the client does not have this privilege, you must grant it to the user.
To do so, follow this example:
D:\oracle\ora10g\BIN>sqlplus matt/matt SQL*Plus: Release 10.1.0.2.0 Production on Thu Jun 10 10:09:19 2004 Copyright (c) 1982, 2004, Oracle. All rights reserved. ERROR: ORA-01045: user MATT lacks CREATE SESSION privilege; logon denied Here is an example of how you can grant the CREATE SESSION privilege to a user: SQL> grant create session to matt; Grant succeeded SQL>
In this example, the DBA has granted the CREATE SESSION privilege to user Matt. Matt now has the ability to make a connection to the database.
Server-Side Network Checks After you validate that the server where the database is located is up and available and you verify that the user has proper privileges, begin checking for any underlying network problems on the server. In the following sections, we will detail some of the common areas of the server to check when you are experiencing connection problems.
Check Listener Make sure that the listener is running on the Oracle server. Make sure that you check the services for all the listeners on the Oracle server; you can use the lsnrctl status command to do this.
The following command shows the status of the default listener named LISTENER:
Also check the services for which the listener is listening. You must see the service to which the client is attempting to connect. If the service is not listed, the client may be entering the wrong service, or the listener may not be configured to listen for this service.
Check GLOBAL_DBNAME If the client is using the hostnaming method, make sure that the GLOBAL_DBNAME parameter is set to the name of the host machine. You can find this parameter in the service definition of the listener.ora file. Verify the setting by reviewing the listener.ora configuration. In the following sample code, we can see that the GLOBAL_DBNAME parameter has been set to mweishan-dell.
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = mweishan-dell) – machine listener is on (ORACLE_HOME = d:\oracle\ora10g) (SID_NAME = orcl)
Check Listener Protocols Check the protocols for which the listener is configured. This is displayed by the lsnrctl services command. You can see an example of this command in the section, "Listing the Services for the Listener," earlier in this chapter. Make sure that the protocol of the service matches the protocol the client is using when requesting a connection. If the client is requesting to connect with a protocol that the listener is not listening for, the user will receive an ORA-12541 "No Listener" error message.
Check Server Protocols Make sure that the underlying network protocol on the server is active. For systems that run TCP/IP, you can attempt to use the ping command to ping the server. This will verify that the TCP/IP daemon process is active on the server. You can also check this by verifying the services on Windows or using the ps command on Unix. An example of the ping command can be found earlier in this chapter in the section "Check Server Machine."
Check Server Protocol Adapters Make sure that the appropriate protocol adapters are installed on the server. On most platforms, you can invoke the Oracle Universal Installer program and check the list of installed protocols. On Unix platforms, you can use the adapter utility to ensure that the appropriate adapters are linked to Oracle. The following example shows how to run this utility, which is located in the $ORACLE_HOME/bin directory. The following adapters summarize all of the protocol adapters that have been installed as part of this Oracle installation. You can see that we have installed four types of adapters. [root@localhost] ./adapters oracle Net protocol adapters linked with oracle are: BEQ IPC TCP/IP RAW Net Naming Adapters linked with oracle are:
Oracle TNS Naming Adapter Oracle Naming Adapter Advanced Networking Option/Network Security products linked with oracle are: Oracle Security server Authentication Adapter If the required protocol adapter is not listed, you have to install the adapter. You can do so by using the Oracle Installer, installing the Oracle Net Server software, and choosing the appropriate adapters during the installation process.
Check for Connection Timeouts If the client is receiving an ORA-12535 or an ORA-12547 error message, the client is timing out before a valid connection is established. This can occur if you have a slow network connection. You can attempt to solve this problem by increasing the time that the listener will wait for a valid response from the client; simply set the INBOUND_CONNECT_TIMEOUT parameter to a higher number. This is the number of seconds that the listener waits for a valid response from the client when establishing a connection.
Oracle Net Logging and Tracing on the Server If a network problem persists, you can use logging and tracing to help resolve it. Oracle generates information into log files and trace files that can assist you in tracking down network connection problems. You can use logging to find out general information about the success or failure of certain components of the Oracle network. You can use tracing to get in-depth information about specific network connections. By default, Oracle produces logs for clients and the Oracle listener. You cannot disable client logging.
Logging records significant events, such as starting and stopping the listener, along with certain kinds of network errors. Errors are generated in the log in the form of an error stack. The listener log records information such as the version number, connection attempts, and the protocols for which it is listening. You can enable logging at the client, middle-tier, and server locations.
Use Tracing Sparingly Use tracing only as a last resort if you are having connectivity problems between the client and server. Complete all the server-side checks described earlier before you resort to tracing. The tracing process generates a significant amount of overhead, and, depending on the trace level set, it can create some rather large files. This activity will impede system I/O performance because of all the information that is written to the logs, and if left unchecked, it could fill your disk or file system.
To ensure unnecessary growth of trace files and consequent filling up of the disk where tracing is being collected, periodically ensure that the trace parameters are not turned on. If so, turn them off.
Tracing, which you can also enable at the client, middle-tier, or server location, records all events that occur on a network, even when an error does not occur. The trace file provides a great deal of information that logs do not, such as the number of network round-trips made during network connection or the number of packets sent and received during a network connection. Tracing enables you to collect a thorough listing of the actual sequence of the statements as a network connection is being processed. This gives you a much more detailed picture of what is occurring with connections that the listener is processing.
Use the Oracle Net Manager to enable most logging and tracing parameters. Many of the logging and tracing parameters are found in the sqlnet.ora file. Let’s take a look at how to enable logging and tracing for the various components in an Oracle network.
Server Logging By default, the listener is configured to enable the generation of a log file. The log file records information about listener startup and shutdown, successful and unsuccessful connection attempts, and certain types of network errors. By default, the listener log location is $ORACLE_ HOME/network/log on Unix and %ORACLE_HOME%\network\log on Windows. The default name of the file is listener.log.
Information in the listener.log file is a fixed-length, delimited format with each field separated by an asterisk. If you
want to further analyze the information in the log, you can load the data into an Oracle table using a tool such as
SQL*Loader. Notice in the following sample listing that the file contains information about connection attempts, the
name of the program executing the request, and the name of the client attempting to connect. The last field
contains a zero if a request was successfully completed.
TNSLSNR for 32-bit Windows: Version 10.1.0.2.0 Production on 27-APR-2004 16:05:13
Copyright (c) 1991, 2004, Oracle. All rights reserved.
Log messages written to D:\oracle\ora10g\network\log\listener.log
Trace information written to
Trace level is currently 0
Started with pid=2260
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=mweishandell.corp.goxroads.net)(PORT=1521)))
TIMESTAMP * CONNECT DATA [* PROTOCOL INFO] * EVENT [* SID] * RETURN CODE
27-APR-2004 16:05:17 * (CONNECT_DATA=(CID=(PROGRAM=)(HOST=)(USER=mweishan))(COMMAND=status)
(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=168821248)) * status * 0
27-APR-2004 16:05:18 *
(CONNECT_DATA=(CID=(PROGRAM=)(HOST= jdbc )(USER=))(SERVICE_NAME=or cl)) * (ADDRESS=(PROTOCOL=tcp)
(HOST=188.8.131.52)(PORT=2021)) * establish * orcl * 12514
TNS-12514: TNS:listener does not currently know of service requested in connect descriptor
As mentioned earlier, tracing gathers information about the flow of traffic across a network connection. Data is transmitted back and forth in the form of packets. A packet contains sender information, receiver information, and data. Even a single network request can generate a large number of packets.
In the trace file, each line file starts with the name of the procedure executed in one of the Oracle Net layers and is followed by a set of hexadecimal numbers. The hexadecimal numbers are the actual data transmitted. If you are not encrypting the data, sometimes you will see the actual data after the hexadecimal numbers. Each of the Oracle Net procedures is responsible for a different action. The code type of each packet depends on the action being taken. All the packet types start with NSP. Here is a summary of the common packet types:
|Packet Keyword||Packet Type|
If you are doing server-to-server communications and have a sqlnet.ora file on the server, you can enter information in the Server Information section located on the Tracing tab from the Profile screen in Oracle Net Manager tracing. This provides tracing information for server-to-server communications.
Several numeric codes are also used to help diagnose and troubleshoot problems with Oracle Net connections. These codes can be found in the trace files. Here is an example of a line from the trace file that contains a code value: nspsend: plen=12, type=4
Here is a summary of the numeric codes that you could encounter in a trace file:
|7||Null, empty data|
Server Tracing can be enabled from the same Oracle Net Manager screens shown earlier. Simply click the Tracing
Enabled radio button. The default filename and location is $ORACLE_HOME/ network/trace/listener.trc in Unix and
%ORACLE_HOME%\network\trace\listener.trc on Windows. You can set the trace level to OFF, USER, ADMIN, or
SUPPORT. The USER level detects specific user errors. The ADMIN level contains all the user-level information along
with installation-specific errors. SUPPORT is the highest level and can produce information that might
be beneficial to Oracle Support personnel. This level also can produce large trace files.
The following listing shows an example of a listener trace file:
nsglhfre: Deallocating cxd 0x4364d0.
nsglma: Reporting the following error stack:
TNS-01150: The address of the specified listener name is incorrect TNS-01153: Failed to process string:
nsrefuse: entry nsdo: entry
nsdo: cid=0, opcode=67, *bl=437, *what=10, uflgs=0x0, cflgs=0x3
nsdo: rank=64, nsctxrnk=0
nsdo: nsctx: state=2, flg=0x4204, mvd=0 nsdo: gtn=152, gtc=152, ptn=10, ptc=2019 nscon: entry
nscon: sending NSPTRF packet nspsend: entry
nspsend: plen=12, type=4
ntpwr: entry ntpwr: exit
You can tell which section of the Oracle Net the trace file is in by looking at the first two characters of the program names in the trace file. In the previous example, nscon refers to the network session (NS) sublayer of Oracle Net. A message is being sent back to the client in the form of an NSPTRF packet. This is a refuse packet, which means that the requested action is being denied.
You see the Oracle error number embedded in the error message. In the previous example, a TNS-01153 error was generated. This error indicates that the listener failed to start. It also shows the line of information on which the listener is failing. This error could be the result of a problem with another process listening on the same location, or it could be a syntax problem in the listener.ora file. Basically, this error message states that a syntax error has occurred because the protocol was specified as TC and not TCP. In addition to this error, there are some more recent ones. The most recent errors are at the bottom of the file.
The following example shows a section of the listener.ora file with the logging and tracing parameters enabled:
# D:\ORACLE\ora10g\NETWORK\ADMIN\LISTENER.ORA Configuration
# Generated by Oracle Oracle Net Manager
TRACE_LEVEL_LISTENER = ADMIN TRACE_FILE_LISTENER = LISTENER.trc
TRACE_DIRECTORY_LISTENER = D:\Oracle\ora10g\network\trace LOG_DIRECTORY_LISTENER =
D:\Oracle\ora10g\network\log LOG_FILE_LISTENER = LISTENER.log
The following table summarizes the meaning of each of these parameters.
|TRACE_LEVEL_LISTENER||Turns tracing on and off. The levels are OFF, USER, ADMIN, and SUPPORT. SUPPORT generates the greatest amount of data.|
|TRACE_FILE_LISTENER||The name of the trace file.|
|TRACE_DIRECTORY_LISTENER||The directory where trace files are written.|
|LOG_DIRECTORY_LISTENER||The directory where log files are written.|
|LOG_FILE_LISTENER||The name of the listener log file.|