RelationalDBDesignRelationalDBDesign


Logging | Tracing   « Prev  Next »
Lesson 8 Using log and trace files
ObjectiveAnalyze and troubleshoot network problems.

Using Log | Trace Files

Due to the complexity of network communications, network errors may originate from a variety of sources, for a variety of reasons. If an error occurs, applications such as Net8 and SQL*Forms, which depend on network services from Net8, will normally generate an error message. The purpose of this lesson is to learn how to evaluate Net8 traces and resolve the connectivity problem.
You can look at trace files manually or by using Oracle’s Trace Assistant (trcasst) tool if you are using Net8. The information in the trace files will help you to diagnose and troubleshoot network problems by giving you a better understanding of the following:
  1. Flow of packets between network nodes
  2. Component of Net8 that is failing (listener, MTS, client, TNS)
  3. codes and messages

In general, the end user receives a generic Oracle message such as an ORA-12154 “TNS: could not resolve service name". To an end user, this cryptic message is of no help, and they immediately call the DBA for assistance. In this example, the DBA knows that an ORA-12154 is always caused by a bad entry in the tnsnames.ora file, so the problem should be quickly solved.
The most common Oracle Network Services problem is the loss of the TNS listener process. This occurs when the listener process has crashed or is no longer able to bequeath incoming connections. When large numbers of end-user complain about an inability to connect to the database, the listener should be the first thing to check.
While every situation is different, there are some general rules that can be followed to diagnose a Net8 problem. The following simulation walks you through the steps that you’ll always follow, regardless of the nature of the problem.

Instructions

The first step in diagnosing network problems is to obtain the error number from the end user. If the user did not write it down, ask him or her to attempt to re-connect and copy down the error number. More than 80 percent of the time, the error number will take you to the problem. You can display the error text with the "oerr" utility in Oracle. Click to the right of the command prompt to place your cursor there and type (S1) oerr ora 12154 (S0).
oerr ora 12154
The second step is to try to ping the Oracle server from the client. This can be performed by the DOS ping command or by running the Oracle tnsping utility. Let's try running the tnsping command from a DOS prompt. Click to the right of the command prompt to place your cursor there and type (S1)tnsping dilbert (S0).
If you can connect with tnsping, you need to go to the server and verify that the listener is accepting connections. This is done with a loopback command in UNIX. Click your cursor to the right of the command prompt and type
(S1)sqlplus system/[email protected] (S0).

Oracle Net Services Problems
Now that you have reviewed the steps for resolving Net8 connectivity problems, let’s explore how you can use Oracle’s trace assistant to help read Oracle trace files.