16 Troubleshooting Oracle Net Services

Server Diagnostics

Answer the following questions:

• Is any other system (workstation/server) able to connect to the server using Net8?

• Has the server, database, or listener configuration remained the same for some time?

If you answered YES to any of the preceding questions/statements, then skip this section and continue to "Client Diagnostics".

If you are unsure, or answered NO to any of the preceding questions, then continue.

Diagnosing Net8 Services on the server involves the following tasks:

• Task 1: Verify the Database Is Running

• Task 2: Perform a Loopback Test

SQLPLUS system/manager 

Client Diagnostics

At this point, you know the serverside listener works properly, because you could verify at least one of the following statements:

• The database server passed a loopback test, showing that the connection worked.

• Other computers connect also using Net8 Services to this same database.

• Connections from this workstation worked previous to making changes on this computer, such as the installation of a new product or a modification to the network configuration.

To perform diagnostics on the client:

1. Check that you have installed the same protocol support as was installed on the database server.

   On UNIX you can use the ADAPTERS utility to verify protocol support. On the database server, run the adapters 'which oracle' command from $ORACLE_HOME/bin to display the protocol support, naming methods, and security options linked with the oracle executable. The adapters utility displays output similar to the following:

   Oracle Net transport protocols linked with ./oracle are:
   
       IPC
       BEQ
       TCP/IP
       SSL
       RAW
   
   Oracle Net naming methods linked with ./oracle are:
   
       Local Naming (tnsnames.ora)
       Oracle Directory Naming
       Oracle Host Naming
       NIS Naming
   
   Oracle Advanced Security options linked with ./oracle are:
   
       RC4 40-bit encryption
       RC4 56-bit encryption
       RC4 128-bit encryption
       RC4 256-bit encryption
       DES40 40-bit encryption
       DES 56-bit encryption
       3DES 112-bit encryption
       3DES 168-bit encryption
       AES 128-bit encryption
       AES 192-bit encryption
       AES 256-bit encryption
       MD5 crypto-checksumming
       SHA crypto-checksumming (for FIPS)
       SHA-1 crypto-checksumming
       Kerberos v5 authentication
       RADIUS authentication
       ENTRUST authentication
   
   

   On the client, run the adapters command from $ORACLE_HOME/bin to display the configured Oracle protocol support, naming methods, and security options. The ADAPTERS utility displays output similar to the following:

   Installed Oracle Net transport protocols are:
   
       IPC
       BEQ
       TCP/IP
       SSL
       RAW
   
   Installed Oracle Net naming methods are:
   
       Local Naming (tnsnames.ora)
       Oracle Directory Naming
       Oracle Host Naming
       NIS Naming
   
   Installed Oracle Advanced Security options are:
   
       RC4 40-bit encryption
       RC4 56-bit encryption
       RC4 128-bit encryption
       RC4 256-bit encryption
       DES40 40-bit encryption
       DES 56-bit encryption
       3DES 112-bit encryption
       3DES 168-bit encryption
       AES 128-bit encryption
       AES 192-bit encryption
       AES 256-bit encryption
       MD5 crypto-checksumming
       SHA-1 crypto-checksumming
       Kerberos v5 authentication
       RADIUS authentication
       ENTRUST authentication
   

   Note:

   RAW is an internal protocol used by Oracle Net.

   See Also:

   Oracle UNIX operating system-specific Administrator's Reference for further information about the adapters utility

2. Check base connectivity for underlying network transport. Net8 technology depends on the underlying network for a successful connection.

   Protocol	Verify that you can...
   TCP/IP	Use terminal emulation or file transfer utilities, (PING, FTP, TELNET) from the client to the database server.
   Named Pipes	See other computers or servers on the Microsoft network. Ensure that you are able to share drives within the network.

   
   

3. To ensure that both the Net8 foundation layer and the appropriate Oracle protocol support are present, verify that all Net8 Services software for the client has been installed.

4. Ensure that the client computer has the tnsnames.ora and the sqlnet.ora files exist in the correct locations.

   See Also:

   "Localized Configuration File Support"

   If you have any other working client computers connecting to the selected Oracle database, back up your existing files and copy both the working tnsnames.ora and sqlnet.ora files from the working computer onto the non-working client workstations. This eliminates the possibility of errors in the files.

5. Test the Net8 foundation layer.

   See Also:

   "Testing Network Connectivity from the Client"

   Note:

   Do not use the TNSPING utility. The TNSPING utility works like the TCP/IP PING utility and does not create and open a socket, nor does it connect with the listener. It ensures that the listener is present on the database server.

