Oracle client trace

Da Emigar.
Versione del 22 set 2017 alle 09:14 di WikiSysop (discussione | contributi)
(diff) ← Versione meno recente | Versione attuale (diff) | Versione più recente → (diff)
Jump to navigation Jump to search
La versione stampabile non è più supportata e potrebbe contenere errori di resa. Aggiorna i preferiti del tuo browser e usa semmai la funzione ordinaria di stampa del tuo browser.

How to Perform Client-Side Tracing of Programmatic Interfaces on Windows Platforms [ID 216912.1]   

--------------------------------------------------------------------------------
 
  Modified 18-JUN-2009     Type BULLETIN     Status PUBLISHED   


PURPOSE
-------

To describe how to obtain trace/log level information when trouble-shooting
problems occurring through the use of any of the following programmatic
interfaces to Oracle on a Windows platform:

    1. Oracle ODBC Driver
    2. Oracle Provider for OLE DB
    3. Oracle Objects for OLE (OO4O)
    4. Oracle Data Provider for .NET (ODP.NET)
    5. Oracle Service for Microsoft Transaction Server (ORAMTS)

As all of these interfaces use SQL*Net (also known as Net*8 or Net Services) to
communicate with the database, client-side SQL*Net tracing can be used to
obtain trace/log information on all of the programmatic inferfaces as well.

    6. Client-Side SQL*Net Tracing

  
SCOPE & APPLICATION
-------------------

Developers and general users of front-end and middle-tier applications
encountering errors when using an Oracle or third-party programmatic interface
to connect to an Oracle database.  This information may be requested by Oracle
support when trouble-shooting client-side issues.


TRACING/LOGGING CAPABILITIES BY PROGRAMMATIC INTERFACE ON WINDOWS
-----------------------------------------------------------------

GENERAL INFORMATION ON GENERATING TRACE/LOG FILES

When tracing the problematic behavior of an application, you want to minimize
the size of the trace/log file in order to reduce the time it takes to analyze
the trace/log file and pull out the meaningful information.  The general rule
of thumb is to follow these five simple steps:

    (a) determine the actions to take to consistently reproduce the problem
    (b) when ready to start, turn tracing/logging on
    (c) immediately demonstrate the problem by taking the least number of
        application interactions that ensure the problem is reproduced
    (d) as soon as the problem has been reproduced immediately turn
        tracing/logging off
    (e) verify that the trace/log files were generated successfully and zip
        the files up before uploading to support

One final IMPORTANT NOTE:

    If you are not generating trace files after setting the proper trace
    file parameters this is most likely due to not restarting the the process
    the application is running as.  Hence, the process was not able to read
    these changes and is still running under the old trace parameter settings
    or no trace parameters at all.  To resolve this you must restart that
    process which norammly means restarting the application.  If your
    application is running in a web environment you will need to restart the
    web server process in order for the trace parameter changes to be
    recognized by your web application.  If after doing this you still are not
    generating trace files, restart the machine.

 
