This discussion is archived
8 Replies Latest reply: Aug 7, 2008 1:15 AM by LaurenzAlbe RSS

How to generate SQL*Net trace with Instant Client

ddevienne Newbie
Currently Being Moderated
I'm testing deployment of a test OCI app using Instant Client Light (ICL) win32 11g (11.1.0.6.0), on a WinXP machine which also has the server installed.

I've copied my exe and dll along with the ICL dll/sym files in a separate dir (C:\deploy), and running at the command line in a shell where no ORA* or TNS* environment variables are defined.

1) I've verified I can connect to the local DB using an EZConnect string (of the form localhost:1521/service_name for the service).

2) I've verified I cannot connect using a TNS alias (since has no tnsnames.ora to resolve the alias)

3) I've verified I can connect using a TNS alias, after I define TNS_ADMIN to have for value the full path to the directory containing the tnsnames.ora defining the alias.

So far, so good. But now I'm trying to generate a SQL*Net trace, so that I can simulate trying to troubleshoot a client problem, and I can't seem to get any trace file generated...

I've read "Net Services Administrator’s Guide", chap 16, and also "Net Services Administrator’s Guide", chap1 "OCI Instant Client" section.

So I've added a C:\deploy\fake_home\NETWORK\ADMIN dir containing a sqlnet.ora containing this:
SQLNET.AUTHENTICATION_SERVICES= (NTS)

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

TRACE_DIRECTORY_CLIENT = C:\deploy\fake_home\traces
TRACE_FILE_CLIENT = MyClient
TRACE_LEVEL_CLIENT = SUPPORT
TRACE_UNIQUE_CLIENT = on

# Same 10MB per file, with a max of 10 files (thus 100MB)
#TRACE_FILELEN_CLIENT = 10240
#TRACE_FILENO_CLIENT = 10
#TRACE_TIMESTAMP_CLIENT = on
I've created the C:\deploy\fake_home\traces directory, and defined ORACLE_HOME to have C:\deploy\fake_home, so that the sqlnet.ora file can be located. I've basically followed what the OCI manual said on 1-20:

If the TNS_ADMIN environment variable is not set, and TNSNAMES entries such as
inst1, and so on, are used, then the ORACLE_HOME variable must be set, and the
configuration files are expected to be in the $ORACLE_HOME/network/admin
directory.

Note that the ORACLE_HOME variable in this case is only used for locating Oracle Net
configuration files, and no other component of Client Code Library (OCI, NLS, and so
on) uses the value of ORACLE_HOME.


My test app can still connect (either using EZConnect or a TNS alias), but no trace file is generated at all in C:\deploy\fake_home\traces.

What am I missing?

Are SQL*Net traces supported with Instant Client Light? Instant Client non-Light?