6. If the connection still fails:

   • Use tracing, as described in section "Troubleshooting Network Problems Using Log and Trace Files"

   • Check the Problem/Solution Database Web site at http://support.oracle.com for a specific diagnostics bulletin on the error received

   • Contact Oracle Support Services

Troubleshooting Directory Naming Errors

Directory naming issues associated with connectivity errors such as ORA-12154, ORA-12543, or ORA-12541 for database service or net service name entries in a directory server require analysis of the data. You can analyze the data contained within a directory server with the ldifwrite command line tool.

ldifwrite enables you to convert all or part of the information residing in a directory server to LDAP Data Interchange Format (LDIF). The ldifwrite tool performs a subtree search, including all entries following the specified distinguished name (DN), including the DN itself.

The ldifwrite tool syntax is as follows:

ldifwrite -c net_service_name/database_service -b base_DN -f ldif_file 

The following example writes all the directory naming entries under dc=us,dc=acme,dc=com to the output1.ldi file:

ldifwrite -c ldap -b "dc=us,dc=acme,dc=com" -f output.ldif

Questions to Ask When Troubleshooting Oracle Net Services

Here are some questions to ask yourself when diagnosing a problem:

• Do all computers have a problem, or is it just one?

  If one computer works and another does not, and you are confident that the same software (Oracle and third-party products) is installed, on each computer, swap out the network cables, if they are close enough, to see if the problem moves. If it does move, it indicates that the problem has something to do with the client/server connection and is not local to the PC.

• What kind of links exist between the client and the server, for example, X.25, ISDN, Token Ring, or leased line?

  Sniffers and LAN analyzers are useful for intermittent failing connections or detecting time outs and resent packets. You can also see what side of the conversation is waiting for a response.

Problem Description for TNS-12154

The TNS-12154 error appears when SQL*Net cannot find the alias specified for a connection in the TNSNAMES.ORA file or other naming adapter.

Before attempting to resolve this problem, it is helpful to print out or view both the TNSNAMES.ORA file and the SQLNET.ORA file. Looking at these files at the same time is helpful because references will be made to both.

TNSNAMES.ORA and SQLNET.ORA are located in the default network administration directory <<<on the client machine.>>>

Troubleshooting TNS-12154 on UNIX

Be sure that the TNSNAMES.ORA file and the SQLNET.ORA file resemble the following examples.

DEV1.WORLD =
     (DESCRIPTION =
       (ADDRESS_LIST =
           (ADDRESS =
             (PROTOCOL = TCP)
             (Host = 145.45.78.56)
             (Port = 1521)
           )
       )
       (CONNECT_DATA = (SID = ORCL)
       )
     )

TRACE_LEVEL_CLIENT = OFF
SQLNET.AUTHENTICATION_SERVICES = (NONE)
NAMES.DIRECTORY_PATH = (TNSNAMES)
AUTOMATIC_IPC = OFF

Oracle Net Error Stacks

Log files provide information contained in an error stack. An error stack refers to the information that is produced by each layer in an Oracle communications stack as the result of a network error.

The error stack components are described in Table 16-2.

sqlplus scott/tiger@hrserver.com 

ORA-12543: TNS:Unable to connect to destination

Fatal OSN connect error 12543, connecting to:
 (DESCRIPTION=(CONNECT_DATA=(SID=trace)(CID=(PROGRAM=)
   (HOST=lala)(USER=sviavant)))(ADDRESS_LIST=(ADDRESS=
   (PROTOCOL=ipc)(KEY=trace))(ADDRESS=(PROTOCOL=tcp)
   (HOST=lala)(PORT=1521))))

VERSION INFORMATION:
TNS for SunOS:
Oracle Bequeath NT Protocol Adapter for SunOS:
Unix Domain Socket IPC NT Protocol Adaptor for SunOS: 
TCP/IP NT Protocol Adapter for SunOS:
  Tracing to file: /home/sviavant/trace_admin.trc
  Tns error struct:
    TNS-12543: TNS:unable to connect to destination
    ns main err code: 12541
    TNS-12541: TNS:no listener
    ns secondary err code: 12560
    nt main err code: 511
    TNS-00511: No listener
    nt secondary err code: 61
    nt OS err code: 0

Oracle Net Services Log File Names

Each Oracle Net Services component produces its own log file. Table 16-3 provides the default log file names and lists the components that generate the log files.

Setting Logging Parameters

Parameters that control logging, including the type and amount of information logged, as well as the location where the files are stored, are set in the configuration file of each network component as described in Table 16-4.

This section contains these topics:

• sqlnet.ora Log Parameters

• listener.ora Log Parameters

• cman.ora Log Parameters

• Setting Logging Parameters in Configuration Files

  See Also:

  Oracle Database Net Services Reference for more information about these parameters

Setting Logging Settings During Runtime of Control Utilities