1. ODBC DRIVER

   The following information applies to the Oracle ODBC Driver and any
   third-party ODBC driver (such as the Microsoft ODBC Driver for Oracle)
   that interfaces with Oracle through its native API, Oracle Call Interface
   (OCI), which comes as part of the Oracle client software installation.


   ARCHITECTURE

                    Front-End/Middle-Tier Application
                                   ||
                      Microsoft ODBC Administrator
                                   ||
                              [ODBC Trace]
                                   ||
                               ODBC Driver
                                   ||
                          Oracle Client Software
                                   ||
                             [SQL*Net Trace]                     Client
               --------------------||------------------------------------
                             Oracle Database                     Server


   TRACING

   As you can see from the depiction above, there are two types of tracing that
   can be turned on:

             o ODBC Tracing
             o SQL*Net Tracing

     An ODBC Trace provides trace information containing ODBC API calls made
     from the application layer before they are processed by the ODBC driver.
     It also traces the results of the calls sent back from the ODBC driver to
     the application.  An ODBC Trace will not capture the effects of any
     processing the ODBC driver performs when making native Oracle Call
     Interface (OCI) API calls to the client software layer.  In order to see
     any processing effects of the ODBC driver and the raw SQL statements that
     are being passed, you would need to look at a trace performed at a lower
     layer.  A SQL*Net trace should be able to provide this information.  It is
     recommended that ODBC and SQL*Net traces be performed simultaneously to
     better pin-point the layer where the problem is occurring.

       (See section 6 below for more information on SQL*Net tracing)

     NOTE: Wire Protocol ODBC drivers cannot be SQL*Net traced.  You should
           contact the third-party vendor for information on how to generate
           lower level network traces using their Wire Protocol ODBC driver.

   
   PERFORMING AN ODBC TRACE

   (1) Open the ODBC Administrator control panel.  It is located in your
       Control Panel folder (under the "Administrative Tools" folder on certain
       versions of Windows).

   (2) Choose the "Tracing" tab.  Note the location of the log file path or
       change it to where you want the trace file to be saved.  For example,
       setting the log file path to "C:\traces\odbc\odbctrace.log" would save
       a file named "odbctrace.log" into the folder "C:\traces\odbc".

   (3) Click on the "Start Tracing Now" button.  It's name should change to
       "Stop Tracing Now" indicating that tracing is now active.

   (4) To make sure these setting have taken affect, click the "Apply" and "OK"
       buttons, close the ODBC Administrator and reopen it.  If the changes
       remain then tracing has been turned on successfully.  If the old settings
       remain, please repeat steps (1) through (4).

       NOTE: ODBC tracing may work intermittantly.  If this is the case you can
             try reinstalling Microsoft's Data Acces Components (MDAC) from
        
                  http://msdn.microsoft.com/library/default.asp?url=/downloads/list/dataaccess.asp

             and the reboot the computer.  Also, be sure you do not run the
             application you want to trace from a remote network share but
             rather locally on the computer where the Oracle client software
             is installed.  In the case of a remote network share, it is
             typical behavior to generate a 0 byte trace file that contains
             no data.  Please contact Microsoft if you require further
             assistance performing an ODBC Trace.
   
   (5) Immediately run the application that uses ODBC and generate the error.
       
   (6) Immediately go back to the ODBC Administator and click the "Stop Tracing
       Now" button.  It's name should change back to "Start Tracing Now".

   (7) To make sure these setting have taken affect, click the "Apply" and "OK"
       buttons, close the ODBC Administrator and reopen it.  If the changes
       remain then tracing has been turned off successfully.  If the old
       settings remain, please repeat steps (6) and (7).

   (8) Retrieve the ".log" file from its specified location.  If you did not
       specify a location, search your hard drive for all ".log" files.  Check
       the size of the ".log" file and make sure it does not have a size of
       0 bytes but does have the current time and date.  Zip up the file and
       upload it to support.


2. OLE DB PROVIDER

   The following information applies to the Oracle Provider for OLE DB that
   interfaces with Oracle through its native API, Oracle Call Interface (OCI),
   which comes as part of the Oracle client software installation.

   Please check with the vendor of any third-party OLE DB Provider (such as the
   Microsoft OLE DB Provider for Oracle) to see if they provide tracing for
   their provider.


   ARCHITECTURE

                    Front-End/Middle-Tier Application
                                   ||
                             OLE DB Provider
                                   ||
                             [OLE DB Trace]
                                   ||
                          Oracle Client Software
                                   ||
                             [SQL*Net Trace]                     Client
               --------------------||------------------------------------
                             Oracle Database                     Server


   TRACING

   As you can see from the depiction above, there are two types of tracing that
   can be turned on:

             o OLE DB Tracing
             o SQL*Net Tracing

     An OLE DB Trace provides trace information containing OLE DB calls
     made from the application layer sent to the OLE DB provider.  It also
     traces the results of the calls sent back from the OLE DB provider to
     the application.  An OLE DB Trace will not capture the effects of any
     processing the OLE DB provider performs when making native Oracle Call
     Interface (OCI) API calls to the client software layer.  In order to see
     any processing effects of the OLE DB provider and the raw SQL statements
     that are being passed, you would need to look at a trace performed at a
     lower layer.  A SQL*Net trace should be able to provide this information.
     It is recommended that OLE DB and SQL*Net traces be performed
     simultaneously to better pin-point the layer where the problem is
     occurring.

       (See section 6 below for more information on SQL*Net tracing)

   
   PERFORMING AN OLE DB TRACE

   (1) Open the Windows registry by choosing

         Start | Run, type REGEDIT

       and go to the following location:

         - if using Oracle9i or earlier

             HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\OLEDB

         - if using Oracle10g or later

             HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_<ORACLE_HOME>\OLEDB

       Modify the following registry keys to the values below to maximize the
       amount of trace information that will be logged:

         TraceFileName = C:\OraOLEDB.trc
         TraceCategory = 7
         TraceLevel = 95
         TraceOption = 1
       
       Tracing is now turned on.     

       NOTE: To reduce the amount of trace information taken, please see the
       following reference which goes into more detail on the values these
       parameters can be set to:

         Oracle Provider for OLE DB Developer's Guide
           Appendix A - Provider-Specific Information
              Section - OraOLEDB Tracing
   
   (2) Immediately run the application that uses OLE DB and generate the error.
       
   (3) Immediately go back into the registry and modify the following registry
       keys to turn tracing off:

         TraceCategory = 0
         TraceLevel = 0

   (4) Retrieve the ".trc" file from the location "C:\OraOLEDB.trc" or wherever
       you have saved the file(s) based on what was specified in the registry
       parameter "TraceFileName".  If you did not specify a location, search
       your hard drive for all ".trc" files.  Check the size of the ".trc" file
       and make sure it does not have a size of 0 bytes but does have the
       current time and date.  Zip up the file and upload it to support.


3. ORACLE OBJECTS FOR OLE (OO4O)

   The following information applies to Oracle Objects for OLE (OO4O) which 
   interfaces with Oracle through its native API, Oracle Call Interface (OCI),
   which comes as part of the Oracle client software installation.

   The specification of this programmatic interface is Oracle proprietary.
   There are no third-party vendors who write OO4O drivers.


   ARCHITECTURE

                    Front-End/Middle-Tier Application
                                   ||
                      Oracle Objects for OLE (OO4O)
                                   ||
                          Oracle Client Software
                                   ||
                             [SQL*Net Trace]                     Client
               --------------------||------------------------------------
                             Oracle Database                     Server


   TRACING

   As you can see from the depiction above, there is only one type of tracing
   that can be turned on:

             o SQL*Net Tracing

     Tracing has not been implemented at the driver level.  Therefore you are
     not able to trace any higher than the SQL*Net layer.  In order to see the
     raw SQL statements that are being passed to the database, you can take a
     SQL*Net trace.
 
       (See section 6 below for more information on SQL*Net tracing)

     NOTE:  Since driver level tracing does not exist, in addition to a SQL*Net
            trace, make sure you are using the appropriate error handling in
            you application to trap full details on the error being generated
            and pass this information on to support. 


4. ORACLE DATA PROVIDER FOR .NET (ODP.NET)

   The following information applies to the Oracle Data Provider for .NET
   (ODP.NET) that interfaces with Oracle through its native API, Oracle Call
   Interface (OCI), which comes as part of the Oracle client software
   installation.  This is a native data provider for the Microsoft .NET
   framework.


   ARCHITECTURE

                    Front-End/Middle-Tier Application
                                   ||
                 Oracle Data Provider for .NET (ODP.NET)
                                   ||
                             [ODP.NET Trace]
                                   ||
                          Oracle Client Software
                                   ||
                             [SQL*Net Trace]                     Client
               --------------------||------------------------------------
                             Oracle Database                     Server


   TRACING

   As you can see from the depiction above, there are two types of tracing that
   can be turned on:

             o ODP.NET Tracing
             o SQL*Net Tracing

     An ODP.NET Trace provides trace information containing the ODP.NET calls
     made from the application layer sent to the ODP.NET.  It also traces the
     results of the calls sent back from the ODP.NET to the application.  An
     ODP.NET Trace will not capture the effects of any processing the ODP.NET
     provider performs when making native Oracle Call Interface (OCI) API calls
     to the client software layer.  In order to see any processing effects of
     the ODP.NET and the raw SQL statements that are being passed, you would
     need to look at a trace performed at a lower layer.  A SQL*Net trace
     should be able to provide this information.  It is recommended that
     ODP.NET and SQL*Net traces be performed simultaneously to better pin-point
     the layer where the problem is occurring.

       (See section 6 below for more information on SQL*Net tracing)

   
   PERFORMING AN ODP.NET TRACE

   (1) Open the Windows registry by choosing

         Start | Run, type REGEDIT

       and go to the following location:

         - if using ODP.NET 9iR2 or earlier

             HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET

         - if using ODP.NET 10g PRIOR to version 10.2.0.2.20

             HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_<ORACLE_HOME>\ODP.NET

         - if using ODP.NET 10g version 10.2.0.2.20 or later

             HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ODP.NET\x.xxx.x.xx
             
             where x.xxx.x.xx is the version of the .NET Framwork the provider
             supports followed by the version of the ODP.NET, for example:

               1.102.2.20 (.NET Framework 1, using ODP.NET version 10.2.0.2.20)

               2.102.2.20 (.NET Framework 2, using ODP.NET version 10.2.0.2.20)

       Modify the following registry keys to the values below to maximize the
       amount of trace information that will be logged:

         TraceFileName = C:\ODPNET.trc
         TraceLevel = 63
         TraceOption = 1
       
       Tracing is now turned on.     

       NOTE: To reduce the amount of trace information taken, please see the
       following reference which goes into more detail on the values these
       parameters can be set to:

         Oracle Data Provider for .NET Developer's Guide
           Chapter 3 - Features of Oracle Data Provider for .NET
             Section - Debug Tracing
   
   (2) Immediately run the application that uses ODP.Net and generate the error.
       
   (3) Immediately go back into the registry and modify the following registry
       key to turn tracing off:

         TraceLevel = 0

   (4) Retrieve the ".trc" file from the location "C:\ODPNET.trc" or wherever
       you have saved the file(s) based on what was specified in the registry
       parameter "TraceFileName".  If you did not specify a location, search
       your hard drive for all ".trc" files.  Check the size of the ".trc" file
       and make sure it does not have a size of 0 bytes but does have the
       current time and date.  Zip up the file and upload it to support.

   NOTE: If you are receiving the following types of errors when using ODP.NET

         (a) an Oracle Data Provider for .Net internal error -3000 or

         (b) an exception with the message "System.NullReferenceException:
             Object reference not set to an instance of an object"

         Please consider taking additional mini-dumps to help further
         trouble-shoot the problem by referencing the following note:

           Note:299972.1 - Tracing Unmanaged Exceptions in
                           Oracle Data Provider for .NET


5. ORACLE SERVICE FOR MICROSOFT TRANSACTION SERVER (ORAMTS)

   The following information applies to the Oracle Service for Microsoft
   Transaction Server (ORAMTS) that interfaces with Oracle through its native
   API, Oracle Call Interface (OCI), and also interfaces with the Microsoft
   Transaction Server (MTS).  ORAMTS is a COM-based transaction processing
   service used to manage transactional COM components registered under
   Microsoft Transaction Server (MTS) interacting with the Oracle database.
   ORAMTS works with any of the following programmatic interfaces:

             1. Oracle ODBC Driver
             2. Oracle Provider for OLE DB
             3. Oracle Objects for OLE (OO4O)
             4. Oracle Data Provider for .NET (ODP.NET)


   ARCHITECTURE

                    Front-End/Middle-Tier Application
                                   ||
                    Microsoft Transaction Server (MTS) === ORAMTS
                                   ||                        ||
                               ODBC, OLEDB             [ORAMTS Trace]
                             OO4O or ODP.NET                 ||
                                   ||                        ||
                         Oracle Client Software ===============
                                   ||
                             [SQL*Net Trace]                     Client
               --------------------||------------------------------------
                             Oracle Database                     Server


   TRACING

   As you can see from the depiction above, there are two types of tracing that
   can be turned on:

             o ORAMTS Tracing
             o SQL*Net Tracing

     An ORAMTS Trace provides trace information containing the ORAMTS calls
     made between ORAMTS and MTS and the calls made between ORAMTS and the
     Oracle database.  All calls made between ORAMTS and the database travel
     over SQL*Net so the raw SQL statements can be obtained by performing a
     SQL*Net trace.  It is recommended that ORAMTS and SQL*Net traces be
     performed simultaneously to gather the most information when investigating
     the root cause of a problem.

       (See section 6 below for more information on SQL*Net tracing)

   
   PERFORMING AN ORAMTS TRACE OF THE ORAMTS.DLL (Oracle9i and Later)

   (1) Open the Windows registry by choosing

         Start | Run, type REGEDIT

       and go to the following location:

         HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEx

       Where 'x' signifies the Oracle Home you want to trace.  Modify the
       following registry keys to the values below to maximize the amount of
       trace information that will be logged:

         ORAMTS_CP_TRACE_DIR = ORACLE_BASE\ORACLE_HOME\oramts\Trace
         ORAMTS_CP_TRACE_LEVEL = 5

       ORACLE_BASE\ORACLE_HOME is the location of the Oracle Home folder where
       the ORAMTS software is located (i.e. D:\Oracle\Ora92).  The trace file
       will have the following name:

         ORAMTSCP<pid>.TRC

       Tracing is now turned on.     

       NOTE: To reduce the amount of trace information taken, please see the
       following reference which goes into more detail on the values these
       parameters can be set to:

         Oracle Services for Microsoft Transaction Server Developer's Guide
           Release 9.2 for Windows
             Chapter 7 - Troubleshooting Oracle Services for Microsoft
                         Transaction Server
               Section - Tracking Oracle Services for Microsoft Transaction
                         Server Performance
   
   (2) Immediately run the application that enlists in a distributed transaction
       with the Microsoft Transaction Server (MTS).
       
   (3) Immediately go back into the registry and modify the following registry
       key to turn tracing off:

         ORAMTS_CP_TRACE_LEVEL = 0

   (4) Retrieve the ".trc" file from the location

         "ORACLE_BASE\ORACLE_HOME\oramts\Trace"
  
       or wherever you have saved the file(s) based on what was specified in
       the registry parameter "ORAMTS_CP_TRACE_DIR".  If you did not specify a
       location, search your hard drive for all ".trc" files.  Check the size
       of the ".trc" file and make sure it does not have a size of 0 bytes but
       does have the current time and date.  Zip up the file and upload it to
       support.
   
   (5) The Oracle MTS Recovery Service is part of the new OraMTS Architecture
       in Oracle9i.  This service will automatically generate trace files in
       the ORACLE_BASE\ORACLE_HOME\oramts\trace directory.  It is recommended
       that you move the existing trace files into a backup folder such that
       this folder remains empty to easily identify new trace files that may
       be generated by the Oracle MTS Recovery Service while tracing the
       ORAMTS.DLL (steps 1-4 above).  Please include these trace files along
       with the trace files generated by the ORAMTS.DLL when uploading to
       support.  The trace files generated by the Oracle MTS Recovery Service
       can be identified by the following name:

         OracleMTSRecoveryService(<pid>).trc

       Where <pid> is the process ID the Oracle MTS Recovery Service was
       running as when the trace file was generated.


   NOTE: With Oracle Services for MTS 8.1.7 and earlier you can trace the
         ORAMTS service in addition to the ORAMTS.DLL.  Therefore, the
         following two sections explain how to trace each piece in this
         architecture.

   PERFORMING AN ORAMTS TRACE OF THE ORAMTS.DLL (Oracle8i and Earlier)

   (1) Open the Windows registry by choosing

         Start | Run, type REGEDIT

       and go to the following location:

         HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOMEx

       Where 'x' signifies the Oracle Home you want to trace.  Modify or create
       the following registry keys with these values to maximize the amount of
       trace information that will be logged.  If you have to create a new key,
       create one as a new "string value" (REG_SZ):

         ORAMTS_CP_TRACE_LEVEL = 4
         ORAMTS_RPC_TRACE_LEVEL = 4

       A trace file will be generated in the working directory of the process
       that is using ORAMTS.DLL.  For example, if you are running a COM+
       Component on Windows 2000 you would find the trace files in the
       "C:\WINNT\system32" folder with the following names:

         ORAMTSCP<pid>.TRC 
         ORAMTSRPC<pid>.TRC
 
       Tracing is now turned on.     

       NOTE: To reduce the amount of trace information taken, please see the
       following reference which goes into more detail on the values these
       parameters can be set to:

         Using Microsoft Transaction Server with Oracle8i
           Chapter 7 - Troubleshooting
             Section - Using Trace File
   
   (2) Immediately run the application that enlists in a distributed transaction
       with the Microsoft Transaction Server (MTS).
       
   (3) Immediately go back into the registry and modify or remove the following
       registry keys to turn tracing off:

         ORAMTS_CP_TRACE_LEVEL = 0
         ORAMTS_RPC_TRACE_LEVEL = 0

   (4) Retrieve the ".trc" files from the working directory of the process
       that was using ORAMTS.DLL.  If you are not sure where the trace files
       were saved, search your hard drive for all ".trc" files with these
       particular names.  Check the size of the ".trc" files and make sure they
       do not have a size of 0 bytes but do have the current time and date.
       Zip up the files and upload them to support.


   PERFORMING AN ORAMTS TRACE OF THE ORACLE SERVICE FOR MTS (Oracle8i and Earlier)

   (1) Open the Oracle Manager for Microsoft Transaction Server

         Start | Programs | Oracle - OraHome81 | Application Development |
           Oracle Manager for Micrsoft Transaction Server

       The Microsoft Management Console (MMC) will load the Oracle Manager for
       MTS called "orammcmts8US".  Expand the Tree until you find the Oracle
       Service you want to trace:

         Oracle Manager for MTS
         |
         = Oracle Managed Objects
           |
           = Computers
             |
             = <Computer Name>
               |
               = Oracle Manager For MTS Services
                 |
                 = orcl(OraHome81)

       Right-click the name of the Oracle Service (i.e. orcl(OraHome81)) and
       choose Properties.  Click the "Advanced" tab.  In the "Tracing" section
       set the "Trace Level" and "Trace Filename" to the values below to
       maximize the amount of trace information that will be logged:

         Trace Level    = LEVEL4
         Trace Filename = OracleMTSService0.trc

       By not specifying the PATH along with the "Trace Filename" the file will
       be created in the ORACLE_BASE\ORACLE_HOME\oramts\TRACE folder.
       ORACLE_BASE\ORACLE_HOME is the location of the Oracle Home folder where
       the ORAMTS software is located (i.e. D:\Oracle\Ora81).  If you want the
       trace file to be saved in a different location than the default, specify
       the full PATH of the trace file in the "Trace Filename" box:

           "C:\trace\oramts\OracleMTSService0.trc"
      
       Click "Ok" to close the properties box.

       Tracing is now turned on.     

   (2) Immediately run the application that enlists in a distributed transaction
       with the Microsoft Transaction Server (MTS).
       
   (3) Immediately go back into the Properties of the Oracle Service for MTS.
       Click the "Advanced" tab set the "Trace Level" to "NOTRACE" to turn
       tracing off.

   (4) Retrieve the ".trc" file from the location

           "ORACLE_BASE\ORACLE_HOME\oramts\TRACE"
  
       or wherever you have saved the file(s) based on what was specified in
       the "Trace Filename" box.  Check the size of the ".trc" file and make
       sure it does not have a size of 0 bytes but does have the current time 
       and date.  Zip up the file and upload it to support.

    
