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:
[[email protected] 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 Comments. Leave new
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.
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
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.
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
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