Set up Network Tracing

1. Documentation in Tahiti -> Masters Book List -> Net Services Administrator’s Guide -> 16 Troubleshooting Oracle Net Services -> Tracing Error Information for Oracle Net Services

2. We can enable trace in the LISTENER but can also generate trace customers using the SQL * Net (For SQL * Plus example).

Enabling traces started in the LISTENER. To enable trace in the LISTENER have to modify the listener.ora file. We review the various key parameters that can enable the LISTENER:

     DIAG_ADR_ENABLED_listener_name => Enables repository hierarchy traces and logs (default = ON)
     Based ADR_BASE_listener_name => Directory ADR (default = $ ORACLE_BASE)
     LOGGING_listener_name => Enables logging of LISTENER, not to be confused with traces (default = ON)
     TRACE_LEVEL_listener_name => Level trace: OFF [0] / USER [4] / ADMIN [10] / SUPPORT [16] (default = OFF)
     TRACE_TIMESTAMP_listener_name => Enter a timestamp on each event (default = ON)

The following parameters apply only when we have the DIAG_ADR_ENABLED = parameter OFF

     TRACE_DIRECTORY_listener_name => Directory destination trace file (default = ORACLE_HOME / network / trace)
     TRACE_FILE_listener_name => Name of trace file (default = listener.trc)
     TRACE_FILELEN_listener_name => KB maximum file size (default = unlimited)
     TRACE_FILENO_listener_name => Number of trace files (default = 1)

By default, we DIAG_ADR_ENABLED parameter = ON (but do not specify), then to enable trace TRACE_LEVEL enough to define the parameter. If we want to go to a different trace file that was used by default with ADR, we set the DIAG_ADR_ENABLED = OFF parameter and then specify the directory and file name (TRACE_FILE and TRACE_DIRECTORY parameters).

# Habilitemos traces for LISTENER
# Add the following lines to the file $ ORACLE_HOME / network / admin / listener.ora
TRACE_LEVEL_LISTENER = USER
# Load the configuration to apply the changes LISTENER
lsnrctl reload
# We can obtain the trace file name making the STATUS LISTENER
lsnrctl status | grep “Trace File”

# To disable it, just we have to erase and reload parameter settings LISTENER
lsnrctl reload

You can enable trace without modifying the listener.ora file with the LSNRCTL utility.

# Start the session with the command lsnrctl
lsnrctl
# Activate traces the USER level LISTENER
USER trace LISTENER
# This command is equivalent to “set TRC_LEVEL USER”
# We disabled the traces
OFF trace LISTENER

3. Now we will enable traces for client processes SQL * Net and also for server processes.

# Create two directories to store trace SQL * Net
mkdir -p / u01 / app / oracle / trace / client
mkdir -p / u01 / app / oracle / trace / server
# Add the following lines to the file $ ORACLE_HOME / network / admin / sqlnet.ora
DIAG_ADR_ENABLED = OFF
TRACE_LEVEL_CLIENT = SUPPORT
TRACE_LEVEL_SERVER = SUPPORT
TRACE_DIRECTORY_CLIENT = / u01 / app / oracle / trace / client
TRACE_DIRECTORY_SERVER = / u01 / app / oracle / trace / server
# We open a connection via SQL * Net with SQL * Plus to check traces
sqlplus system @ ocm
# Launched inquiries, eg .: – SELECT SYSDATE FROM DUAL;

# We can review the trace in the directory configured
# First we see traces the client process (PID process 5859 corresponds to my process Sqlplus)
more /u01/app/oracle/trace/client/cli_5859.trc
# Then examine the server process that is created (The process with PID 5891 is the server process)
more /u01/app/oracle/trace/server/svr_5891.trc
# We can find the point where the consultation launched
grep -n date /u01/app/oracle/trace/server/svr_5891.trc

# Remove the lines added to the file $ ORACLE_HOME / network / admin / sqlnet.ora to disable traces
DIAG_ADR_ENABLED = OFF
TRACE_LEVEL_CLIENT = SUPPORT
TRACE_LEVEL_SERVER = SUPPORT
TRACE_DIRECTORY_CLIENT = / u01 / app / oracle / trace / client
TRACE_DIRECTORY_SERVER = / u01 / app / oracle / trace / server
# Remove directories we created
rm rf / u01 / app / oracle / trace / client
rm rf / u01 / app / oracle / trace / server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s