Enable tracing for Oracle Net Components

You can enable tracing in Oracle Network Services at various levels, depending on what you're trying to troubleshoot. Here's a breakdown of the common tracing targets and when you might use them:

1. Listener Tracing:
   • Purpose: Diagnose problems with the listener itself, such as:
     • Listener failing to start
     • Clients unable to connect to the listener
     • Issues with listener registration
   • How to enable: Modify the listener.ora file and set the TRACE_LEVEL parameter. You can specify different levels (e.g., ADMIN, SUPPORT, USER) to control the amount of detail in the trace files.
   • Example:

     LISTENER =
       (DESCRIPTION_LIST =
         (DESCRIPTION =
           (ADDRESS = (PROTOCOL = TCP)(HOST = myhost)(PORT = 1521))
           (TRACE_LEVEL_listener = SUPPORT) 
         )
       )
             

2. Shared Server Tracing:
   • Purpose: Troubleshoot problems related to shared server processes (dispatchers and shared server processes), including:
     • Poor performance with shared server connections
     • Errors related to dispatchers
     • Issues with shared server process creation
   • How to enable:
     • Set the TRACE_LEVEL parameter for the dispatcher in the listener.ora file.
     • Use the DBMS_SHARED_POOL package to enable tracing for specific shared server processes.
   • Example in listener.ora:

     SID_LIST_LISTENER =
       (SID_LIST =
         (SID_DESC =
           (SID_NAME = PLSExtProc)
           (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
           (PROGRAM = extproc)
           (TRACE_LEVEL_listener = SUPPORT)  
         )
       )
             

3. TNS Tracing (Client-Side and Server-Side):
   • Purpose: Capture the communication between the client and the server. Helpful for diagnosing:
     • Connection establishment problems (e.g., TNS-12541, ORA-12154)
     • Network latency issues
     • Authentication errors
   • How to enable:
     • Client-side: Set the SQLNET.TRACE_LEVEL parameter in the sqlnet.ora file on the client machine.
     • Server-side: Set the SQLNET.TRACE_LEVEL parameter in the sqlnet.ora file on the database server machine.
   • Example in sqlnet.ora:

     SQLNET.TRACE_LEVEL = SUPPORT
             

Important Notes:

• Trace File Location: Trace files are typically generated in the diag/tnslsnr/<hostname>/listener/trace or diag/rdbms/<dbname>/<instance>/trace directory within the Automatic Diagnostic Repository (ADR).
• Performance Impact: Tracing can have a performance impact, so use it judiciously and disable it when you're finished troubleshooting.
• Security: Trace files may contain sensitive information (like passwords), so protect them accordingly.

Remember to analyze the trace files using tools like `trcasst` (for older versions) or `tkprof` to understand the network interactions and pinpoint the root cause of the problem.

The steps used to invoke tracing are outlined here. Each step is fully described in subsequent sections. The first step is to choose the component to be traced. You may trace a client, a server, or a listener. The steps to enable tracing are very simple:

1. For any component, you can invoke tracing by editing the component configuration file that corresponds to the component traced. The component configuration files are listener.ora, sqlnet.ora or names.ora.
2. Execute or start the component to be traced. If the trace component configuration files are modified while the component is running, the modified trace parameters will take effect the next time the component is invoked or restarted. Instructions for each component follow:
3. Be sure to turn off tracing when you do not need it for a specific diagnostic purpose.

Trace levels
You can set trace levels to provide different amounts of trace information. For each component, there are three levels of trace information:

1. This default setting signifies that no trace output should be generated. The command is:

trace_level_client = off

1. This setting is intended for DBAs who do not have extensive network knowledge. The USER trace level generates only English sentences describing high-level significant networking events. The command is:

trace_level_client = user

1. This setting is intended for network administrators with more knowledge of network terms and concepts. The ADMIN trace level generates all of the USER level statements plus additional information such as TNS addresses and detailed events. The ADMIN level of tracing provides the highest level of detail about Oracle Network Connections. The command is:

trace_level_client = admin

Remember, the trace files are very large. If this parameter is set to USER or ADMIN, the number of large trace files created could interfere with the available disk space on your machine. Therefore, turn on this parameter only for specific reasons and delete the resulting files when they are no longer useful.
Note: If an existing trace file from a previous trace session exists and you need to retain the information in it, you will need to save that file under another name. Otherwise Oracle will overwrite the existing trace file.
Pinging Server If you are having trouble pinging a server, and need a detailed trace, you can manually add the following two tracing parameters to your sqlnet.ora file to perform tracing on the TNSPING utility:

TNSPING.TRACE_LEVEL

TNSPING.TRACE_DIRECTORY

The next lesson discusses the location of the log and trace files on an Oracle server.