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 Oracle Network Services and SQL*Forms, which depend on network services from Oracle, will normally generate an error message. The purpose of this lesson is to learn how to evaluate "Oracle Network Services" 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. Components of Oracle Network Services that is failing (listener, Shared Server, client, TNS)
3. codes and messages

Oracle's Trace Assistant (`trcasst`) is a utility for analyzing Oracle trace files, particularly for debugging and performance optimization.
For modern alternatives and approaches in Oracle 11g R2, Oracle 12c, and later versions, you can consider the following:

1. Oracle Trace File Analyzer (TFA)
   • Description: The Oracle Trace File Analyzer is a comprehensive diagnostic collection and analysis tool. It includes trace file parsing capabilities, and it provides insights into performance issues and database diagnostics.
   • Advantages:
     • It can automatically collect, organize, and analyze trace files, logs, and diagnostic data from various Oracle components.
     • Available as part of the Oracle Database Support Tools Bundle.
   • Compatibility: Supported from Oracle 11g R2 and later, with advanced features in Oracle 12c+.
2. Automatic Diagnostic Repository Command Interpreter (`adrci`)
   • Description: The ADR Command Interpreter is a command-line tool for managing and querying the Automatic Diagnostic Repository (ADR), where Oracle stores diagnostic data, including trace files.
   • Advantages:
     • Simplifies locating and reviewing trace files.
     • Provides filtering and querying capabilities for specific incidents or problems.
   • Usage Example:

     adrci> show tracefile
     adrci> show incident
     adrci> describe tracefile <tracefile_name>
                     

3. SQLT (SQL Tuning Tools)
   • Description: SQLT is a powerful tool provided by Oracle for performance diagnostics and tuning. It can analyze trace files and provide insights into SQL execution plans, statistics, and other details.
   • Advantages:
     • Focuses on SQL performance tuning and optimization.
     • Can integrate with SQL trace files for deeper analysis.
   • Compatibility: Widely used with Oracle 11g and Oracle 12c.
4. Oracle Real-Time SQL Monitoring
   • Description: Available in Oracle Enterprise Manager or via SQL Developer, this tool allows real-time monitoring of SQL execution, often serving as an alternative to manual trace analysis.
   • Advantages:
     • Avoids manual trace file parsing.
     • Provides real-time and post-execution visualizations of SQL performance.
   • Usage:
     • Query via SQL Developer.
     • Use Enterprise Manager for detailed session and SQL monitoring.
5. Oracle Diagnostics Pack (via AWR and ADDM)
   • Description: The Diagnostics Pack, including the Automatic Workload Repository (AWR) and Automatic Database Diagnostic Monitor (ADDM), provides automated performance diagnostics and insights that can often replace manual trace file analysis.
   • Advantages:
     • Comprehensive system-wide analysis.
     • No need for manual trace file review; the data is aggregated and analyzed automatically.
   • Requirements: Requires additional licensing.
6. Third-Party Tools
   • Examples:
     • Quest Spotlight for Oracle: Monitors Oracle databases and provides insights into trace data and performance bottlenecks.
     • Toad for Oracle: Includes features for analyzing and visualizing trace files.

Which to Choose?

• For manual analysis and trace file review: Use adrci or TFA.
• For SQL performance tuning: Use SQLT or Real-Time SQL Monitoring.
• For comprehensive diagnostics: Use AWR/ADDM or TFA.
• For third-party solutions: Consider tools like Quest Spotlight or Toad.

These alternatives reflect Oracle's shift toward integrated and automated diagnostics to streamline performance monitoring and troubleshooting.

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 "Network Services" 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.

 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

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.
Go to the command prompt and type

sqlplus system/manager@fred.

The ORA-12154: TNS: could not resolve the connect identifier specified error still exists for 'cloud-enabled' Oracle databases, just as it does for on-premises databases. This error is related to Oracle Net Services, which are used to establish communication between client applications and the database.
Why It Still Exists in Cloud-Enabled Databases:
Cloud-enabled databases still rely on Oracle Net Services configurations such as "TNSNAMES.ORA", "EZCONNECT", or other connection methods. These configurations are applicable whether the database is hosted on-premises or in the cloud. If the connection configuration is incorrect or incomplete, the error can occur.
Common Causes of ORA-12154 in Cloud-Enabled Environments

1. Misconfigured TNSNAMES.ORA:
   • If using TNSNAMES.ORA, the connect identifier specified in the client application doesn't match an entry in the TNSNAMES.ORA file.
2. Incorrect EZCONNECT Syntax:
   • When using the EZCONNECT method (e.g., sqlplus user/password@hostname:port/service_name), errors in syntax or missing components can trigger this issue.
3. Network Configuration Issues:
   • Firewall rules, network security groups (NSGs), or Virtual Cloud Network (VCN) configurations in the cloud might block connectivity to the database.
4. Missing or Incorrect Wallet Configuration:
   • For secure connectivity using wallets (common in Autonomous Databases or other Oracle Cloud services), missing or improperly configured wallets can result in a failed connection.
5. Incorrect Service Name or Host Information:
   • Cloud-enabled databases often use service names instead of SID. A mismatch here can lead to connection failures.
6. DNS or Host Resolution Issues:
   • If the hostname in the connect identifier cannot be resolved (e.g., due to DNS configuration), this error may occur.

How to Troubleshoot ORA-12154 in Cloud-Enabled Databases

1. Verify the TNS Entry:
   • Check the TNSNAMES.ORA file or equivalent configuration to ensure the connect identifier matches the required format for the database.
2. Test EZCONNECT:
   • Use the EZCONNECT method and ensure all components (hostname, port, service_name) are correctly specified.
3. Check Networking:
   • Ensure that firewall rules, NSGs, and VCNs allow traffic to the database port (typically 1521).
4. Verify Database Details:
   • Confirm that the hostname, port, and service name or SID are correct.
5. Review Oracle Wallet Configuration:
   • If using a wallet, verify its location and configuration using the SQLNET.ORA file.
6. Use Oracle Cloud Console:
   • Check database connection details in the Oracle Cloud console to confirm the correct information.
7. Test with SQL*Plus or Other Tools:
   • Run a direct test connection from the client machine:

     sqlplus user/password@//hostname:port/service_name

If the issue persists after checking these areas, more specific diagnostics or tools like `tnsping` and SQL*Net tracing may be necessary to identify the root cause.

The following steps outline what you should always do to diagnose a Oracle Network Services problem, regardless of its nature.

1. 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.
2. 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.
3. 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.
4. 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.
5. 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.
6. Now click OK to connect to the server. This is the end of the simulation.

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