6. CLIENT-SIDE SQL*NET TRACING

   The following information applies to performing client-side SQL*Net tracing.
   This type of tracing will log network traffic between the Oracle client
   software and the Oracle database.  This type of tracing can be used with all
   five of the programmatic interfaces:

             1. ODBC Driver
             2. OLE DB Provider
             3. Oracle Objects for OLE (OO4O)
             4. Oracle Data Provider for .Net (ODP.Net)
             5. Oracle Service for Microsoft Transaction Server (ORAMTS)


   ARCHITECTURE

                    Front-End/Middle-Tier Application
                                   ||
                          Programmatic Interface
                                   ||
                          Oracle Client Software
                                   ||
                             [SQL*Net Trace]                     Client
               --------------------||------------------------------------
                             Oracle Database                     Server


   TRACING

     NOTE: Wire Protocol ODBC drivers cannot be SQL*Net traced.  You should
           contact the third-party vendor for information on how to generate
           lower-level network traces using their Wire Protocol ODBC driver.

   
   PERFORMING A CLIENT-SIDE SQL*NET TRACE

   (1) Open the SQLNET.ORA file typically found in the following location:

          ORACLE_BASE\ORACLE_HOME\Network\Admin  (7.3.x, 8.1.x or later)
          ORACLE_BASE\ORACLE_HOME\Net80\Admin    (8.0.x)
 
   (2) Add the following parameters at the end of the file:

          #CLIENT-SIDE SQL*NET TRACE PARAMETERS
          #====================================
          TRACE_UNIQUE_CLIENT = ON
          TRACE_LEVEL_CLIENT = 16
          TRACE_DIRECTORY_CLIENT = C:\temp
          TRACE_FILE_CLIENT = SQLNetTrace
          TRACE_TIMESTAMP_CLIENT = ON
          #DIAG_ADR_ENABLED = OFF
          #TRACE_FILELEN_CLIENT = 2048
          #TRACE_FILENO_CLIENT = 2

       Parameters prefixed with a "#" are interpreted as comments and will not
       affect tracing.

       If you want to change the location where the trace file will be saved
       modify the following two parameters:

         (a) TRACE_DIRECTORY_CLIENT

       Set this parameter to the folder where you want the trace file to be
       saved.

       WARNING: Do not end the path with a "\".  This is not necessary and may
                prevent the trace file from being generated.

         (b) TRACE_FILE_CLIENT

       Set this parameter to the filename of the trace file that will be
       created.  The actual trace filename may contain additional information
       appended to the end of the filename such as

         SQLNetTrace_<pid>_<#>.trc

       where <pid> is the application's process id while it was running and
       <#> distinguishes different client connections established from the
       same process id (or session) to the database.  This occurs since the
       parameter TRACE_UNIQUE_CLIENT is set to ON.

       The parameter TRACE_LEVEL_CLIENT=16 ensures that maximum trace
       information is logged.

         (c) DIAG_ADR_ENABLED = OFF
            
       You ONLY need to uncomment this parameter if your Oracle Client software
       is 11g or later.  If using 11g or later Oracle Client software set this
       parameter to OFF by uncommenting it.  It must be set to OFF to disable
       the Automatic Diagnostic Repository (ADR) feature of Oracle's networking
       layer but at the same time enables SQL*Net tracing.

         (d) Cyclical Tracing

       If you think the size of the trace file will exceed the free space on 
       the drive where TRACE_DIRECTORY_CLIENT is saving the file, then
       uncomment the last two parameters which will have the following affect:
             
         TRACE_FILELEN_CLIENT - will create trace files of the size specified
                                in kilobytes (KB)

         TRACE_FILENO_CLIENT  - will write to multiple trace files up to the 
                                maximum size specified by TRACE_FILELEN_CLIENT
                                and then cycle through the files again
                                overwriting previously written trace information

       The default values specified above for these two paramters will cycle
       the trace output between two trace files with a maximum size of 2MB each.

       NOTE: If the application you are tracing is running from inside a web
             server environment, such as Microsoft's Internet Information
             Server (IIS), you will need to RESTART THE WEB SERVER process
             at this point in order for these changes to be seen by the
             web server.  This is due to the fact that web servers will cache
             the SQLNET.ORA file for performance.

   (3) Immediately run the application that uses one of the programmatic
       interfaces and generate the error.

   (4) Immediately go back to the SQLNET.ORA file and turn tracing off by using
       any of the following methods:
       
         (a) Modify the following parameter:

               TRACE_LEVEL_CLIENT = OFF
 
         (b) Remove all the tracing parameters you added to this file or
 
         (c) Comment all the tracing parameters out by placing the "#"
             character before each one.

       Save the changes to the SQLNET.ORA file.

   (5) Retrieve the ".trc" file(s) from the location "C:\temp\SQLNetTrace\" or
       wherever you have saved the file(s) based on what was specified in the
       parameters TRACE_DIRECTORY_CLIENT and TRACE_FILE_CLIENT.  If you did not
       specify a location please check the location of the directory where the
       application was run from or search your hard drive for all ".trc" files.
       Check the size of the ".trc" file(s) and make sure they do not have a
       size of 0 bytes but do have the current time and date.  Zip the file up
       and upload it to support.


