How To Limit Connections Generating Trace when Oracle Net Server Enabled (Doc ID 401134.1)

Last updated on JANUARY 17, 2012

Applies to:

Oracle Net Services - Version: 8.1.5.0.0 to 11.1.0.7   [Release: 8.1.5 to 11.1]
Information in this document applies to any platform.
TRACE_LEVEL_SERVER
Please note this is not an option in 11gR2, due to a change in functionality of the listener code.

Goal

How to limit the number of connection being traced when Oracle Net server tracing is enabled.

There maybe cases when it is not possible to setup Oracle Net server tracing, for example. When the system is in production or under heavy load. For situations like these, we can limit the connections that are server traced to allow a problem to be diagnosed. By using a second TNS Listener, which has the environment variable TNS_ADMIN set, we can point connections to a separate SQLNET.ORA file. Then only connections to this second listener, would be server traced. Allowing normal operations to continue on the primary TNS listener, which are reading the normal SQLNET.ORA file, which does not have tracing enabled.

Restrictions are that the client must connect using SID and not SERVICE_NAME. So that means it will be a dedicated connection model and not a shared server. And this does not work on Windows. Due to the architecture of Oracle in windows, where threads are used instead of processes, there is no possibility of passing environment variables when spawning a thread.

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms