OCM 11g Preparation - Set up network tracing

Set up network tracing is very important when some client complains "I'm having troubles when trying to connect in the DB and the application logs are inaccessible". It's also important for OCM if we need to find the RC of some network problem.

So, how to enable it? Probably touching the network configuration files listener.ora and sqlnet.ora.

Question: Do I know by heart the parameter name?
Answer: No, but I know by heart how to retrieve it's name in the Official Docs in less than 15 seconds.

What do you need to study:

  • Parameter to enable traceability of the connections coming through the listener (listener.ora)
  • Parameter to enable traceability of all clients / servers connections (sqlnet.ora)
  • The different trace levels to use

Path to Documentation:

Database Administration -> Net Services Reference


Click here to go back to the Main OCM 11g Preparation page.

Have you enjoyed? Please leave a comment or give a 👍!

4 comments

Skip to comment form

    • Udayakumar on February 25, 2016 at 05:08
    • Reply

    Hey buddy, hope you are doing good..

    I am 5 years experience in Oracle DBA 11g and attained OCP and RAC Expert 11g.

    Planned to become OCM by this year end. Following your blogs as reference for my preparation, thank you for the information.

    But I am quite nervous to complete OCM exam.

    1. Hi Udayakumar,

      I'm happy this blog is helping you! I was extremely nervous during my OCM exam, just relax and prepare yourself. Prepare to be very fast in all the topics and I'm sure you'll pass.
      Let me know when this happens!

      Regards,
      RJ

    • Radek on April 14, 2016 at 16:08
    • Reply

    Worth mentioning if you set tracing in listener file you need to bounce listener.
    Be careful when you set tracng on non-deafult listener. TRACE_LEVEL_ will need to be different.

    Also, in documentation, I couldnt find info on the location of the trace files in case you use ADR locations. And it goes as the following:

    For listener.ora:
    ----------------
    ADR_BASE_LISTENER = /u01/app/oracle
    TRACE_LEVEL_LISTENER = SUPPORT

    Listener trace file location:

    $ADR_BASE/diag/tnslsnr//listener/trace/ora__.trc

    You can check it in lsnrctl status:

    Alias LISTENER
    Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
    Start Date 14-APR-2016 16:44:02
    Uptime 0 days 3 hr. 7 min. 24 sec
    Trace Level support
    Security ON: Password or Local OS Authentication
    SNMP OFF
    Listener Parameter File /u01/app/oracle/product/11.2.0/db_1/network/admin/listener.ora
    Listener Log File /u01/app/oracle/diag/tnslsnr/dziunia/listener/alert/log.xml
    Listener Trace File /u01/app/oracle/diag/tnslsnr/dziunia/listener/trace/ora_13825_140629499909952.trc

    or

    LSNRCTL> show trc_file
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=dziunia.localdomain)(PORT=1521)))
    LISTENER parameter "trc_file" set to ora_13825_140629499909952.trc

    For the profile (sqlnet.ora):
    -------------------------------

    TRACE_LEVEL_CLIENT = ADMIN
    TRACE_LEVEL_SERVER = USER
    ADR_BASE = /u01/app/oracle

    Profile trace file location (for the server):

    $ADR_BASE/diag/rdbms///trace

    Example:

    SQL> select * from v$diag_info where name like '%Diag Trace%'

    INST_ID NAME VALUE
    ---------- ---------------------------------------------------------------- --------------------------------------------------
    1 Diag Trace /u01/app/oracle/diag/rdbms/baza2/baza2/trace

    example:
    /u01/app/oracle/diag/rdbms/baza2/baza2/trace/baza2_ora_29181.trc

    Profile trace file location (for the client) - set it only on CLIENT computer. In ths case client is on the server. Just for illustrative purposes:

    $ADR_BASE/oradiag_oracle/diag/clients/user_oracle/host_489049234_80/trace/ora_29180_140584486827840.trc

    1. Yes, good point!
      Thanks,
      RJ

Leave a Reply

Your email address will not be published.