Oracle MVA

Tales from a Jack of all trades

Tracing sqlnet in 11g

with 5 comments

When I was still young, you could easily trace a sqlplus session by adding some parameters to the appropriate sqlnet.ora file e.g.:

TRACE_LEVEL_CLIENT=16
TRACE_DIRECTORY_CLIENT=/home/oracle
TRACE_FILE_CLIENT=client
TRACE_UNIQUE_CLIENT=ON
TRACE_TIMESTAMP_CLIENT=ON

When I tried to trace a session with an 11g client, the trace-file didn’t appear in the expected TRACE_DIRECTORY_CLIENT. After a quick look around, it turned out that the trace went to $ORACLE_BASE/diag/clients/user_oracle/host_$NUMBER/trace. Documentation seems clear, so I figured I was going crazy.

My personal lesson for today was: never ever trust your knowledge when you move to a new version of Oracle. Old-school (Oracle 10) tricks are useless, when Oracle introduces a new sqlnet parameter called DIAG_ADR_ENABLED and sets the default to ON. This setting causes Oracle to ignore the TRACE_*_CLIENT parameters.

What is even more sad is that I was not the only person to fall for this caveat.


While reading up some more about tracing I ended up on this page, which provides a utillity for non intrusive jdbc tracing.

Advertisements

Written by Jacco H. Landlust

August 27, 2009 at 10:23 am

Posted in RDBMS

5 Responses

Subscribe to comments with RSS.

  1. that jdbc tracing link is broken. Can you fix it?

    eric givler

    April 1, 2016 at 2:37 pm

  2. That was certainly quick, thank you. Is there a way to get the binds, *and* to know when the database is performing a commit?

    The reason that I ask is that we have an application recently rewritten with Spring/MyBatis and for some reason, editing a detail record (on one page) and returning to the main page (with a redirect and get), will requery the DB and not see the record. If I check the database, in most cases, it is there. Sometimes it takes 6-15 seconds for it to show up. It’s almost like there is some sort of Cache Flush and Commit happening? If I introduce a thread.sleep(), most of the time it shows the correct data. Albeit, in cases where the commit seems to be taking a long time. The queries from the list page are actually two queries unioned together that use data from local tables and data from a remote reporting database via a dblink.

    I’m not sure where/when the commit is occurring, but my assumption with Spring was that it occurred as part of the call to the DAO since it is not wrapped with a service layer. And the display page should see this data.

    eric givler

    April 1, 2016 at 8:37 pm

    • taking this off the blog into email, that is a little more easier for conversation 🙂

      Jacco H. Landlust

      April 1, 2016 at 9:26 pm

  3. Thanks for DIAG_ADR_ENABLED . I was getting lost trying to figure out where my trace files were going.

    Kevin

    June 15, 2016 at 5:35 pm


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: