Logging | Tracing   « Prev  Next »

Lesson 6Enable tracing for Oracle Net components
ObjectiveInvoke tracing for all Oracle components.

Enable tracing for Oracle Net Components

To invoke tracing for all Oracle components in Oracle Database 13c, you can follow these tentative steps. Please note that the actual steps may vary depending on your specific environment and requirements:
  1. Log in as a privileged use: Ensure you have administrative privileges to configure tracing for Oracle components.
  2. Identify the Components: Determine which Oracle components you want to trace. Oracle Database consists of various components like the database instance, listener, and different background processes.
  3. Enable Database Tracing:
    • To enable tracing for the entire database instance, you can set initialization parameters in the database parameter file (init.ora or spfile.ora).
    • Use parameters like `SQL_TRACE`, `TIMED_STATISTICS`, and `STATISTICS_LEVEL` to control various levels of tracing and statistics gathering. For example:

  4. Listener Tracing: To enable tracing for the listener, locate the listener configuration file (typically listener.ora) and add or modify entries for tracing. For example:
  5. Background Process Tracing: If you want to trace specific background processes or components (e.g., PMON, SMON), you may need to use debugging or tracing tools like `trcroute` or `oradebug`. This requires knowledge of the specific process and debugging procedures.
  6. Diagnostic and Tuning Packs: Oracle 13c provides advanced diagnostic and tuning packs, which offer comprehensive tracing and diagnostic capabilities. You may need to license and enable these packs to access their features.
  7. Start/Restart Components: After making configuration changes, you may need to start or restart the Oracle components to apply the tracing settings.
  8. Collect and Analyze Trace Files: Once tracing is enabled, Oracle components will generate trace files in the specified directory. Collect these trace files and use tools like Oracle Enterprise Manager or Oracle Trace Analyzer to analyze the trace data for troubleshooting or performance tuning.
  9. Disable Tracing: After you have collected the necessary trace data and completed your diagnostic or tuning activities, consider disabling tracing to reduce overhead. Simply reverse the changes made in step 3, 4, or 5, as needed.
  10. Documentation: Document the tracing configuration, including parameters, file paths, and any changes made. This documentation is essential for reference and troubleshooting in the future.

Always exercise caution when enabling tracing, as it can generate a significant amount of data and impact performance. Additionally, be aware of licensing requirements for certain diagnostic and tuning features. Consult the Oracle documentation and relevant Oracle support resources for detailed and up-to-date information specific to Oracle Database 13c.

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 Net8 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:


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