| Lesson 8 | Using log and trace files |
| Objective | Analyze 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:
- Flow of packets between network nodes
- Component of Net8 that is failing (listener, MTS, client, TNS)
- codes and messages
Generic Oracle message
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 will always follow, regardless of the nature of the problem.
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 will 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
ORA-12154: TNS:could not resolve the connect identifier specified
Yes, the Oracle error ORA-12154 still exists and is relevant in current versions of Oracle Database. It is classified as a TNS error and indicates:
This error message means that the Oracle Net Services (TNS) listener could not resolve the connect identifier (service name or alias) provided in the connection attempt. There are several possible causes for this error, including:
To diagnose and resolve ORA-12154, you need to analyze the context of the error occurrence and investigate the potential causes. Here are some steps you can take:
By carefully analyzing the error message and its context, you can identify the cause of ORA-12154 and implement appropriate solutions to resolve the connection issue.
- Incorrect connect identifier: The service name or alias specified in the connection string might be misspelled, contain invalid characters, or refer to a non-existent service.
- Faulty TNS configuration: The TNS listener configuration might be incorrect or incomplete, missing the relevant service registration entry.
- Network issues: Network connectivity problems between the client and the database server might prevent communication and name resolution.
- Security restrictions: Firewall configurations or network access control lists might be preventing the connection attempt.
To diagnose and resolve ORA-12154, you need to analyze the context of the error occurrence and investigate the potential causes. Here are some steps you can take:
- Verify the connect identifier: Double-check the service name or alias used in the connection string for any typos or errors.
- Review the TNS configuration: Check the `tnsnames.ora` file for the corresponding service entry and ensure all parameters are correctly defined.
- Test network connectivity: Confirm that the client can connect to the database server on the desired port using ping or other network tools.
- Analyze firewall rules: Check if any firewall rules are blocking communication between the client and the database server on the designated port.
- Consult the Oracle documentation: Refer to the official Oracle documentation for detailed information about ORA-12154 and troubleshooting strategies.
By carefully analyzing the error message and its context, you can identify the cause of ORA-12154 and implement appropriate solutions to resolve the connection issue.
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/manager@fred (S0).
Oracle Net Services Problems
The following steps outline what you should always do to diagnose a Oracle Network Services problem, regardless of its nature.- 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
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 us try running the tnsping command from a DOS prompt. Click to the right of the command prompt to place
your cursor there and type
tnsping dilbert. - 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
sqlplus system/manager@fred. - Now you have verified that the server listener is accepting connections and that the WINDOWS client is able to ping the server. The last step is to use SQL*Plus on the client to attempt to connect to the server using the tns service name. Click the next button, and we will load SQL*Plus for you.
- Click in the User Name field to place your cursor there and type system. Then click in the Host String field to place your cursor there and type fred. Click in the Password field and we willl enter your password for you.
- Now click OK to connect to the server. This is the end of the simulation.
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.