8 Replies Latest reply: Aug 7, 2008 3:15 AM by Laurenz Albe RSS

    How to generate SQL*Net trace with Instant Client

    ddevienne
      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
          Laurenz Albe
          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
            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
              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
                Laurenz Albe
                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
                  BTW Laurenz, congrats for your afterthoughtful win ;-) Nice one! --DD                                                                                                                                                                                   
                  • 6. Re: SOLVED: Re: How to generate SQL*Net trace with Instant Client
                    Laurenz Albe
                    Thanks :^} Seems like Big Brother is watching me...

                    Laurenz
                    • 7. No sqlnet sql trace output for 11g?
                      387084
                      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?
                        Laurenz Albe
                        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