Oracle Net trace in 11G … or build in ITIL

Oct 11, 2010 / By Yury Velikanov

Tags: , , ,

If you ever had tried to switch Oracle net trace than you know that it is quite straight forward to switch it on. You would say that I need to set the following parameters in the sqlnet.ora file:

TRACE_UNIQUE_CLIENT = on
TRACE_LEVEL_CLIENT=16
TRACE_DIRECTORY_CLIENT = /tmp

It was true up until 11GR2. I hope this post will save you a bit of time. In order to see the trace files in the specified location you should specify yet another parameter:

DIAG_ADR_ENABLED=OFF

Otherwise Oracle will create the trace files depending on type Client/Server in the following directories:

Client – OS Home dir/oradiag_os username/diag/clients/user_oradb/host_number/trace/

RDBMS – ORACLE_BASE/diag/rdbms/db_name/instance_name/trace/

The directories structure is part of 11G ADR – Automatic Diagnostic Repository. It looks like Oracle put a lot of efforts to build in ITIL principles in the RDBMS trace facility. Each trace file is associated with an incident. Incident … Well I think I better stop here as ADR is a big topic for a separate blog post or set of post. The messages I would like to pass to you this time are:

-1- If you don’t find Oracle Net flies in the directories where you expect those just set DIAG_ADR_ENABLED=OFF parameter in the relative sqlnet.ora file
-2- Oracle 11G trace/log functionality has build in ITIL principles. Be ready and aware about new great idea from Oracle DB software architects.

PS There is a utility called “adrci” that is part of 11G installation. This purpose of this utility is to centralize trace files management and help you report these to Oracle Support (or Pythian;).

All the Best in all your efforts,
Yury

Automatic Diagnostic Repository Command Interpreter (adrci) usage example:

[oradb@epa01 trace]$ adrci

ADRCI: Release 11.2.0.1.0 - Production on Mon Oct 11 21:55:19 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

ADR base = "/home/oradb/oradiag_oradb"
adrci> SHOW TRACEFILE
     diag/clients/user_oradb/host_2533336231_76/trace/sqlnet.log
     diag/clients/user_oradb/host_2533336231_76/trace/ora_5589_3086898880.trc
adrci>

6 Responses to “Oracle Net trace in 11G … or build in ITIL”

  • bernard polarski says:

    I never made the link between ITIL and the new incident management construction brought by adrci. I was more on the impression that it was a standardization of incidents reporting aimed at the automation of the process for most Oracle support requests.

  • Yury says:

    Hi Bernard,

    Thank you for following my posts.
    Well IMHO all the terminology that Oracle brings to ADR is alike the ITIL. However I am not an ITIL or ADR expert ether.
    I hope that comment about the trace files location will help somewhere anyway :)

    Regards,
    Yury

    • Yury and all,

      Be aware that keeping the DIAG_ADR_ENABLED=ON (or not specified) can create tons, and I mean gigs and gigs of trace files in local user folders. And therefore can cause some serious space shortage. That is how I discovered about this “feature” myself :(

      Cheers,
      Marc

  • Tim H says:

    I am banging my head trying to get SQL commands logged under 11g. Your tip about turning off DIAG_ADR_ENABLED worked like a charm, but only trace is being recorded, not a log of the SQL commands. My sqlnet.ora has:

    NAMES.DIRECTORY_PATH=(TNSNAMES, EZCONNECT)
    SQLNET.AUTHENTICATION_SERVICES=(NTS)
    LOG_DIRECTORY_CLIENT=C:\temp
    LOG_FILE_CLIENT=oracle_log.txt
    TRACE_DIRECTORY_CLIENT=C:\temp
    TRACE_FILE_CLIENT=oracle_trace.txt
    DIAG_ADR_ENABLED=OFF
    TRACE_UNIQUE_CLIENT = on
    TRACE_LEVEL_CLIENT=16

    Is there something else that I should do to log the SQL?

    Many thanks for any help.

  • Yury says:

    Hello Tim,

    Thank you for following,
    The SQL trace or 10046 (the other name for the SQL trace). Is generated on DB host side. To find out the directory for that trace us the following SQL:

    select
    substr(name,0,40) Name,
    substr(nvl(value,’TXT NULL’),0,100) Value
    from
    V$SYSTEM_PARAMETER2
    where
    lower(name) like lower(‘user_dump_dest’)
    order by name
    /

    PS Let me know if I misunderstand your question.

    Regards,
    Yury

  • vinay says:

    Hi,

    I added the following to sqlnet.ora and the trace are generating. Now even after removing them from the sqlnet.ora. I still see trace files generating. Can you please suggest what to do.

    TRACE_LEVEL_SERVER=16
    TRACE_DIRECTORY_SERVER=
    TRACE_FILE_SERVER=server
    TRACE_TIMESTAMP_SERVER=ON

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>