Any insight would be appreciated. Thanks, --DD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  • 1. Re: How to generate SQL*Net trace with Instant Client
    LaurenzAlbe Journeyer
    Currently Being Moderated
    Why did you install an extra client if there is already a server installed? A client is included with the server.

    You can do Oracle Net Traces with all flavors of Instant Client.

    The sqlnet.ora file you quote looks fine.

    My recommendation is not to set ORACLE_HOME and instead set TNS_ADMIN and point it to the directory containing sqlnet.ora, as recommended by the Instant Client documentation.

    Still, what you did should work.

    There are several pitfalls I have experienced in similar situations:

    Oracle reads sqlnet.ora when oci.dll is first used, not whenever a new connection is established. So you need to stop the process that uses oci.dll (your test app in this case). The safe thing is to reboot.
    That seems to be ok in your case since you can use TNS names.

    Your program needs permission to read sqlnet.ora (which it seems to be able to do since you can use TNS aliases) and to write to the trace directory.

    You can use Process Monitor to find out what happens.

    Yours,
    Laurenz Albe
  • 2. Re: How to generate SQL*Net trace with Instant Client
    ddevienne Newbie
    Currently Being Moderated
    Well, I still can't make it work...

    I started from scratch this morning:
    1) mkdir C:\deploy
    2) Copy my executable (oci202.exe) and support lib (pdgm_oci.dll)
    3) Copy 11gR1 Instant Client files
    4) mkdir traces and config dirs in C:\deploy
    5) create tnsnames.ora and sqlnet.ora in C:\deploy\config and point TNS_ADMIN to that directory

    The full DOS session is below, showing the files/dirs, content of both .ora files, that the app runs fine using either a TCP or IPC connection to the local 11gR1 DB.

    But no traces... All the app is doing on this test is connection, get one handle attribute "CURRENT_SCHEMA" using OCIAttrGet, and disconnecting. That should be enough to get a trace, right?

    At this point I'm at a loss at what I could try next.

    Could anyone more experienced give a similar test a try?

    I'd really like to be able to generate a trace. Thanks, --DD
    Microsoft Windows XP [Version 5.1.2600]
    (C) Copyright 1985-2001 Microsoft Corp.
    
    C:\deploy>title DEPLOY
    
    C:\deploy>dir
     Volume in drive C has no label.
     Volume Serial Number is 84E6-7AE5
    
     Directory of C:\deploy
    
    05/23/2008  08:22 AM    <DIR>          .
    05/23/2008  08:22 AM    <DIR>          ..
    05/20/2008  04:56 PM            86,016 oci202.exe
    05/20/2008  04:56 PM           159,744 pdgm_oci.dll
                   2 File(s)        245,760 bytes
                   2 Dir(s)  10,106,961,920 bytes free
    
    C:\deploy>set ORA
    Environment variable ORA not defined
    
    C:\deploy>set TNS
    Environment variable TNS not defined
    
    C:\deploy>oci202 connect
    Initializing OCI environment in OBJECT mode...
    charset id of AL32UTF8: 873
    Connecting to GOCAD as oci202
    Connecting to Oracle...
    Error: ORA-12541: TNS:no listener
    
    C:\deploy>set TNS_ADMIN=C:\deploy\config
    
    C:\deploy>oci202 connect
    Initializing OCI environment in OBJECT mode...
    charset id of AL32UTF8: 873
    Connecting to GOCAD as oci202
    Connecting to Oracle...
    Connected to 'GOCAD' as oci202
    CURRENT_SCHEMA: null
    Disconnecting and terminating environment...
    
    C:\deploy>oci202 connect -s GOCADIPC
    Initializing OCI environment in OBJECT mode...
    charset id of AL32UTF8: 873
    Connecting to GOCADIPC as oci202
    Connecting to Oracle...
    Connected to 'GOCADIPC' as oci202
    CURRENT_SCHEMA: null
    Disconnecting and terminating environment...
    
    C:\deploy>dir
     Volume in drive C has no label.
     Volume Serial Number is 84E6-7AE5
    
     Directory of C:\deploy
    
    05/23/2008  08:25 AM    <DIR>          .
    05/23/2008  08:25 AM    <DIR>          ..
    10/03/2007  06:48 PM            13,824 adrci.exe
    10/03/2007  06:48 PM             3,363 adrci.sym
    10/03/2007  06:50 PM               325 BASIC_LITE_README
    05/23/2008  08:28 AM    <DIR>          config
    10/03/2007  06:48 PM            29,696 genezi.exe
    10/03/2007  06:48 PM            13,027 genezi.sym
    05/17/2005  12:48 PM         1,060,864 mfc71.dll
    05/09/2005  05:29 AM           348,160 msvcr71.dll
    10/03/2007  06:38 PM           516,096 oci.dll
    10/03/2007  06:38 PM           246,731 oci.sym
    05/20/2008  04:56 PM            86,016 oci202.exe
    09/10/2007  12:57 PM            77,824 ocijdbc11.dll
    09/10/2007  12:57 PM            13,143 ocijdbc11.sym
    10/03/2007  04:03 PM            18,944 ociw32.dll
    10/03/2007  04:03 PM             4,516 ociw32.sym
    07/25/2007  10:47 AM         1,879,924 ojdbc5.jar
    07/25/2007  10:48 AM         1,977,444 ojdbc6.jar
    10/03/2007  05:04 AM         1,388,544 orannzsbb11.dll
    10/03/2007  05:04 AM           295,819 orannzsbb11.sym
    10/03/2007  06:07 PM           569,344 oraocci11.dll
    10/03/2007  06:07 PM               380 oraocci11.dll.manifest
    10/03/2007  06:49 PM           446,291 oraocci11.sym
    10/03/2007  06:48 PM        28,479,488 oraociicus11.dll
    10/03/2007  06:48 PM         2,860,523 oraociicus11.sym
    05/20/2008  04:56 PM           159,744 pdgm_oci.dll
    05/23/2008  08:24 AM    <DIR>          traces
                  24 File(s)     40,490,030 bytes
                   4 Dir(s)  10,066,337,792 bytes free
    
    C:\deploy>type config\tnsnames.ora
    GOCAD =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = GOCAD.PARADIGMGEO.NET)
        )
      )
    
    GOCADIPC =
      (DESCRIPTION =
        (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = IPC)(KEY = gocad-ipc))
          (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
        )
        (CONNECT_DATA =
          (SERVICE_NAME = GOCAD.PARADIGMGEO.NET)
        )
      )
    
    C:\deploy>type config\sqlnet.ora
    SQLNET.AUTHENTICATION_SERVICES= (NTS)
    
    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)
    
    TRACE_DIRECTORY_CLIENT = C:\deploy\traces
    #TRACE_FILE_CLIENT = GocadDB
    TRACE_LEVEL_CLIENT = SUPPORT
    TRACE_UNIQUE_CLIENT = on
    
    # Same 10MB per file, with a max of 10 files (thus 100MB)
    #TRACE_FILELEN_CLIENT = 10240
    #TRACE_FILENO_CLIENT = 10
    #TRACE_TIMESTAMP_CLIENT = on
    
    C:\deploy>dir traces
     Volume in drive C has no label.
     Volume Serial Number is 84E6-7AE5
    
     Directory of C:\deploy\traces
    
    05/23/2008  08:24 AM    <DIR>          .
    05/23/2008  08:24 AM    <DIR>          ..
                   0 File(s)              0 bytes
                   2 Dir(s)  10,066,333,696 bytes free
    
    C:\deploy>
  • 3. SOLVED: Re: How to generate SQL*Net trace with Instant Client
    ddevienne Newbie
    Currently Being Moderated
    C:\deploy>type config\sqlnet.ora
    SQLNET.AUTHENTICATION_SERVICES= (NTS)

    NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

    TRACE_DIRECTORY_CLIENT = C:\deploy\traces
    Turns out the traces were generated after all, but not in $TRACE_DIRECTORY_CLIENT. Instead they were generated in C:\Documents and Settings\$USER\Oracle\oradiag_$USER. To control where the diagnostic info goes to in 11g, in the absence of a ORACLE_HOME, one needs to set ADR_BASE in sqlnet.ora, or disable ADR (new to 11g) by setting DIAG_ADR_ENABLE = false and remove ADR_BASE in sqlnet.ora. Only then does the trace show up in the directory TRACE_DIRECTORY_CLIENT points to.

    No need to say that I didn't find this out on my own ;-) --DD                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  • 4. Re: SOLVED: Re: How to generate SQL*Net trace with Instant Client
    LaurenzAlbe Journeyer
    Currently Being Moderated
    Thanks for sharing.
    I hope I don't have to change to Oracle 11 anytime soon...

    Yours,
    Laurenz Albe
  • 5. Re: SOLVED: Re: How to generate SQL*Net trace with Instant Client
    ddevienne Newbie
    Currently Being Moderated
    BTW Laurenz, congrats for your afterthoughtful win ;-) Nice one! --DD                                                                                                                                                                                   
  • 6. Re: SOLVED: Re: How to generate SQL*Net trace with Instant Client
    LaurenzAlbe Journeyer
    Currently Being Moderated
    Thanks :^} Seems like Big Brother is watching me...

    Laurenz
  • 7. No sqlnet sql trace output for 11g?
    387084 Newbie
    Currently Being Moderated
    I'm trying to get SQLNET client-side tracing working. I've tried what's described in this thread and I'm still getting nothing.

    Here's what my sqlnet.ora file looks like:
    SQLNET.AUTHENTICATION_SERVICES= (NTS)

    # NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

    TRACE_DIRECTORY_CLIENT = C:\temp\oracletrace
    TRACE_LEVEL_CLIENT = SUPPORT
    TRACE_UNIQUE_CLIENT = on

    DIAG_ADR_ENABLE = false

    ## END OF FILE ##

    I'm not getting anything in the directory I specify OR in the c:\users directory on Vista either.

    I don't particularly care where it gets output, I'd just like a down-and-dirty way to write out the trace.

    Any help for this Oracle noob would be greatly appreciated.

    Thanks,
    -Ben Day
    http://blog.benday.com
  • 8. Re: No sqlnet sql trace output for 11g?
    LaurenzAlbe Journeyer
    Currently Being Moderated
    In my experience that usually means that either

    a) the client doesn't use your sqlnet.ora because TNS_ADMIN is not set appropriately in the client's environment

    or

    b) the process does not have the permission to create the trace file in the directory.

    Yours,
    Laurenz Albe