You can set logging during control utility runtime. Setting logging with a control utility does not set parameters in the *.ORA files; the setting is only valid for the session of the control utility:

• For a listener, use the SET LOG_FILE and SET LOG_DIRECTORY commands from the Listener Control utility.

• For an Oracle Connection Manager, use the SET LOG_DIRECTORY, SET LOG_LEVEL, and SET EVENT commands from the Oracle Connection Manager control utility.

  See Also:

  Oracle Database Net Services Reference

Using Log Files

To use a log file to diagnose a network error:

1. Review the log file for the most recent error number you received from the application. Note that this is almost always the last entry in the log file.

2. Starting from the bottom of the file, locate the first nonzero entry in the error report. This is usually the actual cause.

3. If that error does not provide the desired information, review the next error in the stack until you locate the correct error information.

4. If the cause of the error is still not clear, turn on tracing and repeat the statement that produced the error message.

Analyzing Listener Log Files

This section describes what is recorded in the listener log file, including:

• Listener Log Audit Trail Information

• Listener Service Registration Event Information

• Listener Direct Hand-Off Information

Timestamp * Connect Data [* Protocol Info] * Event [* SID | Service] * Return Code

14-JUL-2002 00:29:54 *
(connect_data=(cid=(program=)(host=sales-server)(user=jdoe))(command=stop)
(arguments=64)(service=listener)(version=135290880))
* stop * 0

14-JUL-2002 15:28:58 * 
(connect_data=(service_name=sales.us.acme.com)(cid=(program=)(host=sales-server)
(user=jdoe))) 
* (address=(protocol=tcp)(host=10.10.150.35)(port=41349)) * establish 
* sales.us.acme.com * 0 

03-JUL-2002 16:41:57 * 
(CONNECT_DATA=(CID=(PROGRAM=)(HOST=sales-server)(USER=jdoe))(COMMAND=status)
(ARGUMENTS=64)(SERVICE=LISTENER)(VERSION=153092352)) * status * 0
03-JUL-2002 16:42:35 * <unknown connect data> * (ADDRESS=(PROTOCOL=tcp)(HOST=10.10.150.35)(PORT=53208)) * establish * 
<unknown sid> * 12525
TNS-12525: TNS:listener has not received client's request in time allowed
TNS-12604: TNS: Application timeout occurred

Timestamp * Event *  Instance Name * Return Code

------------------------------- 
14-JUL-2002 15:28:43 * service_register * sales * 0 
14-JUL-2002 15:28:43 * service_register * sales * 0 
14-JUL-2002 15:28:58 * 
(connect_data=(service_name=sales.us.acme.com)(cid=(program=)(host=sales-server)
(user=jdoe))) 
* (address=(protocol=tcp)(host=10.10.150.35)(port=41349)) * establish 
* sales.us.acme.com * 0 
14-JUL-2002 15:38:44 * service_update * sales * 0 
14-JUL-2002 15:38:44 * service_update * sales * 0 
14-JUL-2002 15:48:45 * service_update * sales * 0 
14-JUL-2002 15:48:45 * service_update * sales * 0 
14-JUL-2002 15:50:57 * 
(connect_data=(service_name=sales.us.acme.com)(cid=(program=)(host=sales-server)(u
ser=jdoe))) 
* (address=(protocol=tcp)(host=10.10.150.35)(port=41365)) * establish 
* sales.us.acme.com * 0 
14-JUL-2002 15:51:26 * service_died * sales * 12537 
14-JUL-2002 15:51:26 * service_died * sales * 12537 
14-JUL-2002 15:52:06 * 
(connect_data=(service_name=sales.us.acme.com)(cid=(program=)(host=sales-server)(u
ser=jdoe))) 
* (address=(protocol=tcp)(host=10.10.150.35)(port=41406)) * establish 
* sales.us.acme.com * 12514 
TNS-12514: TNS:listener could not resolve SERVICE_NAME given in connect 
descriptor  
--------------------------------

Timestamp * Presentation * Handoff  * Error Code

21-JUL-2002 10:54:55 * oracle.aurora.net.SALESHttp2 * handoff * 0

WARNING: Subscription for node down event still pending 

Listener completed notification to CRS on start
Listener completed notification to CRS on stop

Analyzing Oracle Connection Manager Logs

Oracle Connection Manager generates four types of log files: one each for its listener, gateway, and CMADMIN processes and one for alerts. The last is a chronological record of all critical errors. In addition to logging critical errors, the alert log captures information about instance startup and shutdown. It also records the value of all configuration parameters at the beginning and end of a session. See Table 16-3 for file name syntax.

