Oracle Net trace in 11G … or build in ITIL

Posted in: Technical Track

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>

Interested in working with Yury? Schedule a tech call.

About the Author

Yury is a nice person who enjoys meeting and working with people on challenging projects in the Oracle space. He started to work as an Oracle DBA 14 years ago (1997). For the past 10 years (since 2001) his main area of expertise is Oracle e-Business Suite. Yury is an OCP 7,8,9,10g and OCM 9i,10g. He is a frequent presenter at Oracle related conferences such as Hotsos, UKOUG and AUOUG. Yury is a socially active person. Apart from Social Media (Twitter, Bloging, Facebook) he is the primary organizer of Sydney Oracle Meetup group (250 people). So if you happen to be in Sydney (Australia) drop Yury a message and stop by at one of his Meetups.

6 Comments. Leave new

bernard polarski
October 14, 2010 2:28 am

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.

Reply

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

Reply

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

Reply

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.

Reply

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

Reply

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

Reply

Leave a Reply

Your email address will not be published. Required fields are marked *