How to enable listener logging and tracing

Enable listener logging and tracing helps you to identify the network problems and troubleshooting connection problems in oracle.It also help to identify when a client is connected to oracle database.

Enabling Logging and Tracing in listener.ora:

1)Putting entry in listener.ora
go to location for ex:$cd $ORACLE_HOME/network/admin
vi listener.ora
2)Put an entry of LOGGING_LISTENER for logging and TRACE_LEVEL_LISTENER for tracing.

TRACE_FILE_LISTENER=listenertrace.trc (The destination file for the trace file)
TRACE_DIRECTORY_LISTENER=/home/oracle (The destination directory for the trace file)
LOGGING_LISTENER=on
TRACE_LEVEL_LISTENER=support

TRACE_LEVEL:It specifies the level of detail the trace facility records for the listener.The trace level value can either be a value within the range of 0 (zero) to 16 (where 0 is no tracing and 16 represents the maximum amount of tracing) or a value of off, admin, user, or support.

i))off (equivalent to 0) provides no tracing.

ii))user (equivalent to 4) traces to identify user-induced error conditions.

iii)admin (equivalent to 6) traces to identify installation-specific problems.

iv)support (equivalent to 16) provides trace information for troubleshooting information for Oracle Support Services.

c)Reload The Listener:
exp:$lsnrctl reload

TROUBLESHOOTING:

Format of the Listener Log Audit Trail:
---------------------------------------------
The audit trail formats text into the following fields:

Timestamp * Connect Data [* Protocol Info] * Event [* SID | Service] * Return Code


Properties of the audit trail are as follows:

-Each field is delimited by an asterisk (*).
-Protocol address information and service name or SID information appear only when a connection is attempted.
-A successful connection or command returns a code of zero.
-A failure produces a code that maps to an error message.

With the return code you can be able to see which type of error and when it occurs.

Audit Trail information can be used to view trends and user activity by first storing it in a table and then collating it into a report format.

Using trcasst Assistant to examine trace files

With trcasst examine the trace file, for ex:

trcasst trace_file_name_here.
For example to see statistics we can use,

trcasst -s /home/oracle/mytracefile.trc