RELATED DOCUMENTS
-----------------

  o Oracle Provider for OLE DB Developer's Guide
      Appendix A - Provider-Specific Information
        Section - OraOLEDB Tracing

  o  Oracle Data Provider for .NET Developer's Guide
      Chapter 3 - Features of Oracle Data Provider for .NET
        Section - Debug Tracing

  o  Note:299972.1 - Tracing Unmanaged Exceptions in Oracle Data Provider
                     for .NET

  o  Oracle Services for Microsoft Transaction Server Developer's Guide
     Release 9.2 for Windows
        Chapter 7 - Troubleshooting Oracle Services for Microsoft Transaction
                    Server
          Section - Tracking Oracle Services for Microsoft Transaction Server
                    Performance

  o  Using Microsoft Transaction Server with Oracle8i
       Chapter 7 - Troubleshooting
         Section - Using Trace File

  o  Oracle9i Net Services
       Chapter 6 - Profile Parameters (sqlnet.ora)

  o  Net8 Administrators Guide
       Chapter 12 - Troubleshooting
          Section - Trace Error Information
            Sub-Section - Understanding and Setting Trace Parameters

 Related



--------------------------------------------------------------------------------
Products 
--------------------------------------------------------------------------------

•Oracle Database Products > Oracle Database > Platform specific utilities > Oracle Data Provider for .NET 
•Oracle Database Products > Oracle Database > Platform specific utilities > Oracle Services for Microsoft Transaction Server 
•Oracle Database Products > Oracle Database > Platform specific utilities > Oracle Objects for OLE 
•Oracle Database Products > Oracle Database > Platform specific utilities > Oracle Provider for OLE DB 
•Oracle Database Products > Oracle Database > Platform specific utilities > Oracle ODBC Driver 
•Oracle Database Products > Oracle Database > Platform specific utilities > Oracle ODBC Driver 
•Oracle WebServer ODBC Cartridge 
Keywords 
--------------------------------------------------------------------------------
MTS; ODBC; ODP; OLEDB; OO4O; ORAMTS; TRACE 


 
Back to top