The CMADMIN and gateway log files are reproduced here. Table 16-9 explains some of the log entries. Each entry consists of a timestamp and an event. You can configure cman.ora to log events in the following categories:

• Initialization and termination

• Memory operations

• Connection handling

• Process management

• Registration and load update

• Events related to CMADMIN wakeup queue

• Gateway timeouts

• Command processing

• Events associated with connection control blocks

Use the SET EVENT command to specify which events to log.

-------------------------------
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:46:40)(EVENT=Parameter list)
    (listener_address=(address=(protocol=tcp)(host=usunnae16)(port=1574)))
    (aso_authentication_filter=OFF)
    (connection_statistics=ON)
    (log_directory=/home/user/network/admin/log)
    (log_level=support)
    (max_connections=256)
    (idle_timeout=5)
    (inbound_connect_timeout=0)
    (session_timeout=20)
    (outbound_connect_timeout=0)
    (max_gateway_processes=1)
    (min_gateway_processes=1)
    (password=OFF)
    (remote_admin=ON)
    (trace_directory=/home/user/network/admin/log)
    (trace_level=off)
    (trace_timestamp=OFF)
    (trace_filelen=0)
    (trace_fileno=0)
)
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:46:40)(EVENT=Shared Memory Size)
(BYTES=82524))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:46:40)(EVENT=GMON Attributes validated)
(Type=Information))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:46:40)(EVENT=NS Listen Successful)
((ADDRESS=(PROTOCOL=tcp)(HOST=usunnae16)(PORT=55878))))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:46:44)(EVENT=Received command)(CMD=verify
password))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:46:44)(EVENT=Received command)
(CMD=version))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:46:44)(EVENT=Received command)
(CMD=show status))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:46:44)(EVENT=Failed to get procedure id))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:49:12)(EVENT=Received command)(CMD=verify
password))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:49:15)(EVENT=Failed to get procedure id))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:49:29)(EVENT=Received command)(CMD=verify
password))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:49:46)(EVENT=Failed to get procedure id))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:49:50)(EVENT=Received command)(CMD=verify
password))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:49:50)(EVENT=Received command)
(CMD=probe monitor))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:49:50)(EVENT=Received command)
(CMD=shutdown normal))
-------------------------------

-------------------------------
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:46:41)(EVENT=NS Initialised))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:46:41)(EVENT=Memory Allocated)
(BYTES=1024))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:46:41)(EVENT=NCR Initialised))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:46:41)(EVENT=Connected to Monitor))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:46:41)(EVENT=State Change from Empty to 
Init))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:46:41)(EVENT=Memory Allocated)
(BYTES=251904))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:46:41)(EVENT=Memory Allocated)
(BYTES=2048))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:46:41)(EVENT=CCB Initialised))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:46:41)(EVENT=Started Listening))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:46:41)(EVENT=State Change from Init to 
Ready))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:46:47)(EVENT=Housekeeping))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:48:06)(EVENT=Ready)(CONN NO=0))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:48:06)(EVENT=Ready)(CONN NO=0))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:48:07)(EVENT=Housekeeping))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:48:12)(EVENT=Housekeeping))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:48:13)(EVENT=Idle Timeout)(CONN NO=0))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:48:17)(EVENT=Housekeeping))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:48:22)(EVENT=Housekeeping))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:48:25)(EVENT=Ready)(CONN NO=0))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:48:25)(EVENT=Ready)(CONN NO=0))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:48:27)(EVENT=Housekeeping))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:48:30)(EVENT=Idle Timeout)(CONN NO=0))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:48:32)(EVENT=Housekeeping))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:48:37)(EVENT=Housekeeping))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:48:42)(EVENT=Ready)(CONN NO=0))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:48:42)(EVENT=Ready)(CONN NO=0))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:48:42)(EVENT=Housekeeping))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:48:47)(EVENT=Housekeeping))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:48:52)(EVENT=Housekeeping))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:48:57)(EVENT=Housekeeping))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:49:02)(EVENT=Session Timeout)(CONN NO=0))
(LOG_RECORD=(TIMESTAMP=08-MAY-2003 08:49:02)(EVENT=Housekeeping))
-------------------------------

Oracle Net Services Trace File Names

Each Oracle Net Services component produces its own trace file. Table 16-10 provides the default trace file names and lists the components that generate the trace files.

Setting Tracing Parameters

Parameters that control tracing, including the type and amount of information trace, as well as the location where the files are stored, are set in the configuration file of each network component as described in Table 16-11.

This section contains these topics:

• sqlnet.ora Trace Parameters

• listener.ora Trace Parameters

• cman.ora Trace Parameters

• Setting Tracing Parameters in Configuration Files

  See Also:

  Oracle Database Net Services Reference for more information about these parameters