Recently I had to assist in troubleshooting a connection issue from an application middle tier to an Oracle database. The issue I faced immediately was how to turn on tracing for the .Net connection and the sqlnet.ora connection, so that both levels of tracing could be captured for investigation.
To enable tracing at both levels, you need to modify the registry values TraceLevel and TraceOption. These are set to 0 or disabled by default. This is a good thing otherwise we would fill up our hard drive rather quickly.
The registry key is typically located under the HKEY_LOCAL_MACHINE, SOFTWARE, oracle, ODP.NET, then the specific version 2.x or 4.x that your client uses.
Change the values to
The SQL Net tracing is straight forward with the exception of changing where the trace file will be stored as this changed during the 11g release.
In 11g, Oracle changed the default behavior of this with the ADR (Automatic Diagnostics Repository). To turn off the option DIAG_ADR_ENABLED to place the trace file in a location of your choosing. Modify your sqlnet.ora file located in your ORACLE_HOME/network/admin folder, open notepad using the "Run as administrator" option and add the values.
The values documented for the trace level client are 0 for no trace, 4 for user trace, 10 for administration trace, and 16 for Oracle support services trace. This last option provides the most verbose information. Be careful to monitor the file sizes they will grow rapidly on a busy system and fill up your drive.
Be sure to turn these setting back to their original values when you have completed your troubleshooting, otherwise your drive will fill up with trace files.
